Replace the NOT EXISTS clause

Hi,

You can usually use IN sub-queries to get the same results as EXISTS sub-queries:

For example:

SELECT  c, d, e, f

FROM    a

WHERE   (c, d, e)  NOT IN (

                              SELECT  c, d, e

                              FROM    b

                          )

;

An anti-join is another way, but it would probably be less efficient than either NOT IN or NOT EXISTS.

Frank Kulash wrote:

 

Hi,

 

You can usually use IN sub-queries to get the same results as EXISTS sub-queries:

For example:

SELECT  c, d, e, f

FROM    a

WHERE   (c, d, e)  NOT IN (

                              SELECT  c, d, e

                              FROM    b

                          )

;

NOT IN and NOT EXISTS are not the same. Beware of NULL's !!

You need to provide a explanation on why do you want to eliminate the use of NOT EXISTS? If its related to performance how did you narrowed down that the problem is with the NOT EXISTS clause?

Hi,

Karthick_Arp wrote:

...

Beware of NULL's !!

Good point! 

This is one example of why posting some sample data and expected results is so important.

Hi,

user5123346 wrote:

...

I am encountering the performance issue while using the not in and not exists , that is the reason I want to get rid of these.

...

For performance issues, see the Forum FAQ: Re: 3. How to  improve the performance of my query? / My query is running slow.

use a left join

SELECT   A.c,

         A.d,

         A.e,

         A.f

FROM   A left join B on B.c = A.c AND B.d = A.d AND B.e = A.e

where B.C is null and B.d is null and B.e is null

the ( + ) can behave odd with this

user5123346 wrote:

 

I am encountering the performance issue while using the not in and not exists , that is the reason I want to get rid of these.

Is there any other way to convert this into in - line view and do it.

 

Note: The table A has around 100 million records , however the table B has only 24,000 records

You haven't supplied an execution plan so there's no way for us to know how the optimizer has chosen to handle your query, so it's possible that there is a way to improve the performance of the code you have written. However, your requirement is to check FOR EVERY ROW IN A that a match does not exist in B, so your code HAS to access every row in A at some point - have you allowed for that volume of work when considering the performance of the query ?

If it were my problem I would first hope that the optimizer had transformed the query into a hash anti-join with B as the build table, minising the work to little more than a tablescan of A with a little CPU for each probe, so if the query runs with a filter subquery plan I'd want to find out why.

Regards

Jonathan Lewis

user5123346 wrote:

 

Hi Jonathan,

 

 

Here the repl_item_loc has - 100 millions record

wh_uda_matrix has only 70 records. Without exists clause it is fetcing records in 5 min but not returning any value with the not exists clause.

I have attached the explain plan of the query.

Thanks,

Have you done the following analysis:

a) How many rows are returned if you DON'T have the "not exists" subquery ?

b) How long does it take to return ALL the rows when you DON'T have the "not exists" subquery ?

c) What is the execution plan when you DON'T have the "not exists" subquery ?

d) How many rows do you expect to see returned when you include the "not exists" subquery.

Your execution plan shows a hash anti-join (right outer), which is probably the fastest way of handling the subquery - so you first need to check that the rest of the plan is the same as the select would be without the subquery. It's possible (especially in 10g) that the underlying plan changed because of a flaw in the optimizer's model for subquery processing; if that's the case then you could play all sorts of games with complicated hinting, or you could check what plan you get for code shaped like this:

select {list of columns}

from (

   select  /*+ no_merge */  ....original query

)

where not exists (

   ...

)

Regards

Jonathan Lewis

Thanks for providing more details.

Strange thing is: according to the trace output, your query runs in less than a sec and processes far less rows.

Your execution plan is showing something completely different.

Did you obtain the plan and trace output from the same environment?

Also, I suggest you followup on Jonathan's latest reply.