Reverse Key Indexes


The pros (and some cons) of reverse key indexes are starting to become more widely known, however the simple question of "How to I tell if an index is created as reverse?" is lacking from the documentation.

Unfortunately in what seems to be a documentation bug, the INDEX_TYPE column in USER_INDEXES is listed in the Oracle Reference as being one of NORMAL, BITMAP, FUNCTION-BASED NORMAL, FUNCTION-BASED BITMAP, or DOMAIN.

The important one that has been missed is NORMAL/REV

Note: Don't try create an index that is both REVERSE and DESC. It doesn't make a lot of sense and you're meant to get an ORA-14134, although on some versions it can also crash your session with an ORA-00600.