Re: Tuning 12 table join

On Tue, 19 Mar 2002 14:34:55 -0500, 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.

If your table is not in the OLTP database but in some kind of a reporting database (operational data store, data mart, data warehouse), you can put bitmap indexes on your fact table and detail table and use the star transformation. Your query sounds like a textbook example of star transformation. Beware, if the table is accessed by a large number of concurrent users, updating the index locks ALL THE RECORDS MAPPING TO THE SMAE BITMAP VALUE. As bitmap indexes are normally used on low cardinality columns, you can end up locking 90% of the table with a single update statement. That is not really good for concurrency and simultaneous access. If your table is not in a reporting database, get it there because OLTP database is no place for such abominations. For more about the star transformation, look in the documentation and on metalink.

-- 
Mladen Gogala