** view join query

From: A Joshi <ajoshi977_at_yahoo.com>
Date: Thu, 16 Jun 2005 12:03:47 -0700 (PDT)
Message-ID: <20050616190347.21756.qmail@web60724.mail.yahoo.com>

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

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

Received on Thu Jun 16 2005 - 15:09:05 CDT