| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Global hint in subquery in where clause doesn't work?
A further oddity - if you simply use FULL(v1) rather than the correct global hint, then the table does get a full scan.
If you've run the full explain plan on 9.2 you've probably noticed that the FILTER_PREDICATES column of the filtering row quotes the rewritten subquery without a hint in the global hint case - it looks like Oracle has simply lost it.
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminars UK Sept, Nov USA x 2 November http://www.jlcomp.demon.co.uk/seminar.html Richard Kuhler wrote in message ...Received on Wed Sep 04 2002 - 04:23:27 CDT
>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
>
 |
 |