CREATE INDEX statement is used to create Indexes using table columns.An index allows faster retrieval of records.It is mainly used as a performance-tuning method.An index creates an entry for each value that appears in the indexed columns.
You can create indexes explicitly using the SQL statement CREATE INDEX or as a part of CREATE TABLE script.
SYNTAX:
CREATE [UNIQUE] INDEX indexON table(column1,.. column_n)[ STORAGE CLAUSE ];
For example let us create a table MYTABLE;
SQL> CREATE TABLE MYTABLE (name varchar2(50),age number,id number); Table created
Now create an unique index for MYTABLE using coulmns name and id.
SQL> CREATE UNIQUE INDEX MY_IDX ON MYTABLE(name,id); Index created SQL>
Now each row in the table will be uniquely indexed using the index MY_IDX.This will help faster retrieval of records from huge tables.
The above two steps can be combined into one step process as follows.
SQL> CREATE TABLE MYTABLE (name varchar2(50),age number,id number, CONSTRAINT my_Constraint unique(name,id) USING INDEX (CREATE UNIQUE INDEX MY_IDX on MYTABLE(name,id))); SQL> Table created
Related Articles,Oracle Tables: Create Table as Select
Oracle/PLSQL: Create table with foreign key constraint
CREATE TABLE statement: create a table with composite primary key
Oracle PL/SQL:CREATE TABLE statement: create a table with primary key.
Oracle PL/SQL:Create Table
Technorati Tags:
CREATE INDEX, CONSTRAINT, CREATE UNIQUE INDEX, UNIQUE INDEX, CREATE TABLE
Pingback: Oracle Cursors | OPEN ,FETCH and CLOSE Cursor statements. | SQL and PLSQL
Pingback: Oracle 'ALTER TABLE' command to ADD columns syntax | SQL and PLSQL