optimizing nested select sql query

Question

I was wondering what is the best performance implementation of this query:

select subscriber 
from DEGREE
where sub_type is null 
 and (subscriber like '91_9%' 
      or SUBSCRIBER like '919%' 
      or (SUBSCRIBER not in (select msisdn from MCI)))

FYI MCI table has around 50m records with defined index on msisdn and DEGREE table has index on subscriber.

Best regards.

How many rows does table DEGREE have? And how many of those have sub_type NULL and how many rows does the query return? — Rob van Wijk
– Rob van Wijk, Commented Apr 15, 2014 at 12:48
Table degree has around 2 m records. around 1m of them have sub_typ= null and the query should return around 700k records. — Ali
– Ali, Commented Apr 15, 2014 at 12:58

Gordon Linoff · Accepted Answer · 2014-04-15 21:31:47Z

3

I would suggest writing the query like this:

select d.subscriber 
from DEGREE d
where d.sub_type is null and
      (d.subscriber like '91_9%' or
       d.subscriber like '919%' or
       not exists (select 1 from MCI where MCI.msisdn = d.subscriber)
      );

Then create indexes on degree(sub_type, subscriber) and MCI(msisdn).

EDIT:

The index on degree should cause the following to happen. Instead of a full table scan on DEGREE, the query will do an index scan on the index. I'm not sure if Oracle is smart enough to use the index for like conditions connected by or, but at least this is a covering index for the query.

The index on MCI should then result in a simple index lookup.

So, with these two indexes, only index operations will be needed to satisfy the query. That should have a significant impact on performance.

edited Apr 15, 2014 at 21:31

answered Apr 15, 2014 at 13:07

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

8 Comments

Rob van Wijk Over a year ago

This rewrite is possible. But can you explain why he should rewrite his query and why the indexes will help him?

deroby Over a year ago

@RobvanWijk Unless the query optimizer is smart enough to do it for you, a WHERE NOT EXISTS() is going to outperform a NOT IN (). Also, it simply is easier to read IMHO.

Gordon Linoff Over a year ago

@RobvanWijk . . . I added in an explanation.

Rob van Wijk Over a year ago

@deroby if that's always the case, why would Oracle have added the NOT IN clause to its vocabulary?

Rob van Wijk Over a year ago

@GordonLinoff Thanks for adding the explanation. Selecting 1M rows out of a 2M row table isn't likely going to benefit from an index scan. Hopefully the optimizer will prevent using this index. The second index on MCI likely helps if the table contains more than 1 column.

|

StanislavL · Accepted Answer · 2014-04-15 12:42:53Z

2

select subscriber 
from DEGREE left outer join MCI on DEGREE.SUBSCRIBER=MCI.msisdn 
where sub_type is null 
 and (subscriber like '91_9%' 
      or SUBSCRIBER like '919%' 
      or MCI.msisdn is null)

answered Apr 15, 2014 at 12:42

StanislavL

57.5k9 gold badges75 silver badges100 bronze badges

6 Comments

Ali Over a year ago

It works but i dont get what is the role of MCI.msisdn is null?

StanislavL Over a year ago

LEFT OUTER JOIN means we need all rows from DEGREE table and matching rows in the joined one. If no match found null is set for all columns of the second table. Thus MCI.msisdn is null means skip rows without matching in MCI.

Ali Over a year ago

Are you sure it return right answer? left outer join would return rows from degree that are match condition in MCI table. But I want rows that are not match in MCI tables.

StanislavL Over a year ago

You can run both queries and compare results

Ali Over a year ago

same result but Gordon answer was much faster (around 6 times faster)

|

Collectives™ on Stack Overflow

optimizing nested select sql query

2 Answers 2

8 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related