index

Oracle Database provides several indexing schemes that provide complementary performance functionality. These are:

• B-tree indexes: the default and the most common

• B-tree cluster indexes: defined specifically for cluster

• Hash cluster indexes: defined specifically for a hash cluster

• Global and local indexes: relate to partitioned tables and indexes

• Reverse key indexes: most useful for Oracle Real Application Clusters applications

• Bitmap indexes: compact; work best for columns with a small set of values

• Function-based indexes: contain the precomputed value of a function/expression

• Domain indexes: specific to an application or cartridge.

manage index:

1.  Create Indexes After Inserting Table Data

It is more efficient to create an index for a table after inserting or loading the data. If you create one or more indexes before loading data, the database then must update every index as each row is inserted.

   在表中插入完数据后，再建立索引的效率会高一些。如果先在表上建立索引，再加载数据，那么每插入完一条数据，同时要更新每个索引。

2    when to create an index

Create an index if you frequently want to retrieve less than 15% of the rows in a large table,The percentage varies greatly according to the relative speed of a table scan and how the distribution of the row data in relation to the index key. The faster the table scan, the lower the percentage; the more clustered the row data, the higher the percentage.

在一个表中，如果查询结果的行数小于总行数的%15，可以建立索引。这个百分比是根据表的查询速度和索引中数据的分布 而改变。百分比越小，查询速度越快。数据的集中度越高，这个百分比越高。

   To improve performance on joins of multiple tables, index columns used for joins.

   Small tables do not require indexes.

       If a query is taking too long, then the table might have grown from small to large.

3   Some columns are strong candidates for indexing. Columns with one or more of the following characteristics are candidates for indexing

对于一些列具体有很强的选择性

Values are relatively unique in the column.

There is a wide range of values (good for regular indexes).

There is a small range of values (good for bitmap indexes).

The column contains many nulls, but queries often select all rows having a value. In this case, use the following phrase:

WHERE COL_X > -9.99 * power(10,125)

Using the preceding phrase is preferable to:

WHERE COL_X IS NOT NULL

This is because the first uses an index on COL_X (assuming that COL_X is a numeric column

4  Columns with the following characteristics are less suitable for indexing 

下列特征的列不舍和索引：

There are many nulls in the column and you do not search on the not null values.  列上有很多空值，而且你不用查询这些非空的值

LONG and LONG RAW columns cannot be indexed                       long和long raw不能有索引

The size of a single index entry cannot exceed roughly one-half (minus some overhead) of the available space in the data block. 索引的列的宽度不能超过block可用空间的一般

 Order Index Columns for Performance 创建组合索引列的顺序也会影响性能

The order of columns in the CREATE INDEX statement can affect query performance. In general, specify the most frequently used columns first. 一般来说经常使用的列放在前面

6 Limit the Number of Indexes for Each 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.

 

7  Drop Indexes That Are No Longer Required

Consider dropping an index if:

• It does not speed up queries. The table could be very small, or there could be many rows in the table but very few index entries.

• The queries in your applications do not use the index.

• The index must be dropped before being rebuilt.

8Estimate Index Size and Set Storage Parameters

    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, it can be more convenient to perform database maintenance (such as tablespace or file backup) or to ensure application availability. All the related data is always 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. Disk contention is reduced. But, 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.

9 Consider Parallelizing Index Creation

  You can parallelize index creation, much the same as you can parallelize table creation. Because multiple processes work together to create the index, the database 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 anINITIAL value of 5M and a parallel degree of 12 consumes at least 60M of storage during index creation

10Consider Creating Indexes with NOLOGGING

   You can create an index and generate minimal redo log records by specifyingNOLOGGING in theCREATE INDEX statement.

In general, the relative performance improvement is greater for larger indexes created withoutLOGGING than for smaller ones. Creating small indexes withoutLOGGING has little effect 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.

11Consider Costs and Benefits of Coalescing or Rebuilding Indexes

To Rebuild or Coalesce ... That Is the Question

 

Rebuild Index	Coalesce Index
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.

 

12 Creating Indexes

  （1）Creating an Index Explicitly

     CREATE INDEX emp_ename ON emp(ename)      TABLESPACE users      STORAGE (INITIAL 20K      NEXT 20k      PCTINCREASE 75);

    （2）Creating a Unique Index Explicitly

          CREATE UNIQUE INDEX dept_unique_index ON dept (dname)      TABLESPACE indx;

    （3）Creating an Index Associated with a Constraint

          Oracle Database enforces a UNIQUE key or PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key.

 

    （4）Specifying Storage Options for an Index Associated with a Constraint

         CREATE TABLE emp (     empno NUMBER(5) PRIMARY KEY,

                                                       age INTEGER)  

                                            ENABLE PRIMARY KEY USING INDEX     TABLESPACE users;

 

    （5）Specifying the Index Associated with a Constraint

   CREATE TABLE a (     a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));

   或

     CREATE INDEX ci ON c (c1, c2);

     ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;

 

  （6）Collecting Incidental Statistics when Creating an Index

  Oracle Database provides you with the opportunity to collect statistics at very little resource cost during the creation or rebuilding of an index. These statistics are stored in the data dictionary for ongoing use by the optimizer in choosing a plan for the execution of SQL statements. The following statement computes index, table, and column statistics while building index emp_ename on columnename of tableemp:

CREATE INDEX emp_ename ON emp(ename)  COMPUTE STATISTICS;

（7）Creating a Large Index

When creating an extremely large index, consider allocating a larger temporary tablespace for the index creation using the following procedure:

1. Create a new temporary tablespace using the CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE statement.

2. Use the TEMPORARY TABLESPACE option of the ALTER USER statement to make this your new temporary tablespace.

3. Create the index using the CREATE INDEX statement.

4. Drop this tablespace using the DROP TABLESPACE statement. Then use theALTER USER statement to reset your temporary tablespace to your original temporary tablespace.

Using this procedure can avoid the problem of expanding your usual, and usually shared, temporary tablespace to an unreasonably large size that might affect future performance.

（8）Creating an Index Online

You can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the index build is taking place, but DDL operations are not allowed. Parallel execution is not supported when creating or rebuilding an index online.

The following statements illustrate online index build operations:

CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;

Note:

While you can perform DML operations during an online index build, Oracle recommends that you do not perform major/large DML operations during this procedure. This is because while the DML on the base table is taking place it holds a lock on that resource. The DDL to build the index cannot proceed until the transaction acting on the base table commits or rolls back, thus releasing the lock.

For example, if you want to load rows that total up to 30% of the size of an existing table, you should perform this load before the online index build.

 

（9)Creating a Function-Based Index

  To create a function-based index, you must have the COMPATIBLE parameter set to 8.1.0.0.0 or higher.

to use a function-based index:

• The table must be analyzed after the index is created.

• The query must be guaranteed not to need any NULL values from the indexed expression, sinceNULL values are not stored in indexes

  CREATE INDEX area_index ON rivers (area(geo));

  SELECT id, geo, area(geo), desc     FROM rivers          WHERE Area(geo) >5000

(10) Creating a Key-Compressed Index

         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 for each index block while improving performance.

Key compression can be useful in the following situations:

• You have a non-unique index where ROWID is appended to make the key unique. If you use key compression here, the duplicate key is stored as a prefix entry on the index block without theROWID. The remaining rows become suffix entries consisting of only theROWID.

• You have a unique multicolumn index.

      You enable key compression using the COMPRESS clause. The prefix length (as the number of key columns) can also be specified to identify how the key columns are broken into a prefix and suffix entry. For example, the following statement compresses duplicate occurrences of a key in the index leaf block:

                CREATE INDEX  emp_ename ON emp(ename)   TABLESPACE users   COMPRESS 1;

