Oracle indexes : Adding database performanceOracle 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.
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.
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.
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:
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)
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
altering an index while maintaining constraints:
ALTER TABLE emp
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
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,
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:
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
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 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.
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.
Free eBookSubscribe 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.Read more Testimonials
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."
Theory & Practice
SQL Server DBA
Install SQL Server
Database Normalization eBook:
Copyright © www.databasedesign-resource.com / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.
Return to top
The name Oracle is a trademark of Oracle Corporation.