Archives of the TeradataForum

Message Posted: Tue, 28 Mar 2006 @ 13:35:54 GMT

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

Anomy,

Unless you are able to use constraints in order to horizontally partition these 2 fact tables, I think you will have a hard time getting the NUSI to be used by the optimizer. The optimizer doesn't know that the data is mutually exclusive; therefore, it has do union the information together before joining to the dimension tables. Once these objects are 'unioned' together, the stats are thrown out the window; therefore, the optimizer will not use the NUSI without good stats.

Maybe horizontal partitioning will work for you, if not, this looks like a good candidate for 2 Sparse Join Indexes. You could load all of the data into the same physical table. Then, based upon a 'where' condition that physically seperates your 2 sets of data, you can create 2 sparse join indexes each with their own set of NUSIs...of course, this assumes that all queries accessing this data contain the necessary filters to specify which Sparse JI to use....if not, you will wind up doing full-table scans.

Hope this helps,

Clay

Claybourne L. Barrineau
Senior Application DBA
Coca-Cola Enterprises, Inc.

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