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 <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
