 |
 |
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 |
Anomy.Anom wrote:
| | 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.
Dieter
| |
