Hi All,
I am having a query which is using an OR CONDITION and the columns in conditions are having indexes. But the table scan is going for FTS. I tried to apply hints but it still going for FTS. So I have to split the query into two parts and convert the same into UNION ALL.
Wondering is there a way we can force the optimizer to use two indexes on the same table when using OR conditions.
Oracle Version: 19c.
Table_a is having around 1000 records and Table_b is having 90 million records and because of this the query execution is taking more than 18 hours. But if I split the query it is taking under 2 seconds.
query
SELECT ttd.ROWID AS t_rowid, ttd.*, sdons.col_1, sdons.dtval AS sub_transaction_dt,
sdons.job_log_id AS sub_job_log_id, ROW_NUMBER () OVER (PARTITION BY ttd.ROWID ORDER BY sdons.job_log_id DESC) AS rnk
FROM Table_a ttd JOIN Table_b sdons ON (
(ttd.col_1 = sdons.col_1) OR ( UPPER (ttd.col_2) = sdons.col_2 AND ttd.col_3 = sdons.col_3)) ;
Plan
SELECT STATEMENT ALL_ROWSCost: 20,648,021 Bytes: 924 Cardinality: 3
5 WINDOW SORT Cost: 20,648,021 Bytes: 924 Cardinality: 3
4 NESTED LOOPS Cost: 20,648,020 Bytes: 924 Cardinality: 3
1 TABLE ACCESS FULL TABLE table_a Cost: 35 Bytes: 3,870 Cardinality: 15
3 PARTITION RANGE ALL Cost: 1,376,532 Bytes: 50 Cardinality: 1 Partition #: 4 Partitions accessed #1 - #1048575
2 TABLE ACCESS FULL TABLE table_b Cost: 1,376,532 Bytes: 50 Cardinality: 1 Partition #: 4 Partitions accessed #1 - #1048575
Now if I remove the OR Clause and split the query into two queries using UNION ALL, then the execution is taking under 1 to 2 seconds.
SELECT ttd.ROWID AS t_rowid, ttd.*, sdons.col_1, sdons.dtval AS sub_transaction_dt,
sdons.job_log_id AS sub_job_log_id, ROW_NUMBER () OVER (PARTITION BY ttd.ROWID ORDER BY sdons.job_log_id DESC) AS rnk
FROM Table_a ttd JOIN Table_b sdons ON (
(ttd.col_1 = sdons.col_1) )
UNION ALL SELECT ttd.ROWID AS t_rowid, ttd.*, sdons.col_1, sdons.dtval AS sub_transaction_dt,
sdons.job_log_id AS sub_job_log_id, ROW_NUMBER () OVER (PARTITION BY ttd.ROWID ORDER BY sdons.job_log_id DESC) AS rnk
FROM Table_a ttd JOIN Table_b sdons ON ( UPPER (ttd.col_2) = sdons.col_2 AND ttd.col_3 = sdons.col_3);
Explain plan:
Plan
SELECT STATEMENT ALL_ROWSCost: 3,541 Bytes: 100,366 Cardinality: 176
13 UNION-ALL
6 WINDOW SORT Cost: 456 Bytes: 49,875 Cardinality: 175
5 NESTED LOOPS Cost: 455 Bytes: 49,875 Cardinality: 175
3 NESTED LOOPS Cost: 455 Bytes: 49,875 Cardinality: 175
1 TABLE ACCESS FULL TABLE TABLE_A Cost: 35 Bytes: 18,060 Cardinality: 70
2 INDEX RANGE SCAN INDEX SPRINT_COMM.SPR_SUB_ACTIVITY_TRUEUP_INDX2 Cost: 3 Cardinality: 2
4 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE TABLE_B Cost: 6 Bytes: 54 Cardinality: 2 Partition #: 7 Partition access computed by row location
12 WINDOW SORT Cost: 3,085 Bytes: 308 Cardinality: 1
11 NESTED LOOPS Cost: 3,084 Bytes: 308 Cardinality: 1
9 NESTED LOOPS Cost: 3,084 Bytes: 308 Cardinality: 762
7 TABLE ACCESS FULL TABLE TABLE_A Cost: 35 Bytes: 196,596 Cardinality: 762
8 INDEX RANGE SCAN INDEX SPRINT_COMM.SPR_SUB_ACTIVITY_TRUEUP_INDX3 Cost: 3 Cardinality: 1
10 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE TABLE_B Cost: 4 Bytes: 50 Cardinality: 1 Partition #: 13 Partition access computed by row location
Wondering is there a hint that I can use to use both indexes when using OR condition.
Appreciate your response.
Thanks
MK.