Re: Oracle Performance

Hi,

don't know what's your understanding of 'key columns', but I'd put indexes on:

p.prod_code
f.prod_code
c.channel_code
f.channel_code
t.month 
f.month
g.geo_code
f.geo_code.

Try using RULE-based optimizer to force use of indexes and check performance. Try rearranging your statement :

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 
		t.month = f.month
	and 
		g.geo_code = f.geo_code
	and 
		c.channel_code = f.channel_code
	and 
		p.prod_code = f.prod_code
	group by 
		t.month, g.geo_desc, c.channel_desc,p.prod_desc,;

Maybe a subquery could improve performance:

select

	p.prod_desc, c.channel_desc, t.month, g.geo_desc, f.sales
	from 
		product p, channel c, time t, geography g, 
		(Select f.month, f.geo_code, f.channel_code, f.prod_code, sum(f.sales) sales 
			from 
				fact_table 
			group by
				f.month, f.geo_code, f.channel_code, f.prod_code) f
	where 
		t.month = f.month
	and 
		g.geo_code = f.geo_code
	and 
		c.channel_code = f.channel_code
	and 
		p.prod_code = f.prod_code
	group by 
		t.month, g.geo_desc, c.channel_desc,p.prod_desc,;

On 7 Apr 1998 02:13:31 GMT, eweisscns_at_aol.com (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

--

Regards

Matthias Gresz    :-)

GreMa_at_T-online.de