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 INDEX PK_REVERSE_TEST ON REVERSE_TEST(ID);
   

6. Populate the table:

   INSERT INTO REVERSE_TEST (NAME)
     SELECT CUST_LAST_NAME || CUST_FIRST_NAME FROM CUSTOMERS;
   COMMIT;
   

7. Analyze the index:

   ANALYZE INDEX PK_REVERSE_TEST VALIDATE STRUCTURE;
   

8. Query the...