Oracle7 Server Administrator's Guide | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
Before attempting tasks described in this chapter, familiarize yourself with the concepts
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.
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
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.
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.
See Also: For more information about setting these parameters, see "Setting Storage Parameters" .
See Also: PCTUSED cannot be specified for indexes. See "Managing the Space Usage of Data Blocks" for information about the PCTFREE parameter.
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.
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.
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:
Estimating the size of an index before creating one is useful for the following reasons:
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.
Before you can create a new index you must own or have the INDEX object privilege for the corresponding table. The schema that contains the index must also have a quota for the tablespace intended to contain the index, or the UNLIMITED TABLESPACE system privilege. To create an index in another user's schema, you must have the CREATE ANY INDEX system privilege.
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 the storage options for an index, they are automatically set to the default storage options of the host tablespace.
CREATE TABLE emp (
empno NUMBER(5) PRIMARY KEY, . . . )
ENABLE PRIMARY KEY USING INDEX
TABLESPACE users
PCTFREE 0;
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.
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 Oracle7 Server SQL Reference.
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 (already 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;
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 re-building it.
See Also: For information about analyzing an index's structure, see "Analyzing Tables, Indexes, and Clusters" .
You might want to drop an index for any of the following reasons:
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.
Note: If a table is dropped, all associated indexes are dropped automatically.
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" .
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |