Creating Indexes

Creating an Index Explicitly

You can create indexes explicitly (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);

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 NEXT) for an index, the default storage options of the default or specified tablespace are automatically used.

Creating a Unique Index Explicitly

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.

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;

Alternatively, you can define UNIQUE integrity constraints on the desired columns. The database enforces UNIQUE integrity 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.

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 is 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 clause to exercise control over its creation. This includes both when a constraint is defined and enabled, and when a defined but disabled 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.

CREATE TABLE emp (
     empno NUMBER(5) PRIMARY KEY, age INTEGER)
     ENABLE PRIMARY KEY USING INDEX
     TABLESPACE users;

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

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;

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

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

Creating a Function-Based Index

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.

In addition to the prerequisites for creating a conventional index, if the index is based on user-defined 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.

To illustrate a function-based index, consider the following statement 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) is 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 lets you disable the use of a function-based index. Consider doing this if you are working on the body of the function.

Creating a Key-Compressed Index

Creating an index using key compression enables you to eliminate repeated 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 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 the ROWID. The remaining rows become suffix entries consisting of only the ROWID.

• 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. For example, during rebuild you can disable compression as follows:

ALTER INDEX emp_ename REBUILD NOCOMPRESS;

Creating an Unusable Index

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.

If the index is partitioned, then all index partitions are marked UNUSABLE.

Beginning with Oracle Database 11g Release 2, the database does not create an index segment when creating an unusable index.

The following procedure illustrates how to create unusable indexes and query the database for details about the index.

To create an unusable index: 

1. If necessary, create the 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
     2    PARTITION BY HASH (employee_id) PARTITIONS 2
     3    AS SELECT * FROM employees;
    
   Table created.
   
   hr@PROD> SELECT COUNT(*) FROM employees_part;
    
     COUNT(*)
   ----------
          107
   

2. 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)
     2    LOCAL (PARTITION p1_i_emp_ename UNUSABLE, PARTITION p2_i_emp_ename);
    
   Index created.
   

3. Optionally, 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
     2  FROM   USER_INDEXES
     3  WHERE  INDEX_NAME = 'I_EMP_ENAME'
     4  UNION ALL
     5  SELECT PARTITION_NAME AS "INDEX OR PARTITION NAME", STATUS
     6  FROM   USER_IND_PARTITIONS
     7  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
   

4. Optionally, query the data dictionary to determine whether storage exists for the partitions.

   For example, 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",
     2         p.SEGMENT_CREATED AS "SEG_CREATED",   
     3  FROM   USER_IND_PARTITIONS p, USER_SEGMENTS s
     4  WHERE  s.SEGMENT_NAME = 'I_EMP_ENAME';
    
   PARTITION_NAME PART_STA SEG_CREATED 
   -------------- -------- ----------- 
   P2_I_EMP_ENAME USABLE   YES       
   P1_I_EMP_ENAME UNUSABLE NO
   

Creating an Invisible Index

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.

To create an invisible index: 

• Use the CREATE INDEX statement with the INVISIBLE keyword.

  The following statement creates an invisible 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)
        INVISIBLE;