15 Managing Indexes

< h2>Guidelines for Managing Indexes

This section discusses gu idelines for managing indexes and contains the following topics:

Create Indexes Afte r Inserting Table Data
Index the Correct Tables and Columns
Order Index Columns for Performance
Limit the N umber of Indexes for Each Table
Drop Indexes That Are No Longer Required
Specify Index Block Space Use
Esti mate Index Size and Set Storage Parameters
Specify the Tablespace for Each Index
Consider Parallelizing Index Creation
Consider Creating Indexes with NOLOGGING
Consider Costs and Benefits of Coalescing or Rebuilding Indexes

Consider Cost Before Disabling or Dropping Constraints

See Also:

Oracle Database C oncepts for conceptual information about indexes and indexing, including descriptions of the various indexing schemes offere d by Oracle
Oracle Database Performance Tuning Guide and Oracle Data Warehousing Guide for information about bitmap indexes
Oracle Data Cartridge Developer's Guide for information about def ining domain-specific operators and indexing schemes and integrating them into the Oracle Database server

< a id="sthref2099" name="sthref2099">

Create Indexes After Insertin g Table Data

Data is often inserted or loaded into a table using either the S QL*Loader or an import utility. It is more efficient to create an index for a table after inserting or loading the data. If you creat e one or more indexes before loading data, the database then must update every index as each row is inserted.

Creating an index on a table that already has data requires sort space. Some sort space comes from memory allocated fo r the index creator. The amount for each user is determined by the initialization parameter SORT_AREA_SIZE. The database also swaps sort information to and from temporary segments that are only allocated during the index creation in the users temporary tablespace.

Under certain conditions, data can be loaded into a table with SQL*Loader direct-path load and an index can be cre ated as data is loaded.

See Also:

Oracle Database Utilities for information about using SQL*Loader for direct-path load

< /a>

Index the Correct Tables and Columns

Use the following guidelines for determining 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 th e 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, th e lower the percentage; the more clustered the row data, the higher the percentage.
To improve performan ce on joins of multiple tables, index columns used for joins.

Note:
Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key.
Small tables do n ot require indexes. If a query is taking too long, then the table might have grown from small to large.

Some columns are strong candidates for indexing. Columns with one or more of the following characteristi cs 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 th is case, use the following phrase:
```
WHERE COL_X > -9.99 * power(10,125)
```
Using the precedi ng 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).

Columns with the following characteri stics are less suitable for indexing:

There are many nulls in the column and you do not search on the no t null values.

LONG and LONG RAW columns cannot be indexed.

The size o f a single index entry cannot exceed roughly one-half (minus some overhead) of the available space in the data block.

Order Index Columns for Performance

The order of columns in the CREATE INDEX statement can affe ct query performance. In general, specify the most frequently used columns first.

If you create a single index across columns to speed up queries that access, for example, col1, col2, and col3; then queries that access j ust col1, or that access just col1 and col2, are also speeded up. But a query that accessed ju st col2, just col3, or just col2 and col3 does not use the index.

Limit the Number of Indexes for Each Table

A table can have any number of indexes. However, the more indexes there are, t he 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 tra de-off between the speed of retrieving data from a table and the speed of updating the table. For example, if a table is primarily re ad-only, having more indexes can be useful; but if a table is heavily updated, having fewer indexes could be preferable.

Drop Indexes That Are No Longer Required

Consider dropping an index if:

It does not speed up queries. The table could be very sma ll, or there could be many rows in the table but very few index entries.
The queries in your application s do not use the index.
The index must be dropped before being rebuilt.

See Also:
"Monitoring Index Usa ge"

Specify Index Block Space Use

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 ent ries).

