In this recipe, we will introduce reverse key indexes. We will look at when to use them and how they are related to performance.
The following steps will demonstrate reverse keys:
Connect to SQL*Plus as user SH:
CONNECT sh@TESTDB/shCreate a simple table:
CREATE TABLE REVERSE_TEST ( ID NUMBER NOT NULL, NAME VARCHAR(100) );
Create a sequence to generate the IDs for the table:
CREATE SEQUENCE REV_SEQ START WITH 1 INCREMENT BY 1 CACHE 1000;
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;
Create a
UNIQUE INDEXon ID:CREATE UNIQUE INDEX PK_REVERSE_TEST ON REVERSE_TEST(ID);Populate the table:
INSERT INTO REVERSE_TEST (NAME) SELECT CUST_LAST_NAME || CUST_FIRST_NAME FROM CUSTOMERS; COMMIT;
Analyze the index:
ANALYZE INDEX PK_REVERSE_TEST VALIDATE STRUCTURE;Query the...