Archives of the TeradataForum

Message Posted: Mon, 09 Apr 2001 @ 10:48:28 GMT

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

Dave,

We created 'KeyTables' to resolve our Star Schema Performace problems for large Star Schemas.

For instance, we took the 3 most commonly filtered columns from the atomic table and made it a NUPI. Next, we created a table containing just these 3 columns. Third, we created a view of the Atomic Table which joins the Atomic table to the 'Key Table.' This view selects the 3 Key Columns from the Key table, the rest of the columns are selected from the Atomic Table. To the user, this new view will look exactly like the Atomic table, and should be treated as such in regards to joining to the dimension tables.

The idea is as follows (assuming the size and row count of the cross-product of the 3 Key Columns tables is smaller than the Atomic table): the optimizer will choose to take a cross product of the 3 filtered Key Columns before it does anything else, then perform a merge join of the 'Cross Product Spool File' and the Atomic table; thus reducing the amount of data redistribution in later steps. The optimizer still has the option of filtering on another column if it will reduce the Atomic table to a smaller size than the 3 filtered Key Columns.

An important note, remove any NUSIs from the 3 Key Columns in the Atomic Table. Also, each of the 3 Key Columns need to be the UPI of their respective dimension tables.

Let me know how this works.

Clay

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