If no more space is available in the appropriate index block, the indexed value is placed where it belongs (based on th e 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 inde x can be low so that fewer blocks are required to hold the index data.

PCTUSED cannot be specified for indexes.

Estimate Index Si ze and Set Storage Parameters

Estima ting the size of an index before creating one can facilitate better disk space planning and management. You can use the combined esti mated size of indexes, along with estimates for tables, the undo tablespace, and redo log files, to determine the amount of disk spac e that is required to hold an intended database. From these estimates, you can make correct hardware purchases and other decisions.

Use the estimated size of an individual index to better manage the disk space that the index uses. When an index is created, yo u can set appropriate storage parameters and improve I/O performance of applications that use the index. For example, assume that you estimate the maximum size of an index before creating it. If you then set the storage parameters when you create the index, fewer ex tents are allocated for the table data segment, and all of the index data is 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 a pproximately one-half the data block size.

See Also:

"Managing Space in Data Blocks" for information about the PCTFREE parameter

Specify the Tablespace for Each Index

Indexes can be create d in any tablespace. An index can be created in the same or different tablespace as the table it indexes. If you use the same tablesp ace for a table and its index, it can be more convenient to perform database maintenance (such as tablespace or file backup) or to en sure 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 r educed. But, if you use different tablespaces for a table and its index and one tablespace is offline (containing either data or inde x), then the statements referencing that table are not guaranteed to work.

Consider Parallelizing Index Creation

You can parallelize index creation, much the same as you can parallelize table creation. Because multiple processes work toget her to create the index, the database can create the index more quickly than if a single server process created the index sequentiall y.

When creating an index in parallel, storage parameters are used separately by each query server process. Therefore, an inde x created with an INITIAL value of 5M and a parallel degree of 12 consumes at least 60M of storage during index creation .

See Also:

Oracle Database Concepts for more i nformation about parallel execution

Oracle Data Warehousing Guide for information about utilizing parallel execution in a data warehousing envi ronment

Consider Creating Indexes with NOLOGGING

You can create an index and generate minimal redo log records by specifying NOLOGGING in t he CREATE INDEX statement.

Note:

Because indexes created using NOLOGGING are not archived, perform a backup after you create the ind ex.

Creating an index with NOLOGGING has the following benefits:

Space is saved in the redo log files.
The time it take s to create the index is decreased.
Performance improves for parallel creation of large indexes.

In general, the relative performance improvement is greater for larger indexes created without LOGGING than fo r smaller ones. Creating small indexes without LOGGING has little effect on the time it takes to create an index. Howeve r, for larger indexes the performance improvement can be significant, especially when you are also parallelizing the index creation.< /p>

Consider Costs and Benefits of Coalescing or Rebuilding Indexes

< a id="sthref2134" name="sthref2134">Improper sizing or increased growth can produce index fragmentation. To eliminate or reduce f ragmentation, you can rebuild or coalesce the index. But before you perform either task weigh the costs and benefits of each option a nd choose the one that works best for your situation. Table 15-1 is a comparison of the costs and benefits as sociated with rebuilding and coalescing indexes.

Table 15-1 To Rebuild or Coalesce ... That Is the Question

Rebuild Index	Coalesce Index
Quickly moves index to another tablespace	Cannot move index to another tabl espace
Higher costs: requires more disk sp ace	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 inde x.	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 me rge those leaf blocks using the following statement:

ALTER INDEX vmoore COALESCE;

Figure 15-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. This means you have an oppor tunity to reduce fragmentation and completely fill the first block, while freeing up the second. In this example, assume that P CTFREE=0.

< em>Figure 15-1 Coalescing Indexes

Description of the ill ustration admin026.gif

Con sider Cost Before Disabling or Dropping Constraints

Because unique and primary keys have associated indexes, you should factor i n 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 can save time by leaving the constraint enabled rather than dropping and re-creating the large index. You also have the option of ex plicitly specifying that you want to keep or drop the index when dropping or disabling a UNIQUE or PRIMARY KEY constraint.









See Also:

"Managing Integrity Constraints"



 
 
Creating Indexes
This section describes how to create indexes. To create an index in your own schema,
at least one of the following conditions must be true:


The table or cluster to be indexed is in
 your own schema.

You have INDEX privilege on the table to be indexed.

You have CREATE ANY INDEX system privilege.
To create an index in another schema, all
 of the following conditions must be true:


You have CREATE ANY INDEX system privilege.<
/li>

The owner of the other schema has a quota for the tablespaces to contain the index or index partitions, or <
code>UNLIMITED TABLESPACE system privilege.
This section contains the following topics:


Creating an Index Explicitly

Creating a Unique Index
 Explicitly

Creating an Index Associated with a Constraint

Collecting Incidental Statistics when Creating an Index

Creating a Large Index

Creating an Index Online

Creating a Function-Based Index

Creating a Key-Comp
ressed Index
 
 
<
h3>Creating an Index Explicitly
You can create indexes expli
citly (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 and
 a tablespace are explicitly specified for the index. If you do not specify storage options (such as INITIAL and N
EXT) for an index, the default storage options of the default or specified tablespace are automatically used.







See Also:
Oracle Database SQL Reference for syntax and restric
tions on the use of the CREATE INDEX statement





 


Creating a Unique Index Explicitly
Indexes can be uniqu
e or nonunique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Nonunique
indexes do not impose this restriction on the column values.
Use the CREATE UNIQUE INDEX statement to create a unique index. The following example creates a unique
index:
CREATE UNIQUE INDEX dept_unique_index ON dept (dname)
      TABLESPACE indx;



Alterna
tively, you can define UNIQUE integrity constraints on the desired columns. The database enforces UNIQUE in
tegrity constraints by automatically defining a unique index on the unique key. This is discussed in the following section. However,
it is advisable that any index that exists for query performance, including unique indexes, be created explicitly.







See Also:
Oracle Database Performance Tuning Guide for more information abo
ut creating an index for performance





 
 
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. This index i
s automatically created by the database when the constraint is enabled. No action is required by you when you issue the CREATE
TABLE or ALTER TABLE statement to create the index, but you can optionally specify a USING INDEX cla
use to exercise control over its creation. This includes both when a constraint is defined and enabled, and when a defined but disabl
ed constraint is enabled.
To enable a UNIQUE or PRIMARY KEY constraint, thus creating an associated
index, the owner of the table must have a quota for the tablespace intended to contain the index, or the UNLIMITED TABLESPACE system privilege. The index associated with a constraint always takes the name of the constraint, unless you optionally specify
 otherwise.








Note:
An effi
cient procedure for enabling a constraint that can make use of parallelism is described in"Efficient U
se of Integrity Constraints: A Procedure".




 
Specifying Storage Opt
ions for an Index Associated with a Constraint
You can set the storage options for the
indexes associated with UNIQUE and PRIMARY KEY constraints using the USING INDEX clause. The f
ollowing CREATE TABLE statement enables a PRIMARY KEY constraint and specifies the storage options of the a
ssociated index:
CREATE TABLE emp (
     empno NUMBER(5) PRIMARY KEY, age INTEGER)
     ENABLE PRIMARY
KEY USING INDEX
     TABLESPACE users
     PCTFREE 0;



 
Specifying the Index Associated with a Constraint

If you require more explicit control over the indexes associated with UNIQUE and PRIMARY KEY
constraints, the database lets you:


Specify an existing index that the database is to use to enforce the
 constraint

Specify a CREATE INDEX statement that the database is to use to cr
eate the index and enforce the constraint
These options are specified using the USING INDEX clause. The following statements present some examples.
Example 1:
CREATE TAB
LE a (
     a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));



Example 2:
CREATE TABLE b(
     b1 INT, 
     b2 INT, 
     CONSTRAINT bu1 UNIQUE (b1, b2) 
                    USING INDEX (create un
ique index bi on b(b1, b2)),
     CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);



Example 3:
CREATE TABLE c(c1 INT, c2 INT);
CREATE INDEX ci ON c (c1, c2);
ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) U
SING INDEX ci;



If a single statement creates an index with one constraint and also uses that index for another constraint
, the system will attempt to rearrange the clauses to create the index before reusing it.







See Also:
"Managing Integrity C
onstraints"







 
 
Collecting Incidental Statistics when Creating an Index
Oracle Databa
se 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 sta
tements. The following statement computes index, table, and column statistics while building index emp_ename on column <
code>ename of table emp:
CREATE INDEX emp_ename ON emp(ename)
     COMPUTE STATISTI
CS;









See Also:
<
ul>

Oracle Database Performanc
e Tuning Guide for information about collecting statistics and their use by the optimizer

"Analyzing Tables, Indexes, and Clusters"





 
 
Creating a Large Index
When c
reating an extremely large index, consider allocating a larger temporary tablespace for the index creation using the following proced
ure:


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

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


Create the index us
ing the CREATE INDEX statement.

Drop this tablespace using the DROP TABLESPACE statement.
Then use the ALTER 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.


 
 
Creating an Index Onl
ine
You can create and rebuild indexes online. This enables you to update base tables at the same time you are buildin
g or rebuilding indexes on that table. You can perform DML operations while the index build is taking place, but DDL operations are n
ot allowed. Parallel execution is not supported when creating or rebuilding an index online.
The following statements illustra
te online index build operations:
CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;









