Release 8.0
A58397-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Administrator's Guide Release 8.0 A58397-01 |
|
This chapter describes various aspects of index management, and includes the following topics:
Before attempting tasks described in this chapter, familiarize yourself with the concepts in Chapter 10, Guidelines for Managing Schema Objects.
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 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.
See Also: For information about performance implications of index creation, see Oracle8 Tuning.
For more information about indexes, see Oracle8 Concepts.
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.
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 Oracle8 Utilities for more information.
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 trade-off 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.
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".
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 Space in Data Blocks" for information about the PCTFREE parameter.
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.
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 Oracle8 Tuning.
You can create an index without generating any redo log records by specifying UNRECOVERABLE in the CREATE INDEX statement.
|
Note: Because indexes created using UNRECOVERABLE are not archived, you should perform a backup after you create the index. |
Creating an unrecoverable index has the following benefits:
In general, the relative performance improvement is greater for larger unrecoverable indexes than for smaller ones. Creating small unrecoverable indexes 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.
Appendix A, "Space Estimations for Schema Objects", contains equations that help estimate the size of indexes.
Estimating the size of an index before creating one is useful for the following reasons:
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, "Space Estimations for Schema Objects".
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.
This section describes how to create an index, and includes the following topics:
To enable a UNIQUE key or PRIMARY KEY (which creates an associated index), the owner of the table needs a quota for the tablespace intended to contain the index, or the UNLIMITED TABLESPACE system privilege.
LONG and LONG RAW columns cannot be indexed.
Oracle enforces a UNIQUE key or PRIMARY KEY integrity constraint by creating a unique index on the unique key or primary key. This index is automatically created by Oracle when the constraint is enabled; no action is required by the issuer of the CREATE TABLE or ALTER TABLE statement to create the index. This includes both when a constraint is defined and enabled, and when a defined but disabled constraint is enabled.
In general, it is better to create constraints to enforce uniqueness than it is to use the CREATE UNIQUE INDEX syntax. A constraint's associated index always assumes the name of the constraint; you cannot specify a specific name for a constraint index.
If you do not specify storage options (such as INITIAL and NEXT) for an index, the default storage options of the host tablespace are automatically used.
You can set the storage options for the indexes associated with UNIQUE key and PRIMARY KEY constraints using the ENABLE clause with the USING INDEX option. The following statement defines a PRIMARY KEY constraint and specifies the associated index's storage option:
CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, . . . ) ENABLE PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0;
You can create indexes explicitly (outside of integrity constraints) using the SQL command CREATE INDEX. The following statement creates an index named EMP_ENAME for the ENAME column of the EMP table:
CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75) PCTFREE 0;
Notice that several storage settings are explicitly specified for the index.
You can create an index using an existing index as the data source. Creating an index in this manner allows you to change storage characteristics or move to a new tablespace. Re-creating an index 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;
The REBUILD clause must immediately follow the index name, and precede any other options. Also, the REBUILD clause cannot be used in conjunction with the DEALLOCATE STORAGE clause.
See Also: For more information on the ALTER INDEX command and optional clauses, see the Oracle8 SQL Reference.
To alter an index, your schema must contain the index or you must have the ALTER ANY INDEX system privilege. You can alter an index only to change the transaction entry parameters or to change the storage parameters; you cannot change its column structure.
Alter the storage parameters of any index, including those created by Oracle to enforce primary and unique key integrity constraints, using the SQL command ALTER INDEX. For example, the following statement alters the EMP_ENAME index:
ALTER INDEX emp_ename INITRANS 5 MAXTRANS 10 STORAGE (PCTINCREASE 50);
When you alter the transaction entry settings (INITRANS, MAXTRANS) of an index, a new setting for INITRANS only applies to data blocks subsequently allocated, while a new setting for MAXTRANS applies to all blocks (currently and subsequently allocated blocks) of an index.
The storage parameters INITIAL and MINEXTENTS cannot be altered. All new settings for the other storage parameters affect only extents subsequently allocated for the index.
For indexes that implement integrity constraints, you can also adjust storage parameters by issuing an ALTER TABLE statement that includes the ENABLE clause with the USING INDEX option. For example, the following statement changes the storage options of the index defined in the previous section:
ALTER TABLE emp ENABLE PRIMARY KEY USING INDEX PCTFREE 5;
If key values in an index are inserted, updated, and deleted frequently, the index may or may not use its acquired space efficiently over time. 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';
The percentage of an index's space usage will vary according to how often index keys are inserted, updated, or deleted. Develop a history of an index's average efficiency of space usage by performing the following sequence of operations several times: validating the index, checking PCT_USED, and dropping and re-creating the index. When you find that an index's space usage drops below its average, you can condense the index's space by dropping the index and re-creating or rebuilding it.
See Also: For information about analyzing an index's structure, see "Analyzing Tables, Indexes, and Clusters".
To drop an index, the index must be contained in your schema, or you must have the DROP ANY INDEX system privilege.
You might want to drop an index for any of the following reasons:
When you drop an index, all extents of the index's segment are returned to the containing tablespace and become available for other objects in the tablespace.
How you drop an index depends on whether you created the index explicitly with a CREATE INDEX statement, or implicitly by defining a key constraint on a table.
You cannot drop only the index associated with an enabled UNIQUE key or PRIMARY KEY constraint. To drop a constraint's associated index, you must disable or drop the constraint itself.
DROP INDEX emp_ename;
See Also: For information about analyzing indexes, see "Analyzing Tables, Indexes, and Clusters".
For more information about dropping a constraint's associated index, see "Managing Integrity Constraints".
