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

John Vernes wrote:

> 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.
>
> ...

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

It all depends, but you could try bit-mapped indexes. These (contrary to popular

belief) work well even when the cardinality is not particularly high. Bit-mapped

indexes work well on large numbers of rows ( eg your 40 million) even if the index
is on a column where there there are a fairly large number of distinct values (in your case
if your join column has less than 10 million distict values then its worth a try.)

Bitmap index joins are effectively set operations - in fact bit-map AND/OR operations.
and may result in improved performance. But beware, if the table being queried has
a high update/delete/insert activity then the overhead of a bitmap index may not be
acceptable.

PS I think you'll need to run in cost-based optimizer to use bit-map indexes.

Simon. Received on Wed Jul 01 1998 - 16:32:27 CDT