Oracle indexes : Adding 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
   DEFAULT TABLESPACE ctx_demod
   TEMPORARY TABLESPACE temp;
   
   GRANT CONNECT,RESOURCE,CTXAPP,DBA TO 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:
   /lib:/ctx/lib
   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_LIST_listener=
   (SID_LIST=
   (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)
   )
   (SID_DESC=
   (SID_NAME=OEM1DB)
   (ORACLE_HOME=/u01/app/oracle/product/8.1.7)
   )
   )

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)
TABLESPACE users
STORAGE (INITIAL 20K
NEXT 20k
PCTINCREASE 75)
PCTFREE 0;

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:

ALTER INDEX index name REBUILD;

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
INITRANS 5
MAXTRANS 10
STORAGE (PCTINCREASE 50);

altering an index while maintaining constraints:

ALTER TABLE emp
ENABLE PRIMARY KEY USING INDEX
PCTFREE 5;

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

CTX_INDEX_ERRORS:

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:

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

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:

$ORACLE_HOME/ctx/sample/script/drjobdml.sql

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:

  QUERY_REWRITE_ENABLED=TRUE
  QUERY_REWRITE_INTEGRITY=TRUSTED

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