Archives of the TeradataForum

Message Posted: Mon, 28 Oct 2003 @ 02:15:24 GMT

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

I know I'm a little late to this thread, but my experience has been that one should always collect stats on the primary index of a table, regardless of size. So I would collect stats on the PIs of your large and small table. This is recommended pretty clearly in the Performance Optimization Guide (both V2R4 and V2R5).

Also, unless you will be identifying specific values of Cust_cd and Order_cd, I don't think I would bother with a secondary index on the small table, although it probably wouldn't hurt. And of course, if you keep the NUSI, collect stats on it.

To really understand the optimizer's choices, we would need to better understand the data demographics of the join columns and the nature of the query. It would also be interesting to see the EXPLAIN.

Finally, in regards to an earlier comment about NUSI's on small tables, my experience is that they are often extremely valuable in large table-small table joins, particularly if the small tables, when joined together, make up the columns of the primary index as in a star schema design. In these situations, the optimizer will take advantage of the NUSI to quickly create a spool of PI values which can then be used to "RowHash Match Scan" into the large table using a merge join. This is typically a very efficient strategy.

Hope this helps.

Thomas F. Stanek
TFS Consulting, Inc.

www.tfsconsulting.com

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