Does Column Order in an Index Matter?

Database
Enthusiast

Does Column Order in an Index Matter?

I may be slow on the uptake, but I just can't seem to completely shake this question:

I have a table something like the following :

TABLE_A

(


YR1_UNIT DECIMAL(7,0),


YR2_UNIT DECIMAL(7,0),


YR3_UNIT DECIMAL(7,0),


YR4_UNIT DECIMAL(7,0),


YR5_UNIT DECIMAL(7,0),


DATA_TYP CHAR(1),


FAC_CD CHAR(2),


INSRT_TS TIMESTAMP(0),


ACTV_RCD_IND VARCHAR(1)

)

If the Primary Index is composed of


FAC_CD,


DATA_TYP,


ACTV_REC_IND

Is there anything to be gained with respect to query efficiency by reordering the columns such that all columns of the Primary Index reside (in order) at the beginning of the table row?

In other words reorganize the table as follows:

TABLE_A

(


FAC_CD CHAR(2),


DATA_TYP CHAR(1),


ACTV_RCD_IND VARCHAR(1),


YR1_UNIT DECIMAL(7,0),


YR2_UNIT DECIMAL(7,0),


YR3_UNIT DECIMAL(7,0),


YR4_UNIT DECIMAL(7,0),


YR5_UNIT DECIMAL(7,0),


INSRT_TS TIMESTAMP(0)

)

PRIMARY INDEX (


FAC_CD,


DATA_TYP,


ACTV_REC_IND


)

;

4 REPLIES
Senior Apprentice

Re: Does Column Order in an Index Matter?

Hi Dave,

short answer:

No, at least not regarding the efficiency of the PI

Long answer:

There's one specific case where it matters, multi-column statistics.

Only the first 16 bytes of stats are stored and the order is based on the order within create table:

- "varchar(20), int" -> only the first 16 bytes of the varchar are stored and nothing else

- "int, varchar(20)" -> int plus the first 12 bytes of the varchar are stored

In TD14 this problem is solved, the order is based on the collect stats (no longer on ther create table) and the 16 byte limit is lifted.

Dieter

Teradata Employee

Re: Does Column Order in an Index Matter?

Hi Dieter,

Regarding TD14 16 byte limit  lift  to 25 Byte, is this evaluation done at column by column basis and not in the combined values? e.g this is the order of columns in the Create table DDL

X SMALLINT NOT NULL,

Y VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

 if we are collecting  STATS in this format as follows

COLLECT STATISTICS

COLUMN ( X,Y ) ,

ON <databasename.tablename>;X will be evaluated at 25 byte limit and Y will also be evaluated at 25 byte limit?

Since Y goes over the limit , it is then advisable to extend that length as follows:

COLLECT STATISTICS

USING MAXVALUELENGTH 30

COLUMN ( X,Y ) ,

ON <databasename.tablename> ;

 is that a true statement?

Senior Apprentice

Re: Does Column Order in an Index Matter?

Hi Achieng,

when you do a SHOW STATS VALUES you will see that the 25 bytes (25 chars for strings) are used for each column.

Teradata Employee

Re: Does Column Order in an Index Matter?

Thanks Dieter!

I have verified that indeed its at column by column basis. Why do we have the seconf field evaluated at 24 and not 25??

COLLECT STATISTICS

COLUMNX ,Y )

ON <Databasename.Tablename>;

Here is part of the Histogram values

 /** SummaryInfo **/

/* Data Type and Length: 'I2:2', 'CV:24' */