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.

Unusable indexes

• ·       An unusable index is ignored by the optimizer and is not maintained by DML.
• ·       One reason to make an index unusable is to improve bulk load performance. (Bulk loads go more quickly if the database does not need to maintain indexes when inserting rows.)
• ·       Instead of dropping the index and later re-creating it, which requires you to recall the exact parameters of the CREATE INDEX statement, you can make the index unusable, and then rebuild it.

•  ·       An unusable index or index partition must be rebuilt, or dropped and re-created, before it can be used.

• ·       Truncating a table makes an unusable index valid.
• ·       When you make an existing index unusable, its index segment is dropped.
• ·       If the index is partitioned, then all index partitions are marked UNUSABLE

 Creating an Unusable Index

Create a table to be indexed.

    For example, create a hash-partitioned table called hr.employees_part as follows:

    sh@PROD> CONNECT hr

    Enter password: **

    Connected.

    hr@PROD> CREATE TABLE employees_part

       PARTITION BY HASH (employee_id)

       PARTITIONS 2

      AS SELECT * FROM employees;

    

    Table created.

    hr@PROD> SELECT COUNT(*) FROM employees_part;    

      COUNT(*)

    ----------

           107

  Create an index with the keyword UNUSABLE.

 The following example creates a locally partitioned index on employees_part, naming the index partitions p1_i_emp_ename and p2_i_emp_ename, and making p1_i_emp_ename unusable:

    hr@PROD> CREATE INDEX i_emp_ename ON employees_part (employee_id)

     LOCAL (PARTITION p1_i_emp_ename UNUSABLE, PARTITION p2_i_emp_ename);

     Index created.

Verify that the index is unusable by querying the data dictionary.

     The following example queries the status of index i_emp_ename and its two partitions, showing that only partition p2_i_emp_ename is unusable:

    hr@PROD> SELECT INDEX_NAME AS "INDEX OR PARTITION NAME", STATUS

FROM   USER_INDEXES

WHERE INDEX_NAME = 'I_EMP_ENAME'

UNION ALL

SELECT PARTITION_NAME AS "INDEX OR PARTITION NAME", STATUS

FROM   USER_IND_PARTITIONS

WHERE PARTITION_NAME LIKE '%I_EMP_ENAME%';

    

    INDEX OR PARTITION NAME        STATUS

    --------------------------------------   ------------------------

    I_EMP_ENAME                           N/A

    P1_I_EMP_ENAME                 UNUSABLE

    P2_I_EMP_ENAME                 USABLE

    Query the data dictionary to determine whether storage exists for the partitions.

     The following query shows that only index partition p2_i_emp_ename occupies a segment. Because you created p1_i_emp_ename as unusable, the database did not allocate a segment for it.

    hr@PROD> COL PARTITION_NAME FORMAT a14

    hr@PROD> COL SEG_CREATED FORMAT a11

    hr@PROD> SELECT p.PARTITION_NAME, p.STATUS AS "PART_STATUS",

                         p.SEGMENT_CREATED AS "SEG_CREATED",  

                        FROM   USER_IND_PARTITIONS p, USER_SEGMENTS s

                        WHERE s.SEGMENT_NAME = 'I_EMP_ENAME';

    

    PARTITION_NAME      PART_STATUS    SEG_CREATED

    -------------------------- ----------------------   --------------------------

    P2_I_EMP_ENAME     USABLE               YES      

    P1_I_EMP_ENAME     UNUSABLE         NO

To make an index unusable:

    Query the data dictionary to determine whether an existing index or index partition is usable or unusable.

    hr@PROD> SELECT INDEX_NAME AS "INDEX OR PART NAME", STATUS, SEGMENT_CREATED

      FROM   USER_INDEXES

     UNION ALL

    SELECT PARTITION_NAME AS "INDEX OR PART NAME", STATUS, SEGMENT_CREATED

    FROM   USER_IND_PARTITIONS;

    

    INDEX OR PART NAME             STATUS  SEG

    ------------------------------ -------- ---------------------

    I_EMP_ENAME                           N/A      N/A

    JHIST_EMP_ID_ST_DATE_PK  VALID    YES

    JHIST_JOB_IX                              VALID    YES

    JHIST_EMPLOYEE_IX                 VALID    YES

    JHIST_DEPARTMENT_IX            VALID    YES

    EMP_EMAIL_UK                         VALID    NO

    .

    .

    .

    COUNTRY_C_ID_PK                   VALID    YES

    REG_ID_PK                                  VALID    YES

    P2_I_EMP_ENAME                     USABLE   YES

    P1_I_EMP_ENAME                    UNUSABLE NO

    

    22 rows selected.

    The preceding output shows that only index partition p1_i_emp_ename is unusable.

    Make an index or index partition unusable by specifying the UNUSABLE keyword.

    The following example makes index emp_email_uk unusable:

    hr@PROD> ALTER INDEX emp_email_uk UNUSABLE;

    

    Index altered.

    The following example makes index partition p2_i_emp_ename unusable:

    hr@PROD> ALTER INDEX i_emp_ename MODIFY PARTITION p2_i_emp_ename UNUSABLE;

    

    Index altered.

    Query the data dictionary to verify the status change.

    For example, issue the following query

    hr@PROD> SELECT INDEX_NAME AS "INDEX OR PARTITION NAME", STATUS,  SEGMENT_CREATED 

                            FROM   USER_INDEXES

                             UNION ALL

SELECT PARTITION_NAME AS "INDEX OR PARTITION NAME", STATUS, SEGMENT_CREATED

                             FROM   USER_IND_PARTITIONS;

    

    INDEX OR PARTITION NAME        STATUS   SEG

    ------------------------------ -------- ----------------------------

    I_EMP_ENAME                                         N/A      N/A

    JHIST_EMP_ID_ST_DATE_PK                 VALID    YES

    JHIST_JOB_IX                                             VALID    YES

    JHIST_EMPLOYEE_IX                               VALID    YES

    JHIST_DEPARTMENT_IX                           VALID    YES

    EMP_EMAIL_UK                         UNUSABLE         NO

    .

    .

    .

    COUNTRY_C_ID_PK                                 VALID    YES

    REG_ID_PK                                                VALID    YES

    P2_I_EMP_ENAME                    UNUSABLE         NO

    P1_I_EMP_ENAME                    UNUSABLE         NO

    

    22 rows selected.

    A query of space consumed by the i_emp_ename and emp_email_uk segments shows that the segments no longer exist:

    hr@PROD> SELECT SEGMENT_NAME, BYTES

     FROM   USER_SEGMENTS

      WHERE  SEGMENT_NAME IN ('I_EMP_ENAME', 'EMP_EMAIL_UK');