• The COMPRESS clause can also be specified during rebuild

  ALTER INDEX emp_ename REBUILD NOCOMPRESS

(11)   Altering Storage Characteristics of an Index

Alter the storage parameters of any index, including those created by the database to enforce primary and unique key integrity constraints, using theALTER INDEX statement. For example, the following statement alters theemp_ename index:

ALTER INDEX emp_ename
     STORAGE (PCTINCREASE 50);

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 adjust storage parameters by issuing anALTER TABLE statement that includes theUSING INDEX subclause of theENABLE clause. For example, the following statement changes the storage options of the index created on tableemp to enforce the primary key constraint:

ALTER TABLE emp
     ENABLE PRIMARY KEY USING INDEX;

 

(12) Rebuilding an Existing Index

Rebuilding an index based on an existing data source removes intra-block fragmentation. Compared to dropping the index and using theCREATE INDEX statement, re-creating an existing index offers better performance

The following statement rebuilds the existing index emp_name:

ALTER INDEX emp_name REBUILD;

 

 

Online index rebuilding has stricter limitations on the maximum key length that can be handled, compared to other methods of rebuilding an index. If an ORA-1450 (maximum key length exceeded) error occurs when rebuilding online, try rebuilding offline, coalescing, or dropping and recreating the index.

 

You have the option of rebuilding the index online. Rebuilding online enables you to update base tables at the same time that you are rebuilding. The following statement rebuilds theemp_name index online:

 

ALTER INDEX emp_name REBUILD ONLINE

 

(13)Monitoring Index Usage

Oracle Database provides a means of monitoring indexes to determine whether they are being used. If an index is not being used, then it can be dropped, eliminating unnecessary statement overhead

To start monitoring the usage of an index, issue this statement:

ALTER INDEX index MONITORING USAGE;

Later, issue the following statement to stop the monitoring:

ALTER INDEX index NOMONITORING USAGE;

 

The view V$OBJECT_USAGE can be queried for the index being monitored to see if the index has been used

 

(14)Monitoring Space Use of Indexes

   If key values in an index are inserted, updated, and deleted frequently, the index can lose its acquired space efficiently over time. Monitor index efficiency of space usage at regular intervals by first analyzing the index structure, using theANALYZE INDEX...VALIDATE STRUCTURE statement, and then querying the INDEX_STATS view:

SELECT PCT_USED FROM INDEX_STATS WHERE NAME = 'index'

 

(15)  Dropping Indexes

When you drop an index, all extents of the index segment are returned to the containing tablespace and become available for other objects in the tablespace

 You cannot drop only the index associated with an enabled UNIQUE key or PRIMARY KEY constraint. To drop a constraints associated index, you must disable or drop the constraint itself.

 

Viewing Index Information

The following views display information about indexes:

View	Description
DBA_INDEXES ALL_INDEXES USER_INDEXES	DBA view describes indexes on all tables in the database.ALL view describes indexes on all tables accessible to the user.USER view is restricted to indexes owned by the user. Some columns in these views contain statistics that are generated by theDBMS_STATS package orANALYZE statement.
DBA_IND_COLUMNS ALL_IND_COLUMNS USER_IND_COLUMNS	These views describe the columns of indexes on tables. Some columns in these views contain statistics that are generated by theDBMS_STATS package orANALYZE statement.
DBA_IND_EXPRESSIONS ALL_IND_EXPRESSIONS USER_IND_EXPRESSIONS	These views describe the expressions of function-based indexes on tables.
DBA_IND_STATISTICS ALL_IND_STATISTICS USER_IND_STATISTICS	These views contain optimizer statistics for indexes.
INDEX_STATS	Stores information from the last ANALYZE INDEX...VALIDATE STRUCTURE statement.
INDEX_HISTOGRAM	Stores information from the last ANALYZE INDEX...VALIDATE STRUCTURE statement.
V$OBJECT_USAGE	Contains index usage information produced by theALTER INDEX...MONITORING USAGE functionality.