SQL query tuning (subquery)

Question

I have the below query which i want to tune as the query takes more time to run. Please help me out.

select  info_id 
from info_table 
where info_id not in (select info_id 
                      from some_table 
                      where info_id is not null) 
AND  rownum <= 1000 ;

The innerquery is returning millions of rows and hence the problem.

I would like to look at your explain plan. Don't forget to gather the tables' statistics before. — neshkeev
– neshkeev, Commented Aug 21, 2014 at 9:58

Srini V · Accepted Answer · 2014-08-21 09:30:10Z

1

select info_id
from info_table e
where not exists (select 'x' from some_table where info_id = e.info_id);

This will avoid the internal sort or merge and should be faster

You can even try outer join

select info_id
from info_table e LEFT OUTER JOIN some_table d
ON e.info_id= d.info_id
WHERE d.dept_no is not null;

answered Aug 21, 2014 at 9:30

Srini V

11.5k15 gold badges70 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

neshkeev Over a year ago

According to my experiments your queries have the same explain plan as the author of the question does.

neshkeev Over a year ago

Ok, see now (I rewrite you left join query) As I said, there is not any difference. Oracle Optimizer rewrites this query so they are the same for it. We have to add indexes or change the structure of the tables to get performance improvements.

neshkeev Over a year ago

Yes, I did, for me they last the same time. Of course we can take a look at AWR snapshots and compare numbers of latches and other locks but I don't think it matters now.

Prabhu Over a year ago

Hi Realsprituals, I tried with not exists and is functionally have conflict in the no of rows returned. Pls help me

neshkeev Over a year ago

@realspirituals You might have had some indexes on your tables, I don't have any, so it always performs full table scan.

|

OTTA · Accepted Answer · 2014-08-21 09:32:18Z

0

SELECT info_id
  FROM info_table
  LEFT OUTER JOIN some_table ON info_table.info_id = some_table.info_id
 WHERE some_table.info_id IS NULL

answered Aug 21, 2014 at 9:32

OTTA

1,0797 silver badges8 bronze badges

Collectives™ on Stack Overflow

SQL query tuning (subquery)

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related