RE: ** view join query
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- 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
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.
------------------------------------------------------------------
Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html
------------------------------------------------------------------
-----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
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
BEGIN:VCARD
VERSION:2.1
N:de Haan;Lex
FN:Lex de Haan
ORG:Natural Join B.V.
TEL;WORK;VOICE:+31.30.2515022
TEL;HOME;VOICE:+31.30.2518795
TEL;CELL;VOICE:+31.62.2955714
TEL;WORK;FAX:+31.30.2523366
ADR;WORK:;;Pieter Breughelstraat 10;Utrecht;;3583 SK;Netherlands
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Pieter Breughelstraat 10=0D=0AUtrecht 3583
SK=0D=0ANetherlands
URL;WORK:http://www.naturaljoin.nl
EMAIL;PREF;INTERNET:lex.de.haan@xxxxxxxxxxxxxx
REV:20040224T160439Z
END:VCARD
Other related posts:
