NEEDED: BETTER JOIN PERFORMANCE ON BIG TABLE WITH BIG INDEXES (NESTED LOOPS AND MERGE JOINS)

Our application is working on a very large table (40M records), which needs to be joined with itself all the time. From those joins we never need more than 2 - 200.000 records. This table is used for online queries and also for the use of long running queries.

So we build a couple of indexes on the table to speed up the process of joining the table to itself.

Explaining the queries (using OR7.3.3.5 rule based) we see oracle is performing nested loops on all of those indexes. Nested loops is a record based join which will result in displaying first results faster than set based joins.
We tried to use merge_joins, but the Full Table Scan takes FAR TO MUCH time and like I said, it needs to be joined A LOT (4-6 times is no exception) with
itself. All those FTS's would consume way to much run time, disk time, cpu time, etc. even in parallel.

So my question to you out there is:

Is there any way, we can get Oracle to work performing nested loops based on the retrieval of all index values (set-based), rather than working record set
based (display first).

Why do I want this?
This would greatly improve our application, because the disks could keep on doing sequential reads, which is way faster than jumping all those nasty indexes one by one from the most inner index to the most outer index in the explain plan (this causes an awfull lot of diskhead movement), when eventually returning to the most inner join to get the next result.

Please help me

-John Received on Mon Jun 29 1998 - 17:17:34 CDT