Explain Plan Shows a Cartesian Merge Join, How Would You Help?

24 12 2010

December 24, 2010

Imagine that a developer approached you with the following SQL statement, and explained that he (or she) noticed that the execution plan showed a Cartesian merge join.  Even adding an ORDERED hint did not affect the execution plan, and the execution performance was the same with the ORDERED hint.

SELECT X.TIME_PERIOD EXPOSURE_PERIOD,Y.TIME_PERIOD EVALUATION_PERIOD,BUSINESS_UNIT,
TO_NUMBER(LOB_VALUE) BUSINESS_UNIT_LOB_ID_FIN,0 CALC_VALUE

FROM ACTUARIAL_REF_DATA.TIME_PERIOD_HIERARCHY X,ACTUARIAL_REF_DATA.TIME_PERIOD_HIERARCHY Y,ANALYSIS_BUSINESS_UNITS, ANALYSIS_LOBS

WHERE X.TIME_PERIOD BETWEEN TO_NUMBER('200001') AND TO_NUMBER('200212')
AND Y.TIME_PERIOD BETWEEN TO_NUMBER('200001') AND TO_NUMBER('200212')
AND BUSINESS_UNIT = '33011';

Cartesian joins are not automatically a sign of problems.  Previous blog articles have demonstrated that a Cartesian join may be more efficient than other methods for certain situations (see Improving Performance by Using a Cartesian Join and Improving Performance by Using a Cartesian Join 2), the execution plan may simply display a Cartesian join rather than a nested loops join when one of the row sources is expected to return a single row, or the optimizer may introduce a Cartesian join as a transformation due of transitive closure (see this article).

How would you help the developer with the above SQL statement?  Would you first attempt to reformat the SQL statement, or would you do something else as the first step (note that this blog article is not specifically interested in just helping the developer with this one SQL statement, but instead is seeking advice to help the developer know what to do the next time)?  Think about the SQL statement for a moment…

—

—

—

—

Almost a year ago I put together another blog article (SQL Basics – Working with ERP Data) that was based on a small portion of a presentation that I gave to an ERP user’s group.  I almost created a new blog article that basically stated the same tips shortly after I saw the above SQL statement in this OTN thread (please do not visit the thread until you have had a chance to think about the SQL statement, and how you would help the developer).

« »


Actions

Information

  • Date : December 24, 2010
  • Categories : SQL

7 responses

24 12 2010
Rodger (15:47:40) :

Hi Charles,

Makes perfect sense that he is getting an indication of a cartesian products.

There are 4 tables:

FROM ACTUARIAL_REF_DATA.TIME_PERIOD_HIERARCHY X,
ACTUARIAL_REF_DATA.TIME_PERIOD_HIERARCHY Y,
ANALYSIS_BUSINESS_UNITS,
ANALYSIS_LOBS

and zero join conditions.

Only filter conditions.

This is the best way to get a cartesian product:
Don’t have any join conditions.

To eliminate cartesain products, you need n – 1 join conditions.
Where n = the number of tables.
Here 4 tables, so 3 join conditions are necessary.

Add the join conditions first.
Then add the filter conditions.

You also need to ensure that for each join condition, at least one side
of the join is one a unique set of fields.

See the in depth presentation that I did at the Oracle User’s Group on cartesian products here:
http://rodgersnotes.wordpress.com/2010/09/15/stamping-out-cartesian-products/

Cheers!

Reply
24 12 2010
Gus Spier (21:58:30) :

Roger points out one very good point: there are no join conditions.

WHERE X.TIME_PERIOD BETWEEN TO_NUMBER(‘200001’) AND TO_NUMBER(‘200212’)
AND Y.TIME_PERIOD BETWEEN TO_NUMBER(‘200001’) AND TO_NUMBER(‘200212’)
AND BUSINESS_UNIT = ‘33011’;

I thinkthat once the develop decides which records he actually needs (join conditions) his next step will be to re-cast the ACTUALRIAL_REF_TABLE

Reply
24 12 2010
Gus Spier (22:02:02) :