Note:
While you can perform D
ML operations during an online index build, Oracle recommends that you do not perform major/large DML operations during this procedur
e. 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 canno
t 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.<
/td>











See Also:
"Rebuilding an Existing Index"<
/table>





 
 
Creating a Func
tion-Based Index
Function-ba
sed indexes facilitate queries that qualify a value returned by a function or expression. The value of the function or expre
ssion is precomputed and stored in the index.
To create a function-based index, you must have the COMPATIBLE para
meter set to 8.1.0.0.0 or higher. In addition to the prerequisites for creating a conventional index, if the index is based on user-d
efined functions, then those functions must be marked DETERMINISTIC. Also, you just have the EXECUTE object
 privilege on any user-defined function(s) used in the function-based index if those functions are owned by another user.
Addi
tionally, 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, since N
ULL values are not stored in indexes.



<
/table>

To illustrate a function-based index, consider the following stat
ement 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) i
s referenced in the WHERE clause, the optimizer considers using the index area_index.
SELECT id, geo, area(geo), desc
     FROM rivers     
     WHERE Area(geo) >5000;

Table owners should have EXECUTE privileges on the functions used in function-based indexes.
Because a function-based index depends upon any
 function it is using, it can be invalidated when a function changes. If the function is valid, you can use an ALTER INDEX ...
ENABLE statement to enable a function-based index that has been disabled. The ALTER INDEX ... DISABLE statement l
ets you disable the use of a function-based index. Consider doing this if you are working on the body of the function.








Note:
CREATE INDEX stores the timestamp of the most recent function used in the function-based index
. This timestamp is updated when the index is validated. When performing tablespace point-in-time recovery of a function-based index,
 if the timestamp on the most recent function used in the index is newer than the timestamp stored in the index, then the index is ma
rked invalid. You must use the ANALYZE INDEX ... VALIDATE STRUCTURE statement to validate this index.














See Also:


<
p>Oracle Database Concepts for more info
rmation about function-based indexes

Oracle Database Application Developer's Guide - Fundamentals for information about using function-based i
ndexes in applications and examples of their use





 
 
Creating a Key-Compressed Index
Creating an index using key compression enables you to eliminate repeat
ed 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 sp
ace, allowing you to store more keys for each index block while improving performance.
Key compression can be useful in the fo
llowing situations:


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

You have a unique m
ulticolumn index.
You enable key compression using the COMPRESS clause. The prefix length (as the numbe
r of key columns) can also be specified to identify how the key columns are broken into a prefix and suffix entry. For example, the f
ollowing 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 reb
uild. For example, during rebuild you can disable compression as follows:
ALTER INDEX emp_ename REBUILD
 NOCOMPRESS;









See Also:

Oracle Database Concepts for a m
ore detailed discussion of key compression







 
 
Altering Indexes

