Archives of the TeradataForum

Message Posted: Thu, 08 Mar 2001 @ 17:03:25 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

This has come from Lee Brown who is one of our key resources on the Merchandise MIS development I have mentioned in this group before. I am passing it on as he does not have access to the group from here currently:

Dave Sedgwick

+++

If anyone can offer any suggestions with the following it would be much appreciated...

We have a fact table with approx 20 million rows and 3 core dimensions, Time(Date), Product and Offer. When this table was first developed it had a PI of Product, however as this table has grown, i.e. taken on more historical data and used more, two things have emerged:

Of the 350,000 distinct Primary Index values, approx 60,000 had more than 100 duplicates (some had as many as 2000)

After an iterative report development process it became clear that the most common access path was actually Product and Date

It made sense therefore, to modify the PI accordingly. However, since this change we have been experiencing a number of spool space issues that we assume can only be attributed to the change in PI.

The report is relatively simple, requiring only Product Descriptions, Offer Descriptions, Time Periods and measures with a qualification on Product (All time is required, but not all products are in all offers)

Examining the 'explains' reveals...

When the PI is Product:
Average running time = 30s

Creates a Spool of Product details
Product Joins Time with Offers
Rowhash Merge Join - Product Spool with Fact table
Rowhash Merge Join - (Product Spool/Fact Table) with (Time/Offer)

When the PI is Product and Time
Always runs out of spool - even on 10Gig

Creates a Spool of Product details
Product Joins Offers with Product Spool
Product Joins Time with Fact
Rowhash Merge Join - (Offers/Product Spool) with (Time/Fact)

What I would have hoped for is a product join between Time and Product followed by a merge with the Fact.

extra Info: We have a secondary index of Offer Number on both versions of Fact table and statistics have been collected on all joining columns and indexes.

NCR4800
Teradata v2r3

Thanks in advance

	<Prev	Next>		<<First	<Prev	Next>	Last>>