Oracle Database 11gR2 Performance Tuning Cookbook

Using reverse key indexes

In this recipe, we will introduce reverse key indexes. We will look at when to use them and how they are related to performance.

How to do it...

The following steps will demonstrate reverse keys:

1. Connect to SQL*Plus as user SH:

   CONNECT sh@TESTDB/sh
   

2. Create a simple table:

   CREATE TABLE REVERSE_TEST (
     ID NUMBER NOT NULL,
     NAME VARCHAR(100)
   );
   

3. Create a sequence to generate the IDs for the table:

   CREATE SEQUENCE REV_SEQ
     START WITH 1 INCREMENT BY 1 CACHE 1000;
   

4. Create the trigger to insert sequence-generate values:

   CREATE OR REPLACE TRIGGER TR_REVERSE_TEST_INS
     BEFORE INSERT ON REVERSE_TEST FOR EACH ROW
     WHEN (NEW.ID IS NULL)
   BEGIN
     SELECT REV_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
   END;
   

5. Create a UNIQUE INDEX on ID:

   CREATE UNIQUE ...