Term: REVERSE

Definition:
In Oracle PL/SQL, REVERSE can be used as a SQL function and also as a keyword to create a REVERSE key index.

1. As a SQL Function

REVERSE is an undocumented Oracle string function, which returns the input string in its reverse order.

Example Syntax:

REVERSE( string )

Example Usage:

The SQL below uses the REVERSE function to reverse the order of input string "PSOUG".

SQL> SELECT REVERSE('PSOUG') FROM DUAL;



REVER

-----

GUOSP

2. Usage in Reverse Key Indexes

In Oracle PL/SQL, REVERSE key indexes are used to reduce the Index block contention during concurrent insert operations. If multiple programs are concurrently inserting values into the table key columns, index block contention increases, which results in performance degradation. By declaring an index as REVERSE, its values are stored in reverse order across the structure and not in serial order.

Example Syntax:

CREATE INDEX [NAME] ON TABLE(COLUMN) REVERSE;

Example Usage:

The SQL below creates a table name "REVT" with an ID column. A reverse index named "IDX_REV" is created on the ID column.

SQL> CREATE TABLE REVT(id number);



Table created.



SQL> CREATE INDEX idx_rev on REEVT(id) reverse;



Index created.

Related Links:

Related Code Snippets:

String Functions: REVERSE - This function reverses the order of the characters in a string.
INDEXES: Reverse Key Indexes - Creating a reverse key index, compared to a standard index, reverse...
INDEXES: Rebuild Reverse - REVERSE stores the bytes of the index block in reverse order and exclud...