Archives of the TeradataForum
Message Posted: Mon, 20 Mar 2006 @ 23:36:26 GMT
| Subj: | | Re: Performance issue on a huge table.. |
| |
| From: | | Stover, Terry |
218 columns sounds like a seriously denormalized model, you may want to reconsider your physical design. Sounds like one of those SAS
monstrosities to me.
You could try a value ordered NUSI on c1, their main purpose is to make the BETWEEN predicates run faster. I never tried a Value Ordered NUSI
on a UPI before though. Do you know what percentage of the rows the BETWEEN is returning? If it's significant you may not be able to do a whole
lot about the performance.
It would help to select only the needed columns instead of using select *, all those compressed columns are going to uncompress in spool.
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%.
| 
