Use of Oracle 11g hints to get desired execution Plan
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Use of Oracle 11g hints to get desired execution Plan

  1. #1
    Join Date
    Mar 2014
    Posts
    1

    Use of Oracle 11g hints to get desired execution Plan

    I am using oracle 11g hints to get desired plan for one of the query in TPCH benchmark. Following is the query and the desired plan.
    Query:
    explain plan for select
    s_acctbal, s_name, n_name, p_partkey,
    p_mfgr, s_address, s_phone, s_comment
    from
    part, supplier, partsupp, nation, region
    where
    p_partkey = ps_partkey
    and s_suppkey = ps_suppkey
    and p_size = 50
    and p_type like '%COPPER'
    and s_nationkey = n_nationkey
    and n_regionkey = r_regionkey
    and r_name = 'ASIA'
    and ps_supplycost = (
    select
    min(ps_supplycost)
    from
    partsupp,
    supplier,
    nation,
    region
    where
    p_partkey = ps_partkey
    and s_suppkey = ps_suppkey
    and s_nationkey = n_nationkey
    and n_regionkey = r_regionkey
    and r_name = 'ASIA'
    )
    order by
    s_acctbal desc, n_name, s_name, p_partkey;

    DESIRED PLAN:

    Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 19866 | 4074K| 199K (1)| 00:39:55 |
    | 1 | SORT ORDER BY | | 19866 | 4074K| 199K (1)| 00:39:55 |
    |* 2 | VIEW | VW_WIF_1 | 19866 | 4074K| 199K (1)| 00:39:55 |
    | 3 | WINDOW SORT | | 19866 | 5645K| 199K (1)| 00:39:55 |
    |* 4 | HASH JOIN | | 19866 | 5645K| 199K (1)| 00:39:55 |
    |* 5 | TABLE ACCESS FULL | REGION | 1 | 29 | 3 (0)| 00:00:01 |

    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |* 6 | HASH JOIN | | 99328 | 24M| 199K (1)| 00:39:55 |
    | 7 | TABLE ACCESS FULL | NATION | 25 | 800 | 3 (0)| 00:00:01 |
    |* 8 | HASH JOIN | | 99328 | 21M| 199K (1)| 00:39:55 |
    | 9 | NESTED LOOPS | | | | | |
    | 10 | NESTED LOOPS | | 99328 | 8342K| 192K (1)| 00:38:27 |
    |* 11 | TABLE ACCESS FULL | PART | 24615 | 1370K| 118K (1)| 00:23:43 |
    |* 12 | INDEX RANGE SCAN | PK_PARTSUPP | 4 | | 2 (0)| 00:00:01 |
    | 13 | TABLE ACCESS BY INDEX ROWID| PARTSUPP | 4 | 116 | 3 (0)| 00:00:01 |
    | 14 | TABLE ACCESS FULL | SUPPLIER | 1280K| 175M| 7314 (1)| 00:01:28 |
    --------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    2 - filter("VW_COL_9" IS NOT NULL)
    4 - access("N_REGIONKEY"="R_REGIONKEY")
    5 - filter("R_NAME"='ASIA')
    6 - access("S_NATIONKEY"="N_NATIONKEY")
    8 - access("S_SUPPKEY"="PS_SUPPKEY")
    11 - filter("P_SIZE"=50 AND "P_TYPE" LIKE '%COPPER' AND "P_TYPE" IS NOT NULL)
    12 - access("P_PARTKEY"="PS_PARTKEY")


    I tried using ORDERED hint in outer select but no use. Kindly help me in getting the desired plan. I am working on Query ERT prediction model. This need is part of building my model. In fact, I need good documentation on hints which can be used to get desired plan for any complex query (involving 10-12 joins) like reporting queries etc.

    I really appreciate any help in this regard.

    Warm Regards

    Rekha Singhal

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    You need more than a hint in this case. When you have a where clause such as column = ( query ),
    the query is run once for every row. You would be better off moving the subquery to part of the inner
    join rather than having it where you have it. Also you should use the 9i and above inner join rather than
    a comma delimited list of tables. using inner join makes it clear how each table is joined to the previous
    table. It makes it easier to see when you have a cartesean product. You should also alias the tables
    so that it is clear which column comes from which table.

    Finally, check all of the joins because I could have missed something.

    Code:
    SELECT s_acctbal,   s_name,    n_name,   p_partkey,
           p_mfgr,      s_address, s_phone,  s_comment
      FROM part
     INNER JOIN partsupp
        ON p_partkey     = ps_partkey
     INNER JOIN supplier
        ON ps_suppkey    = s_suppkey
     INNER JOIN nation
        ON s_nationkey   = n_nationkey
     INNER JOIN region
        ON n_regionkey   = r_regionkey
     INNER JOIN
        ( SELECT s_suppkey, ps_partkey, MIN(ps_supplycost) min_ps_supplycost
            FROM partsupp
           INNER JOIN supplier
              ON ps_partkey  = p_partkey
           INNER JOIN nation
              ON s_nationkey = n_nationkey
           INNER JOIN region
              ON n_regionkey = r_regionkey
           WHERE r_name      = 'ASIA' ) min_partsupp
        ON ps_suppkey = s_suppkey
       AND p_partkey  = min_partsupp.ps_partkey
    WHERE p_size      = 50
      AND p_type LIKE '%COPPER'
      AND r_name        = 'ASIA'
    ORDER BY s_acctbal DESC, n_name, s_name, p_partkey;
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width