Indexes

Indexes

It is a database object used to sort the values of a column for retrieving data fastly from a table. By default the rows in a table are identified with their row id’s

When ever you create an index on a table, the system gives index id’s which are stored in the index. If a table is having index then the system retrieves the rows basing on the index id’s else it retrieves value using row id’s

Types of Index

There are two types of indexes

1. Simple Index

2. Composite Index

There are two ways to create an index. They are

1. Creating index on a single column
2. Creating index on multiple columns

Simple Index

Any index created on a single table is called a simple index

Syntax : create index <index name> on <table name>(column);

Creating Index On A Single Column

When you create an index on a single column,

Create Table temporary(sno number(3), sname varchar2(10));

NOTE :

Index name must be unique in the database and it is user choice. Since index is a data base object, system allocates some memory in the database. The index stores all index id’s allocated for a column.

Note : we can not create any index on views.

SQL> select * from temporary;

SNO NAME

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

110 Nithya

111 Saloni

112 Mahesh

113 Priya

114 Prasad

115 Aruna

creating an index

create index idx_temp on temporary(sno);

Note : We can not see the Details of Index file

select * from idx_temp;

ERROR at line 1:

ORA-00942: table or view does not exist

Creating Unique indexes on a Single column

Syntax :

Create unique index <index name> on <table name> (<column>)

Note : Unique index can be created on any table, except it does not contain any duplicate values on the column for which you are creating a unique index

Example :

create unique index idx_temp on sample1(sno);

insert into sample1 values(&sno,’&sname’);

enter any value for sno: 1

enter any value for sname : Mahesh

enter any value for sno: 2

enter any value for sname : Prasad

enter any value for sno: 1

enter any value for sname : Nithya

Error :

Ora-00001: unique constraint (scott.idx_temp) violated

Note :

1. Once if u create any index on a particular column, then that column can not be re indexed. To re index on that column, first remove that existing index using drop index command and then create new index on that column
2. If you create an index on an existing index, it will show an error message

create unique index idx_temp1 on temporary(sno)

ERROR at line 1:

ORA-01408: such column list already indexed

Dropping an Index

Drop Index : This Command Is Used To Drop Any Index

Syntax : Drop Index <Index Name>

Example : drop index idx_temp;

Composite index

An index created on 2 or more columns in a table is called composite index.

Syntax :

Create index <index name> on

<table name> (<column1>, <column2>,-------)

first create a table

create table bank(accno number(3),accna varchar2(10),ddno number(6),

amt number(6));

next Create the index on that table

create index idx_bank on bank (accno,ddno);

next Insert some values into that table

• insert into bank values (100,'Prasad',11111,2343)
• insert into bank values (102,'Mahesh',22222,4334)
• insert into bank values (102,'Nithya',22222,4334)

select * from bank;

ACCNO ACCNA DDNO AMT

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

100 Prasad 11111 2343

102 Mahesh 22222 4334

102 Nithya 22222 4334

100 Saloni 11111 3433

Composite unique index

We can create unique index on two or more columns also using composite unique index

Syntax :

Create unique index <index name> on <table name> (<column1>,<column2>,-------)

create unique index idx_bank1 on bank1(accno,ddno);

• insert into bank values (100,'Priya',11111,3445)
• insert into bank values (102,'Mahi',22222,4334)
• insert into bank values (102,'Sound',22222,4334)

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.IDX_BANK1) violated

Note :

1. There is a problem in the above index, the index will give an error only when both columns value are given duplicate values. If any one is different the index will accept those values.

2. Once a column is indexed already, it is not possible to reindex that column to any other index file

Drop index

This command is used to drop any index

Syntax : Drop index <index name>

Ex : Drop Index Idx_Sample;

There are two built in objects, which gives information about the indexs that are in the data base.

1. user_indexes : contains all user defined index’s information

Example :

select * from user_indexes;

select index_name, table_name, table_owner from user_indexes;

1. All_indexes : contains all user defined index’s and pre defined index’s information

Example :

select * from all_indexes;

select index_name, table_name, table_owner from all_indexes;