Indexes

Indexing  a table is an access strategy that is a way to sort and search records in the table. Indexes  are essential to improve the speed with which the records can be located and retrieved from a table.
An Index is an ordered list of the contents of a column, or group of columns of a table.
Indexing involves forming a two dimensional matrix completely independent of the table on which the index is being created.

• A column which will hold sorted data extracted from the table on which the index is being created.
• An address field that identifies the location of the record in the oracle database. This address field is called Rowid.

When data is inserted in the table the oracle engine inserts the data value in the index. For every data value held in the index the oracle engine inserts a unique rowid value. This is done for every data value inserted into the index, without exception. This ROWID  indicates exactly where the record is stored in the table.

Addressfield in the index:-
The address field of an index Is called ROWID. ROWID is an internal generated and maintained binary value which identifies a record. The information in the ROWID column provides Oracle engine the location of the table and a specific record in the oracle database.
The ROWID format is as follows:-
BBBBBBB.RRRR.FFFF
Where
BBBBBBB   à Data Blocks
RRRR          à Records.
FFFF           à Unique Field number
Duplicate Or Unique Index
Oracle follows the two types of indexes. These are

• Indexes that allows duplicate values for the indexed columns i.e Duplicate Index.
• Indexes that deny duplicate values for the indexed columns i.e Unique index.

Instances when the Oracle engine uses an index for data extraction:-

• A select statement with where clause specified on the column on which an index is created.
• A select statement with order by caluse specified on the column on which an index is exists.

Instances when the Oracle engine does not use an index for data extraction:-

• A select statement without search criteria and order by clause.
• A select statement with where clause specified on the column on which an index is not defined.
• A select statement with order by clause specified on the column on which an index is not defined.
• Problem When Too many Indexes are created:-

Each time a record is inserted into the table oracle locates free space in the blocks in the data files. The oracle engine then inserts a record in all the indexes associated with the table. The index entries are sorted in the ascending order as well. If too many indexes were created the oracle engine would take longer to insert a record in the table since index processing must be done after every record.
Thus though indexes spends up data retrieval the inserts would be slow. A balance must be maintained such that only the columns that are frequently used for retrieval are indexed.
An index can be created one or more columns. Based on the number of columns included in the index . an index can be

• Simple Index

• Composite index

Simple Index
An index created on single column of a table is called simple index.
Syntax:- Create index indexname on tablename(columnname);
Ex:-     1)   create index myindex on vision(eno);
2)   Select * from vision where eno=300;
3)   select * from vision order by eno;
4)   Select * from vision where sal>2000; 
( in this example index will not applicable because condition is not on
indexed column)

Composite Index:- An index created on more than one column it is called composite index . That allows duplicate values.
Syntax:- create index indexname on tablename(columnname,columname,……);
Ex:-     1)   create index myindex1 on vision (eno,sal);
            2)   Select * from vision where eno=300;
             3)   select * from vision order by eno;
             4)   Select * from vision where sal>2000;

Unique Index :- A unique index can also be created on one or more columns. If an index is created on a single column it is called simple unique index. An index is created on more that one column is called composite unique index.
Syntax:-   1) create unique index myindex2 on stock1(icode);
                        (This will show error because duplicates are existed)
            2) create unique index myindex2 on vision(eno);
            3) create unique index myindex3 on vision(eno,sal);
           4) select * from vision where eno=300;
            5) select * from vision where sal>2000;
Note:- Once the index is created on a table the same table should not use for creating index again.

• Ex:-Create index myind on vision(eno);

Dropping Index:- Indexed files can be dropped by using the drop index command
Syntax:- Drop index indexname;
Ex:-         drop index myindex;

Deleting duplicate records using ROWID
 If the user wants to delete duplicate values in the table, a delete statement with a where clause deletes all the records as per the condition. But by using rowid and we can remove duplicate values by grouping the data.
Ex:-      create table vision6(eno number(5),na varchar2(20),sal number(5));
            Insert into vision6 values(100,’prasad’,3200);
            Insert into vision6 values(200,’rajesh’,2100);
            Insert into vision6 values(100,’prasad’,3200);
            Insert into vision6 values(200,’rajesh’,2100);
            delete from vision6 where rowid not in(select min(rowid) from
            vision6 group by eno,na,sal);

Using ROWNUM and ROWID in Select statement
Ex:- select rownum,eno,na,sal from vision;
            Select * from vision where rownum<3;

Select rownum,rowid,eno,na,sal from vision;

ORDER BY clause, Index used and ROWNUM :
If the order by clause is specified and the oracle engine does not use an index the rows will be retrieved in the order in which the data was inserted and thus the rownum assigned will also be in the same order.
Ex:-     select * from client;
            select rownum,cno,na,amt from client where rownum<5 order by na;

 

For
Online Classes

Contact Us: +919885348743