Categories
howto

Eliminating Mysterious Oracle Indexes

Oracle by default will create cryptic names for indexes unless you specify your own. To get a list of all the indexes currently in use for your schema you can execute:
> select * from user_indexes;
Anything that starts with SYS_ and ends with a $ is an autogenerated index name. It is good to have a common format for your indexes so that any errors are meaningful. address_id_pk is much more readable than SYS_IL0000088969C00006$$. Over here we use the [table]_[column]_[type] format so we can know at a glance where to hunt for the problem.

While doing this I noticed that LOB and CLOB field types automatically generate a SYS_blahblahblah$$ index, polluting your clean design. If that rubs you the wrong way, you can specify the index name for them as well, but the code is a bit messier.