order of column in primary index

Database

order of column in primary index

Hi,

Is the order of columns in Primary Index has an impact on sql qeries?

(Primary index has multiple columns)
2 REPLIES
Junior Contributor

Re: order of column in primary index

No.

It's a hash index, so you always need all PI-columns to calculate the hash value. That calculation is the same regardless of column order:

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

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

The order of columns within any index depends on the column order within the CREATE TABLE statement:

create table dummy1 (i int, c char) primary index(i,c);
show table dummy1;

CREATE SET TABLE TERADATA_EDUCATION.dummy1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
i INTEGER,
c CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( i ,c );

create table dummy2 (i int, c char) primary index(c,i);
show table dummy2;

CREATE SET TABLE TERADATA_EDUCATION.dummy2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
i INTEGER,
c CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( i ,c );

Dieter

Re: order of column in primary index

Thanks,
is this true for Secondary Indexes ,Primary Key as well?