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?
following the advice of a_horse_with_no_name,
Please show us the execution plan for the second query –
a_horse_with_no_name 4 hours ago
I have posted the relevenat parts of the execution plan... however I can't make heads or tails of what is wrong with it from that....
I followed Mat's advice and guess that lack of indexing is the issue. Because there is no indexing, it must be as Matt says, that this simple query is doing a full table scan, which could take longer than the few minutes I have been waiting for the query to complete...
Useful link about the concept of indexing: http://www.orafaq.com/node/1403
Is that column indexed? Otherwise you'll get a full table scan, which
can take a while if the table is big. – Mat 7 hours ago
select index_name, column_name from dba_ind_columns where index_owner
= 'THE_SCHEMA' and table_name = 'YOUR_TAB' order by index_name will get you all the indexed columns. Look at the explain plan to see what
it is doing (look into set autotrace or use a GUI that has that
feature, like sqldeveloper) – Mat 7 hours ago
Thanks for your help guys. Let me know if I might be wrong in that assumption, or if you might have additional insight a_horse_with_no_name....
Best Answer
You can do something like this :
You are using parameterized queries and not just a query build by string concatination in the frontend I hope.