Wait a minute, that post wasn’t finished. It is supposed to read, once the developer constructs his join conditions, he should re-cast the ACTUARIAL_REF_TABLE to use the DATE datatype for timeperiods rather than jury rig something based on the NUMBER datatype.

Reply
25 12 2010
Narendra (02:16:32) :

Imagine that a developer approached you with the following SQL statement, and explained that he (or she) noticed that the execution plan showed a Cartesian merge join. Even adding an ORDERED hint did not affect the execution plan, and the execution performance was the same with the ORDERED hint
How would you help the developer with the above SQL statement? Would you first attempt to reformat the SQL statement, or would you do something else as the first step (note that this blog article is not specifically interested in just helping the developer with this one SQL statement, but instead is seeking advice to help the developer know what to do the next time)?
I will have to make some assumptions here before offering any help/suggestions
1) The developer is willing to listen and learn.
2) The developer understands that my intention is to help and not to just criticise or show-off my superiority
I know this sounds bizzare but my experience is a rational, correct and long-term solution is generally not possible without above assumptions.
Now, suggestions:
1) I will ask the developer if he (or she) can help me understand what business requirement the query is expected to address (I am sure the discussion during this step will take care of some of the following points)
2) Qualify all the column names in the SELECT list with the corresponding table aliases
3) Is there a relation between the tables in the FROM clause? Is the developer aware of the relationship?
4) Why does the developer think the CARTESIAN MERGE join is not the best plan ? Does the developer know any other better plan and why the other plan is better?
5) How much time the query currently take to execute? How much time does the develope expects query to take to execute?
6) How many records is the query expected to return? Does the developer know what he (or she) is doing with the results of the query, especially if the query is expected to return large number of records?
7) If the query is part of some processing (package or procedure), is the query really needed (or can it be eliminated)?
8) If the answers to above questions prove that the query needs tuning, then I would follow the guidelines posted by Rob Van Wijk / Randolf Geist to troubleshoot the problem (by checking accuracy of statistics, estimated vs actual cardinalities, non-default values of OPTIMIZER* and/or undocumented parameters etc.)

Reply
25 12 2010
Narendra (03:34:45) :

Well, I saw the OTN thread after I posted the reply and it seems your approach to explain with a small example was sufficient to address the issue. Seems my rant above was an overkill

Reply
25 12 2010
Mohamed (03:47:31) :

Naranda has pointed out, in a very well and elegant manner, all what I was thinking about. I would just add few remarks about the SQL statement

(1) We need always to consider date as date, varchar as varchar and number as number (WHERE X.TIME_PERIOD BETWEEN TO_NUMBER (‘200001’) AND TO_NUMBER (‘200212’))
(2) The SQL statement doesn’t show to what table belongs the BUSINESS_UNIT attribute
(3) If the developer can phrase in English (or in his native language) what he is willing to get by this query, it might be possible that one or two tables are not necessary at all
(4) In case the developer wants to use the ORDERED hint he has better to use the LEADING hint instead

Best Regards

Reply
25 12 2010
Charles Hooper (08:42:10) :

I am impressed with the quality of the comments in this blog article. Combined, the comments would not only help the developer understand why the current SQL statement produces a Cartesian join, but also provides general tips to improve the readability of the developer’s SQL statements and to help the optimizer make better calculations for cardinality estimates. I hope that my article https://hoopercharles.wordpress.com/2010/11/29/i-ordered-a-hint-why-wont-you-listen-to-me/ that attempted to use an ORDERED hint to force a Cartesian join is not the reason why the developer attempted to use an ORDERED hint to fix the Cartesian join.

Rodger, nice explanation. The link that you provided includes a link to a 153 slide presentation on the topic of eliminating developer introduced Cartesian joins.

Gus Spier, nice idea to point out the potential data type problems.

Narendra, very impressive list that very effectively helps the developer understand whether or not the presence of Cartesian joins in a particular execution plan represents a problem (there is a small chance that it does not represent a problem).

Mohamed, excellent points for helping to improve (all) SQL statements and data type definitions.

