Oracle Full-Text Search – How to Get a List of CTXSYS.CONTEXT Indices

It will take marginally longer to generate the plan when there are more indexes to consider, but I doubt the difference would be measurably significant. The reasons for dropping an index does not list query performance. On the other hand, in general you shouldn't create indexes unless you know they will be used to make a query more efficient.

From the Oracle Concepts Guide, here are the criteria for creating an index.

In general, consider creating an index on a column in any of the following situations:

• The indexed columns are queried frequently and return a small percentage of the total number of rows in the table.

• A referential integrity constraint exists on the indexed column or columns. The index is a means to avoid a full table lock that would otherwise be required if you update the parent table primary key, merge into the parent table, or delete from the parent table.

• A unique key constraint will be placed on the table and you want to manually specify the index and all index options.

All indexes will be considered in the sense that all indexes on the tables in the query are examined to determine whether they could be used. Those that could be are further examined to determine usefulness.

Primary index

A primary index is an index on a set of fields that includes the unique primary key for the field and is guaranteed not to contain duplicates. Also Called a Clustered index. eg. Employee ID can be Example of it.

Secondary index

A Secondary index is an index that is not a primary index and may have duplicates. eg. Employee name can be example of it. Because Employee name can have similar values.

Dense Index

Index record appears for every search­ key value in the file. Dense indexes point directly to individual records.

Sparse index

contains index records for only some search ­key values. Applicable when records are sequentially ordered on search ­key. Just as with book indexes, sparse database indexes don’t point to individual records, but to ‘pages'