Re: Tuning 12 table join

The shop I just joined regularly uses some large joins (20 to 30 tables) for reporting--some of the tables get up to 1 to 3 million rows with the larger customers. This is an OLAP db. Larger customers are running into problems with slow reporting..

I read that joining too many tables reflects a data model design that needs rethinking. I'd like pointers to any information (or just opinions) on:

• Techniques to optimize the data model
• Strategies for use of a reporting database or related
• Strategies for use of materialized views, etc.

The main complaint here with materialized views is that we allow extensive reporting criteria (report on a specific date range in last October, for example). Although I suspect customers really only want reports such as "all inventory shipped yesterday from warehouse 1", "all inventory shipped last month from all warehouses", etc.

Thanks in advance.

--steve

"Mladen Gogala" <mgogala_at_adelphia.net> wrote in message news:a79fe1$jnfoj$1_at_ID-82084.news.dfncis.de...
> 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
Received on Wed Mar 20 2002 - 11:05:07 CST