Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Tuning 12 table join

Re: Tuning 12 table join

From: Steve Guilford <sguil4d_at_pacbell.net>
Date: Tue, 19 Mar 2002 20:27:09 GMT
Message-ID: <xaNl8.11192$BZ5.40645820@newssvr21.news.prodigy.com>


Wow, 12 tables huh ?

Write a function that accepts a value from your base table and returns your lookup values. Probably 1 for each lookup table. Then include calls to the functions in your select statement.

Steve Guilford...>

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.
>
Received on Tue Mar 19 2002 - 14:27:09 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US