Oracle Database Discussions

Hello everyone:

yesterday I had a closer look on reverse key indexes to understand what they do and how they work. I created an example table called 'Buchung':

CREATE TABLE Buchung (
 Buchungs_ID     NUMBER,
 Betrag          NUMBER(12,2),
 Buchungsdatum   DATE,
 Konto_id        NUMBER,
 Transaktions_id NUMBER);

I filled this table with more than 400.000 records.

After that I created a reverse key index on the 'buchungs_id' column

CREATE UNIQUE INDEX idx_buchung
ON buchung(Buchungs_ID)
REVERSE;

I used dba_indexes and dba_ind_columns to check that the index was created correctly.

SELECT index_name, INDEX_TYPE, UNIQUENESS, BLEVEL
FROM   dba_index
WHERE index_name LIKE 'IDX_BUCHUNG'

Result:

------------------------------ --------------------------- --------- ----------

SELECT index_name, column_name
FROM   dba_ind_columns
WHERE index_name LIKE 'IDX_BUCHUNG'

Result:

INDEX_NAME    COLUMN_NAME

----------------------  -------------------------------

IDX_BUCHUNG BUCHUNGS_ID

dba_ind_columns shows only one column 'buchungs_id'.

I dumped the header_block of idx_buchung and the dump showed me this:

block_row_dump:
tab 0, row 0, @0x1cf1
tl: 27 fb: --H-FL-- lb: 0x0  cc: 5
col  0: [ 2]  c1 08
col  1: [ 5]  3d 0e 40 29 66
col  2: [ 7]  78 71 03 02 01 01 01
col  3: [ 3]  c2 0d 4d
col  4: [ 2]  c1 08

Why are there all five columns within the Index? DBA_IND_COLUMNS told me that there is only one column.

I used DBMS_STATS.CONVERT_RAW_VALUE to decode the Hex-Values to readable value. The above dump shows one row from the table buchung:

Buchungs_ID 7 Betrag -8737,6 Buchungsdatum: 02.03.13 Konto_id 1276 Transaktions_id 7

I thought that there must be only the buchungs_id column but not the other four.

Does anyone know why there are all five columns in this revese key index?

Any help is appreciated. Thanks to everyone who reads this.

Best Regards

Florian W.

PS.

SELECT *
FROM   v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0    Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production