 |
 |
Archives of the TeradataForum
Message Posted: Tue, 21 Mar 2006 @ 10:34:40 GMT
| Subj: | | Re: Performance issue on a huge table.. |
| |
| From: | | Victor Sokovin |
| | A value ordered single table join index might work too. In the past I had one very wide table (almost 1kbyte/row) that had a 5 column UPI.
We created a separate table of just the UPI columns (which is essentially a single table join index), all the joins that applied the various
filters ran against the narrow UPI table, it would join back to the wide table on the last step. Getting rid of all the large spool file sorts
& redistributions for joins cut the query time by 90%. | |
Terry, this is a very interesting experience. Thank you for sharing it. It would probably not occur to me to try this approach. I thought
single table SI were only useful when we want to have an "alternative PI", an alternative fast access path to the same data. If they are useful
even on the same PI, I will start appreciating JI even more. Having said that, I would probably tend to think that we do not "need" to do things
like that, the optimizer should probably select the appropriate PI values before copying the data to the spool file. Don't know whether you agree
with this. Anyway, it is good to be aware of this tuning possibility.
Regards,
Victor
| |
