Oracle Tutorials - Index - Data Structure for Query Performance

Oracle Tutorials - Index - Data Structure for Query Performance

What Is an Index?

Index is an optional structure associated with a table that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.

How To Create a Table Index?

If you have a table with a lots of rows, and you know that one of the columns will be used often a search criteria, you can add an index for that column to in improve the search performance. To add an index, you can use the CREATE INDEX statement as shown in the following script:

CREATE TABLE tip (id NUMBER(5) PRIMARY KEY,

  subject VARCHAR(80) NOT NULL,

  description VARCHAR(256) NOT NULL,

  create_date DATE DEFAULT (sysdate));

Table created.

 

CREATE INDEX tip_subject ON tip(subject);

Index created.

How To List All Indexes in Your Schema?

If you log in with your Oracle account, and you want to get a list of all indexes in your schema, you can get it through the USER_INDEXES view with a SELECT statement, as shown in the following SQL script:

SELECT index_name, table_name, uniqueness 

  FROM USER_INDEXES WHERE table_name = 'EMPLOYEES';

INDEX_NAME              TABLE_NAME            UNIQUENES

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

EMP_EMAIL_UK            EMPLOYEES             UNIQUE

EMP_EMP_ID_PK           EMPLOYEES             UNIQUE

EMP_DEPARTMENT_IX       EMPLOYEES             NONUNIQUE

EMP_JOB_IX              EMPLOYEES             NONUNIQUE

EMP_MANAGER_IX          EMPLOYEES             NONUNIQUE

EMP_NAME_IX             EMPLOYEES             NONUNIQUE

As you can see, the pre-defined table EMPLOYEES has 6 indexes defined in the default sample database.

What Is an Index Associated with a Constraint?

An index associated with a constraint because this constraint is required to have an index. There are two types of constraints are required to have indexes: UNIQUE and PRIMARY KEY. When you defines a UNIQUE or PRIMARY KEY constraint in a table, Oracle will automatically create an index for that constraint. The following script shows you an example:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

  first_name VARCHAR(80) NOT NULL,

  last_name VARCHAR(80) NOT NULL,

  birth_date DATE NOT NULL,

  social_number VARCHAR(80) UNIQUE NOT NULL);

Table created.

SELECT index_name, table_name, uniqueness 

  FROM USER_INDEXES WHERE table_name = 'STUDENT';

INDEX_NAME              TABLE_NAME            UNIQUENES

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

SYS_C004123             STUDENT               UNIQUE

SYS_C004124             STUDENT               UNIQUE

The result confirms that Oracle automatically created two indexes for you.

How To Drop an Index?

If you don't need an existing index any more, you should delete it with the DROP INDEX statement. Here is an example SQL script:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

  first_name VARCHAR(80) NOT NULL,

  last_name VARCHAR(80) NOT NULL,

  birth_date DATE NOT NULL,

  social_number VARCHAR(80) UNIQUE NOT NULL);

Table created.

CREATE INDEX student_birth_date ON student(birth_date);

Index created.

SELECT index_name, table_name, uniqueness 

  FROM USER_INDEXES WHERE table_name = 'STUDENT';

INDEX_NAME              TABLE_NAME            UNIQUENES

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

SYS_C004129             STUDENT               UNIQUE

SYS_C004130             STUDENT               UNIQUE

STUDENT_BIRTH_DATE      STUDENT               NONUNIQUE 

DROP INDEX STUDENT_BIRTH_DATE;

Index dropped.

Can You Drop an Index Associated with a Unique or Primary Key Constraint?

You can not delete the index associated with a unique or primary key constraint. If you try, you will get an error like this: ORA-02429: cannot drop index used for enforcement of unique/primary key.

What Happens to Indexes If You Drop a Table?

If you drop a table, what happens to its indexes? The answer is that if a table is dropped, all its indexes will be dropped too. Try the following script to see yourself:

What Happens to the Indexes If a Table Is Recovered?

If you dropped a table, and recovered it back from the recycle bin, what happens to its indexes? Are all indexes recovered back automatically? The answer is that all indexes will be recovered, if you recover a dropped table from the recycle bin. However, the indexes' names will not be the original names. Indexes will be recovered with the system assigned names when they were dropped into the cycle bin. 

How To Rebuild an Index?

If you want to rebuild an index, you can use the "ALTER INDEX ... REBUILD statement as shown in the following SQL script:

ALTER INDEX EMP_NAME_IX REBUILD;

Statement processed.

How To See the Table Columns Used in an Index?

You can a list of indexes in your schema from the USER_INDEXES view, but it will not give you the columns used in each index in the USER_INDEXES view. If you want to see the columns used in an index, you can use the USER_IND_COLUMNS view. Here is an example script for you:

SELECT index_name, table_name, column_name 

  FROM USER_IND_COLUMNS WHERE table_name = 'EMPLOYEES';

INDEX_NAME           TABLE_NAME       COLUMN_NAME

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

EMP_EMAIL_UK         EMPLOYEES        EMAIL

EMP_EMP_ID_PK        EMPLOYEES        EMPLOYEE_ID

EMP_DEPARTMENT_IX    EMPLOYEES        DEPARTMENT_ID

EMP_JOB_IX           EMPLOYEES        JOB_ID

EMP_MANAGER_IX       EMPLOYEES        MANAGER_ID

EMP_NAME_IX          EMPLOYEES        LAST_NAME

EMP_NAME_IX          EMPLOYEES        FIRST_NAME

How To Create a Single Index for Multiple Columns?

If you know a group of multiple columns will be always used together as search criteria, you should create a single index for that group of columns with the "ON table_name(col1, col2, ...)" clause.

CREATE INDEX student_names ON student(first_name,last_name);

Index created.