Eweisscns wrote:
>
> Fellow Oracle Users
>
> If anyone out there can make a useful suggestion for a performance problem, I
> would appreciate it. We are working with a simple star schema, in which the
> fact table primary key is composed of prod_code, channel_code, month, and
> geo_code. The dimension tables contain:
>
> Product: prod_code, prod_desc
> Channel: channel_code, channel_desc
> Time: month, quarter, year
> Geography: geo_code, geo_desc, state, etc.
>
> The fact table contains about half a million rows. The largest of the
> dimension tables contains 100,000 rows. The following query runs in about 30
> seconds:
>
> select prod_code, channel_code, month, geo_code, sum(sales)
> from fact_table
> group by prod_code, channel_code, month, geo_code;
>
> On the other hand, this next query runs almost forever without returning a
> result:
>
> select p.prod_desc, c.channel_desc, t.month, g.geo_desc, sum(f.sales)
> from product p, channel c, time t, geography g, fact_table f
> where p.prod_code = f.prod_code
> and c.channel_code = f.channel_code
> and t.month = f.month
> and g.geo_code = f.geo_code
> group by p.prod_desc, c.channel_desc, t.month, g.geo_desc;
>
> All key columns are indexed, most using bitmap indexing. The fact table and
> all its indexes have been analyzed. The optimizer ignores the indexing whether
> the optimizer goal is set to Cost or First_Rows. Results are returned in a more
> reasonable time if the scope is narrowed, say by specifying one product or one
> geography. But the performance on an unrestricted query is much poorer than I
> am used to getting.
>
> We recently increased the memory allocations for sort space and disk cache, but
> I don't know if the amount is sufficient. I am not a DBA, but I'm saddled with
> the problem, so any suggestions would be helpful.
>
> Regards
>
> Eric
Hi,
Because your selecting the description field of each column and
doing a group by on those, SQL is doing a FULL table scan on each,
HASH joins, then the a final sort of your result. Even if you force
rule base I think the Optimizer will ignore.
Actually I would be more inclined to partition the Fact Table with the
similar dimensions you have on the supporting tables. But if you want
to try indexes, at least partition the indexes on the Fact table.
bob.
Received on Tue Apr 07 1998 - 00:00:00 CDT
