Index in Oracle

 

Indexes

 

Index is also one of Database object.

Suppose we have SAL column

If you run query internally complete SAL column scan and each value of SAL column will be verified with condition.

Total 9 SAL are there and are checked with SAL < 700 condition.

=======================================================================

Suppose there are 90000 records in the column so all 90000 SAL should be verified against the condition.

Frequently selecting SAL based on condition, it will decrease the performance of the query because it has to verify all SAL in column .

Q) How can I decrease the number of verification?

Ans: By creating Index on the column.

 

 I created INDEX on the column SAL.

Index maintains Order data, by default it is ascending order data and also maintains address of each value.

It will reduce the number of comparison.

That’s why it is said that index access data faster and performance will be fast.

--> Index is also Two-dimensional object just like table.

--> Index maintaining column data in ascending order.

Fig. Address path is nothing but ROWID.

ROWID is virtual column (pseudo column) and it contains physical address of each records.

Index is Database object, it is 2-dimensional table

In index we have 2 parts.

1)      ROWID - contains physical address of each records.

2)      DATA - by default data is in ascending order

Advantages:

1)      Decreasing number of value verification

 

  

Above fig. : If we inserted 5000 SAL data, in table data will be inserted randomly , but Index will always maintains order

See in fig. how it is searching empno=50;

It is nothing but FTS (Full Table Scan)

Index internal structure:

1)     Index Unique Scan:-

SQL> select * from hr.emp where emp_id=100;

 

2)     Index Full Scan

Note: In a full index scan , the database reads the entire index in sorted order by of indexed key.

SQL> select emp_id from hr.emp order by emp_id;