5 Replies Latest reply: Nov 20, 2012 10:54 AM by ranit B RSS

    Reg : Query Plan --

    ranit B
      Hi Experts,

      I've got a doubt regarding the Explain Plan(s) of 2 queries -
      /*------------------------
      -- Query [1]
      ------------------------*/
      SELECT *
        FROM xx, yy
       WHERE xx.literature_id = yy.literature_id
             AND (merge_req_date IS NOT NULL AND merged_date IS NULL)
                  OR (merge_req_date > merged_date AND merge_status IS NULL);            
      
      /** same as **/           
      
      SELECT *
        FROM xx, yy
       WHERE (xx.literature_id = yy.literature_id
             AND (merge_req_date IS NOT NULL AND merged_date IS NULL))
                  OR (merge_req_date > merged_date AND merge_status IS NULL);            
      
      /*------------------------
      -- Query [2]
      ------------------------*/
      SELECT *
        FROM xx, yy
       WHERE xx.literature_id = yy.literature_id
             AND ( (merge_req_date IS NOT NULL AND merged_date IS NULL)
                  OR (merge_req_date > merged_date AND merge_status IS NULL));
      Explain Plans -
      /*------------------------
      -- Plan [1]
      ------------------------*/
      Plan hash value: 3551575557
       
      ------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                       | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
      ------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                |                                |     1 |    39 |    34M  (4)|132:50:29 |
      |   1 |  HASH GROUP BY                  |                                |     1 |    39 |            |          |
      |   2 |   CONCATENATION                 |                                |       |       |            |          |
      |   3 |    MERGE JOIN CARTESIAN         |                                |  1861M|    67G|    34M  (4)|132:49:58 |
      |*  4 |     TABLE ACCESS FULL           | DS_MERGE_LOG                   | 69469 |  1763K|  1663   (2)| 00:00:24 |
      |   5 |     BUFFER SORT                 |                                | 26797 |   340K|    34M  (4)|132:49:34 |
      |   6 |      VIEW                       |                                | 26797 |   340K|   492   (4)| 00:00:07 |
      |   7 |       SORT UNIQUE               |                                | 26797 |   435K|   492  (53)| 00:00:07 |
      |   8 |        UNION-ALL                |                                |       |       |            |          |
      |*  9 |         VIEW                    | index$_join$_003               | 22095 |   302K|   241   (3)| 00:00:04 |
      |* 10 |          HASH JOIN              |                                |       |       |            |          |
      |* 11 |           INDEX RANGE SCAN      | X_LITERATURE_LITERATURE_NUMBER | 22095 |   302K|    45   (7)| 00:00:01 |
      |  12 |           INDEX FAST FULL SCAN  | SYS_C0013568                   | 22095 |   302K|   197   (2)| 00:00:03 |
      |  13 |         NESTED LOOPS            |                                |  4702 |   133K|   247   (4)| 00:00:04 |
      |* 14 |          HASH JOIN              |                                |  4664 |   109K|   246   (3)| 00:00:04 |
      |  15 |           INDEX FAST FULL SCAN  | ORIG_LIT_TRANS_LIT_ASSOC_PK    |  4661 | 46610 |     4   (0)| 00:00:01 |
      |* 16 |           VIEW                  | index$_join$_006               | 22095 |   302K|   241   (3)| 00:00:04 |
      |* 17 |            HASH JOIN            |                                |       |       |            |          |
      |* 18 |             INDEX RANGE SCAN    | X_LITERATURE_LITERATURE_NUMBER | 22095 |   302K|    45   (7)| 00:00:01 |
      |  19 |             INDEX FAST FULL SCAN| SYS_C0013568                   | 22095 |   302K|   197   (2)| 00:00:03 |
      |* 20 |          INDEX UNIQUE SCAN      | SYS_C0013568                   |     1 |     5 |     0   (0)| 00:00:01 |
      |* 21 |    HASH JOIN                    |                                |  1191K|    44M|  2168   (3)| 00:00:31 |
      |  22 |     VIEW                        |                                | 26797 |   340K|   492   (4)| 00:00:07 |
      |  23 |      SORT UNIQUE                |                                | 26797 |   435K|   492  (53)| 00:00:07 |
      |  24 |       UNION-ALL                 |                                |       |       |            |          |
      |* 25 |        VIEW                     | index$_join$_003               | 22095 |   302K|   241   (3)| 00:00:04 |
      |* 26 |         HASH JOIN               |                                |       |       |            |          |
      |* 27 |          INDEX RANGE SCAN       | X_LITERATURE_LITERATURE_NUMBER | 22095 |   302K|    45   (7)| 00:00:01 |
      |  28 |          INDEX FAST FULL SCAN   | SYS_C0013568                   | 22095 |   302K|   197   (2)| 00:00:03 |
      |  29 |        NESTED LOOPS             |                                |  4702 |   133K|   247   (4)| 00:00:04 |
      |* 30 |         HASH JOIN               |                                |  4664 |   109K|   246   (3)| 00:00:04 |
      |  31 |          INDEX FAST FULL SCAN   | ORIG_LIT_TRANS_LIT_ASSOC_PK    |  4661 | 46610 |     4   (0)| 00:00:01 |
      |* 32 |          VIEW                   | index$_join$_006               | 22095 |   302K|   241   (3)| 00:00:04 |
      |* 33 |           HASH JOIN             |                                |       |       |            |          |
      |* 34 |            INDEX RANGE SCAN     | X_LITERATURE_LITERATURE_NUMBER | 22095 |   302K|    45   (7)| 00:00:01 |
      |  35 |            INDEX FAST FULL SCAN | SYS_C0013568                   | 22095 |   302K|   197   (2)| 00:00:03 |
      |* 36 |         INDEX UNIQUE SCAN       | SYS_C0013568                   |     1 |     5 |     0   (0)| 00:00:01 |
      |* 37 |     TABLE ACCESS FULL           | DS_MERGE_LOG                   |   231K|  5875K|  1665   (2)| 00:00:24 |
      ------------------------------------------------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
       
         4 - filter("MERGE_STATUS" IS NULL AND "MERGE_REQ_DATE">"MERGED_DATE")
         9 - filter("A"."LITERATURE_NUMBER" LIKE 'SN%')
        10 - access(ROWID=ROWID)
        11 - access("A"."LITERATURE_NUMBER" LIKE 'SN%')
        14 - access("B"."ORIGINAL_LITERATURE_ID"="C"."LITERATURE_ID")
        16 - filter("C"."LITERATURE_NUMBER" LIKE 'SN%')
        17 - access(ROWID=ROWID)
        18 - access("C"."LITERATURE_NUMBER" LIKE 'SN%')
        20 - access("B"."TRANSLATED_LITERATURE_ID"="A"."LITERATURE_ID")
        21 - access("XX"."LITERATURE_ID"="YY"."LITERATURE_ID")
        25 - filter("A"."LITERATURE_NUMBER" LIKE 'SN%')
        26 - access(ROWID=ROWID)
        27 - access("A"."LITERATURE_NUMBER" LIKE 'SN%')
        30 - access("B"."ORIGINAL_LITERATURE_ID"="C"."LITERATURE_ID")
        32 - filter("C"."LITERATURE_NUMBER" LIKE 'SN%')
        33 - access(ROWID=ROWID)
        34 - access("C"."LITERATURE_NUMBER" LIKE 'SN%')
        36 - access("B"."TRANSLATED_LITERATURE_ID"="A"."LITERATURE_ID")
        37 - filter("MERGED_DATE" IS NULL AND (LNNVL("MERGE_STATUS" IS NULL) OR 
                    LNNVL("MERGE_REQ_DATE">"MERGED_DATE")) AND "MERGE_REQ_DATE" IS NOT NULL)
      
      
      
      
      /*------------------------
      -- Plan [2]
      ------------------------*/
      Plan hash value: 724152957
       
      ----------------------------------------------------------------------------------------------------------------
      | Id  | Operation                     | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT              |                                |     1 |    39 |  2300   (9)| 00:00:33 |
      |   1 |  HASH GROUP BY                |                                |     1 |    39 |  2300   (9)| 00:00:33 |
      |*  2 |   HASH JOIN                   |                                |  1549K|    57M|  2171   (3)| 00:00:31 |
      |   3 |    VIEW                       |                                | 26797 |   340K|   492   (4)| 00:00:07 |
      |   4 |     SORT UNIQUE               |                                | 26797 |   435K|   492  (53)| 00:00:07 |
      |   5 |      UNION-ALL                |                                |       |       |            |          |
      |*  6 |       VIEW                    | index$_join$_003               | 22095 |   302K|   241   (3)| 00:00:04 |
      |*  7 |        HASH JOIN              |                                |       |       |            |          |
      |*  8 |         INDEX RANGE SCAN      | X_LITERATURE_LITERATURE_NUMBER | 22095 |   302K|    45   (7)| 00:00:01 |
      |   9 |         INDEX FAST FULL SCAN  | SYS_C0013568                   | 22095 |   302K|   197   (2)| 00:00:03 |
      |  10 |       NESTED LOOPS            |                                |  4702 |   133K|   247   (4)| 00:00:04 |
      |* 11 |        HASH JOIN              |                                |  4664 |   109K|   246   (3)| 00:00:04 |
      |  12 |         INDEX FAST FULL SCAN  | ORIG_LIT_TRANS_LIT_ASSOC_PK    |  4661 | 46610 |     4   (0)| 00:00:01 |
      |* 13 |         VIEW                  | index$_join$_006               | 22095 |   302K|   241   (3)| 00:00:04 |
      |* 14 |          HASH JOIN            |                                |       |       |            |          |
      |* 15 |           INDEX RANGE SCAN    | X_LITERATURE_LITERATURE_NUMBER | 22095 |   302K|    45   (7)| 00:00:01 |
      |  16 |           INDEX FAST FULL SCAN| SYS_C0013568                   | 22095 |   302K|   197   (2)| 00:00:03 |
      |* 17 |        INDEX UNIQUE SCAN      | SYS_C0013568                   |     1 |     5 |     0   (0)| 00:00:01 |
      |* 18 |    TABLE ACCESS FULL          | DS_MERGE_LOG                   |   300K|  7639K|  1664   (2)| 00:00:24 |
      ----------------------------------------------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
       
         2 - access("XX"."LITERATURE_ID"="YY"."LITERATURE_ID")
         6 - filter("A"."LITERATURE_NUMBER" LIKE 'SN%')
         7 - access(ROWID=ROWID)
         8 - access("A"."LITERATURE_NUMBER" LIKE 'SN%')
        11 - access("B"."ORIGINAL_LITERATURE_ID"="C"."LITERATURE_ID")
        13 - filter("C"."LITERATURE_NUMBER" LIKE 'SN%')
        14 - access(ROWID=ROWID)
        15 - access("C"."LITERATURE_NUMBER" LIKE 'SN%')
        17 - access("B"."TRANSLATED_LITERATURE_ID"="A"."LITERATURE_ID")
        18 - filter("MERGED_DATE" IS NULL AND "MERGE_REQ_DATE" IS NOT NULL OR "MERGE_STATUS" IS NULL AND 
                    "MERGE_REQ_DATE">"MERGED_DATE")
      Can anybody please explain me what is the difference between the WHERE clause of the 2 queries?
      I guess something but not sure... Please help me understand this.

      Sorry, i can't give the exact query... but i guess the WHERE clause is of main concern.

      Oracle database -- 10.2.0
        • 1. Re: Reg : Query Plan --
          sb92075
          ranit B wrote:
          Hi Experts,

          I've got a doubt regarding the Explain Plan(s) of 2 queries -
          please post the two EXPLAIN PLANS
          • 2. Re: Reg : Query Plan --
            ranit B
            Ohh SB.... u are toooo fast mann !!! ;-)
            • 3. Re: Reg : Query Plan --
              Kim Berg Hansen
              Look at your parentheses.

              First where clause:
               WHERE (
                       xx.literature_id = yy.literature_id
                       AND
                       (merge_req_date IS NOT NULL AND merged_date IS NULL)
                     )
                   OR
                     (
                       merge_req_date > merged_date AND merge_status IS NULL
                     )
              That returns those where xx and yy join on literature_id and some date filtering, and it also returns all those with a different date filtering no matter if they match on literature_id.

              Second where clause:
               WHERE xx.literature_id = yy.literature_id
                   AND (
                         (merge_req_date IS NOT NULL AND merged_date IS NULL)
                         OR
                         (merge_req_date > merged_date AND merge_status IS NULL)
                       )
              The second returns join of xx and yy on literature_id, filtered on some rule on dates and status.

              The two where clause return wildly different results ;-)
              • 4. Re: Reg : Query Plan --
                Paul  Horth
                Query1 is is saying like

                where (A and B) or C

                Query 2 is saying like

                where A and (B or C)

                completely different conditions, see the truth table:
                A    B    C    A and B    (A and B) or C
                T    T    T        T                 T
                T    T    F        T                 T
                T    F    T        F                 T
                T    F    F        F                 F
                F    T    T        F                 T
                F    T    F        F                 F
                F    F    T        F                 T
                F    F    F        F                 F
                
                A    B    C    B or C     A and (B or C)
                T    T    T        T                 T
                T    T    F        T                 T
                T    F    T        T                 T
                T    F    F        F                 F
                F    T    T        T                 F
                F    T    F        T                 F
                F    F    T        T                 F
                F    F    F        F                 F
                • 5. Re: Reg : Query Plan --
                  ranit B
                  Thanks a ton!... Kim & Paul.

                  It was really a nice and precise explanation. Cooool.