Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Fwd: Re: Optimizer help, get query to run as good as with RULE

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

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Mon, 24 Feb 2003 06:03:52 -0800
Message-ID: <F001.00556871.20030224060352@fatcity.com> 






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 <ORACLE-L_at_fatcity.com>

>>X-Comment: Oracle RDBMS Community Forum

>>X-Sender: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>

>>Sender: root_at_fatcity.com

>>Reply-To: ORACLE-L_at_fatcity.com

>>From: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>

>>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 <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

>>





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

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 Mon Feb 24 2003 - 08:03:52 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US