global indexes are made unusable when you drop or move a partition. This is because there are entries pointing to physical addresses in your partition, that are no longer valid. If you can, try to avoid global indexes. You can do this by making them local.
create index idx_fk_opt_run_case on opt_run (case_id) local;
Should do the trick.
If you can use reference partitioning, you don't have to add the redundant data in the table because the relation is already defined in the partition definition.
an example:
RONR SQL>create table parent
(id number not null
, x varchar2(10)
)
partition by list (id) (
partition par_1 values (1),
partition par_2 values (2)
); 2 3 4 5 6 7 8
Table created.
RONR SQL>create unique index pk_parent
on parent (id) local;
2
Index created.
RONR SQL>alter table parent
add CONSTRAINT pk_parent
PRIMARY KEY (id)
USING INDEX; 2 3 4
Table altered.
RONR SQL>create table child
( parent number not null
, y varchar2(10)
,
constraint fk_p_c
foreign key (parent) references parent(id))
partition by reference (fk_p_c); 2 3 4 5 6 7
Table created.
RONR SQL>insert into parent (id, x) values (1,'boe');
insert into parent (id, x) values (2,'oeps');
1 row created.
RONR SQL>
1 row created.
RONR SQL>select * from parent;
ID X
---------- ----------
1 boe
2 oeps
RONR SQL>insert into child (parent, y) values (1,'ggg');
1 row created.
RONR SQL>insert into child (parent, y) values (1,'ggg');
1 row created.
RONR SQL>insert into child (parent, y) values (2,'ppp');
1 row created.
RONR SQL>select * from child;
PARENT Y
---------- ----------
1 ggg
1 ggg
2 ppp
RONR SQL>select table_name, partition_name from user_tab_partitions;
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
PARENT PAR_1
PARENT PAR_2
CHILD PAR_1
CHILD PAR_2
RONR SQL>select index_name, partition_name, status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
PK_PARENT PAR_1 USABLE
PK_PARENT PAR_2 USABLE
RONR SQL>alter table parent drop partition par_1;
Table altered.
RONR SQL>select * from parent;
ID X
---------- ----------
2 oeps
RONR SQL>select * from child;
PARENT Y
---------- ----------
2 ppp
RONR SQL>select table_name, partition_name from user_tab_partitions;
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
PARENT PAR_2
CHILD PAR_2
RONR SQL>select index_name, partition_name, status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
PK_PARENT PAR_2 USABLE
I see no unusable index.
What I do see is that I can drop a parent partition and this cascades to the child partition.
does this help?
Your new design will make it easier to query across all of the tables without the need to know which tables to union when you write a query.
I'm not sure if you need to worry about the partitioning aspect. It sounds OK, but I've never worked with something like this on this scale (not in MySQL).
If you are worried about the partitioning, you could stay with your current solution, but instead of a new table for every year+month, have 12 tables, one for each month, for all years. At least your unions would be constant.
Best Answer
What is the problem with the table becoming large? Generally, any sort of OLTP query will access the table using an appropriate index in which case the size of the table is more or less irrelevant. The cost of using an index will grow at an
O(log(n))rate-- practically, a b*-tree index will only add one or two levels for any realistically sized table. And you can potentially limit that further by using function-based indexes to limit the size of the index by doing things like only indexing the active rows.The only queries that should care about the size of the table are queries where you want to do a full scan on the table in order to do things like produce metrics about how many tickets have been opened since the beginning of time (or, at least, over a significant fraction of history). If you are concerned about those sorts of reporting queries, you can do things like use materialized views to pre-aggregate the data.
Normally, I would suggest keeping a single table and ensuring that appropriate indexes and/or materialized views exist to support the queries about whose performance you are concerned.