Archives of the TeradataForum
Message Posted: Thu, 07 Feb 2008 @ 09:27:52 GMT
| Subj: || || Re: Dealing with concentrated values in the data |
| From: || || Dieter Noeth |
| ||We have some tables that have concentrated values within the NUPI.|| |
| ||CASE 1: 10M row table; 1M rows with modal value Normal_Tuesday|| |
| ||CASE 2: 10M row table; 100K rows with modal value FAT_Tuesday|| |
| ||CASE 3: 10M row table; 10K rows with modal value Super_Tuesday|| |
| ||CASE 4; 10M row table; 1k have the with modal value Tuesday|| |
| ||Row lengths are 125 bytes, blocking is at 128K, so ~1000 rows per block.|| |
| ||What is the performance impact on loads in the cases?|| |
Basic condition: table must be MULTISET.
Forget about case 4.
Case 1 & 2 (& 3): There will be hardly any impact for inserts, but deletes and updates will suffer, if restricted using the PK or just a
few rows, because the file system has to locate the matching rows searching all the datablocks.
| ||What is the performance impact on queries that target the modal value?|| |
Fast, if you really request all the rows, but the same as Updates/Deletes above if just a few rows.
| ||What is the performance hit on table scans with residual conditions involving joins on the PI to another table with|| |
I'd suggest to run some tests, might be slower, especially if the table is heavily skewed.