Hi
I am using oracle 12c database and trying to look at indexes in order to speed up an update statement.
Reading up on indexes they suggest the columns in WHERE clauses but want to ensure I get the right index created.
What would be a good index to create in order to speed up the following statement..
Update statement...
UPDATE schema1.tab1 t SET new_rec='Y' WHERE (MA_CDE,MA_NO,MR_CDE,DAT) NOT IN (SELECT MA_CDE,MA_NO,MR_CDE,DAT FROM schema2.tab2 a WHERE a.period = 202112) AND (MA_CDE,MA_NO,MR_CDE,DAT) NOT IN (SELECT MA_CDE,MA_NO,MR_CDE,DAT FROM schema2.tab1 a WHERE a.period = 202112);
Current indexes...
CREATE INDEX schema2.tab2_IDX ON schema2.tab2 (MA_CDE, MA_NO, MR_CDE, DAT, STATE, PERIOD);
CREATE INDEX schema2.tab1_IDX ON schema2.tab1(MAC_CDE, MA_NO, MR_CDE, DAT, STATE, PERIOD);
CREATE INDEX schema1.tab1_IDX ON schema1.tab1(MA_CDE, MA_NO, MR_CDE, DAT, STATE, USER_ID, CHANGED_DATE);
CREATE INDEX schema1.tab2_IDX ON schema1.tab2(MA_CDE, MA_NO, MR_CDE, DAT, STATE, USER_ID, CHANGED_DATE);
Number of records in the tables in question....
schema1.tab1 - 1 million records
schema1.tab2 - 1.5 million records
schema2.tab1 - 187 million records
schema2.tab2 - 150 million records
Regards