Oracle Query Taking Time

Question

I am running this oracle query , But its taking lot of time, I have also tried indexing, but still taking lot of time. Can the below Query can be tuned or can be write in other way to execute fast.

select * 
from table1  
where (col_1 like '8%' OR col_1 like '9%') 
    and col_1 not in (select col_11 
                        from table2 
                        where id =2 
                            and (col_11 like '9%' OR col_11 like '8%'))

Please run an explain plan and post the results. – OldProgrammer Apr 11 '18 at 12:26 — OldProgrammer, Apr 11 '18 at 12:26

score 2 · Answer 1 · answered Apr 11 '18 at 12:31

2

This is slow

where field not in (subquery)

This is less intuitive, but faster

where field in (select all the values
minus
select the values you don't want)

Other variations include

where not exists(subquery)

or

from table1 left join table2 on table1.field = table2.field
where table2.field is null

answered Apr 11 '18 at 12:31

Dan Bracuk

20,699
4
26
43

1

`in` and `not in` are slow in oracle. Your second example with `join` should work better – Ludovic Feltz Apr 11 '18 at 12:34
can u please give the query with conditions, mine is getting error – Suyash Gupta Apr 11 '18 at 12:39
1

This is not a spoon feeding site. You have to sort out your own errors, just like I have to sort out mine. – Dan Bracuk Apr 11 '18 at 13:23

score 1 · Answer 2 · answered Apr 11 '18 at 12:40

I would suggest writing the query as:

select t1.* 
from table1 t1
where (col_1 like '8%' or col_1 like '9%') and
      not exists (select 1
                  from table2 t2
                  where t2.col_11 = t1.col1 and t2.id = 2
                 );

The condition in the subquery on (t2.col_11 like '9%' OR t2.col_11 like '8%') is already handled by t2.col_11 = t1.col1 and the outer condition.

Then for this query, you want an index on table2(col_11, id).

You can try an index on table1(col_1). However, this is probably not selective enough to improve query performance.

Oracle Query Taking Time

2 Answers2