Tuning 12 table join

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 - 13:34:55 CST