Re: Global hint in subquery in where clause doesn't work?

Richard Kuhler <noone_at_nowhere.com> wrote in message news:<vjbd9.9342$MV5.3606466_at_twister.socal.rr.com>...
> I have a 'not exists' subquery that references a view. Oracle is
> selecting a poor index for this lookup. However, I cannot get Oracle to
> accept a hint in this case. I am almost certain I am using the correct
> syntax. Is this just a bug? Here is a demonstration of the problem ...
>
> Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
> Oracle9i Enterprise Edition Release 9.0.1.3.0 - Production
> Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
>
> create table t (id number)
> /
>
> create index i on t(id)
> /
>
> create view v as select * from t
> /
>
> set autotrace trace explain
>
> select *
> from v
> where id = 1
> /
>
> ... INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE)
>
>
> -- global hint works correctly at top level
>
> select /*+ full(v.t) */ *
> from v
> where id = 1
> /
>
> ... TABLE ACCESS (FULL) OF 'T' (Cost=1 Card=2 Bytes=26)
>
>
> -- global hint in subquery in where clause does not work
>
> select *
> from dual
> where not exists (
> select /*+ full(v.t) */ *
> from v
> where id = 1
> )
> /
>
> ... INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE) (Cost=1 Card=2 Bytes=26)
>
>
> -- hint in subquery in where clause is fine with table instead of view
>
> select *
> from dual
> where not exists (
> select /*+ full(t) */ *
> from t
> where id = 1
> )
> /
>
> ... TABLE ACCESS (FULL) OF 'T' (Cost=1 Card=2 Bytes=26)
>
>
> -- global hint works in subquery in the from clause correctly
>
> select *
> from (
> select /*+ full(v.t) */ *
> from v
> where id = 1
> )
> /
>
> ... TABLE ACCESS (FULL) OF 'T' (Cost=1 Card=2 Bytes=26)
>
>
> Richard

Richard, this is pretty much well known behavior of SQL with hints on views. Because of the way the optimizer handles code with views: merges SQL into the view, merge view into SQL, and finally if it is not possible to do one of the preceeding execute view and feed the result set to SQL, the hint can be basically lost. Normally you have to imbed the hint in the view and even then the hint may be lost if the CBO chooses a join plan that does not allow the hint to be applied. Example you provide an index hint but the optimizer chooses a hash join rendering the index useless. In a case like this you normally have to use the ORDERED and USE_NL hints along with the index hint to get the plan you want.

HTH -- Mark D Powell -- Received on Wed Sep 04 2002 - 07:49:57 CDT