Jonathan,
Thanks. I am able to get better performance running -- SELECT /*+ CHOOSE */ DISTINCT -- SELECT /*+ RULE */ DISTINCT -- SELECT /*+ index (prcd_instruction, prcd_instruction_pk) */ DISTINCT -- SELECT /*+ index (prcd_instruction_runsheet, prcd_instruction_runsheet_pk) */ DISTINCT -- SELECT /*+ index (part, part_pk) */ DISTINCT -- 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 SELECT /*+ ordered index (part part_pk) use_nl (part ) index (prcd PRCD_IK03) use_nl (prcd) index (PRCD_INSTRUCTION_RUNSHEET PRCD_INSTRUCTION_RUNSHEET_PK) use_nl(PRCD_INSTRUCTION_RUNSHEET) index (STAGE STAGE_PK) use_nl (STAGE) index (RECIPE RECIPE_NDX_1) use_nl (RECIPE) */ 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 PRCD_INSTRUCTION, PART, PRCD, PRCD_INSTRUCTION_RUNSHEET, STAGE, RECIPE 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 (RECIPE.RECIPE_ACTIVE_FLAG = 'A') ) AND ( (PRCD.PRCD_ID LIKE PRCD_INSTRUCTION.CALL_PRCD_NAME || '.%') AND (PRCD.PRCD_ACTIVE_FLAG = 'A') ) 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%' ) /
Also, yes, multiple index hints ARE working as SELECT /*+ index (part, part_pk) index (prcd, prcd_ik03) index (prcd_instruction_runsheet prcd_instruction_runsheet_pk) index (stage stage_pk) index (recipe recipe_ndx_1) */ DISTINCT
I had encountered an error message when trying multiple index hints earlier and I
cannot reproduce it now.
I couldn't find examples of multiple hints in the documentation and I came
across a Metalink Forum entry posting where Helene Schoone [whose advice I generally respect]
had stated "You cannot specify multiple tables in the index hint. "
I didn't catch that she would have meant a single hint but that it did not
exclude seperate hints !
Regards Hemant
Date: Wed, 19 Feb 2003 13:05:11 -0800 To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> X-Comment: Oracle RDBMS Community Forum X-Sender: "Jonathan Lewis" <[EMAIL PROTECTED]> Sender: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] From: "Jonathan Lewis" <[EMAIL PROTECTED]> Subject: Re: Optimizer help, get query to run as good as with RULE hint Organization: Fat City Network Services, San Diego, California
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]
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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).