To alter an index, your schema must
 contain the index or you must have the ALTER ANY INDEX system privilege. Among the actions allowed by the ALTER I
NDEX statement are:


Rebuild or coalesce an existing index

Deallocate
 unused space or allocate a new extent

Specify parallel execution (or not) and alter the degree of paral
lelism

Alter storage parameters or physical attributes

Specify LOGGIN
G or NOLOGGING

Enable or disable key compression

Mark t
he index unusable

Start or stop the monitoring of index usage
You cannot alter index co
lumn structure.
More detailed discussions of some of these operations are contained in the following sections:


Altering Storage Characteristics of an Index

R
ebuilding an Existing Index

Monitoring Index Usage
 
 
Altering Storage Characteristics of an Index
Alter the storage parameters of any index, including t
hose created by the database to enforce primary and unique key integrity constraints, using the ALTER INDEX statement. F
or example, the following statement alters the emp_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 impl
ement integrity constraints, you can adjust storage parameters by issuing an ALTER TABLE statement that includes the USING INDEX subclause of the ENABLE clause. For example, the following statement changes the storage options o
f the index created on table emp to enforce the primary key constraint:
ALTER TABLE emp
   ENABLE PRIMARY KEY USING INDEX
     PCTFREE 5;









See Also:
Oracle Database SQL Reference for syntax and restrictions on the use of the ALTER INDEX statement






 
 
R
ebuilding an Existing Index
Before r
ebuilding an existing index, compare the costs and benefits associated with rebuilding to those associated with coalescing indexes as
 described in Table 15-1.
When you rebuild an index, you use an existing index as the data source. Cre
ating an index in this manner enables you to change storage characteristics or move to a new tablespace. Rebuilding an index based on
 an existing data source removes intra-block fragmentation. Compared to dropping the index and using the CREATE INDEX st
atement, re-creating an existing index offers better performance.
The following statement rebuilds the existing index em
p_name:
ALTER INDEX emp_name REBUILD;



The REBUILD clause must immediate
ly follow the index name, and precede any other options. It cannot be used in conjunction with the DEALLOCATE UNUSED cla
use.
You have the option of rebuilding the i
ndex online. The following statement rebuilds the emp_name index online:
ALTER INDEX emp_n
ame REBUILD ONLINE;



If you do not have the space required to rebuild an index, y
ou can choose instead to coalesce the index. Coalescing an index is an online operation. 







See Also:


"Creating an Index Online"

"Monitoring Space U
se of Indexes"





 
 
Monitoring Index Usage
Oracle Database provides a means of monitoring indexes to determine w
hether 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<
/em> NOMONITORING USAGE;



The view V$OBJECT_USAGE can be queried for the index being monitored to see if the index has been used. The view contains a USED
 column whose value is YES or NO, depending upon if the index has been used within the time period b
eing monitored. The view also contains the start and stop times of the monitoring period, and a MONITORING column (YES/NO) to indicate if usage monitoring is currently active.
Each time that you specify MONITORING
USAGE, the V$OBJECT_USAGE view is reset for the specified index. The previous usage information is cleared or res
et, and a new start time is recorded. When you specify NOMONITORING USAGE, no further monitoring is performed, and the e
nd time is recorded for the monitoring period. Until the next ALTER INDEX ... MONITORING USAGE statement is issued, the
view information is left unchanged.




<
/a> 
 
Moni
toring Space Use of Indexes
If key v
alues 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 the ANALYZE INDEX ... VALID
ATE STRUCTURE statement, and then querying the INDEX_STATS view:
SELECT PCT_USED FR
OM INDEX_STATS WHERE NAME = 'index';



The percentage of index space usage varies according to how often index key
s are inserted, updated, or deleted. Develop a history of average efficiency of space usage for an index by performing the following
sequence of operations several times:


Analyzing statistics

Validating the i
ndex

Checking PCT_USED

Dropping and rebuilding (or coalescing)
 the index
When you find that index space usage drops below its average, you can condense the index space by droppin
g the index and rebuilding it, or coalescing it.







