Optimizing Storage Structures - Oracle Database 11gR2 Performance Tuning - page 126

9. In the next screenshot, we can see the results of the previous query:

10. Drop the index:
DROP INDEX PK_REVERSE_TEST;

11. Empty the table:
TRUNCATE TABLE REVERSE_TEST;

12. Create a unique REVERSE KEY index on ID:
CREATE UNIQUE INDEX PK_REVERSE_TEST
ON REVERSE_TEST(ID) REVERSE;

13. Populate the table:
INSERT INTO REVERSE_TEST (NAME)
SELECT CUST_LAST_NAME || CUST_FIRST_NAME FROM CUSTOMERS;
COMMIT;

14. Analyze the index:
ANALYZE INDEX PK_REVERSE_TEST VALIDATE STRUCTURE;

15. Query the statistics on the REVERSE KEY index:
SELECT
BLOCKS, LF_BLKS, LF_ROWS_LEN,
BTREE_SPACE, USED_SPACE, PCT_USED
FROM INDEX_STATS WHERE NAME = ‹PK_REVERSE_TEST›;