Oracle 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;