See Also:
"Analyzing Tables, Indexes, and Clusters"





 
 
Dropping Inde
xes
To drop an index, the index must be contained in your schema, or you must
 have the DROP ANY INDEX system privilege.
Some reasons for dropping an index include:


The index is no longer required.

The index is not providing anticipated performance improvements for queries issued against the associated table. For ex
ample, the table might be very small, or there might be many rows in the table but very few index entries.

Applications do not use the index to query the data.

The index has become invalid and must be dropped
 before being rebuilt.

The index has become too fragmented and must be dropped before being rebuilt.

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.
How you drop an index depends on whether you created the index explicitly with a C
REATE 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 dro
ps the emp_ename index:
DROP INDEX emp_ename;



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







Note:
If a table is dropped, al
l associated indexes are dropped automatically.











See Also:



Oracle Database SQL Reference for syntax and restrictions on the use of the DROP INDEX statement


"Managing Integrity Constraints"





 

 
Viewing Index Information
The following views display
information about indexes:

INDEX_STATS



View
Description


DBA_INDEXES 
ALL_INDEXES
USER_INDEXES
DBA view describes indexes on all tables in the database. ALL view describes i
ndexes 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 the DBMS_STATS package or ANALYZE 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 the DBMS_STATS package or 
ANALYZE statement.

DBA_IND_EX
PRESSIONS 
ALL_IND_EXPRESSIONS
USER_IND_EXPRESSIONS
These views describe the expressions of function-based indexes on tables.

Stores inf
ormation from the last ANALYZE INDEX ... VALIDATE STRUCTURE statement.

INDEX_HISTOGRAM
Stores infor
mation from the last ANALYZE INDEX ... VALIDATE STRUCTURE statement.

V$OBJECT_USAGE
Contains index
usage information produced by the ALTER INDEX ... MONITORING USAGE functionality.











See
 Also:
Oracle Database Reference fo
r a complete description of these views

View	Description
`DBA_INDEXES` `ALL_INDEXES` `USER_INDEXES`	`DBA` view describes indexes on all tables in the database. `ALL` view describes i ndexes 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 the `DBMS_STATS` package or `ANALYZE` 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 the `DBMS_STATS` package or `ANALYZE` statement.
`DBA_IND_EX PRESSIONS` `ALL_IND_EXPRESSIONS` `USER_IND_EXPRESSIONS`	These views describe the expressions of function-based indexes on tables.
Stores inf ormation from the last `ANALYZE INDEX ... VALIDATE STRUCTURE` statement.
`INDEX_HISTOGRAM`	Stores infor mation from the last `ANALYZE INDEX ... VALIDATE STRUCTURE` statement.
`V$OBJECT_USAGE`	Contains index usage information produced by the `ALTER INDEX ... MONITORING USAGE` functionality.

15 Managing Indexes

About Indexes

Create Indexes After Insertin g Table Data

Index the Correct Tables and Columns

Order Index Columns for Performance

Limit the Number of Indexes for Each Table

Drop Indexes That Are No Longer Required

Specify Index Block Space Use

Estimate Index Si ze and Set Storage Parameters

Specify the Tablespace for Each Index

Consider Parallelizing Index Creation

Consider Creating Indexes with NOLOGGING

Consider Costs and Benefits of Coalescing or Rebuilding Indexes

Con sider Cost Before Disabling or Dropping Constraints

Creating a Unique Index Explicitly

Creating an Index Associated with a Constraint

Specifying Storage Opt ions for an Index Associated with a Constraint

Specifying the Index Associated with a Constraint

Collecting Incidental Statistics when Creating an Index

Creating a Large Index

Creating an Index Onl ine

Creating a Func tion-Based Index

Creating a Key-Compressed Index

Altering Indexes

Altering Storage Characteristics of an Index

R ebuilding an Existing Index

Monitoring Index Usage

Moni toring Space Use of Indexes

Dropping Inde xes

Viewing Index Information