Databases Reference
In-Depth Information
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 statistics on the index:
SELECT
BLOCKS, LF_BLKS, LF_ROWS_LEN,
BTREE_SPACE, USED_SPACE, PCT_USED
FROM INDEX_STATS WHERE NAME = ‹PK_REVERSE_TEST›;
