The Database Design Resource Center

Oracle indexes : Adding database performance

Oracle indexes : Proper database indexing is a crucial factor for your database performance.

Most Oracle databases have hundreds or even thousands of indexes. This large number of indexes and their complexity make index tuning and monitoring a difficult task for the DBA.

As time goes, even originally efficient indexes may become inefficient due to various index distortions caused by data changes in the indexed tables.

The question is: How to manage Oracle indexes and what different options are available to use them?

Indexes are logically and physically independent of the data in the associated table. The DBA can create or drop an index at anytime without affecting the base tables or other indexes. If the DBA drops an index, all applications continue to work.

However, access to previously indexed data might be slower.

Indexes, being independent structures, require storage space.

Oracle automatically maintains and uses indexes after they are created. Oracle automatically reflects changes to data, such as adding new rows, updating rows, or deleting rows, in all relevant indexes with no additional action by users.

Oracle Text supports the creation of three types of Oracle indexes depending on Oracle application and text source. The DBA uses the CREATE INDEX statement to create all Oracle Text index types.

Context index:

Use this index to build a text retrieval application when the database consists of large coherent documents. The DBA can index documents of different formats such as MSWord, HTML, XML, or plain text.

With a context index, it is possible to customize the index in a variety of ways.

Ctxact index:

Use this index type to improve mixed query performance. Suitable for querying small text fragments with structured criteria like dates, item names, and prices that are stored across columns.

Ctxrule Index:

Use a CTXRULE index to build a document classification application. The CTXRULE index is an index created on a table of queries, where each query has a classification. Single documents (plain text, HTML, or XML) can be classified using the MATCHES operator.

Setting the environment for indexing:

  1. Create a user to be used for the following examples:

    CREATE USER ctx_demo
    IDENTIFIED BY ctx_demo


  2. Set the instance parameter 'text_enable = FALSE'. Ensure that the parameter now is set to FALSE.

  3. Include $ORACLE_HOME/ctx/bin in your PATH variable. Set the environment variable to:
    where is the explicit full path for oracle home. Do not use the $ORACLE_HOME environment variable. The variable can also be set in the ENVS section of the listener.ora file (note: version-specific patch names):

    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = /u01/app/oracle/product/8.1.7)
    (ENVS=LD_LIBRARY_PATH = /u01/app/oracle/product/8.1.7/ctx/lib:/u01/app/oracle/product/8.1.7/lib)
    (PROGRAM = extproc)

Managing Oracle Indexes:

The DBA creates Oracle indexes for a table after inserting or loading data into the table. When an index is created on a table that already has data, Oracle must use sort space.

Oracle uses the sort space in memory allocated for the creator of the index (the amount per user is determined by the initialization parameter SORT_AREA_SIZE). To create an Index :

CREATE INDEX emp_ename ON emp(ename)
NEXT 20k

The DBA can also create an index using an existing index as the data source. Re-creating Oracle indexes based on an existing data source also removes intra-block fragmentation.

In fact, compared to dropping the index and using the CREATE INDEX command, re-creating an existing index offers better performance. Issue the following statement to re-create an existing index:


To alter an index, the database schema must contain the index or it must have the ALTER ANY INDEX system privilege.

The DBA can alter Oracle indexes only to change the transaction entry parameters or to change the storage parameters but it is not possible to change its column structure.

ALTER INDEX emp_ename

altering an index while maintaining constraints:


The DBA can monitor an index's efficiency of space usage at regular intervals by first analyzing the index's structure and then querying the INDEX_STATS view:

SELECT pct_used FROM sys.index_stats WHERE name = 'indexname';

To drop an index, the index must be contained in the database schema and the DBA will follow the procedure as given below:

DROP INDEX emp_ename;

Detecting / Viewing Errors during Index Creation

There are times when index creation operations fail. Whenever the system encounters an error indexing a row, it logs the error into an Oracle Text view.

The DBA should be connected to the database as the user who created the index and query the view CTX_USER_INDEX_ERRORS. The DBA may also view errors on ALL indexes in the database by connecting as CTXSYS and querying the view


SELECT err_timestamp, err_text
FROM ctx_user_index_errors
ORDER BY err_timestamp DESC;

DML indexing:

DML operations to the base table refer to when documents are inserted, updated or deleted from the base table.

When documents in the base table are inserted, updated, or deleted, their ROWIDs are held in a DML queue until you synchronize the index. You can view this queue with the CTX_USER_PENDING view.

For example, to view pending DML on all your indexes, issue the following statement:

SELECT pnd_index_name, pnd_rowid,
TO_CHAR(pnd_timestamp, 'dd-mon-yyyyhh24:mi:ss') timestamp
FROM ctx_user_pending;

Synchronizing the Index:

Synchronizing the index involves processing all pending updates, inserts, and deletes to the base table.

You can do this in PL/SQL with the CTX_DDL.SYNC_INDEX procedure.

The following example synchronizes the index with 2 megabytes of memory:

ctx_ddl.sync_index('myindex', '2M');

Setting Background DML:

You can set CTX_DDL.SYNC_INDEX to run automatically at regular intervals using the DBMS_JOB.SUBMIT procedure. Oracle Text includes a SQL script you can use to do this. The location of this script is:


To use this script, The DBA has to be the index owner and he must have execute privileges on the CTX_DDL package. He/she should also set the job_queue_ processes parameter in the Oracle initialization file.

For example, to set the index synchronization to run every 360 minutes on myindex, the DBA can issue the following in SQL*Plus:

SQL: @drjobdml myindex 360

Function indexes:

The DBA and the programmer are using index functions easily and efficiently. This capability allows you to have case insensitive searches or sorts, search on complex equations, and extend the SQL language efficiently by implementing your own functions and operators and then searching on them.

The following is a list of what needs to be done to use function based Oracle indexes:

  • The DBA must have the system privilege query rewrite to create function based indexes on tables in your own schema.
  • The DBA must have the system privilege global query rewrite to create function based Oracle indexes on tables in other schemas.
  • For the optimizer to use function based Oracle indexes, the following session or system variables must be set:


The DBA may enable these at either the session level with ALTER SESSION or at the system level via ALTER SYSTEM or by setting them in the init.ora parameter file.

The meaning of query_rewrite_enabled is to allow the optimizer to rewrite the query allowing it to use the function based index. The meaning of is to tell the optimizer to trust that the code marked deterministic by the programmer is in fact deterministic.

If the code is in fact not deterministic, the resulting rows from the index may be incorrect.

  • Use the Cost Based Optimizer. Function based indexes are only visible to the Cost Based Optimizer and will not be used by the Rule Based Optimizer ever.
  • Use SUBSTR() to constrain return values from user written functions that return VARCHAR2 or RAW types.

Once the above list has been satisfied, it is as easy as CREATE INDEX from there on in. The optimizer will find and use the Oracle indexes at runtime for the user.

Return to Oracle DBA

Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant

Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:

What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials

Database Normalization eBook:

Database Normalization eBook

Copyright © / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.