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

In article <6777t9$ii4$0_at_204.179.92.82>, dshi_at_magpage.com (David Shi) writes:

>

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)?

You may want to look into foreign keys and views. The foreign key constraint will enforce the relationship between the tables and build the index you need. If you use a view to access the data from both of the tables, the join will be done the same way every time. The other advantage of the view is that if you still think that you're not getting the performance you want, just modify the view--not your application code. Since you're keeping the data in separate segments, versus combining them into a cluster, you can still access either table fairly efficiently. The only clusters I have on my system is within the SYSTEM table space--not in my applications!

--Bob. Received on Fri Jan 02 1998 - 00:00:00 CST