Archives of the TeradataForum

Message Posted: Tue, 12 Nov 2002 @ 15:37:06 GMT

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

Mathew,

There are a few ways of going about solving this issue, but I'm afraid that Soft RI will not be part of the solution.

I believe that Soft RI was incorporated in the Optimizer to eliminate joins (for instance, if your SQL generator always specified joins to all 4 Dim tables and you only needed Dimensional detail from 2 of the Dim tables.) I'm pretty certain that it was not intended to 'add' joins by pulling non-selected objects into cross-product joins in hopes of accessing your fact table's PI.

Performing cross-products of the filtered dim tables prior to accessing the Atomic table is a fast way to get at your data; however, if this cross-product of your filtered dim tables is larger than your fact table, the optimizer will probably redistribute your 200 million fact table to all AMPs based upon the PI of the cross-prodcut dim table's spool file.

For me, this is bad.

Depending on the subject area and related data demographics, we have mulitple approaches to optimizing Star Schema performance. We can alter the PI of the Atomic table, use key tables hidden in views, or even alter the SQL generator itself. I'd be happy to help, so if your interested, just let me know your answers to the following questions.

1) What tool are you using to access this Star Schema?

2) Which of the dimension tables are used most often?

3) What percentage of the atomic table is being retrieved on average per incoming query?

4) If using an OLAP Tool, when the user chooses information from the lowest level of a dimension, does the SQL generator pull this data from the Atomic or Dimension table?

Thanks,

Claybourne Barrineau

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