Data is often inserted or loaded into a table using either the SQL*Loader or an import utility. 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, then the database 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 for 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 user's temporary tablespace.

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

Follow guidelines about tables and columns that are suitable for indexing.

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 the relative speed of a table scan and how the row data is distributed 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.

• To improve performance 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 not require indexes. If a query is taking too long, then the table might have grown from small to large.

The order of columns in the CREATE INDEX statement can affect 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 just col1, or that access just col1 and col2, are also speeded up. But a query that accessed just col2, just col3, or just col2 and col3 does not use the index.

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, then having more indexes can be useful; but if a table is heavily updated, then having fewer indexes could be preferable.

It is best practice to 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.

  See Also:

  "Monitoring Index Usage"

Index segment creation is deferred when the associated table defers segment creation. This is because index segment creation reflects the behavior of the table with which it is associated.

Estimating the size of an index before creating one can facilitate better disk space planning and management.

You can use the combined estimated size of indexes, along with estimates for tables, the undo tablespace, and redo log files, to determine the amount of disk space 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, you 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, then fewer extents 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 dependent on the block size of the database.

Storage parameters of an index segment created for the index used to enforce a primary key or unique key constraint can be set in either of the following ways:

• In the ENABLE ... USING INDEX clause of the CREATE TABLE or ALTER TABLE statement

• In the STORAGE clause of the ALTER INDEX statement

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 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.

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 an INITIAL value of 5M and a parallel degree of 12 consumes at least 60M of storage during index creation.

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:

• Space is saved in the redo log files.

• The time it takes 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 for smaller ones. Creating small indexes without LOGGING 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.

Use unusable or invisible indexes when you want to improve the performance of bulk loads, test the effects of removing an index before dropping it, or otherwise suspend the use of an index by the optimizer.

You can create multiple indexes on the same set of columns when the indexes are different in some way. For example, you can create a B-tree index and a bitmap index on the same set of columns.

When you have multiple indexes on the same set of columns, only one of these indexes can be visible at a time, and any other indexes must be invisible.

You might create different indexes on the same set of columns because they provide the flexibility to meet your requirements. You can also create multiple indexes on the same set of columns to perform application migrations without dropping an existing index and recreating it with different attributes.

Different types of indexes are useful in different scenarios. For example, B-tree indexes are often used in online transaction processing (OLTP) systems with many concurrent transactions, while bitmap indexes are often used in data warehousing systems that are mostly used for queries. Similarly, locally and globally partitioned indexes are useful in different scenarios. Locally partitioned indexes are easy to manage because partition maintenance operations automatically apply to them. Globally partitioned indexes are useful when you want the partitioning scheme of an index to be different from its table's partitioning scheme.

You can create multiple indexes on the same set of columns when at least one of the following index characteristics is different:

• The indexes are of different types.

  See "About Indexes" and Oracle Database Concepts for information about the different types of indexes.

  However, the following exceptions apply:

  • You cannot create a B-tree index and a B-tree cluster index on the same set of columns.

  • You cannot create a B-tree index and an index-organized table on the same set of columns.

• The indexes use different partitioning.

  Partitioning can be different in any of the following ways:

  • Indexes that are not partitioned and indexes that are partitioned

  • Indexes that are locally partitioned and indexes that are globally partitioned

  • Indexes that differ in partitioning type (range or hash)

• The indexes have different uniqueness properties.

  You can create both a unique and a non-unique index on the same set of columns.

Improper sizing or increased growth can produce index fragmentation. To eliminate or reduce fragmentation, you can rebuild or coalesce the index. But before you perform either task weigh the costs and benefits of each option and choose the one that works best for your situation.

Table 21-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 21-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. Therefore, you have an opportunity to reduce fragmentation and completely fill the first block, while freeing up the second.

Figure 21-1 Coalescing Indexes

Description of "Figure 21-1 Coalescing Indexes"

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, then you can save time by leaving the constraint enabled rather than dropping and re-creating the large index. You also have the option of explicitly specifying that you want to keep or drop the index when dropping or disabling a UNIQUE or PRIMARY KEY constraint.

The In-Memory Column Store is an optional portion of the system global area (SGA) that stores copies of tables, table partitions, and other database objects that is optimized for rapid scans. In the In-Memory Column Store, table data is stored by column rather than row in the SGA.

For tables used in OLTP or data warehousing environments, multiple indexes typically are created to improve the performance of analytic and reporting queries. These indexes can impede the performance of data manipulation language (DML) statements. When a table is stored in the In-Memory Column Store, indexes used for analytic or reporting queries can be greatly reduced or eliminated without affecting query performance. Eliminating these indexes can improve the performance of transactions and data loading operations.

You can create indexes explicitly (outside of integrity constraints) using the SQL statement CREATE INDEX.

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

Indexes can be unique or non-unique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Non-unique indexes do not impose this restriction on the column values.

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

When creating an extremely large index, consider allocating a larger temporary tablespace for the index creation.

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 the ALTER USER statement to reset your temporary tablespace to your original temporary tablespace.

You can create and rebuild indexes online. Therefore, you can update base tables at the same time you are building or rebuilding indexes on that table.

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 precomputed and stored in the index.

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

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

• Creating an Index Using Prefix Compression
  Creating an index using prefix compression (also known as key compression) eliminates repeated occurrences of key column prefix values. Prefix compression is most useful for non-unique indexes with a large number of duplicates on the leading columns.
