RE: rule hint ignored?

also, if the underlying object(s) have a nonzero parallel degree; the rule hint 
can be ignored...

-----------------------------------------------------------------------------------------------------------------------------------------------------
Bruce McCartney |DBIS |É403 615 3350 | bruce.mccartney@xxxxxxxxxxxxxxxxx



From: zhu chao
Sent: Wed 2/8/2006 10:19 AM
To: mschmitt@xxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: rule hint ignored?


In some cases, rule hint will not work, like partition table/iot etc.

Could it be your table has different DDL script in TEST and DEV/Prod?  Like in 
TEST someone changed one of the tables to IOT/Partition  etc?

 
On 2/9/06, Mike Schmitt <mschmitt@xxxxxxxxxxxx> wrote: 

Hi all,

We have the same query using the /*+ rule */ hint in DEV, TST, and PRD.  In our 
TST environment in looks like the plan is being generated by the cost based 
optimizer instead. 

Part of explain plan from TEST:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE (Cost=5207 Card=2031 B
          ytes=89364) 
   1    0   SORT (UNIQUE) (Cost=5207 Card=2031 Bytes=89364)
   2    1     HASH JOIN (Cost=5190 Card=2031 Bytes=89364)
 .......................

Part of explain plan from DEV, PRD
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   SORT (UNIQUE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESS'
   3    2       NESTED LOOPS 
........................


The DEV and TST boxes were both created from copies of production a couple of 
months ago, and the data is kept partly in sync.  The data differs in each 
instance by at most 5%, and stats are all up to date. Databases at 9206 and all 
three have pretty much the same init.ora (except open_cursors, sga, buffers etc)

Since this is the same exact query (cut an pasted), does anyone have any 
suggestions on what might be causing the TST system to ignore the rule hint? 
(BTW, the RBO does give the better results, just not sure why rule is ignored 
in one instance and used in the other two) 

QUERY: 
select /*+ rule */ distinct ent.id_number from entity ent ,address adr ,degrees 
deg where (ent.delete_ind = 'N' and adr.id_number = ent.id_number and 
deg.id_number = ent.id_number ) and ( ent.record_status_code = 'M' and 
adr.state_code = 'LA' and deg.school_code = 'JYM' )

parallel query and query rewrite are turned off for all three instances too

Thanks




 






-- 
Regards
Zhu Chao
http://www.cnoug.org/

Other related posts: