Re: Tuning 12 table join

Dave wrote:

> I have a base table (BASE) that is partitioned on a date column and
> has 250,000,000 rows. BASE has 12 code columns, each matching to a
> Lookup table (LKUP1-12). LKUP1-12 tables have the code column and a
> matching description. I have a view (VIEW_ALL) that joins BASE to
> LKUP1-12, to pull in the code column descriptions, instead of storing
> 12x50 byte fields across 250mm rows. The code columns may or may not
> be NULL, so each join in VIEW_ALL is an outer-join.
>
> My problem is VIEW_ALL is insanely slow. An explain plan shows a "hash
> outer join" for each lookup back to the driving table, BASE. This
> means 12 HJs! If I create a unique index on all the LKUP1-12 tables, I
> get a plan using "nested loops outer" for all 12 tables, again
> scanning BASE 12 times, once per join.
>
> Is there any way to join all 12 at once, scanning BASE only once? I
> thought the AND_EQUAL hint would work, listing the indexes from
> LKUP1-12 but it was ignored.
>

At a presentation at IOUG-A 2001, someone mentioned loading your lookup tables into package variables (indexed) so that you could use a function to return the values, without using gets. Sounds interesting.

I don't have my conference CD here, but his name is Kevin Toepke, and he belongs to the Ohio Oracle User's Group in Columbus. Check out http://www.ooug.org for contact info. It should also be available through the ioug website.

hth,

Paul Received on Tue Mar 19 2002 - 23:52:23 CST