Oracle indexes tuning

Indexes in Oracle database.

The indexes are

  • B-Tree indexes or balanced tree indexes;
  • bitmap indexes
  • partitioned indexes
  • domain indexes
  • function-based indexes

The B-Tree Indexes
          B-Tree Indexes are the most convenient thing when querying for a small amount of data from a large table. Indexes help out the most in reducing the i/o in this case.The bigger the table and lower the number of rows that you want to get them, then the more effective way is the index.
          Even if you see a book it has an index, this shows the importance of index. Even a book needs a index then think of the case of a terabyte oracle database. The reasons are a lot. When searching an article it would take huge time to find the particular area of interest but without using the index then it would to read the whole book. It's similar when you're querying against a database. so by working on the index, the database would have to read more data.
          The indexes improves read and update operations using sub-queries. However when updates are performed often in a table with index then every time the index has to get updated regarding the change in the oracle database. Sincethe amount of i/o is drastically increased causing burden to the DB server. So having indexes for read only table is recommended by oracle.The oracle dba should ensure the usage of the index. Otherwise its going to be a performance degrading for the oracle database.


hits count
Database | Oracle PartnerNetwork Blog