Archives of the TeradataForum

Message Posted: Thu, 26 Jun 2003 @ 13:40:21 GMT

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

I'd take a look at the requests accessing the tables using only the two columns you have in your PI. If you add a 3rd column and can't re-work these requests to add the 3rd column as well, the performance of these requests could go from instant to dreadful! If there are few of them and they're not critical, it may not matter.

Here're a couple of strategies you may want to consider:

- Leave your pi alone and define a usi (if possible) on the 3 columns. This may be a good compromise since when the 3 columns are available, the optimizer may chose the usi. Defining a nusi on the 3 columns probably won't be of much benefit.

- Add the 3rd column to your pi and define a ji hashed by the 2 original columns. I agree the space committment and the compromises you need to make with respect to ETL operations may make this un-attractive.

- If the third columns domain is small, say fewer than 10,000, consider creating a table containing a row for each unique value and join your base table to it on this 3rd column in your views. This will often lead to the following behavior:

- Duplicate the "small" table on all amps

- Local amp join between the duplicated spool using the two columns in the pi.

which can be pretty fast. You'll also get the added bonus that when you add the 3rd column to the predicate, it'll correspondingly reduce the size (and time it takes to duplicate and scan) of the spool for the duplicated table.

Pat Belle
Certified Master

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