Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL REVERSE Function      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 199 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?