SQL & PL/SQL :: Order Of Bytes Returned By Dump() Function In Reverse Key Indexes?

Oracle Version: 11gR2: 11.2.0.1.0 - 64bit
OS: Linux Fedora Core 17 X86_64

Currently, I'm reading the online book Oracle Concepts, Chapter 3: 3 Indexes and Index-Organized Tables, section: Reverse Key Indexes in order to understand this topic.

As I understand for each pair of (key, rowid) in the index structure, the rowid for each row in the table obviously remains the same but the bytes of the key are reversed before the key is stored. So for example on a 32 bit machine (just an example) a key = 10 AB CD EF will be stored as FE DC BA 01 , am I right?

According to the documentation, this becomes interesting in RAC environments in order to remove a hot spot from the index (when multiple instances repeatedly modify the same block) with the disadvantage that in some cases there cannot be Index Range Scan any more as data in the index is not sorted by column key when it is stored.

I was just curious to see how bytes of each key are reversed and after a bit googling I found an article where Tom Kyte shows with an example by using dump function (which as I understand gives the internal representation of a given expression) the difference in the sequence of bytes. Here is the link

[URL]

So based on his instructions I tried to do my own test, yet I don't get the same result, that is, the bytes are not reversed for me once we rebuild the index by REVERSE key word.

I'm going to write down here the test that I did, where is/are my error(s)

Test Case:
(I use a copy of the employees table in hr sample schema)
SQL> CREATE TABLE emp_test AS SELECT * FROM hr.employees;
Table created.
SQL> CREATE INDEX emp_test_idx ON emp_test(first_name);

[code]...

Now, a test SQL Query using the index we've just defined (just for giving an example)

SQL> SELECT first_name,
2 dump(first_name, 16) as dump_result
3 FROM emp_test
4 WHERE first_name = 'Kelly';

FIRST_NAME DUMP_RESULT
-------------- ----------------------------------
Kelly Typ=1 Len=5: 4b,65,6c,6c,79

[code]...

So, according to the above Execution plan, Oracle does an Index Range Scan using the index that I defined on my table that is, emp_test_idx. According to the output of dump, the key (first_name) in that index is stored (in terms of bytes) as 4b,65,6c,6c,79

SQL> SELECT chr(to_number('4b', 'xx')) ||
2 chr(to_number('65', 'xx')) ||
3 chr(to_number('6c', 'xx')) ||
4 chr(to_number('6c', 'xx')) ||
5 chr(to_number('79', 'xx')) first_name
6 FROM DUAL;

FIRST_NAME
--------------------
Kelly

Which as we can see corresponds to the first name 'Kelly', the first name we specified in the above SQL query.

Now let's rebuild the index

SQL> ALTER INDEX emp_test_idx REBUILD REVERSE;
Index altered.
SQL>

Once the index keys have been reversed, I run the very same query in order to see the difference

SQL> SELECT first_name,
2 dump(first_name, 16) as dump_result
3 FROM emp_test
4 WHERE first_name = 'Kelly';

[code]...

So the second time after the index has been reversed, I still get the very same sequence of bytes, that is, 4b,65,6c,6c,79, whereas I expected to get 79,6c,6c,65,4b (that is, the reversed order of the initial bytes sequence)