Archives of the TeradataForum
Message Posted: Wed, 01 May 2002 @ 20:59:32 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|