 |
 |
Archives of the TeradataForum
Message Posted: Wed, 11 Oct 2006 @ 09:51:40 GMT
| Subj: | | Re: Index columns reversed in SHOW TABLE |
| |
| From: | | Victor Sokovin |
| | Never noticed this, but order of columns in INDEX specification logically doesn't matter. | |
Logically, the order does matter, and in some RDBMS the order is important in the physical implementation. "Primary index" as a term is often
confusing for people coming to Teradata from other database systems. One of the fist things to understand about it is that it is not an index, it
is a hash (the other thing being that it is not primary). Hash keys happen to be physically implemented as commutative on the column order, this
is just how it is. Potentially, they could have been non-commutative.
| | And in fact it won't physically differ either since assigned sequence in the index, i.e. DBC.Indexes.FieldPosition, will actually
depend on FieldID and not the order in the DDL. | |
I think this is just a technical consequence of commutativity, not the real explanation.
It would be interesting if somebody could comment on why exactly Teradata went for commutativity. I can't recall seeing any background
information on this. My guess is that although commutativity implied higher probability of hash collisions it was more economical to implement /
easier to program, and the higher collision rate was still low enough for anything practical. But this is just my guess.
Regards,
Victor
| |
