Reputation: 9845

Oracle reverse index and query

If I create a reverse index in Oracle on a VARCHAR2 column do I also need to reverse the value used in the WHERE clause?

CREATE INDEX myindex ON myTable(name) REVERSE;

SELECT name FROM myTable WHERE name='Peter';

SELECT name FROM myTable WHERE name='reteP';

Upvotes: 1

Answers (3)

Reputation: 146209

The value you are searching for is 'Peter'. If you search for 'reteP' you will return zero rows (or at least not the rows you're looking for).

Upvotes: 5

Reputation: 425281

You need it to improve the data distribution.

Like, many movie names begin with The which would degrade the index performance.

You don't need to reverse the condition: Oracle will do it for you automatically.

However, you cannot do range queries anymore:

SELECT name FROM myTable WHERE name BETWEEN 'A' AND 'C'

will result in a full scan

Upvotes: 2

Reputation: 10186

No you don't - see Burleson for the full skinny.

Upvotes: 0