Oracle7 Server Administrator's Guide
Guidelines for Managing Indexes
This section describes guidelines to follow when managing indexes, and includes the following topics:
An index is an optional structure associated with tables and clusters, which you can create explicitly to speed SQL statement execution on a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle index provides a faster access path to table data.
The absence or presence of an index does not require a change in the wording of any SQL statement. An index merely offers a fast access path to the data; it affects only the speed of execution. Given a data value that has been indexed, the index points directly to the location of the rows containing that value.
Indexes are logically and physically independent of the data in the associated table. You can create or drop an index at anytime without effecting the base tables or other indexes. If you drop an index, all applications continue to work; however, access of previously indexed data might be slower. Indexes, as 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.
See Also: For information about performance implications of index creation, see the Oracle7 Server Tuning manual.
For more information about indexes, see the Oracle7 Server Concepts guide.
Create Indexes After Inserting Table Data
You should create an index for a table after inserting or loading data (via SQL*Loader or Import) into the table. It is more efficient to insert rows of data into a table that has no indexes and then create the indexes for subsequent access. If you create indexes before table data is loaded, every index must be updated every time a row is inserted into the table. You should also create the index for a cluster before inserting any data into the cluster.
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), but must also swap sort information to and from temporary segments allocated on behalf of the index creation.
If the index is extremely large, you may want to perform the following tasks:
To Manage a Large Index
1. Create a new temporary segment tablespace.
2. Alter the index creator's temporary segment tablespace.
4. Remove the temporary segment tablespace and re-specify the creator's temporary segment tablespace, if desired.
See Also: Under certain conditions, data can be loaded into a table with SQL*Loader's "direct path load" and an index can be created as data is loaded; see the Oracle7 Server Utilities guide for more information.
Limit the Number of Indexes per Table
A table can have any number of indexes. However, the more indexes there are, the more overhead is incurred as the table is modified. Specifically, when rows are inserted or deleted, all indexes on the table must be updated as well. Also, when a column is updated, all indexes that contain the column must be updated.
Thus, there is a tradeoff between the speed of retrieving data from a table and the speed of updating the table. For example, if a table is primarily read-only, having more indexes can be useful, but if a table is heavily updated, having fewer indexes may be preferable.
Specify Transaction Entry Parameters
By specifying the INITRANS and MAXTRANS parameters during the creation of each index, you can affect how much space is initially and can ever be allocated for transaction entries in the data blocks of an index's segment.
See Also: For more information about setting these parameters, see "Setting Storage Parameters" ![[*]](jump.gif)
.
Specify Index Block Space Use
When an index is created for a table, data blocks of the index are filled with the existing values in the table up to PCTFREE. The space reserved by PCTFREE for an index block is only used when a new row is inserted into the table and the corresponding index entry must be placed in the correct index block (that is, between preceding and following index entries); if no more space is available in the appropriate index block, the indexed value is placed in another index block. Therefore, if you plan on inserting many rows into an indexed table, PCTFREE should be high to accommodate the new index values; if the table is relatively static without many inserts, PCTFREE for an associated index can be low so that fewer blocks are required to hold the index data.
See Also: PCTUSED cannot be specified for indexes. See "Managing the Space Usage of Data Blocks" for information about the PCTFREE parameter.
Specify the Tablespace for Each Index
Indexes can be created in any tablespace. An index can be created in the same or different tablespace as the table it indexes.
If you use the same tablespace for a table and its index, then database maintenance may be more convenient (such as tablespace or file backup and application availability or update) and all the related data will always be online together.
Using different tablespaces (on different disks) for a table and its index produces better performance than storing the table and index in the same tablespace, due to reduced disk contention.
If you use different tablespaces for a table and its index and one tablespace is offline (containing either data or index), then the statements referencing that table are not guaranteed to work.
Parallelize Index Creation
If you have the parallel query option installed, you can parallelize index creation. Because multiple processes work together to create the index, Oracle can create the index more quickly than if a single server process created the index sequentially.
When creating an index in parallel, storage parameters are used separately by each query server process. Therefore, an index created with an INITIAL of 5M and a PARALLEL DEGREE of 12 consumes at least 60M of storage during index creation.
See Also: For more information on the parallel query option and parallel index creation, see the Oracle7 Server Tuning manual.
Consider Creating UNRECOVERABLE Indexes
You can create an index without generating any redo log records by specifying UNRECOVERABLE in the CREATE INDEX statement.
Note: Because indexes created unrecoverably are not archived, you should perform a backup after you create the index.
Creating an index unrecoverably has the following benefits:
- Space is saved in the redo log files.
- The time it takes to create the index is decreased.
- Performance improves for parallel creation of large indexes.
In general when creating an index unrecoverably, the relative performance improvement is greater for larger indexes than for smaller ones. Creating small indexes unrecoverably has little affect on the time it takes to create an index. However, for larger indexes the performance improvement can be significant, especially when you are also parallelizing the index creation.
Estimate Index Size and Set Storage Parameters
Appendix A contains equations that help estimate the size of indexes.
Estimating the size of an index before creating one is useful for the following reasons:
- You can use the combined estimated size of indexes, along with estimates for tables, rollback segments, and redo log files, to determine the amount of disk space that is required to hold an intended database. From these estimates, you can make correct hardware purchases and other decisions.
- You can use the estimated size of an individual index to better manage the disk space that the index will use. When an index is created, you can set appropriate storage parameters and improve I/O performance of applications that use the index.
For example, assume that you estimate the maximum size of a table before creating it. If you then set the storage parameters when you create the table, fewer extents will be allocated for the table's data segment, and all of the table's data will be stored in a relatively contiguous section of disk space. This decreases the time necessary for disk I/O operations involving this table.
The maximum size of a single index entry is roughly one-half the data block size minus some overhead.
As with tables, you can explicitly set storage parameters when creating an index. If you explicitly set the storage parameters for an index, try to store the index's data in a small number of large extents rather than a large number of small extents.
See Also: For specific information about storage parameters, see "Setting Storage Parameters" .
For specific information about estimating index size, see Appendix A.
Considerations Before Disabling or Dropping Constraints
Because unique and primary keys have associated indexes, you should factor in the cost of dropping and creating indexes when considering whether to disable or drop a UNIQUE or PRIMARY KEY constraint. If the associated index for a UNIQUE key or PRIMARY KEY constraint is extremely large, you may save time by leaving the constraint enabled rather than dropping and re-creating the large index.