** view join query

  • From: A Joshi <ajoshi977@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 16 Jun 2005 12:03:47 -0700 (PDT)

Hi,
   I have a view xview which joins union all three tables. Oracle version : 
8.1.7.4. All of them have a primary key which is selected as xview_id in the 
view. 
If I have a query : select xview_id  from xview where xview_id = 1 then no 
problem it does use the respective primary keys for the tables.
 
For query : select *  from xview where xview_id = 1 then it goes to primary key 
and then to the table for one table. For other tables it goes full table scan. 
Strange. 
Then if I have select xview_id  from xview where xview_id = 1 and xdept = 'xx' 
then it goes full table scan for all of the tables. 
 
then if I have select x.xview_id,y.ydept  from xview x, ytab y where xview_id = 
y.ytab_id and y.dept = 'yy' then it does full table scan of xview all. I tried 
giving ordered hint and other hints so it goes to table ytab first and then 
goes to xview. Even so it does not use the index for xview_id. 
 
Is it easy to make it use the unique indexes. I cannot change queries to use 
base tables. It is dynamically determined which table it goes to. Thanks 
__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

--
//www.freelists.org/webpage/oracle-l

Other related posts: