Ordering of Columns in Primary Index

Database
Enthusiast

Ordering of Columns in Primary Index

Hi

Can anyone suggest how the ordering of columns in Primary Index relates to performance.
Suppose i have table A with PI as (Col_a,Col_b) and table B with PI as (Col_b,Col_a) and table C again with PI as (Col_a,Col_b)

Then how the ordering of the columns will change the performance if i join
table
1) A with B on PI
and
2) A with C on PI.

Thanks
1 REPLY
Senior Apprentice

Re: Ordering of Columns in Primary Index

The data distribution/hashing algorithm is insensitive to the order of columns, so Performance will not change, because those joins are still AMP-local:

select hashrow('a',1), hashrow(1,'a');

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.

HASHROW('a',1) HASHROW(1,'a')
-------------- --------------
8ABD46C9 8ABD46C9

Dieter