9 Replies Latest reply: Nov 25, 2010 7:42 AM by OraDBA02 RSS

    Subquery Unnesting issue

    OraDBA02
      select *from v$version;
      
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
      PL/SQL Release 11.1.0.7.0 - Production
      CORE    11.1.0.7.0      Production
      TNS for Linux: Version 11.1.0.7.0 - Production
      NLSRTL Version 11.1.0.7.0 - Production
      SQL
      DELETE FROM A 
          WHERE TR_STATUS IN ('C', 'R') 
           OR A.TAX_AUDIT_RECORD_ID IN ( SELECT B.TAX_AUDIT_RECORD_ID FROM B WHERE A.TAX_AUDIT_RECORD_ID = B.TAX_AUDIT_RECORD_ID);
      Current execution plan
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                            | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------
      |   0 | DELETE STATEMENT                     |                           |  5639K|   123M| 14156   (1)| 00:02:50 |       |       |        |      |            |
      |   1 |  PX COORDINATOR                      |                           |       |       |            |          |       |       |        |      |            |
      |   2 |   PX SEND QC (RANDOM)                | :TQ20002                  |  5639K|   123M| 14156   (1)| 00:02:50 |       |       |  Q2,02 | P->S | QC (RAND)  |
      |   3 |    INDEX MAINTENANCE                 | A                               |       |       |            |          |       |       |  Q2,02 | PCWP |            |
      |   4 |     PX RECEIVE                       |                           |  5639K|   123M| 14156   (1)| 00:02:50 |       |       |  Q2,02 | PCWP |            |
      |   5 |      PX SEND RANGE                   | :TQ20001                  |  5639K|   123M| 14156   (1)| 00:02:50 |       |       |  Q2,01 | P->P | RANGE      |
      |   6 |       DELETE                         | A                               |       |       |            |          |       |       |  Q2,01 | PCWP |            |
      |   7 |        BUFFER SORT                   |                           |       |       |            |          |       |       |  Q2,01 | PCWC |            |
      |   8 |         PX RECEIVE                   |                           |  5639K|   123M| 14156   (1)| 00:02:50 |       |       |  Q2,01 | PCWP |            |
      |   9 |          PX SEND HASH (BLOCK ADDRESS)| :TQ20000                  |  5639K|   123M| 14156   (1)| 00:02:50 |       |       |        | S->P | HASH (BLOCK|
      |* 10 |           FILTER                     |                           |       |       |            |          |       |       |        |      |            |
      |  11 |            PX COORDINATOR            |                           |       |       |            |          |       |       |        |      |            |
      |  12 |             PX SEND QC (RANDOM)      | :TQ10000                  |  5639K|   123M| 14156   (1)| 00:02:50 |       |       |  Q1,00 | P->S | QC (RAND)  |
      |  13 |              PX BLOCK ITERATOR       |                           |  5639K|   123M| 14156   (1)| 00:02:50 |     1 |    32 |  Q1,00 | PCWC |            |
      |  14 |               TABLE ACCESS FULL      | B                               |  5639K|   123M| 14156   (1)| 00:02:50 |     1 |    32 |  Q1,00 | PCWP |            |
      |  15 |            PARTITION RANGE ALL       |                           |     1 |    21 |     3   (0)| 00:00:01 |     1 |     8 |        |      |            |
      |* 16 |             INDEX RANGE SCAN         | PK_B                              |     1 |    21 |     3   (0)| 00:00:01 |     1 |     8 |        |      |            |
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
        10 - filter("A"."TR_STATUS"='C' OR "A"."TR_STATUS"='R' OR  EXISTS (SELECT 0 FROM "B"  WHERE
                    "B"."TAX_AUDIT_RECORD_ID"=:B1))
        16 - access("B"."TAX_AUDIT_RECORD_ID"=:B1)
      There is no relationship (pk/fk) between both of these tables. PK are being populated by an oracle sequence in both the tables. There are lot of matching ids in both the tables though (1Million).
      I dont know why Oracle is using FILTER instead of using join methods. I tried using USE_HASH, USE_NL, USE_SJ, but no luck.
      Join column (TAX_AUDIT_RECORD_ID) is NOT NULL in both table definition.

      I even tried UNNEST hint in sub-query, but again, i did not get any success.
        • 1. Re: Subquery Unnesting issue
          Herald ten Dam
          Hi,

          did you try to make two deletes of it? It is a OR so two deletes can do the same. The second one can be rewritten as a exists, will this help?
          DELETE FROM A 
          WHERE TR_STATUS IN ('C', 'R') 
          /
          DELETE FROM A 
          WHERE exists  ( SELECT 1 FROM B WHERE A.TAX_AUDIT_RECORD_ID = B.TAX_AUDIT_RECORD_ID)
          /
          Herald ten Dam
          http://htendam.wordpress.com
          • 2. Re: Subquery Unnesting issue
            OraDBA02
            I tried EXIST but again i am getting execution plan with FILTER.

            I can do two different deletes but want to avoid if it is possible.
            • 3. Re: Subquery Unnesting issue
              Jonathan Lewis
              At present Oracle cannot use concatenation to split a query with the "or subquery" construct into two pieces (see http://jonathanlewis.wordpress.com/2007/02/26/subquery-with-or/ ). The path may become available in a future release - but we can't know that.

              The only thing you can do at present is to rewrite the query (or update in your case) with manual concatenation - something like:
              DELETE FROM A 
              where rowid in (
                  select rowid 
                  from a 
                  where TR_STATUS IN ('C', 'R') 
                  union all
                  select rowid from a 
                  where exists ( 
                      SELECT null FROM B 
                      WHERE B.TAX_AUDIT_RECORD_ID = A.TAX_AUDIT_RECORD_ID
                  )
                  and lnnvl(TR_STATUS IN ('C', 'R'))
                  ;
              When you do something like this, of course, it's because you think that the optimizer will be able to do something efficient with both parts of the UNION ALL - e.g. unnesting the subquery and using it as a semijoin driver.

              Regards
              Jonathan Lewis
              • 4. Re: Subquery Unnesting issue
                OraDBA02
                Thanks very much Jonathan for your pointer. (I am regular reader of your blog and wondering how come i missed this one !)

                I am getting below error while using LNNVL function:
                explain plan for
                DELETE  FROM A tars where rowid in 
                     (select  rowid from A where TR_STATUS IN ('C', 'R')
                     union all
                     select rowid from A tars where exists 
                     (SELECT  null FROM B taxar WHERE B.TAX_AUDIT_RECORD_ID = A.TAX_AUDIT_RECORD_ID)
                     and lnnvl(A.TR_STATUS IN ('C', 'R')));
                
                ORA-13207: incorrect use of the [LNNVL] operator
                Would i expect same result(row count) with/without this function ?
                However, If i remove LNNVL,i get below plan :
                Plan hash value: 1196139345
                
                ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
                | Id  | Operation                                   | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
                ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
                |   0 | DELETE STATEMENT                            |                           |   513G|    21T|  1434K (77)| 04:46:49 |       |       |        |      |            |
                |   1 |  PX COORDINATOR                             |                           |       |       |            |          |       |       |        |      |            |
                |   2 |   PX SEND QC (RANDOM)                       | :TQ10006                  |   513G|    21T|  1434K (77)| 04:46:49 |       |       |  Q1,06 | P->S | QC (RAND)  |
                |   3 |    INDEX MAINTENANCE                        | A                                |       |       |            |          |       |  Q1,06 | PCWP  |      |            |
                |   4 |     PX RECEIVE                              |                           |   513G|    21T|  1434K (77)| 04:46:49 |       |       |  Q1,06 | PCWP |            |
                |   5 |      PX SEND RANGE                          | :TQ10005                  |   513G|    21T|  1434K (77)| 04:46:49 |       |       |  Q1,05 | P->P | RANGE      |
                |   6 |       DELETE                                | A                         |       |       |            |          |       |       |  Q1,05 | PCWP |            |
                |   7 |        PX RECEIVE                           |                           |   513G|    21T|  1434K (77)| 04:46:49 |       |       |  Q1,05 | PCWP |            |
                |   8 |         PX SEND HASH (BLOCK ADDRESS)        | :TQ10004                  |   513G|    21T|  1434K (77)| 04:46:49 |       |       |  Q1,04 | P->P | HASH (BLOCK|
                |*  9 |          HASH JOIN BUFFERED                 |                           |   513G|    21T|  1434K (77)| 04:46:49 |       |       |  Q1,04 | PCWP |            |
                |  10 |           PX RECEIVE                        |                           |  5069K|   159M| 17222   (1)| 00:03:27 |       |       |  Q1,04 | PCWP |            |
                |  11 |            PX SEND HASH                     | :TQ10002                  |  5069K|   159M| 17222   (1)| 00:03:27 |       |       |  Q1,02 | P->P | HASH       |
                |  12 |             PX BLOCK ITERATOR               |                           |  5069K|   159M| 17222   (1)| 00:03:27 |     1 |    32 |  Q1,02 | PCWC |            |
                |  13 |              TABLE ACCESS FULL              | A                         |  5069K|   159M| 17222   (1)| 00:03:27 |     1 |    32 |  Q1,02 | PCWP |            |
                |  14 |           PX RECEIVE                        |                           |    10M|   116M|   322K  (3)| 01:04:35 |       |       |  Q1,04 | PCWP |            |
                |  15 |            PX SEND HASH                     | :TQ10003                  |    10M|   116M|   322K  (3)| 01:04:35 |       |       |  Q1,03 | P->P | HASH       |
                |  16 |             VIEW                            | VW_NSO_1                  |    10M|   116M|   322K  (3)| 01:04:35 |       |       |  Q1,03 | PCWP |            |
                |  17 |              SORT UNIQUE                    |                           |    10M|   338M|   322K (95)| 01:04:35 |       |       |  Q1,03 | PCWP |            |
                |  18 |               PX RECEIVE                    |                           |       |       |            |          |       |       |  Q1,03 | PCWP |            |
                |  19 |                PX SEND HASH                 | :TQ10001                  |       |       |            |          |       |       |  Q1,01 | P->P | HASH       |
                |  20 |                 BUFFER SORT                 |                           |   513G|    21T|            |          |       |       |  Q1,01 | PCWP |            |
                |  21 |                  UNION-ALL                  |                           |       |       |            |          |       |       |  Q1,01 | PCWP |            |
                |  22 |                   PX BLOCK ITERATOR         |                           |  5069K|    67M| 17275   (1)| 00:03:28 |     1 |    32 |  Q1,01 | PCWC |            |
                |* 23 |                    TABLE ACCESS FULL        | A                         |  5069K|    67M| 17275   (1)| 00:03:28 |     1 |    32 |  Q1,01 | PCWP |            |
                |* 24 |                   HASH JOIN SEMI            |                           |  5069K|   270M|   305K  (4)| 01:01:08 |       |       |  Q1,01 | PCWP |            |
                |  25 |                    PX BLOCK ITERATOR        |                           |  5069K|   169M| 17275   (1)| 00:03:28 |     1 |    32 |  Q1,01 | PCWC |            |
                |* 26 |                     TABLE ACCESS FULL       | A                         |  5069K|   169M| 17275   (1)| 00:03:28 |     1 |    32 |  Q1,01 | PCWP |            |
                |  27 |                    BUFFER SORT              |                           |       |       |            |          |       |       |  Q1,01 | PCWC |            |
                |  28 |                     PX RECEIVE              |                           |   276M|  5547M|   287K  (4)| 00:57:34 |       |       |  Q1,01 | PCWP |            |
                |  29 |                      PX SEND BROADCAST LOCAL| :TQ10000                  |   276M|  5547M|   287K  (4)| 00:57:34 |       |       |  Q1,00 | P->P | BCST LOCAL |
                |  30 |                       PX BLOCK ITERATOR     |                           |   276M|  5547M|   287K  (4)| 00:57:34 |     1 |     8 |  Q1,00 | PCWC |            |
                |  31 |                        TABLE ACCESS FULL    | B                         |   276M|  5547M|   287K  (4)| 00:57:34 |     1 |     8 |  Q1,00 | PCWP |            |
                ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                
                   9 - access(ROWID="ROWID")
                  23 - filter("A"."TR_STATUS"='C' OR "A"."TR_STATUS"='R')
                  24 - access("B"."TAX_AUDIT_RECORD_ID"="A"."TAX_AUDIT_RECORD_ID")
                  26 - filter("A"."TR_STATUS"='C' OR "A"."TR_STATUS"='R')
                
                     
                • 5. Re: Subquery Unnesting issue
                  Pavan Kumar
                  Hi,

                  Missing of paranthesis
                  EXPLAIN PLAN FOR
                  DELETE FROM A 
                  WHERE ROWID IN (
                                 SELECT ROWID 
                                 FROM A 
                                 WHERE TR_STATUS IN ('C', 'R') 
                                 UNION ALL
                                 SELECT ROWID 
                                 FROM A 
                                 WHERE EXISTS ( 
                                         SELECT NULL 
                                         FROM B 
                                         WHERE B.TAX_AUDIT_RECORD_ID = A.TAX_AUDIT_RECORD_ID
                                           )
                                 AND LNNVL(TR_STATUS IN ('C', 'R'))
                                  );
                  - Pavan Kumar N
                  • 6. Re: Subquery Unnesting issue
                    Herald ten Dam
                    Hi,
                    LNNVL(TR_STATUS IN ('C', 'R'))
                    will lead to something like tr_status = 'C' or tr_status = 'R', and an OR is not allowed within an lnnvl. So you have to split the lnnvl into two parts with an OR in between:
                    LNNVL(TR_STATUS ='C') OR LNNVL(TR_STATUS = 'R')
                    Herald ten Dam
                    http://htendam.wordpress.com
                    • 7. Re: Subquery Unnesting issue
                      OraDBA02
                      That worked !
                      Many thanks Herald ten Dam.
                      • 8. Re: Subquery Unnesting issue
                        Jonathan Lewis
                        Herald ten Dam wrote:
                        Hi,
                        LNNVL(TR_STATUS IN ('C', 'R'))
                        will lead to something like tr_status = 'C' or tr_status = 'R', and an OR is not allowed within an lnnvl. So you have to split the lnnvl into two parts with an OR in between:
                        LNNVL(TR_STATUS ='C') OR LNNVL(TR_STATUS = 'R')
                        Good catch - I hadn't come across that one before.

                        If you're going to rewrite it like this, though, this one has to be AND, not OR.
                        I suggested the lnnvl() because (based on my previous use) it makes the code look simpler - since this is an example where introducing it also make the code more confusing I think I'd prefer to fall back to an old nvl() trick, viz:
                            and not (nvl(tr_status,'X') in ('C','R'))
                        We reallyl want to say the opposite to +"tr_status in ('C','R')"+ so the first approximation is simply +"not (tr_status in ('C','R'))"+, but then we need to replace occurrences of null with a value that won't be in ('C','R') - and I've arbitrarily picked an 'X'.


                        Regards
                        Jonathan Lewis
                        • 9. Re: Subquery Unnesting issue
                          OraDBA02
                          Thanks Everyone for your valuable inputs.
                          I got the desire execution plan and my delete performed in less than 5 min !