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

John,

The answer may or may not be in making the multi-joins of this massive table faster. One approach might be to use the common driving constants from the requestors. What I mean by that is to go after an initial smaller set of the big table by using index(es) - then join the smaller sets of data rather than have Oracle do massive recursive joins (which I have yet to find run quickly).

This will only work if your users can provide constants that can effectively use indexes. Even though the purists scoff at temporary tables, I have been extremely successful at cutting a lot of time from queries and selects that use several large tables by using them.

Again, without knowing more of your application, this may or may not be helpful. Good luck.

Steve
In article <6n93ul$rac$1_at_news.worldonline.nl>,   "John Vernes" <vernes.j_at_consunet.nl> 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.
>
> 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
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Jul 02 1998 - 22:31:30 CDT