Archives of the TeradataForum
Message Posted: Mon, 26 Nov 2001 @ 15:13:46 GMT
I have 2 tables that I need to join together on the one column they have in common. When I choose this column (Column A) to be the NUPI for these 2 tables, the query runs as a merge join in about 1.5 hours. Unfortunately, Column A is very unevenly distributed, and I have some amps working much harder (~200%) than other amps.
Using the same SQL, as a test, I changed the primary indexes of these 2 tables in a way which "encouraged" the Optimizer to perform an AMP Local Hash Join of the resulting partitions of the larger table to the duplicated smaller table. This query ran about 20% faster, and the query was nearly perfectly distributed across the amps. Total CPU Seconds were slightly higher for this new query.
Pros of altering the PIs:
- Even distribution of each Amps Spool and CPU usage (...resulting in greater overall throughput for the box)
- 20% faster
Cons of altering the PIs:
- Illogical PIs
- Current method of "Encouragment" for the Optimizer might not work in future releases of the RDBMS
- Slighly Higher CPU Seconds
Does anyone have strong Opionons regarding this type of "encouragement" for the optimizer?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|