Archives of the TeradataForum

Message Posted: Tue, 17 Sep 2002 @ 12:27:00 GMT

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

Kumaran,

I have an approach we are using here which might prove helpful. Assuming the user is selecting from a prompt which represents the Primary Index of a table:

1) Create a table of the distinct values of the primary index (best case a NUPI; however, beneficial for UPIs also) - called TableA

2) Create a View of the original table joined to TableA via the Primary Index. Within the view definition, the Primary Index should be selected from TableA, all other columns should come from the original table.

3) Point the BO report against the new view instead of the original table

In this way, the full table scan will occur against TableA, which at a minimum will have fewer columns (resulting in a faster full table scan), in the best case it will have fewer records (if the Primary Index of the original table is non-unique.) Of course, you have the additional cost of joining TableA to the original table; however, since this is an AMP local merge join, the costs should be minimal.

If the user is selecting from a column which doesn't represent the primary index of a table, I would still explore the above approach. TableA should be smallest enough for the optimizer to duplicate it to all AMPs* then join it to the original table view a quick hash join. I don't know if (in your case) this would be quicker than a full table scan of the original table, but it is worth a shot.

Let me know if this helps,

Clay

* note, if you feel the optimizer should be duplicating the table to all AMPS and it is not, make sure you have stats the column joining TableA to the original table. If you are joining TableA to the original table on mulitple columns (and it isn't the PI), then either create a NUSI on the original table's join columns or create a fake PI of '1' for each record in TableA

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