| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: ** view join query
seems like you want "poor man's partitioning" here --
Oracle7 supported partitioned views, before the real partitioning was introduced
in release 8.0.
Indeed, not for free. Partitioned views are supported for backward
compatibility.
If you want more information, you should visit the Oracle7 documentation;
for example, check out http://download-uk.oracle.com/docs/pdf/A48506_1.pdf and
search for "partitioned view".
hope this helps, kind regards,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On
Behalf Of A Joshi
Sent: Thursday, June 16, 2005 21:04
To: oracle-l_at_freelists.org
Subject: ** view join query
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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jun 17 2005 - 06:42:02 CDT
 |
 |