Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Tuning View

Re: Tuning View

From: janet <janet_at_telesph.com>
Date: 1996/12/17
Message-ID: <32B6BCA1.4EFC@telesph.com>#1/1

> create view view1
> as select ID, attr2, attr2, ... from table1, table2
> where KID1 = KID2;
>
> KID1 - first, smal local table
> KID2 - second, big remote table
>
> I woud like, that Oracle first find KID1 from ID (no time, index are
> made for KID1 and ID) and THEN query second table with KID1 (no time,
> index is made for KID2)....

Dusan, try:
create view view1
as select ID, attr2, attr2, ... from table2, table1 where KID2 = KID1;

HTH
janet
Dušan Pirc wrote:
>
> One small problem with Oracle Tuning.
>
> I have two tables in different databases. I need view which connects
> data and I have unique key for connection.
>
> I create view like this:
>
> create view view1
> as select ID, attr2, attr2, ... from table1, table2
> where KID1 = KID2;
>
> KID1 - first, smal local table
> KID2 - second, big remote table
>
> Aplication is using query:
> SELECT * from view1 where ID=one_key
>
> With explain plan I found out that Oracle first select row from BIG
> remote database and than compare with local.
>
> SELECT STATEMENT
> NESTED LOOPS
>
> REMOTE <---------
> AND-EQUAL
>
> INDEX RANGE SCAN table1_id
> INDEX RANGE SCAN table1_kid1
>
> OTHER_TAG by REMOTE : SERIAL_FROM_REMOTE
> OTHER by REMOTE : SELECT "Att1","att2" .... FROM "table2"
>
> I woud like, that Oracle first find KID1 from ID (no time, index are
> made for KID1 and ID) and THEN query second table with KID1 (no time,
> index is made for KID2)....
>
> How can I force Oracle to do that? I can change the definition of
> view, but how? I have made all kind of views, index....
>
> Thanks for your help...
>
> Dussan
>
> P.S. With Oracle "full scan" it takes 20 minuts, with "hand" it takes
> two sql sentence and "no" time...
>
> --------------------------------------------------------------------
> Ministry Of Environment And Physical Planning, GeoInformation Centre
> Zupanciceva cesta 6, 1000 Ljubljana, Slovenia
> email: Dussan.Pirc_at_mop.sigov.mail.si
> http://www.sigov.si:81
> tel: +386 61 178 5397, fax: +386 61 224 548
> --------------------------------------------------------------------
Received on Tue Dec 17 1996 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US