Oracle reverse index and query

Question

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';

or

SELECT name FROM myTable WHERE name='reteP';

Answer 1

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

Answer 2

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

Answer 3

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).