Archives of the TeradataForum

Message Posted: Wed, 01 May 2002 @ 20:59:32 GMT

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

I'll just provide a short review since I don't have much information on you application or usage requirements.

First remember that keys are not equal to indexes. As a result, you can implement different indexes depending on the access required by the end users and performance tuning approaches learned for Teradata. I usually make the PI of a table with a multi-part key a NUPI and only include the often used columns from the key. I also often leave date out of the PI.

However, this assumes that there is enough uniqueness in the NUPI to 1) provide even distribution and 2) not cause a lot of duplicate row checking on loads i.e. NUPI duplicate values < 1 data block worth of rows or about 1300 rows at 50 bytes each (no compression) if using a set table. Nearly unlimited using a multi-set table, but there is a need for a process to avoid duplicate rows.

This design uses a Large Table / Small Table optimization (Star-Join) approach, although the design also would support a ST / LT (hash join) approach as well.

For each PI column in the NUPI, you will need a small reference (hierarchy table) with a UPI on the column(s) included in the large tables NUPI. I also advise lots of secondary indexes on the small tables for further qualification of the result. This approach assumes that the user query (or a view) includes a join for each of the small tables in the NUPI.

Also, I assume that you are using a view or a query tool that easily enables the inclusion of the extra reference tables in the SQL.

Finally, I assume that you are referring to a somewhat summarized table. Although most of the discussion on choosing a NUPI still applies to detail tables, the optimizations and thus join strategies may not apply.

More information on requirements would help me make these differentiations more clear.

Thanks,

Eric

EJK
Eric J. Kohut
Senior Solutions Consultant - Teradata Solutions Group - Retail
Certified Teradata Master
NCR Corp.

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