I will Explain What is an Index ( Advantages and Disadvantages ) in Oracle Database in this Article.
Oracle Index
An index is associated with tables or table cluster that can speed data access and reducing disk I/O. By creating an index, You can retrieve related set of rows from table instead of All Rows. In database technologies (Oracle, SQL Server, Sybase, DB2, MySQL, PostreSQL, etc.), the objects we create to access the data in the table speedy are called Index.
The usage of Index is very common especially in the performance tuning of Oracle Databases. It is one of the most important objects that are indispensable and proper performance tuning of databases.
Now let’s start by giving an answer to the Index questions. What is index and why it is used ?
Let’s give real life examples to understand the Index, thus it will be understood very easily.
You have an English-Turkish translation dictionary with 100,000 different words, and you are constantly looking for Turkish words and their English equivalent. If This dictionary is not sorted according to First letter, You need to full scan dictionary to find any word’s translation. You can find any words translation in a few second in the Sorted Dictionary , but it takes hours in the non Sorted Dictionary. There are page number of alphabet or Letter in the first page of Dictionary, so you can firstly look at the first page for all words and go to related pages instead of scanning all pages.
When any data is queried from a table in Oracle database, Oracle first check if an index exists for that table. If there is an index and the desired number of records is equal to or less than 15-20% of the table, Oracle returns the requested data via the Index. This type of data access takes less time than usual and is less costly and is called Index Scan.
If there is no index or if the number of records requested is more than 15% ( or more ) of the whole table, then Oracle thinks that querying from index is more costly than scans the whole table. In this case Query Optimizer decides whether it is full table scan or index scan.
Although the 15-20% rate I mentioned above changes sometimes, the threshold value that Oracle offers in this respect is 15-20%. Although this 15% value is valid for large tables, it can reach up to 40% in the small tables I have tried in my real life examples. However, as I said in general, Oracle says that if the data you query is less than or equal to 15% of your table, if your table has an index, Oracle will use the index to access the data very quickly.
Thus, when the index is used to access the desired data, the amount of I/O decreases and both the data is found quickly and system resources are not consumed much.
Index Advantages and Disadvantages in Oracle
There are advantages and disadvantages of using Index in our tables. The advantages and disadvantages of using the index are as follows.
Index Advantages Oracle
- Speed up SELECT queries and reports
- Reduce I/O and Lowest I/O
- Fast Data Access
- Prevent duplication (primary,unique)
Index Disadvantages Oracle
- Since Indexes physically take up space on the Disk, using the Index will increase the extra disk cost unless necessary.
- In general, indexes improve performance in our Select queries and slow down DML (insert, update, delete) operations. Especially in tables where too many DML operations are performed, index should not be used.
- When we use index, database has an extra load because the maintenance (index maintenance) load of the database will increase.
In general, Using indexes is very useful in terms of performance and it is an indispensable blessing for us in terms of not consuming system resources unnecessarily. Using too many DML (Insert, Update, Delete) tables in databases (often seen in OLTP systems) is costly and can cause performance problems, so it should not be used in such tables unless necessary.
But You should use Indexes for databases that have many Select queries and reports ( OLAP,DWH )
You can read the following post to learn Index types in the Oracle database.