Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Global hint in subquery in where clause doesn't work?

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

From: Richard Kuhler <noone_at_nowhere.com>
Date: Tue, 03 Sep 2002 23:10:19 GMT
Message-ID: <vjbd9.9342$MV5.3606466@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)

select /*+ full(v.t) */ *
from v
where id = 1
/

... TABLE ACCESS (FULL) OF 'T' (Cost=1 Card=2 Bytes=26)

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)

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)

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US