• Creating an Index Using Advanced Index Compression
  Advanced index compression works well on all supported indexes, including those that are not good candidates for prefix compression. Creating an index using advanced index compression can reduce the size of all unique and non-unique indexes and improves the compression ratio significantly, while still providing efficient access to the indexes.

When you create an index in the UNUSABLE state, it is ignored by the optimizer and is not maintained by DML. An unusable index must be rebuilt, or dropped and re-created, before it can be used.

An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level.

You can create multiple indexes on the same set of columns when the indexes are different in some way.

1. Create a B-tree index on the customer_id and sales_rep_id columns in the oe.orders table:

   CREATE INDEX oe.ord_customer_ix1 ON oe.orders (customer_id, sales_rep_id);
   

   The oe.ord_customer_ix1 index is visible by default.

2. Alter the index created in Step 1 to make it invisible:

   ALTER INDEX oe.ord_customer_ix1 INVISIBLE;
   

   Alternatively, you can add the INVISIBLE clause in Step 1 to avoid this step.

3. Create a bitmap index on the customer_id and sales_rep_id columns in the oe.orders table:

   CREATE BITMAP INDEX oe.ord_customer_ix2 ON oe.orders (customer_id, sales_rep_id);
   

   The oe.ord_customer_ix2 index is visible by default.

   If the oe.ord_customer_ix1 index created in Step 1 is visible, then the CREATE BITMAP INDEX statement in this step returns an error.

The automatic indexing feature automates the index management tasks in an Oracle database. Automatic indexing automatically creates, rebuilds, and drops indexes in a database based on the changes in application workload, thus improving database performance. The automatically managed indexes are known as auto indexes.

Index structures are an essential feature to database performance. Indexes are critical for OLTP applications, which use large data sets and run millions of SQL statements a day. Indexes are also critical for data warehousing applications, which typically query a relatively small amount of data from very large tables. If you do not update the indexes whenever there are changes in the application workload, the existing indexes can cause the database performance to deteriorate considerably.

Automatic indexing improves database performance by managing indexes automatically and dynamically in an Oracle database based on changes in the application workload.

Automatic indexing provides the following functionality:

• Runs the automatic indexing process in the background periodically at a predefined time interval.

• Analyzes application workload, and accordingly creates new indexes and drops the existing underperforming indexes to improve database performance.

• Rebuilds the indexes that are marked unusable due to table partitioning maintenance operations, such as ALTER TABLE MOVE.

• Provides PL/SQL APIs for configuring automatic indexing in a database and generating reports related to automatic indexing operations.

This section describes how automatic indexing works.

The automatic indexing process runs in the background every 15 minutes and performs the following operations:

1. Identifies auto index candidates

   Auto index candidates are identified based on the usage of table columns in SQL statements.

   Ensure that table statistics are up to date. Tables without statistics are not considered for auto indexing. Tables with stale statistics are not considered for auto indexing, if real-time statistics are not available.

2. Creates invisible auto indexes for the auto index candidates

   The auto index candidates are created as invisible auto indexes, that is, these auto indexes cannot be used in SQL statements.

   Automatic indexes can be single-column or multi-column. They are considered for the following:

   • Table columns (including virtual columns)
   • Partitioned and non-partitioned tables

3. Verifies invisible auto indexes against SQL statements

   The invisible auto indexes are validated against SQL statements.

   If the performance of SQL statements is improved by using these indexes, then the indexes are configured as visible indexes, so that they can be used in SQL statements.

   If the performance of SQL statements is not improved by using these indexes, then the indexes are configured as unusable indexes and the SQL statements are blacklisted. The unusable indexes are later deleted by the automatic indexing process. The blacklisted SQL statements are not allowed to use auto indexes in future.

   Note:

   Auto indexes cannot be used by SQL statements that are running for the first time in a database.

4. Deletes the unused auto indexes

   The auto indexes that are not used for a long period are deleted.

   Note:

   By default, the unused auto indexes are deleted after 373 days. The period for retaining the unused auto indexes in a database can be configured using the DBMS_AUTO_INDEX.CONFIGURE procedure.

You can configure automatic indexing in an Oracle database using the DBMS_AUTO_INDEX.CONFIGURE procedure.

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', FALSE);
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', FALSE);

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', NULL);

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, TRUE);

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', '90');

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', NULL);

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', '60');

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', NULL);

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION', '60');

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION', NULL);

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'TBS_AUTO');

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SPACE_BUDGET', '5');

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_COMPRESSION','ON');

You can generate reports related to automatic indexing operations in an Oracle database using the REPORT_ACTIVITY and REPORT_LAST_ACTIVITY functions of the DBMS_AUTO_INDEX package.

declare
  report clob := null;
begin
  report := DBMS_AUTO_INDEX.REPORT_ACTIVITY();
end;

declare
  report clob := null;
begin
  report := DBMS_AUTO_INDEX.REPORT_ACTIVITY(
              activity_start => TO_TIMESTAMP('2018-11-01', 'YYYY-MM-DD'),
              activity_end   => TO_TIMESTAMP('2018-12-01', 'YYYY-MM-DD'),
              type           => 'HTML',
              section        => 'SUMMARY',
              level          => 'BASIC');
end;

declare
  report clob := null;
begin
  report := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY();
end;

declare
  report clob := null;
begin
  report := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY(
              type    => 'HTML',
              section => 'SUMMARY +INDEX_DETAILS +ERRORS',
              level   => 'BASIC');
end;

You can query a set of data dictionary views for getting information about the auto indexes in an Oracle database.

DBA_AUTO_INDEX_CONFIG

DBA_INDEXES
ALL_INDEXES
USER_INDEXES