Release 8.1.5
A67772-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Administrator's Guide Release 8.1.5 A67772-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 12, "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 any time without affecting 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 Oracle8i Tuning.
For more information about indexes, see Oracle8i Concepts.
If you have the Oracle Objects option, you can define domain-specific operators and indexing schemes and integrate them into the server. For more information see the Oracle8i Data Cartridge Developer's Guide.
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 must 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 Oracle8i 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. You should also leave room for updates and later identify long-term (for example, the life of the index) values for these settings.
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 where it belongs (based on the lexical set ordering). 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.
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 value of 5M and a parallel degree of 12 consumes at least 60M of storage during index creation.
See Also: For more information on parallel index creation, see Oracle8i Tuning.
You can create an index and generate minimal redo log records by specifying NOLOGGING in the CREATE INDEX statement.
|
Note: Because indexes created using LOGGING are not archived, you should perform a backup after you create the index. |
Creating an index with NOLOGGING has the following benefits:
In general, the relative performance improvement is greater for larger indexes created without LOGGING than for smaller ones. Creating small indexes without LOGGING 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.
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 index before creating it. If you then set the storage parameters when you create the index, fewer extents will be allocated for the table's data segment, and all of the index'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 index.
The maximum size of a single index entry is approximately one-half the data block size. As with tables, you can explicitly set storage parameters when creating an index.
See Also: For specific information about storage parameters, see "Setting Storage Parameters".
When you encounter index fragmentation (due to improper sizing or increased growth), you can rebuild or coalesce the index. Before you perform either task, though, weigh the costs and benefits of each option and choose the one that works best for your situation. Table 16-1 describes costs and benefits associated with rebuilding and coalescing indexes.
|
Quickly moves index to another tablespace. |
Cannot move index to another tablespace. |
|
Higher costs. Requires more disk space. |
Lower costs. Does not require more disk space. |
|
Creates new tree, shrinks height if applicable. |
Coalesces leaf blocks within same branch of tree. |
|
Enables you to quickly change storage and tablespace parameters without having to drop the original index. |
Quickly frees up index leaf blocks for use. |
In situations where you have B-tree index leaf blocks that can be freed up for reuse, you can merge those leaf blocks using the following statement:
ALTER INDEX vmoore COALESCE;
Figure 16-1 illustrates the effect of an ALTER INDEX COALESCE on the index VMOORE. Before performing the operation, the first two leaf blocks are 50% full, which means you have an opportunity to reduce fragmentation and completely fill the first block while freeing up the second (in this example, assume that PCTFREE=0).
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.
LOBS, 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.
Previously, when creating an index on a table there has always been a DML S-lock on that table during the index build operation, which meant you could not perform DML operations on the base table during the build.
Now, with the ever-increasing size of tables and necessity for continuous operations, you can create and rebuild indexes online--meaning you can update base tables at the same time you are building or rebuilding indexes on that table. Note, though, that there are still DML SS-locks, which means you cannot perform other DDL operations during an online index build.
The following statements perform online index build operations:
ALTER INDEX emp_name REBUILD ONLINE; CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;
See Also: For more information about the syntax for creating online indexes, see the Oracle8i SQL Reference.
Function-based indexes facilitate queries that qualify a value returned by a function or expression. The value of the function or expression is pre-computed and stored in the index.
Advantages of function-based indexing include support for linguistic sorts based on linguistic sort keys (collation), efficient linguistic collation of SQL statements, and an efficient mechanism for evaluating predicates involving functions.
The following statement defines an index on Area(geo):
CREATE INDEX area_index ON rivers (Area(geo)) DESC;
The area_index is defined on the function area(geo):
SELECT id, geo Area(geo), desc FROM rivers r WHERE Area(geo) >5000;
In this SQL statement, when Area(geo) is referenced in the WHERE clause, the optimizer considers using the index area_index.
See Also: For more conceptual information about function-based indexes, see Oracle8i Concepts.
For information about function-based indexing and application development, see the Oracle8i Application Developer's Guide - Fundamentals.
The following statement creates a function-based index, idx on table emp:
CREATE INDEX idx ON emp (UPPER(emp_name));
Now the SELECT statement uses the function-based index on UPPER(emp_name) to retrieve all employees with names like :KEYCOL:
SELECT * FROM emp WHERE UPPER(emp_name) like :KEYCOL;
SELECT statements can use either an index range scan (the expression is a prefix of the index) or index full scan (preferable when the index specifies a high degree of parallelism).
CREATE INDEX idx ON t (a + b * (c - 1), a, b); SELECT a FROM t WHERE a + b * (c - 1) < 100;
You can also use function-based indexes to support NLS sort index as well. NLSSORT is a function that returns a sort key that has been given a string. Thus, if you want to build an index on name using NLSSORT, issue the following statement:
CREATE INDEX nls_index ON t_table (NLSSORT(name, 'NLS_SORT = German'));
This statement creates the nls_index on table t_table with the collation sequence German.
Now, to select from NLS_SORT:
SELECT * FROM t_table ORDER BY name;
Rows will be ordered using the collation sequence in German.
Another use for function-based indexing is to perform non-case-sensitive searches:
CREATE INDEX case_insensitive_idx ON emp_table (UPPER(empname));
A query on this new index would look like the following:
SELECT * FROM emp_table WHERE UPPER(empname) = 'JOE';
This example also illustrates the most common uses of function-based indexing: a case-insensitive sort and language sort.
CREATE INDEX empi ON emp UPPER ((ename), NLSSORT(ename));
Here, an NLS_SORT specification does not appear in the NLSSORT argument because NLSSORT looks at the session setting for the language of the linguistic sort key. If you wish to use a language other than the language specified for the session setting, replace NLSSORT(ename) in the example above with the following:
NLSSORT(ename, NLS_SORT='German')
This line directs the sort to use a German linguistic sort key.
See Also: For more information about function-based indexing, see Oracle8i Concepts and Oracle8i SQL Reference.
Before re-creating or rebuilding an existing index, compare the costs and benefits associated with rebuilding to those associated with coalescing indexes as described in Table 16-1.
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 UNUSED clause.
See Also: For more information on the ALTER INDEX command and optional clauses, see the Oracle8i SQL Reference.
Creating an index using key compression enables you to eliminate repeated occurrences of key column prefix values.
Key compression breaks an index key into a prefix and a suffix entry. Compression is achieved by sharing the prefix entries among all the suffix entries in an index block. This sharing can lead to huge savings in space, allowing you to store more keys per index block while improving performance.
Key compression can be useful in the following situations:
You can enable key compression using the COMPRESS clause.You can also specify the prefix length (as the number of key columns), which identifies how the key columns are broken into a prefix and suffix entry. For example, the following statement will compress away duplicate occurrences of a key in the index leaf block.
CREATE INDEX emp_ename (ename) TABLESPACE users COMPRESS 1
The COMPRESS clause can also be specified during rebuild. For example, during rebuild you can disable compression as follows:
ALTER INDEX emp_ename REBUILD NOCOMPRESS;
See Also: For more details about the CREATE INDEX statement, see the Oracle8i 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 applies only 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:
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 rebuilding it, or coalescing 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".
