Creating Indexes

View All Scripts Login to Run Script

Script Name Creating Indexes
Description Indexes are optional structures associated with tables and clusters that allow SQL queries to execute more quickly against a table. This example illustrates how to create various types of indexes.
Area SQL General
Referenced In Database Administrator's Guide
Contributor Oracle
Created Monday October 05, 2015

Statement 1

Create the dept table. Sample indexes will be created for this table.

Creating Sample Table dept For Use In Index Examples

create table dept(    
  deptno     number(2,0),    
  dname      varchar2(14),    
  loc        varchar2(13),    
  constraint pk_dept primary key (deptno)    
)

Table created.

Statement 2

Create the emp table. Sample indexes will be created for this table.

Creating Sample Table emp For Use In Index Examples

create table emp(    
  empno    number(4,0),    
  ename    varchar2(10),    
  job      varchar2(9),    
  mgr      number(4,0),    
  hiredate date,    
  sal      number(7,2),    
  comm     number(7,2),    
  deptno   number(2,0),    
  constraint pk_emp primary key (empno),    
  constraint fk_deptno foreign key (deptno) references dept (deptno)    
)

Table created.

Statement 3
You can create indexes explicitly (outside of integrity constraints) using the SQL statement CREATE INDEX. This statement creates an index named emp_ename for the ename column of the emp table. Notice that several storage settings 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 An Index On A Column Explicitly
```
CREATE INDEX emp_ename ON emp(ename) 
      STORAGE (INITIAL 20K 
      NEXT 20k)
```
```
Index created.
```
Statement 4
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. This statement creates a unique index. 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. However, it is advisable that any index that exists for query performance, including unique indexes, be created explicitly.
Creating A Unique Index
```
CREATE UNIQUE INDEX dept_unique_index ON dept (dname)
```
```
Index created.
```
Statement 5
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. This statement illustrates an online index build operation. Keep in mind that the time that it takes on online index build to complete is proportional to the size of the table and the number of concurrently executing DML statements. Therefore, it is best to start online index builds when DML activity is low.
Creating An Index Online
```
CREATE INDEX emp_name ON emp (mgr) ONLINE
```
```
Index created.
```
Statement 6
If you require more explicit control over the indexes associated with UNIQUE and PRIMARY KEY constraints, then you can specify an index that the database is to use to enforce the constraint. This statement associates and index with a constraint.
Associating An Index With A Constraint
```
CREATE TABLE a ( 
     a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)))
```
```
Table created.
```
Statement 7
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 when you have a non-unique index where ROWID is appended to make the key unique. If you use key compression here, then 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. Key compression can also be useful when 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, this statement compresses duplicate occurrences of a key in the index leaf block.
Creating An Index Using Key Compression
```
CREATE INDEX  emp_job_comp ON emp(job) 
   COMPRESS 1
```
```
Index created.
```
Statement 8
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. This statement creates and invisible index using the CREATE INDEX statement with the INVISIBLE keyword.
Creating An Invisible Index
```
CREATE INDEX dep_loc_inv ON dept(loc) 
      INVISIBLE
```
```
Index created.
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database