Index in 12C

Unusable indexes

Invisible Indexes

Multiple Indexes on the same set of Columns

·       Oracle 12c allows multiple indexes on the same set of columns, provided only one index is visible and all indexes are different in some way.

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

·       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

Estimate Index Size and Set Storage Parameters

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

CREATE TABLE b (

     b1 INT,

     b2 INT,

     CONSTRAINT bu1 UNIQUE (b1, b2)

     USING INDEX (create unique index bi on b (b1, b2)),

     CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);

CREATE TABLE c (c1 INT, c2 INT);

CREATE INDEX ci ON c (c1, c2);

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

CREATE INDEX emp_ename ON emp(ename)

      TABLESPACE users

      STORAGE (INITIAL 20K NEXT 20k);

CREATE UNIQUE INDEX dept_unique_index ON dept (dname)

      TABLESPACE indx;

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

     ENABLE PRIMARY KEY USING INDEX

     TABLESPACE users;

Specify the Tablespace for Each 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 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.

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

Consider Creating Indexes with NOLOGGING

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.

Understand When to Use Unusable or Invisible Indexes

·       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,

·       Suspend the use of an index by the optimizer.