Archives of the TeradataForum

Message Posted: Tue, 12 Aug 2003 @ 16:24:55 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Muhammad & Cathy,

How do you find the ordering column in a value ordered index? I had to do a little research on this one. It's actually "implied" in the definition of the indices. In WINDDI (which just gets stuff from dbc.Indices), if you look at a multi-columned secondary index with some ordering on it, you'll see:

IndexName   ColumnName   Type            Unique Index   Number  ColumnPosition
            CUST_ID      Primary         N                 1    1
EFF_DT_X    SBU_ID       Value Ordered   N                 4    1
EFF_DT_X    DIV_CD       Value Ordered   N                 4    2
EFF_DT_X    EFF_DT       Value Ordered   N                 4    3
            EFF_DT       I               N                 8    1

The "I" type of Index is the "value-ordered" column for a secondary index. There can only be one column that the index is ordered by (well, at least in v2r4, I guess), since you get a error #5466: "Error in Secondary Index DDL, Order by clause can have only one column". So the index before it (numbered 4 less than it in the index numbering scheme) is the secondary index to which it refers to, and the "I" column is the one that it's ordered on. Not simple, eh? (Yuck!) Sounds to me like when someone designed this, they didn't do a lot of thought about following Cod's rules about relational tables, since this violates one of the higher order forms (Hey, I don't have them memorized). Naughty, naughty!

iv

PS: Mis amigos de la lista, just thought I'd pass this on so that someone much smarter than me can confirm this. Also so someone from NCR could EXPLAIN this particular feature (??? Kludge? Ugly looking thing?) or maybe give me a better way to determine this.

	<Prev	Next>		<<First	<Prev	Next>	Last>>