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_at_uchicago.edu> 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_numberand
> 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
www.cnoug.org

--
http://www.freelists.org/webpage/oracle-l