1. 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.
2. How To Run SQL Statements through the Web Interface?
If you don't like the command line interface offered by SQL*Plus, you can use the Web interface to run SQL statements. Here is how:
◉ Open your Web browser to http://localhost:8080/apex/
◉ Log in to the server with the predefined sample user account: HR/fyicenter
◉ Click the SQL icon
◉ Click the SQL Commands icon
◉ Enter any SQL statement like: "SELECT COUNT(*) FROM USER_TABLES;", in the text area and click Run button
◉ Your Oracle will execute the statement, and display the result in the result area.
3. 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.
4. 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.
5. 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.
6. How To Rename an Index?
Let's say you have an existing index, and you don't like its name anymore for some reason, you can rename it with the ALTER INDEX ... RENAME TO statement. Here is an example script on how to rename an index:
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_C004153 STUDENT UNIQUE
SYS_C004154 STUDENT UNIQUE
ALTER INDEX SYS_C004153 RENAME TO student_pk;
Statement processed.
SELECT index_name, table_name, uniqueness
FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME TABLE_NAME UNIQUENES
----------------------- --------------------- ---------
STUDENT_PK STUDENT UNIQUE
SYS_C004154 STUDENT UNIQUE
7. 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.
8. 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.
9. 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:
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_C004141 STUDENT UNIQUE
SYS_C004142 STUDENT UNIQUE
STUDENT_BIRTH_DATE STUDENT NONUNIQUE
DROP TABLE STUDENT;
Table dropped.
SELECT index_name, table_name, uniqueness
FROM USER_INDEXES WHERE table_name = 'STUDENT';
no data found
No records in USER_INDEXES view found for your name STUDENT, after you dropped STUDENT table.
10. How To Recover a Dropped Index?
If you have the recycle bin feature turned on, dropped indexes are stored in the recycle bin. But it seems to be command to restore a dropped index out of the recycle bin. FLASHBACK INDEX is not a valid statement. See the following script:
ALTER SESSION SET recyclebin = on;
Statement processed.
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.
DROP INDEX STUDENT_BIRTH_DATE;
Index dropped.
SELECT object_name, original_name, type, droptime
FROM recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME
----------------------- ------------------ ----- ----------
BIN$1LlsjTxERKq+C7A==$0 STUDENT_BIRTH_DATE INDEX 2006-04-01
FLASHBACK INDEX student_birth_date TO BEFORE DROP;
ORA-00905: missing keyword
11. 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. The following SQL script shows you this behavior:
ALTER SESSION SET recyclebin = on;
Statement processed.
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_C004141 STUDENT UNIQUE
SYS_C004142 STUDENT UNIQUE
STUDENT_BIRTH_DATE STUDENT NONUNIQUE
DROP TABLE student;
Table dropped.
SELECT index_name, table_name, uniqueness
FROM USER_INDEXES WHERE table_name = 'STUDENT';
no data found
FLASHBACK TABLE student TO BEFORE DROP;
Flashback complete.
SELECT index_name, table_name, uniqueness
FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME TABLE_NAME UNIQUENES
------------------------------ ---------- ---------
BIN$K47Sg+udQv2tDUW5cWAIrQ==$0 STUDENT UNIQUE
BIN$6WI0gc79QNqLSNGp2H2Q1Q==$0 STUDENT UNIQUE
BIN$9HwZermkRt+9gonHS/klsQ==$0 STUDENT NONUNIQUE
If you have trouble running the FLASHBACK statement on your Web interface, run it with SQL*Plus.
12. 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:
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
ALTER INDEX EMP_NAME_IX REBUILD;
Statement processed.
13. 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
14. 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. Here is an example of one index for two columns:
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_names ON student(first_name,last_name);
Index 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
STUDENT_NAMES STUDENT NONUNIQUE
SELECT index_name, table_name, column_name
FROM USER_IND_COLUMNS WHERE table_name = 'STUDENT';
INDEX_NAME TABLE_NAME COLUMN_NAME
-------------------- ---------------- ----------------
SYS_C004135 STUDENT ID
SYS_C004136 STUDENT SOCIAL_NUMBER
STUDENT_NAMES STUDENT FIRST_NAME
STUDENT_NAMES STUDENT LAST_NAME
The USER_IND_COLUMNS view confirms that index STUDEND_NAMES has two columns FIRST_NAME and LAST_NAME.
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.
2. How To Run SQL Statements through the Web Interface?
If you don't like the command line interface offered by SQL*Plus, you can use the Web interface to run SQL statements. Here is how:
◉ Open your Web browser to http://localhost:8080/apex/
◉ Log in to the server with the predefined sample user account: HR/fyicenter
◉ Click the SQL icon
◉ Click the SQL Commands icon
◉ Enter any SQL statement like: "SELECT COUNT(*) FROM USER_TABLES;", in the text area and click Run button
◉ Your Oracle will execute the statement, and display the result in the result area.
3. 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.
4. 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.
5. 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.
6. How To Rename an Index?
Let's say you have an existing index, and you don't like its name anymore for some reason, you can rename it with the ALTER INDEX ... RENAME TO statement. Here is an example script on how to rename an index:
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_C004153 STUDENT UNIQUE
SYS_C004154 STUDENT UNIQUE
ALTER INDEX SYS_C004153 RENAME TO student_pk;
Statement processed.
SELECT index_name, table_name, uniqueness
FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME TABLE_NAME UNIQUENES
----------------------- --------------------- ---------
STUDENT_PK STUDENT UNIQUE
SYS_C004154 STUDENT UNIQUE
7. 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.
8. 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.
9. 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:
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_C004141 STUDENT UNIQUE
SYS_C004142 STUDENT UNIQUE
STUDENT_BIRTH_DATE STUDENT NONUNIQUE
DROP TABLE STUDENT;
Table dropped.
SELECT index_name, table_name, uniqueness
FROM USER_INDEXES WHERE table_name = 'STUDENT';
no data found
No records in USER_INDEXES view found for your name STUDENT, after you dropped STUDENT table.
10. How To Recover a Dropped Index?
If you have the recycle bin feature turned on, dropped indexes are stored in the recycle bin. But it seems to be command to restore a dropped index out of the recycle bin. FLASHBACK INDEX is not a valid statement. See the following script:
ALTER SESSION SET recyclebin = on;
Statement processed.
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.
DROP INDEX STUDENT_BIRTH_DATE;
Index dropped.
SELECT object_name, original_name, type, droptime
FROM recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME
----------------------- ------------------ ----- ----------
BIN$1LlsjTxERKq+C7A==$0 STUDENT_BIRTH_DATE INDEX 2006-04-01
FLASHBACK INDEX student_birth_date TO BEFORE DROP;
ORA-00905: missing keyword
11. 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. The following SQL script shows you this behavior:
ALTER SESSION SET recyclebin = on;
Statement processed.
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_C004141 STUDENT UNIQUE
SYS_C004142 STUDENT UNIQUE
STUDENT_BIRTH_DATE STUDENT NONUNIQUE
DROP TABLE student;
Table dropped.
SELECT index_name, table_name, uniqueness
FROM USER_INDEXES WHERE table_name = 'STUDENT';
no data found
FLASHBACK TABLE student TO BEFORE DROP;
Flashback complete.
SELECT index_name, table_name, uniqueness
FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME TABLE_NAME UNIQUENES
------------------------------ ---------- ---------
BIN$K47Sg+udQv2tDUW5cWAIrQ==$0 STUDENT UNIQUE
BIN$6WI0gc79QNqLSNGp2H2Q1Q==$0 STUDENT UNIQUE
BIN$9HwZermkRt+9gonHS/klsQ==$0 STUDENT NONUNIQUE
If you have trouble running the FLASHBACK statement on your Web interface, run it with SQL*Plus.
12. 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:
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
ALTER INDEX EMP_NAME_IX REBUILD;
Statement processed.
13. 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
14. 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. Here is an example of one index for two columns:
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_names ON student(first_name,last_name);
Index 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
STUDENT_NAMES STUDENT NONUNIQUE
SELECT index_name, table_name, column_name
FROM USER_IND_COLUMNS WHERE table_name = 'STUDENT';
INDEX_NAME TABLE_NAME COLUMN_NAME
-------------------- ---------------- ----------------
SYS_C004135 STUDENT ID
SYS_C004136 STUDENT SOCIAL_NUMBER
STUDENT_NAMES STUDENT FIRST_NAME
STUDENT_NAMES STUDENT LAST_NAME
The USER_IND_COLUMNS view confirms that index STUDEND_NAMES has two columns FIRST_NAME and LAST_NAME.
Posts
0 comments:
Post a Comment