Is the order of columns in a clustered index important?

Question

Does it matters which field I specify first in the index?

would

CREATE SET TABLE my_table
(   
        validity_date DATE
    ,   branch_id SMALLINT 
    ,   account_class_id BYTEINT
    ,   catalog_id INTEGER
    ,   No_events FLOAT
)
PRIMARY INDEX (validity_date, branch_id);

be treated by the server same as

CREATE SET TABLE my_table
(   
        validity_date DATE
    ,   branch_id SMALLINT 
    ,   account_class_id BYTEINT
    ,   catalog_id INTEGER
    ,   No_events FLOAT
)
PRIMARY INDEX (branch_id, validity_date);

Answer 1

No, there's no difference at all in Teradata. The PI is hash-based, both columns must be used in a Where- or Join-condition to allow index-based access.

Answer 2

The ordering of the columns defined in the index generally represents the combinations of all subsets of predicates covered by that index.

For example, an index on the columns (Column1, Column2) will cover predicates, such as a WHERE clause, that filter on Column1 and Column2 in the same clause, or only Column1 in that clause. It will not normally cover Column2 by itself in that clause, and therefore that index is not applicable to that predicate clause.

Conversely if you reversed the order that you defined the columns of that index for, such that it's definition was (Column2, Column1), then it would cover Column2 by itself, or Column1 and Column2 in the same predicate clause. But then it would not cover Column1 by itself in that clause.

Adding the caveat to my above answer that with Teradata there is a type of index known as a hash index which sounds like it shouldn't matter what order the columns are defined in, but that type of index is only applicable to predicates that exactly match in columns to whichever columns are hashed in the index. So it sounds like it depends on which type of index is used, as discussed in their docs.