1 2 Previous Next 15 Replies Latest reply on Apr 8, 2015 6:33 PM by Jonathan Lewis

    Replace the NOT EXISTS clause

    user5123346

      Hi All,

       

      I am using Oracle 10g.

       

      I have a following query and want to get rid of the "NOT EXISTS' clause without changing the end results.

      SELECT   A.c,

               A.d,

               A.e,

               A.f

        FROM   A

      WHERE   NOT EXISTS (SELECT   1

                             FROM   B

                            WHERE   B.c = A.c AND B.d = A.d AND B.e = A.e);

       

       

       

      Thanks,

        • 1. Re: Replace the NOT EXISTS clause
          Frank Kulash

          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.

          • 2. Re: Replace the NOT EXISTS clause
            Karthick2003

            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 !!

            • 3. Re: Replace the NOT EXISTS clause
              Karthick2003

              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?

              • 4. Re: Replace the NOT EXISTS clause
                Frank Kulash

                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.

                • 5. Re: Replace the NOT EXISTS clause
                  user5123346

                  Thanks Frank and Karthik for the reply.

                   

                  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

                  • 6. Re: Replace the NOT EXISTS clause
                    Frank Kulash

                    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.

                    • 7. Re: Replace the NOT EXISTS clause
                      Geert Gruwez

                      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

                      • 8. Re: Replace the NOT EXISTS clause
                        Jonathan Lewis

                        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

                        • 9. Re: Replace the NOT EXISTS clause
                          user5123346

                          Hi Jonathan,

                           

                          Thanks for the reply. I have attached the execution plan. Apology for not supplying the execution plan earlier.

                          Original query-

                           

                          "

                          SELECT /*+ index(uil,pk_uda_item_lov) */

                                                     ril.item,

                                                        ril.location,

                                                        ffswumt.default_wh swi_dflt_wh,

                                                        ffswumt.source_wh swi_src_wh,

                                                        ril.source_wh ril_src_wh,

                                                        ril.wh_lead_time,

                                                        ffswumt.default_wh_ship_date,

                                                        ffswumt.source_wh_ship_date,

                                                        ffswumt.src_to_deflt_wh_lead_time,

                                                        ROWIDTOCHAR (ril.ROWID) rwid

                                                 FROM   repl_item_loc ril,

                                                        str_wh_uda_matrix_temp ffswumt,

                                                        uda_item_lov uil

                                                WHERE   ril.item = uil.item

                                                        AND (ffswumt.source_wh_ship_date =

                                                                TO_DATE (:ls_vdate, 'YYYYMMDD')

                                                                + :li_review_days

                                                             OR ffswumt.default_wh_ship_date =

                                                                  TO_DATE (:ls_vdate, 'YYYYMMDD')

                                                                  + :li_review_days)

                                                        AND ffswumt.uda_id = uil.uda_id

                                                        AND ffswumt.uda_value = uil.uda_value

                                                        AND ril.stock_cat = 'W'

                                                        AND ffswumt.store = ril.location

                                                        AND ffswumt.source_wh != ril.source_wh

                                                        AND NOT EXISTS

                                                              (SELECT   1

                                                                 FROM   wh_uda_matrix ffwum

                                                                WHERE   ffwum.uda_id = ffswumt.uda_id

                                                                        AND ffwum.uda_value =

                                                                              ffswumt.uda_value

                                                                        AND ffwum.source_wh =

                                                                              ril.source_wh);"

                           

                          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,


                          • 10. Re: Replace the NOT EXISTS clause
                            Hoek

                            What happens when you remove the index hint and run the query? Are you sure you need that hint here?

                            Or is it to avoid a full table scan (which may perform way faster than indexed reads)?

                             

                            There's no execution plan attached.  <- obsolete

                            Ideally you want to post the results of DBMS_XPLAN.DISPLAY_CURSOR, see below links to articles for step-by-step instructions:

                            Oracle related stuff: Basic SQL statement performance diagnosis - HOW TO, step by step instructions

                            About Oracle: dbms_xplan.display_cursor

                            1 person found this helpful
                            • 11. Re: Replace the NOT EXISTS clause
                              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

                              • 12. Re: Replace the NOT EXISTS clause
                                user5123346

                                Hi Hoek,

                                 

                                Thanks for the link. That was very useful. I have attached all the diag1.log (for explan plan and autrace) and trace_output.txt.

                                 

                                Please have a look.

                                • 13. Re: Replace the NOT EXISTS clause
                                  Hoek

                                  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.

                                  • 14. Re: Replace the NOT EXISTS clause
                                    user5123346

                                    Hi Jonathan,

                                     

                                    Thanks for replying and for the suggestion

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

                                    Ans: It returns around 1 million records ( it can goes maximum to 5 million)

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

                                    Ans: 5 min

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

                                    I do not see any problem in the execution plan. only the anti join is missing.

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

                                    Ans: All depend upon the data setup in the inner query. It can be 0 to 5 million

                                     

                                    I tried with no_merge as well, no impact. Can you please have  a look on the execution plan and trace file attached.

                                     

                                    Thanks,

                                    1 2 Previous Next