Tuning View

From: Dušan Pirc <Dussan.Pirc_at_mop.sigov.mail.si>
Date: 1996/12/17
Message-ID: <595rkf$sgf@sigov1.sigov.si>#1/1

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