O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required