RE: ** view join query

  • From: "Lex de Haan" <>
  • To: <ajoshi977@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 17 Jun 2005 12:36:51 +0200

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
If you want more information, you should visit the Oracle7 documentation;
for example, check out and
search for "partitioned view".

hope this helps, kind regards,

Steve Adams Seminar

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of A Joshi
Sent: Thursday, June 16, 2005 21:04
To: oracle-l@xxxxxxxxxxxxx
Subject: ** view join query 

   I have a view xview which joins union all three tables. Oracle version : All of them have a primary key which is selected as xview_id in the
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.
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 

N:de Haan;Lex
FN:Lex de Haan
ORG:Natural Join B.V.
ADR;WORK:;;Pieter Breughelstraat 10;Utrecht;;3583 SK;Netherlands
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Pieter Breughelstraat 10=0D=0AUtrecht 3583 

Other related posts: