Reverse indexes reverse the byte order for each column value. They do not reverse the order of columns in the index. So a reverse index would store:
BA 42 36
BA 42 56
DC 42 36
DC 42 56
FE 42 36
(in Oracle's encoding format of course. It doesn't store numbers as decimals so the reverse of 24 isn't 42, but we're just using decimal to make a point here)
You can demonstrate this with block dumps. Dump of normal index:
row#0[8014] flag: -------, lock: 2, len=18
col 0; len 2; (2): 41 42 -- encodes A, B
col 1; len 2; (2): c1 19 -- encodes the number 24
col 2; len 2; (2): c1 40 -- encodes the number 63
col 3; len 6; (6): 00 75 81 d9 00 00
row#1[7996] flag: -------, lock: 2, len=18
col 0; len 2; (2): 41 42 -- encodes A, B
col 1; len 2; (2): c1 19
col 2; len 2; (2): c1 42
col 3; len 6; (6): 00 75 81 d9 00 01
row#2[7978] flag: -------, lock: 2, len=18
col 0; len 2; (2): 43 44 -- encodes C, D
col 1; len 2; (2): c1 19
col 2; len 2; (2): c1 40
col 3; len 6; (6): 00 75 81 d9 00 02
row#3[7960] flag: -------, lock: 2, len=18
col 0; len 2; (2): 43 44 -- encodes C, D
col 1; len 2; (2): c1 19
col 2; len 2; (2): c1 42
col 3; len 6; (6): 00 75 81 d9 00 03
row#4[7942] flag: -------, lock: 2, len=18
col 0; len 2; (2): 45 46 -- encodes E, F
col 1; len 2; (2): c1 19
col 2; len 2; (2): c1 40
col 3; len 6; (6): 00 75 81 d9 00 04
Dump of reverse index:
row#0[8014] flag: -------, lock: 0, len=18
col 0; len 2; (2): 42 41 -- encodes B, A
col 1; len 2; (2): 19 c1 -- encodes the number 24 *backwards*
col 2; len 2; (2): 40 c1 -- encodes the number 63 *backwards*
col 3; len 6; (6): 00 75 81 d9 00 00
row#1[7996] flag: -------, lock: 0, len=18
col 0; len 2; (2): 42 41 -- encodes B, A
col 1; len 2; (2): 19 c1
col 2; len 2; (2): 42 c1
col 3; len 6; (6): 00 75 81 d9 00 01
row#2[7978] flag: -------, lock: 0, len=18
col 0; len 2; (2): 44 43 -- encodes D, C
col 1; len 2; (2): 19 c1
col 2; len 2; (2): 40 c1
col 3; len 6; (6): 00 75 81 d9 00 02
row#3[7960] flag: -------, lock: 0, len=18
col 0; len 2; (2): 44 43 -- encodes D, C
col 1; len 2; (2): 19 c1
col 2; len 2; (2): 42 c1
col 3; len 6; (6): 00 75 81 d9 00 03
row#4[7942] flag: -------, lock: 0, len=18
col 0; len 2; (2): 46 45 -- encodes F, E
col 1; len 2; (2): 19 c1
col 2; len 2; (2): 40 c1
col 3; len 6; (6): 00 75 81 d9 00 04
Note that the byte order has been reversed for both strings and numbers. However Oracle would store the datatype, it simply reverses the byte order. The only exception is the ROWID itself (col 3 in the dump) isn't reversed, obviously. The order of columns relative to each other hasn't changed.
The purpose of reverse indexes is to spread inserts out across the leaf nodes so there isn't a hot right-leading edge when you have every-increasing column values inserted in order. This can help reduce contention if you're experiencing it. There would be no benefit to reversing the column order (you can always reorder the columns yourself if you wished). The downside to reverse indexes is, because the bytes are stored in reverse order, they can only be used for equality predicates, not inequalities like >, <, BETWEEN, LIKE (it can do a full scan, but not range scan/binary search on the starting value). That makes it appropriate for identifiers that have no sequential meaning (like customer IDs), but not for things like dates, numbers reflecting quantities, strings that may be pattern matched, etc. The same benefit can be accomplished by hash partitioning the table on a unique column and making the index local. That too will spread out inserts across many index blocks while still retaining the ability to do inequality searches. Neither approach, however, should be used unless hot-block contention is actually a problem.
