Is Index Expensive in DML?

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> 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 : 1200

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> create table t(a number, b varchar2(30), c date);

Table created.

SQL> create index t_idx1 on t(a);

Index created.

SQL> create index t_idx2 on t(b);

Index created.

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 : 4772

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s