This discussion is archived
7 Replies Latest reply: Dec 22, 2010 5:48 AM by 704978 RSS

Query tuning

704978 Newbie
Currently Being Moderated
Hi Everyone,

I have query like below (example) where it gives merge join cartesian.

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'

I found that Merge join cartesian is not good in the DB. I have used /*+ORDERED*/  hint but the explain plan is the same and time difference is the almost same.

Can you please ;et me know the best method to use.

Regards,
Sundeep
  • 1. Re: Query tuning
    Shaan_dmp Explorer
    Currently Being Moderated
    I would suggests first try with oracle provided tools sql tuning and access options from OEM...
  • 2. Re: Query tuning
    SatishKandi Guru
    Currently Being Moderated
    kangula wrote:
    Hi Everyone,

    I have query like below (example) where it gives merge join cartesian.

    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'
    MERGE JOIN CARTESIAN access plan is a result of missing join predicates - which in your case happen to be the joins between all these tables. What you have is individual table conditions but no joins.
    I found that Merge join cartesian is not good in the DB. I have used /*+ORDERED*/  hint but the explain plan is the same and time difference is the almost same.
    This hint, for sure, is not going to help here.
  • 3. Re: Query tuning
    CharlesHooper Expert
    Currently Being Moderated
    Sundeep,

    This is your query:
    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'
    You are asking Oracle to retrieve data from 4 tables, and you have applied restrictions on the rows to be returned from at least 2 of the tables, possibly 3 tables. The reason for the Cartesian join is that you did not tell Oracle how the tables were related to each other. Usually, there will be one (two, three, four, etc.) columns that the tables will have in common. You need to list these relationships in the WHERE clause.

    Also, why are you specifying TO_NUMBER('200001'), and not just 200001?

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 4. Re: Query tuning
    704978 Newbie
    Currently Being Moderated
    Hi Charles,

    Many thanks for your reply. I have all the 4 tables used in the where condition.

    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' AND LOB_VALUE = TO_NUMBER('2920')

    The reason we used TO_NUMBER as the column needs to convert date as below.

    BETWEEN TO_NUMBER (SYS_CONTEXT('RHS_CONTEXT','EXP_DATE_START'))

    Regards,
    Sundeep
  • 5. Re: Query tuning
    ThierryH. Pro
    Currently Being Moderated
    Hi Sundeep,

    What Charles means is that you did not link any of the tables in your where clause. So, try the following:

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


    Then X is linked to Y !!

    Regards,
    Thierry
  • 6. Re: Query tuning
    CharlesHooper Expert
    Currently Being Moderated
    Hi Sundeep,

    Thierry has pointed you in the right direction. Here is a very quick demonstration, I will create 3 tables, each with 2 rows:
    CREATE TABLE T1 (
      COL1 NUMBER,
      COL2 NUMBER);
     
    CREATE TABLE T2 (
      COL1 NUMBER,
      COL2 NUMBER);
     
    CREATE TABLE T3 (
      COL1 NUMBER,
      COL2 NUMBER);
     
    INSERT INTO T1 VALUES(1, 100);
    INSERT INTO T1 VALUES(2, 101);
     
    INSERT INTO T2 VALUES(1, 1000);
    INSERT INTO T2 VALUES(2, 1001);
     
    INSERT INTO T3 VALUES(1000, 2000);
    INSERT INTO T3 VALUES(1001, 2001);
    Now, let's assume that I want to retrieve the rows from the tables, so I try something like this (after turning on AUTOTRACE to see the execution plan):
    SET AUTOTRACE ON
     
    SELECT
      *
    FROM
      T1,
      T2,
      T3
    WHERE
      T1.COL2 BETWEEN 100 AND 101
      AND T2.COL2 BETWEEN 1000 AND 1001
      AND T3.COL2 BETWEEN 2000 AND 2001;
    In the above, notice that I have restrictions specified for each of the tables, but I have not specified how the tables are related to one another. The query as written returned 8 rows:
          COL1       COL2       COL1       COL2       COL1       COL2
    ---------- ---------- ---------- ---------- ---------- ----------
             1        100          1       1000       1000       2000
             1        100          1       1000       1001       2001
             1        100          2       1001       1000       2000
             1        100          2       1001       1001       2001
             2        101          1       1000       1000       2000
             2        101          1       1000       1001       2001
             2        101          2       1001       1000       2000
             2        101          2       1001       1001       2001
     
    8 rows selected.
    And the execution plan looked like this:
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3820470131
     
    ------------------------------------------------------------------------------
    | Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      |     8 |   624 |     9   (0)| 00:00:01 |
    |   1 |  MERGE JOIN CARTESIAN |      |     8 |   624 |     9   (0)| 00:00:01 |
    |   2 |   MERGE JOIN CARTESIAN|      |     4 |   208 |     5   (0)| 00:00:01 |
    |*  3 |    TABLE ACCESS FULL  | T1   |     2 |    52 |     2   (0)| 00:00:01 |
    |   4 |    BUFFER SORT        |      |     2 |    52 |     3   (0)| 00:00:01 |
    |*  5 |     TABLE ACCESS FULL | T2   |     2 |    52 |     2   (0)| 00:00:01 |
    |   6 |   BUFFER SORT         |      |     2 |    52 |     8   (0)| 00:00:01 |
    |*  7 |    TABLE ACCESS FULL  | T3   |     2 |    52 |     1   (0)| 00:00:01 |
    ------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       3 - filter("T1"."COL2">=100 AND "T1"."COL2"<=101)
       5 - filter("T2"."COL2">=1000 AND "T2"."COL2"<=1001)
       7 - filter("T3"."COL2">=2000 AND "T3"."COL2"<=2001)
     
    Note
    -----
       - dynamic sampling used for this statement
    Notice in the execution plan that there are two MERGE JOIN CARTESIAN operations - that basically states that if there are 2 rows matching the restrictions on the first table, and 2 rows matching the restriction on the second and third tables, 2 * 2 * 2 = 8 rows will be produced. If each of the restrictions on the tables produced 1 row, or a Cartesian join appears between a table row source with a single row and another table with more than one row, that is not an indication of a problem.

    Now, let's fix the SQL statement to indicate how the tables are related:
    SELECT
      *
    FROM
      T1,
      T2,
      T3
    WHERE
      T1.COL2 BETWEEN 100 AND 101
      AND T2.COL2 BETWEEN 1000 AND 1001
      AND T3.COL2 BETWEEN 2000 AND 2001
      AND T1.COL1 = T2.COL1
      AND T2.COL2 = T3.COL1;
    The above change results in two rows:
          COL1       COL2       COL1       COL2       COL1       COL2
    ---------- ---------- ---------- ---------- ---------- ----------
             1        100          1       1000       1000       2000
             2        101          2       1001       1001       2001
    The execution plan produced looks like this:
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 261998084
     
    ----------------------------------------------------------------------------
    | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |      |     2 |   156 |     7  (15)| 00:00:01 |
    |*  1 |  HASH JOIN          |      |     2 |   156 |     7  (15)| 00:00:01 |
    |*  2 |   HASH JOIN         |      |     2 |   104 |     5  (20)| 00:00:01 |
    |*  3 |    TABLE ACCESS FULL| T1   |     2 |    52 |     2   (0)| 00:00:01 |
    |*  4 |    TABLE ACCESS FULL| T2   |     2 |    52 |     2   (0)| 00:00:01 |
    |*  5 |   TABLE ACCESS FULL | T3   |     2 |    52 |     2   (0)| 00:00:01 |
    ----------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - access("T2"."COL2"="T3"."COL1")
       2 - access("T1"."COL1"="T2"."COL1")
       3 - filter("T1"."COL2">=100 AND "T1"."COL2"<=101)
       4 - filter("T2"."COL2">=1000 AND "T2"."COL2"<=1001)
       5 - filter("T3"."COL2">=2000 AND "T3"."COL2"<=2001 AND
                  "T3"."COL1">=1000 AND "T3"."COL1"<=1001)
     
    Note
    -----
       - dynamic sampling used for this statement
    -----------------------

    If a column is defined as a DATE column, it is much better to treat it as a DATE column in WHERE clauses.

    If you want to do a little reading, take a look at a couple of articles that I wrote that will probably be helpful for you:
    http://hoopercharles.wordpress.com/2010/01/24/sql-basics-working-with-erp-data/
    http://hoopercharles.wordpress.com/2010/01/06/date-datatype-or-number-data-type-which-should-be-used/

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 7. Re: Query tuning
    704978 Newbie
    Currently Being Moderated
    Hi Charles,

    Thanks for the Example and I am clear with the issue. I have changed the query adding joining condition and i can see performance improvement in execution time.

    Will execute the entire SQL ad check if this would be helpful.

    Just to add, Nice articles in "http://hoopercharles.wordpress.com"

    Regards,
    Sundeep K

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points