How much expensive are Indexes?

Indexes are used to enhance expensive queries to run more quickly. They provide faster access path to table data. But, there is a trade-off using indexes, DML statements against the table would consume more time as all indexes on the table have to be updated during the DML.

Here is a small test I performed to see how index existence would affect the DML statements. For the test case, I create a small table without indexes, inserted 100,000 records and measure the time taken. I repeat the same test but this time with indexes on all columns. The difference in timing will show us, how much expensive are our indexes.

Test 1: Without Indexes

SQL> set serveroutput on 
SQL> 
SQL> drop table t purge;

Table dropped.

SQL> create table t(a number, b varchar2(30), c date);

Table created.

SQL> declare 
  2   x number;
  3 begin
  4   x := dbms_utility.get_time;
  5   for i in 1..100000 loop 
  6     insert into t values(i, 'value = ' || i, sysdate + mod(i,365));
  7   end loop;
  8   dbms_output.put_line('Time taken WITHOUT indexes : ' ||to_char(dbms_utility.get_time - x));
  9 end; 
 10 /

Time taken WITHOUT indexes : 475

PL/SQL procedure successfully completed.

Now, repeat the same test by creating indexes on all the three columns.

Test 2: With Indexes:

SQL> drop table t purge; 

Table dropped.

SQL> 
SQL> create table t(a number, b varchar2(30), c date); 

Table created. 

SQL> 
SQL> 
SQL> create index t_idx1 on t(a); 

Index created. 

SQL> 
SQL> create index t_idx2 on t(b); Index created. 

SQL> 
SQL> create index t_idx3 on t(c); 

Index created. 

SQL> declare
  2   x number;
  3 begin
  4   x := dbms_utility.get_time;
  5   for i in 1..100000 loop
  6     insert into t values(i, 'value = ' || i, sysdate + mod(i,365));
  7   end loop;
  8   dbms_output.put_line('Time taken *WITH* indexes : ' ||to_char(dbms_utility.get_time - x));
  9 end;
 10 /

Time taken *WITH* indexes : 1006 

PL/SQL procedure successfully completed.

Well, it’s evident from the above tests that having too many indexes surely affects performance of DML statements. When I had three indexes, time taken to process 100,000 records was more than double compared to process the same number of records without indexes.

Moral of the test is to create indexes when required and avoid over-creating them. Oracle documentation discusses some guidelines of using and managing indexes.

Happy reading !!!