How about populating the nulls with a value (0, X, or something else that
has no meaning otherwise), and include this value in you lookup tables as
well, translating it to "None", or something similar, or even NULL.) You can
then avoid the Outer Join, and hopefully partake of your indexes.
"Dave" <dherri_at_acxiom.com> wrote in message
news:d31a72dc.0203191134.3458befc_at_posting.google.com...
> 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:54:30 CST
