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.
