Is the order of columns in a clustered index important?

All we need is an easy explanation of the problem, so here it is.

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);

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Method 2

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply