Archives of the TeradataForum

Message Posted: Sat, 09 Mar 2002 @ 18:38:53 GMT

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

We performed an upgrade a little under a year ago from V2R3 to V2R4. Many of our organizations got hit pretty hard with spool space issues as a result of the upgrade and the changes in the optimizer. We didn't find one single "clear cut" solution to fixing the issues either. Reexamining the columns that we had stats collected on helped out somewhat. Not only does collecting stats on join columns and conditional columns (non-join columns in your WHERE clause) help, but we found that it is occasionally beneficial to drop stats on columns. In order to determine whether or not this is necessary, keep an eye on the row counts in the explain. If the row count estimates start dropping a lot more in the explain than they do in reality, it may be worthwhile to try dropping stats on a couple of the columns to see if the counts improve and the explain changes favorably. Granted, dropping stats is the exception to the rule, but it may be worth looking into.

The other thing that really helped was to create secondary indexes. Prior to V2R4, we found that Teradata didn't use them very frequently and it didn't benefit from having stats collected on the combination of columns involved in the index, so adding secondary indexes was kind of an exception to the rule for us. However, it appears that the optimizer got a whole lot smarter about secondary indexes in V2R4. I would highly recommend reexamining the tables involved in the queries you're having problems with to see if adding a secondary index (or two) helps. We found that in V2R4, even if Teradata doesn't use the secondary index to retrieve the rows, it gets a whole lot smarter about the counts and generally forms a better join plan.

We just recently performed the upgrade from V2R4 to V2R4.1. Overall, this upgrade seemed to go fairly smoothly for us. However, we have gotten into the habit of keeping explain plans from the prior release and comparing them after the upgrade. We did notice some changes in the explain plans, but the changes weren't nearly as drastic as they were from the V2R4 upgrade and they didn't cause many problems.

Dan Nolan

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