Archives of the TeradataForum

Message Posted: Mon, 26 Nov 2001 @ 15:13:46 GMT

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

All,

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?

Thanks,

Claybourne Barrineau

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