• Sharebar

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:
, , , ,

 
Be Sociable, Share!
SEO Powered by Platinum SEO from Techblissonline