Release 2 (8.1.6)
Part Number A76956-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Administrator's Guide Release 2 (8.1.6) Part Number A76956-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.
Oracle provides several indexing schemes, which provide complementary performance functionality: B*-tree indexes (currently the most common), B*-tree cluster indexes, hash cluster indexes, reverse key indexes, and bitmap indexes. Oracle also provides support for function-based indexes and domain indexes specific to an application or cartridge.
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:
|
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.
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.
See Also:
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 "Transaction Entry Settings (INITRANS and MAXTRANS)". |
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. |
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.
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 Designing and Tuning for Performance. |
You can create an index and generate minimal redo log records by specifying NOLOGGING in the CREATE INDEX statement.
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.
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 14-1 is a comparison of the costs and benefits associated with rebuilding and coalescing indexes.
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 14-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 create an index in your own schema, one of the following conditions must be true:
To create an index in another schema, you must have CREATE ANY INDEX system privilege. Also, the owner of the schema to contain the index must have either space quota on the tablespaces to contain the index or index partitions, or UNLIMITED TABLESPACE system privilege.
|
See Also:
For syntax and restrictions on the use of the CREATE INDEX, ALTER INDEX, and DROP INDEX statements, see the Oracle8i SQL Reference. |
You can create indexes explicitly (outside of integrity constraints) using the SQL statement 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. 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.
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.
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.
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, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0;
Oracle recommends that you do not explicitly define UNIQUE indexes on tables (CREATE UNIQUE INDEX). 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.
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;
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. Specific features of function-based indexing include:
To illustrate a function based index, lets consider the following statement that defines a function based index (AREA_INDEX) defined on the function AREA(GEO):
CREATE INDEX area_index ON rivers (Area(geo));
In the following SQL statement, when AREA(GEO) is referenced in the WHERE clause, the optimizer considers using the index AREA_INDEX.
SELECT id, geo, Area(geo), desc FROM rivers r WHERE Area(geo) >5000;
Table owners should have EXECUTE privileges on the functions used in function-based indexes. Also, because a function-based index depends upon any function it is using, it can be invalidated when a function changes. You can use an ALTER INDEX...ENABLE statement to enable a function-based index that has been disabled if the function is valid. The ALTER INDEX...DISABLE statement allows you to disable the use of a function-based index. You might want to do this if you are working on the body of the function.
For the creation of a function-based index in your own schema, you must be granted the CREATE INDEX and QUERY REWRITE system privileges. To create the index in another schema or on another schema's tables, you must have the CREATE ANY INDEX and GLOBAL QUERY REWRITE privileges.
You must have the following initialization parameters defined to create a function-based index:
Additionally, to use a function-based index:
Some examples of using function based indexes follow.
The following statement creates function-based index IDX on table EMP based on an uppercase evaluation of the ENAME column:
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 that start with JOH:
SELECT * FROM emp WHERE UPPER(emp_name) LIKE 'JOH%';
This example also illustrates a case-insensitive search.
This statement creates a function-based index on an expression:
CREATE INDEX idx ON t (a + b * (c - 1), a, b);
SELECT statements can use either an index range scan (in the following SELECT statement the expression is a prefix of the index) or index full scan (preferable when the index specifies a high degree of parallelism).
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 index NLS_INDEX on table T_TABLE with the collation sequence German.
Now, to select from T_TABLE using the NLS_SORT index:
SELECT * FROM t_table ORDER BY name;
Rows will be ordered using the collation sequence in German.
This example combines a case-insensitive sort and a 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. Example 3 illustrated a case where NLS_SORT was specified.
Before rebuilding or re-creating an existing index, compare the costs and benefits associated with rebuilding to those associated with coalescing indexes as described in Table 14-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. Rebuilding 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 statement, re-creating an existing index offers better performance.
Issue the following statement to rebuild 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.
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;
To alter an index, your schema must contain the index or you must have the ALTER ANY INDEX system privilege. You can rebuild or coalesce an index, alter its real and default storage characteristics and some other physical properties, but 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 statement 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. 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. If you created the index explicitly with the CREATE INDEX statement, then you can drop the index with the DROP INDEX statement. The following statement drops the EMP_ENAME index:
DROP INDEX emp_ename;
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. For more information about dropping a constraint's associated index, see "Managing Integrity Constraints".
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|