Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

OR CONDITION CAUSING FTS

816802Dec 3 2020

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.

Comments

4297041
Hello,

If you review your error log what does it state? It might be good to clear it (Tools > Clear Error Log), reproduce the issue, and then review the error log (Tools > View Error Log).
The root cause of the issue will be just before the "Automation Error" notification as FDM write issues in order from the top down.

Thank you,
JeffJon
Is this a x64 server? Specified Cast is invalid indicates that the adpater was not registered properly. If it is a x64 server the adapter needs to be registered manually.

Follow document 1073992.1 to resolve the issue.
Jeo123
Try going into the mapping section and browsing for target value. 9 times out of 10 an automation error seems to be related to an inability to connect to the target system. The most common cause in my experience relates to entereing a Domain on the login page when one isn't required, but there are other causes as well.
1 - 3

Post Details

Added on Dec 3 2020
3 comments
349 views