OR CONDITION CAUSING FTS

816802

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.

mathguy

Not really.

In simple cases, the optimizer can rewrite conditions with OR in such a way that the result is equivalent to the original one, yet the rewritten version does not use OR. In more complicated cases you can do that yourself - the optimizer is not smart enough to do it for you. There is no optimizer hint to tell the optimizer to be smarter than it is.

(Incidentally, this transformation is known as "or expansion", if you want to Google it to read more about it.)

In the most general case, if you have a query like

select ...  where (A) or (B)

(or a query with a join where the join condition looks like that; note that A and/or B can be any conditions, including compound conditions as you have)

you can rewrite it as

select ... where (A)
union all
select ... where (B) and not (A)   --   (the SELECT is otherwise identical to the first one)

First read this a few times and think about it until you understand it. (For example, "understand it" means, in particular, understanding why you need ...and not (A)... in the second member of UNION ALL.)

Of course, it may be simpler - depending on what (A) and (B) are - to write it as

select ... where (B)
union all
select ... where (A) and not (B)

See if this is enough for you to fill in the details. Please write back if you need more help.

816802

Thanks for the explanation.

Jonathan Lewis

The plan you're showing for the original query is extremely surprising. With a prediction of 15 tablescans of table_b that will each cost 1.376M it's very strange that the optimizer hasn't chosen a hash join. (I also note that for no apparent reason the optimizer has also decided that the 15 rows from table_a will now be 70 rows from table_a when you try to rewrite the query - so you don't seem to be giving us a complete picture of what you're doing.

@Mathguy has told you about OR EXPANSION - you could try adding the hint /*+ or_expand(@sel$1) to the query - better still add a query block name as well in case the bit of query you're shoing us isn't the whole thing, e.g. /*+ qb_name(nasty_bit) */ then use the directed hint /*+ or_expand(@nasty_bit) */ at the top-level select (if it is a select -- if the whole thing is DML such as insert as select I think or-expansion may not work at present).

Your rewrite is not logically equivalent to the original, you need to do a lot more to make it equivalent (even after adding the bit that says the "not(A)" you've been advised about by Mathguy).

Regards

Jonathan Lewis