Re: Is it possible to link two big tables which are joined together frequently?

From: David Shi <dshi_at_magpage.com>
Date: 1998/01/04
Message-ID: <68o7dl$mar$0@204.179.92.79>#1/1

I haven't got anwser on this one, and later I thought about using rowid to link the two table, basically join the two big tables once and store rowid for each joined row into a table, then every time when the two table need to be joined again, use a PL/SQL code like in the following. I assume since rowid is the fastest way to access a row, this will be much faster than doing a join every time.

Is this appropriate? Is there any other way to improve speed?

Also besides

      select xxx
      from xxxx
      where a.rowid = my_rowid1 and
            b.rowid = my_rowid2;

Is it any other way to directly grab a row using rowid (even in Pro*C or OCI)?

Thanks a lot.

David

TESTING CODE:

---

/* this test using rowid to join two table together */

set serveroutput on;

DECLARE
   CURSOR c1 IS SELECT rowid1, rowid2 FROM dshi_join;    my_rowid1 ROWID;
   my_rowid2 ROWID;

   x1         number(10);
   x2         number(10);

BEGIN
   dbms_output.enable;
   OPEN c1;

   LOOP

      FETCH c1 INTO my_rowid1, my_rowid2;
      EXIT WHEN c1%NOTFOUND;

      select a.x, b.x
      into x1, x2
      from dshi_test a, dshi_test2 b
      where a.rowid = my_rowid1 and
            b.rowid = my_rowid2;

      dbms_output.put_line( x1 || ' ' || x2 );

   END LOOP;    CLOSE c1;
END;

---

On 17 Dec 1997 00:55:05 GMT, dshi_at_magpage.com (David Shi) wrote:

>Suppose you have two very large tables which you joined together
>frequently, the contents in the two tables do not change. Instead of
>sort and merge the two table every time to join the two, is it
>possible to have certain kind of link which once setup the first time,
>can be used each time later for the table join?
>
>I know cluster can help for such situations but I would like to avoid
>it.
>
>If the above is impossible, then if the key columns used to join in
>the two tables are sorted, will Oracle aware that and directly merge
>the two when joining the two (thus skip the expensive sorting part)?
>
>Thanks a lot.
>
>David
Received on Sun Jan 04 1998 - 00:00:00 CST