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

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 Received on Tue Sep 03 2002 - 18:10:19 CDT