Re: Optimizer help, get query to run as good as with RULE hint

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 19 Feb 2003 13:05:11 -0800
Message-ID: <F001.0055202C.20030219130511@fatcity.com>

Can you clarify what you mean by:

    ">I find that I cannot specify multiple Index Hints."

Just for the sake of checking a point,
arrange the tables in the from clause
in the order indicated by the RULE path, viz:

    PRCD_INSTRUCTION
    PART
    PRCD
    PRCD_INSTRUCTION_RUNSHEET
    STAGE
    RECIPE (NB Your plan seems to have displayed the odd order switch on table RECIPE due to
v9 table prefetching - which is odd because I had heard it was a cost-based thing).

Then put in the ORDERED hint, along with a hint for each table to use the index that appears for that table, with a USE_NL hint viz:

    /*+

        ordered
        index(part PART_PK)
        use_nl(part)
        index(PRCD PRCD_IK03)
        use_nl(prcd)
                ... etc ...

    */

This should give you exactly the same access path as the rule path.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 18 February 2003 04:32
hint

>Mark,
>
>Here's the query in expl_PRCD.sql
>
>delete plan_table where statement_id ='PRCD_H';
>explain plan
>set statement_id='PRCD_H'
>for
>SELECT /*+ CHOOSE */ DISTINCT
>-- SELECT /*+ RULE */ DISTINCT
>-- SELECT /*+ index (prcd_instruction, prcd_instruction_pk) */
DISTINCT
>-- SELECT /*+ index (prcd_instruction_runsheet,
prcd_instruction_runsheet_pk) *T
>-- SELECT /*+ index (part, part_pk) */ DISTINCT
>--SELECT /*+ ordered index (part, part_pk) use_nl (part
prcd_instruction) */ DIT
>-- SELECT /*+ index (stage, stage_pk) */ DISTINCT
>-- SELECT /*+ use_nl (prcd_instruction) */ DISTINCT
>-- SELECT /*+ use_nl (part) */ DISTINCT
>-- SELECT /*+ full(prcd_instruction) parallel(prcd_instruction, 4) */
DISTINCT
> PRCD_INSTRUCTION_RUNSHEET.STAGE_NAME,
> PRCD_INSTRUCTION_RUNSHEET.RECIPE_NAME,
> PRCD_INSTRUCTION_RUNSHEET.RECIPE_ORDER,
> PRCD_INSTRUCTION_RUNSHEET.PRCD_ID,
> PRCD_INSTRUCTION_RUNSHEET.RECIPE_STAGE,
> PRCD_INSTRUCTION_RUNSHEET.INSTRUCTION_NUMBER,
> RECIPE.RECIPE_TITLE,
> PART.PART_NAME,
> RECIPE.EQP_TYPE,
> PRCD_INSTRUCTION_RUNSHEET.STAGE_ORDER,
> STAGE.STAGE_DESC,
> TO_NUMBER (STAGE.MATCH_ORDER),
> DECODE (STAGE.MATCH_ORDER, STAGE.STAGE_SORT_ORDER, 'N', 'Y'),
> PRCD.PRCD_TITLE
>FROM
> PRCD_INSTRUCTION_RUNSHEET,
> RECIPE,
> PART,
> STAGE,
> PRCD,
> PRCD_INSTRUCTION
>WHERE
> ( PART.PART_ID = PRCD_INSTRUCTION.PRCD_ID )
> AND ( STAGE.STAGE_ID = PRCD_INSTRUCTION_RUNSHEET.RECIPE_STAGE )
> AND ( PRCD.PRCD_ID = PRCD_INSTRUCTION_RUNSHEET.PRCD_ID )
> AND ( (RECIPE.RECIPE_NAME = PRCD_INSTRUCTION_RUNSHEET.RECIPE_NAME)
AND (RECI)
> AND ( (PRCD.PRCD_ID LIKE PRCD_INSTRUCTION.CALL_PRCD_NAME || '.%')
AND (PRCD.)
> AND (
> ( PRCD_INSTRUCTION_RUNSHEET.HIERARCHICAL = 'Y' )
> AND ( PRCD.PRCD_ACTIVE_FLAG = 'A' )
> AND ( PART.PART_ACTIVE_FLAG = 'A' )
> AND ( PART.OBSELETE_FLAG <> 'Y' )
> AND PRCD_INSTRUCTION_RUNSHEET.PRCD_ID NOT LIKE 'F-%'
> AND PRCD_INSTRUCTION_RUNSHEET.PRCD_ID NOT LIKE 'L000%'
> AND PRCD_INSTRUCTION_RUNSHEET.PRCD_ID NOT LIKE 'PCW%'
> )
>/
>
>As you can see, I've even tried Index Hints.
>
>The RULE hint gives me the best performance and usage of
>indexes:
>
>Operation Options Object ID PID
Pos
>------------------------- ---------- ---------------------- ---- ----

---

>SELECT STATEMENT 0
>SORT UNIQUE 1 0
1
>TABLE ACCESS BY INDEX R RECIPE 2 1
1
>NESTED LOOPS 3 2
1
>NESTED LOOPS 4 3
1
>NESTED LOOPS 5 4
1
>NESTED LOOPS 6 5
1
>NESTED LOOPS 7 6
1
>TABLE ACCESS FULL PRCD_INSTRUCTION 8 7
1
>TABLE ACCESS BY INDEX R PART 9 7
2
>INDEX UNIQUE SCA PART_PK 10 9
1
>TABLE ACCESS BY INDEX R PRCD 11 6
2
>INDEX RANGE SCAN PRCD_IK03 12 11
1
>TABLE ACCESS BY INDEX R PRCD_INSTRUCTION_RUNSH 13 5
2
> EET
>
>INDEX RANGE SCAN PRCD_INSTRUCTION_RUNSH 14 13
1
> EET_PK
>
>TABLE ACCESS BY INDEX R STAGE 15 4
2
>INDEX UNIQUE SCA STAGE_PK 16 15
1
>INDEX RANGE SCAN RECIPE_NDX_1 17 3
2
>
>18 rows selected.
>
>' ********* ---- Press ENTER for Nested Query Plan ----
******'
>
>
>Query Plan
>---------------------------------------------------------------------

---

>SELECT STATEMENT Cost =
> SORT UNIQUE
> TABLE ACCESS BY INDEX ROWID RECIPE
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> TABLE ACCESS FULL PRCD_INSTRUCTION
> TABLE ACCESS BY INDEX ROWID PART
> INDEX UNIQUE SCAN PART_PK
> TABLE ACCESS BY INDEX ROWID PRCD
> INDEX RANGE SCAN PRCD_IK03
> TABLE ACCESS BY INDEX ROWID PRCD_INSTRUCTION_RUNSHEET
> INDEX RANGE SCAN PRCD_INSTRUCTION_RUNSHEET_PK
> TABLE ACCESS BY INDEX ROWID STAGE
> INDEX UNIQUE SCAN STAGE_PK
> INDEX RANGE SCAN RECIPE_NDX_1
>
>18 rows selected.
>
>
>Better hints would be appreciated.
>I find that I cannot specify multiple Index Hints.
>Hemant

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Feb 19 2003 - 15:05:11 CST