Now for a different cause for a Cartesian join. On page 306 of the “Expert Oracle Practices book Oracle Database Administration from the Oak Table” book Randolf and I showed a case where an Oracle release version upgrade from Oracle Database 8.1.7.3 to 10.2.0.2 (also reproduces on 10.2.0.4, 11.1.0.7, and 11.2.0.1) introduced a Cartesian join into the execution plan for a SQL statement. (If you do not have the book, you can read that part of the book from this link http://books.google.com/books?id=tUNtyMCwDWQC&pg=PA306#v=onepage&q&f=false .) This is the SQL statement that generate a Cartesian join on 10.2.0.2 and above (possibly 10.1 and above), but not on 8.1.7.3:

SELECT
  TOP_LEVEL_PART_ID, REQ.PURC_PART_ID, REQ.TOTAL_QTY,
  REQ.TOTAL_QTY*TPPD.INCREASE "Increase",
  REQ.R1_PART_ID, REQ.R2_PART_ID, REQ.R3_PART_ID,
  REQ.R4_PART_ID, REQ.R5_PART_ID, REQ.R6_PART_ID,
  REQ.R1_CALC_QTY, REQ.R2_CALC_QTY, REQ.R3_CALC_QTY,
  REQ.R4_CALC_QTY, REQ.R5_CALC_QTY, REQ.R6_CALC_QTY
FROM
  (SELECT
    TOP_LEVEL_PART_ID,
    DECODE(R6_PART_ID,NULL,
      DECODE(R5_PART_ID,NULL,
        DECODE(R4_PART_ID,NULL,
          DECODE(R3_PART_ID,NULL,
            DECODE(R2_PART_ID,NULL,
                   R1_PART_ID,R2_PART_ID),
                 R3_PART_ID),
               R4_PART_ID),
             R5_PART_ID),
           R6_PART_ID) PURC_PART_ID,
    NVL(R1_CALC_QTY,0)*NVL(R2_CALC_QTY,1)*NVL(R3_CALC_QTY,1)*NVL(R4_CALC_QTY,1)*NVL(R5_CALC_QTY,1)*NVL(R6_CALC_QTY,1) TOTAL_QTY,
    R1_PART_ID, R1_CALC_QTY, R2_PART_ID, R2_CALC_QTY, R3_PART_ID, R3_CALC_QTY,
    R4_PART_ID, R4_CALC_QTY, R5_PART_ID, R5_CALC_QTY, R6_PART_ID, R6_CALC_QTY
  FROM
    (SELECT
      PL.PART_ID TOP_LEVEL_PART_ID,
      DECODE(R6.PART_ID,NULL,
        DECODE(R5.PART_ID,NULL,
          DECODE(R4.PART_ID,NULL,
            DECODE(R3.PART_ID,NULL,
              DECODE(R2.PART_ID,NULL,
                     R.PART_ID,R2.PART_ID),
                   R3.PART_ID),
                 R4.PART_ID),
               R5.PART_ID),
             R6.PART_ID) PURC_PART_ID,
      NVL(R.CALC_QTY,0)*NVL(R2.CALC_QTY,1)*NVL(R3.CALC_QTY,1)*NVL(R4.CALC_QTY,1)*NVL(R5.CALC_QTY,1)*NVL(R6.CALC_QTY,1) TOTAL_QTY,
      R.PART_ID R1_PART_ID,
      R.CALC_QTY R1_CALC_QTY,
      DECODE(NVL(P.FABRICATED,'NONE'),'Y',R2.PART_ID,NULL) R2_PART_ID,
      DECODE(NVL(P.FABRICATED,'NONE'),'Y',R2.CALC_QTY,NULL) R2_CALC_QTY,
      DECODE(
        DECODE(NVL(P.FABRICATED,'NONE'),'Y',R2.PART_ID,NULL),
        NULL,
        NULL,
        DECODE(NVL(P2.FABRICATED,'NONE'),'Y',R3.PART_ID,NULL)) R3_PART_ID,
      DECODE(
        DECODE(NVL(P.FABRICATED,'NONE'),'Y',R2.PART_ID,NULL),
        NULL,
        NULL,
        DECODE(NVL(P2.FABRICATED,'NONE'),'Y',R3.CALC_QTY,NULL)) R3_CALC_QTY,
      DECODE(
        DECODE(
          DECODE(NVL(P.FABRICATED,'NONE'),'Y',R2.PART_ID,NULL),
          NULL,
          NULL,
          DECODE(NVL(P2.FABRICATED,'NONE'),'Y',R3.PART_ID,NULL)),
        NULL,
        NULL,
        DECODE(NVL(P3.FABRICATED,'NONE'),'Y',R4.PART_ID,NULL)) R4_PART_ID,
      DECODE(
        DECODE(
          DECODE(NVL(P.FABRICATED,'NONE'),'Y',R2.PART_ID,NULL),
          NULL,
          NULL,
          DECODE(NVL(P2.FABRICATED,'NONE'),'Y',R3.PART_ID,NULL)),
        NULL,
        NULL,
        DECODE(NVL(P3.FABRICATED,'NONE'),'Y',R4.CALC_QTY,NULL)) R4_CALC_QTY,
      DECODE(
        DECODE(
          DECODE(
            DECODE(NVL(P.FABRICATED,'NONE'),'Y',R2.PART_ID,NULL),
            NULL,
            NULL,
            DECODE(NVL(P2.FABRICATED,'NONE'),'Y',R3.PART_ID,NULL)),
          NULL,
          NULL,
          DECODE(NVL(P3.FABRICATED,'NONE'),'Y',R4.PART_ID,NULL)),
        NULL,
        NULL,
        DECODE(NVL(P4.FABRICATED,'NONE'),'Y',R5.PART_ID,NULL)) R5_PART_ID,
      DECODE(
        DECODE(
          DECODE(
            DECODE(NVL(P.FABRICATED,'NONE'),'Y',R2.PART_ID,NULL),
            NULL,
            NULL,
            DECODE(NVL(P2.FABRICATED,'NONE'),'Y',R3.PART_ID,NULL)),
          NULL,
          NULL,
          DECODE(NVL(P3.FABRICATED,'NONE'),'Y',R4.PART_ID,NULL)),
        NULL,
        NULL,
        DECODE(NVL(P4.FABRICATED,'NONE'),'Y',R5.CALC_QTY,NULL)) R5_CALC_QTY,
      DECODE(
        DECODE(
          DECODE(
            DECODE(
              DECODE(NVL(P.FABRICATED,'NONE'),'Y',R2.PART_ID,NULL),
              NULL,
              NULL,
              DECODE(NVL(P2.FABRICATED,'NONE'),'Y',R3.PART_ID,NULL)),
            NULL,
            NULL,
            DECODE(NVL(P3.FABRICATED,'NONE'),'Y',R4.PART_ID,NULL)),
          NULL,
          NULL,
          DECODE(NVL(P4.FABRICATED,'NONE'),'Y',R5.PART_ID,NULL)),
        NULL,
        NULL,
        DECODE(NVL(P5.FABRICATED,'NONE'),'Y',R6.PART_ID,NULL)) R6_PART_ID,
      DECODE(
        DECODE(
          DECODE(
            DECODE(
              DECODE(NVL(P.FABRICATED,'NONE'),'Y',R2.PART_ID,NULL),
              NULL,
              NULL,
              DECODE(NVL(P2.FABRICATED,'NONE'),'Y',R3.PART_ID,NULL)),
            NULL,
            NULL,
            DECODE(NVL(P3.FABRICATED,'NONE'),'Y',R4.PART_ID,NULL)),
          NULL,
          NULL,
          DECODE(NVL(P4.FABRICATED,'NONE'),'Y',R5.PART_ID,NULL)),
        NULL,
        NULL,
        DECODE(NVL(P5.FABRICATED,'NONE'),'Y',R6.CALC_QTY,NULL)) R6_CALC_QTY
    FROM
      REQUIREMENT R, REQUIREMENT R2, REQUIREMENT R3,
      REQUIREMENT R4, REQUIREMENT R5, REQUIREMENT R6,
      PART P, PART P2, PART P3, PART P4, PART P5, PART P6,
      (SELECT DISTINCT
        COL.PART_ID
      FROM
        CUSTOMER_ORDER CO,
        CUST_ORDER_LINE COL
      WHERE
        CO.STATUS IN ('F','R','U','P','C')
        AND CO.ID=COL.CUST_ORDER_ID
        AND COL.DESIRED_SHIP_DATE>(SYSDATE-365)) PL
    WHERE
      R.WORKORDER_TYPE='M'
      AND PL.PART_ID=R.WORKORDER_BASE_ID
      AND R.WORKORDER_LOT_ID='0'
      AND R.WORKORDER_SPLIT_ID='0'
      AND R.SUBORD_WO_SUB_ID IS NULL
      AND R.PART_ID=P.ID(+)
      AND P.FABRICATED(+)='Y'
      AND R2.WORKORDER_TYPE(+)='M'
      AND R.PART_ID=R2.WORKORDER_BASE_ID(+)
      AND R2.WORKORDER_LOT_ID(+)='0'
      AND R2.SUBORD_WO_SUB_ID IS NULL
      AND R2.PART_ID=P2.ID(+)
      AND P2.FABRICATED(+)='Y'
      AND R3.WORKORDER_TYPE(+)='M'
      AND R2.PART_ID=R3.WORKORDER_BASE_ID(+)
      AND R3.WORKORDER_LOT_ID(+)='0'
      AND R3.SUBORD_WO_SUB_ID IS NULL
      AND R3.PART_ID=P3.ID(+)
      AND P3.FABRICATED(+)='Y'
      AND R4.WORKORDER_TYPE(+)='M'
      AND R3.PART_ID=R4.WORKORDER_BASE_ID(+)
      AND R4.WORKORDER_LOT_ID(+)='0'
      AND R4.SUBORD_WO_SUB_ID IS NULL
      AND R4.PART_ID=P4.ID(+)
      AND P4.FABRICATED(+)='Y'
      AND R5.WORKORDER_TYPE(+)='M'
      AND R4.PART_ID=R5.WORKORDER_BASE_ID(+)
      AND R5.WORKORDER_LOT_ID(+)='0'
      AND R5.SUBORD_WO_SUB_ID IS NULL
      AND R5.PART_ID=P5.ID(+)
      AND P5.FABRICATED(+)='Y'
      AND R6.WORKORDER_TYPE(+)='M'
      AND R5.PART_ID=R6.WORKORDER_BASE_ID(+)
      AND R6.WORKORDER_LOT_ID(+)='0'
      AND R6.SUBORD_WO_SUB_ID IS NULL
      AND R6.PART_ID=P6.ID(+)
      AND P6.FABRICATED(+)='Y')) REQ,
  TEMP_PART_PRICE_DATE TPPD
WHERE
  REQ.PURC_PART_ID=TPPD.PART_ID
  AND TPPD.INCREASE<>0;

As you can probably tell from the above, it is a fairly simple SQL statement 🙂 that requires the inline view to be resolved before the TEMP_PART_PRICE_DATE table is accessed. Oracle Database 10.2.0.4 generated an execution plan that looked something like this (note that some columns were manually removed from the execution plan):

------------------------------------------------------------------------------------
|Id |Operation                                        | Name                | Rows |
------------------------------------------------------------------------------------
|0  |SELECT STATEMENT                                 |                     |      |
|1  | VIEW                                            |                     |    1 |
|2  |  HASH UNIQUE                                    |                     |    1 |
|3  |   NESTED LOOPS OUTER                            |                     |    1 |
|4  |    FILTER                                       |                     |      |
|5  |     NESTED LOOPS OUTER                          |                     |    1 |
|6  |      NESTED LOOPS OUTER                         |                     |    1 |
|7  |       NESTED LOOPS OUTER                        |                     |    1 |
|8  |        FILTER                                   |                     |      |
|9  |         NESTED LOOPS OUTER                      |                     |    1 |
|10 |          FILTER                                 |                     |      |
|11 |           NESTED LOOPS OUTER                    |                     |    1 |
|12 |            NESTED LOOPS OUTER                   |                     |    1 |
|13 |             NESTED LOOPS OUTER                  |                     |    1 |
|14 |              NESTED LOOPS OUTER                 |                     |    1 |
|15 |               FILTER                            |                     |      |
|16 |                NESTED LOOPS OUTER               |                     |    1 |
|17 |                 FILTER                          |                     |      |
|18 |                  NESTED LOOPS OUTER             |                     |    1 |
|19 |                   NESTED LOOPS                  |                     |    1 |
|20 |                    NESTED LOOPS                 |                     |    1 |
|21 |                     MERGE JOIN CARTESIAN        |                     |    1 |
|22 |                      TABLE ACCESS FULL          | TEMP_PART_PRICE_DATE|    1 |
|23 |                      BUFFER SORT                |                     | 6895 |
|24 |                       TABLE ACCESS FULL         | CUSTOMER_ORDER      | 6895 |
|25 |                     TABLE ACCESS BY INDEX ROWID | CUST_ORDER_LINE     |    1 |
|26 |                      INDEX RANGE SCAN           | SYS_C007880         |   38 |
|27 |                    TABLE ACCESS BY INDEX ROWID  | REQUIREMENT         |    1 |
|28 |                     INDEX RANGE SCAN            | X_REQUIREMENT_5     |    1 |
|29 |                   TABLE ACCESS BY INDEX ROWID   | REQUIREMENT         |    1 |
|30 |                    INDEX RANGE SCAN             | SYS_C008088         |    1 |
|31 |                 TABLE ACCESS BY INDEX ROWID     | REQUIREMENT         |    1 |
|32 |                  INDEX RANGE SCAN               | SYS_C008088         |    1 |
|33 |               TABLE ACCESS BY INDEX ROWID       | PART                |    1 |
|34 |                INDEX UNIQUE SCAN                | SYS_C007999         |    1 |
|35 |              TABLE ACCESS BY INDEX ROWID        | PART                |    1 |
|36 |               INDEX UNIQUE SCAN                 | SYS_C007999         |    1 |
|37 |             TABLE ACCESS BY INDEX ROWID         | PART                |    1 |
|38 |              INDEX UNIQUE SCAN                  | SYS_C007999         |    1 |
|39 |            TABLE ACCESS BY INDEX ROWID          | REQUIREMENT         |    1 |
|40 |             INDEX RANGE SCAN                    | SYS_C008088         |    1 |
|41 |          TABLE ACCESS BY INDEX ROWID            | REQUIREMENT         |    1 |
|42 |           INDEX RANGE SCAN                      | SYS_C008088         |    1 |
|43 |        TABLE ACCESS BY INDEX ROWID              | PART                |    1 |
|44 |         INDEX UNIQUE SCAN                       | SYS_C007999         |    1 |
|45 |       TABLE ACCESS BY INDEX ROWID               | PART                |    1 |
|46 |        INDEX UNIQUE SCAN                        | SYS_C007999         |    1 |
|47 |      TABLE ACCESS BY INDEX ROWID                | REQUIREMENT         |    1 |
|48 |       INDEX RANGE SCAN                          | SYS_C008088         |    1 |
|49 |    TABLE ACCESS BY INDEX ROWID                  | PART                |    1 |
|50 |     INDEX UNIQUE SCAN                           | SYS_C007999         |    1 |
------------------------------------------------------------------------------------

This is the execution plan for a hinted version of the same SQL statement (note that some columns were manually removed from the execution plan):

------------------------------------------------------------------------------------
| Id |Operation                                     | Name                 |A-Rows |
------------------------------------------------------------------------------------
|   1| NESTED LOOPS                                 |                      |   739 |
|   2|  NESTED LOOPS OUTER                          |                      |  3224 |
|   3|   NESTED LOOPS OUTER                         |                      |  3224 |
|   4|    NESTED LOOPS OUTER                        |                      |  3224 |
|   5|     NESTED LOOPS OUTER                       |                      |  3224 |
|   6|      NESTED LOOPS OUTER                      |                      |  3224 |
|   7|       NESTED LOOPS OUTER                     |                      |  3224 |
|*  8|        HASH JOIN                             |                      |  3224 |
|   9|         VIEW                                 |                      |   288 |
|  10|          HASH UNIQUE                         |                      |   288 |
|* 11|           HASH JOIN                          |                      |  4853 |
|* 12|            TABLE ACCESS FULL                 | CUSTOMER_ORDER       |  6872 |
|* 13|            TABLE ACCESS FULL                 | CUST_ORDER_LINE      |  4857 |
|* 14|         FILTER                               |                      | 31826 |
|* 15|          HASH JOIN RIGHT OUTER               |                      | 31826 |
|  16|           TABLE ACCESS BY INDEX ROWID        | REQUIREMENT          | 30970 |
|* 17|            INDEX RANGE SCAN                  | X_REQUIREMENT_5      | 30970 |
|* 18|           FILTER                             |                      | 31826 |
|* 19|            HASH JOIN RIGHT OUTER             |                      | 31826 |
|  20|             TABLE ACCESS BY INDEX ROWID      | REQUIREMENT          | 30970 |
|* 21|              INDEX RANGE SCAN                | X_REQUIREMENT_5      | 30970 |
|* 22|             FILTER                           |                      | 31826 |
|* 23|              HASH JOIN RIGHT OUTER           |                      | 31826 |
|  24|               TABLE ACCESS BY INDEX ROWID    | REQUIREMENT          | 30970 |
|* 25|                INDEX RANGE SCAN              | X_REQUIREMENT_5      | 30970 |
|* 26|               FILTER                         |                      | 31811 |
|* 27|                HASH JOIN RIGHT OUTER         |                      | 31815 |
|  28|                 TABLE ACCESS BY INDEX ROWID  | REQUIREMENT          | 30970 |
|* 29|                  INDEX RANGE SCAN            | X_REQUIREMENT_5      | 30970 |
|* 30|                 FILTER                       |                      | 31556 |
|* 31|                  HASH JOIN OUTER             |                      | 31627 |
|  32|                   TABLE ACCESS BY INDEX ROWID| REQUIREMENT          | 28673 |
|* 33|                    INDEX RANGE SCAN          | X_REQUIREMENT_5      | 28673 |
|  34|                   TABLE ACCESS BY INDEX ROWID| REQUIREMENT          | 30970 |
|* 35|                    INDEX RANGE SCAN          | X_REQUIREMENT_5      | 30970 |
|* 36|        TABLE ACCESS BY INDEX ROWID           | PART                 |  1690 |
|* 37|         INDEX UNIQUE SCAN                    | SYS_C007999          |  3224 |
|* 38|       TABLE ACCESS BY INDEX ROWID            | PART                 |  1020 |
|* 39|        INDEX UNIQUE SCAN                     | SYS_C007999          |  1940 |
|* 40|      TABLE ACCESS BY INDEX ROWID             | PART                 |   255 |
|* 41|       INDEX UNIQUE SCAN                      | SYS_C007999          |  1046 |
|* 42|     TABLE ACCESS BY INDEX ROWID              | PART                 |    26 |
|* 43|      INDEX UNIQUE SCAN                       | SYS_C007999          |   261 |
|* 44|    TABLE ACCESS BY INDEX ROWID               | PART                 |     8 |
|* 45|     INDEX UNIQUE SCAN                        | SYS_C007999          |    27 |
|* 46|   TABLE ACCESS BY INDEX ROWID                | PART                 |     0 |
|* 47|    INDEX UNIQUE SCAN                         | SYS_C007999          |     8 |
|* 48|  TABLE ACCESS BY INDEX ROWID                 | TEMP_PART_PRICE_DATE |   739 |
|* 49|   INDEX UNIQUE SCAN                          | SYS_C008418          |   739 |
------------------------------------------------------------------------------------

The time difference for the two execution plans (and a couple of other execution plans for the same SQL statement) are summarized on page 313 of the book. The key difference in this case was that the developer had a good understanding of the data and how he thought that it should be accessed, while the query optimizer used the various object statistics and query transformation methods to mathematically generate what appeared to be the “best” execution plan.

Reply

Leave a reply to Gus Spier Cancel reply