hints when view involved and 9.2 Oracle

From: GG <grzegorzof_at_interia.pl>
Date: Fri, 30 Mar 2012 17:50:42 +0200
Message-ID: <4F75D652.3060801_at_interia.pl>

Hi,
  I'm dealing with nasty app with views on views on views and Oracle 9.2.0.6 :).
Trying to figure out best way to hint when view is involved in case which can be simplified
  as below (brain dump so some syntax glich provided :)

create table t1 as select rownum id , filler , sysdate - rownum test_date from dual connect by level <= 1000000 ; create table t2 as select rownum id , filler , sysdate - rownum test_date from dual connect by level <= 100000 ; create table t3 as select rownum id , filler , sysdate - rownum test_date from dual connect by level <= 100 ;

create index t1_test_date on t1(test_date);
-- gather stats

create view t12... as select t1.id t12_id , t1.test_date t12_test_date , t2.test_date from t1 , t2 where t1.id = t2.id ;

now , query like that:

select * from t12 , t3 where t3.id = t12.t12_id and t12.t12_test_date = sysdate - 1 ;

so best plan is too use index t1_test_date, but what if somehow Oracle doesnt want to use that index and I have to hint it Whats the proper way to hinting index usage in such situations ? I came to conclusion like that:

--create t12 view with hint

create view t12... as select /*+ index(t1 t1_test_date) */ t1.id t12_id , t1.test_date t12_test_date , t2.test_date from t1 , t2 where t1.id = t2.id ;
--its useless now but should work for query above with t3 join, right ?

what about hinting in queries based on that view: select /*+ HOW_TO_HINT_INDEX_ON_T1 */ * from t12 , t3 where t3.id = v12.t12_id and t12.t12_test_date = sysdate - 1 ;

I mean is there any way to do so in 9.2.0.6 ?

Any comments ?
Regards
GregG

--

http://www.freelists.org/webpage/oracle-l Received on Fri Mar 30 2012 - 10:50:42 CDT