Archives of the TeradataForum
Message Posted: Tue, 12 Aug 2003 @ 16:24:55 GMT
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!
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|