How indexes can degrade performance

articles: 

Indexes may improve the performance of SELECT statements, but what about DML? This simple demo shows how bad they can be.
First, I'll create a table and insert a million rows:

orclz>
orclz> create table t1 (c1 number);

Table created.

orclz> set timing on
orclz> insert into t1 select rownum from dual connect by level <= 1000000;

1000000 rows created.

Elapsed: 00:00:02.58
orclz>
And now repeat the test, but this time with the column indexed:
orclz>
orclz> drop table t1;

Table dropped.

Elapsed: 00:00:00.13
orclz> create table t1 (c1 number);

Table created.

Elapsed: 00:00:00.01
orclz> create index i1 on t1 (c1);

Index created.

Elapsed: 00:00:00.02
orclz> insert into t1 select rownum from dual connect by level <= 1000000;

1000000 rows created.

Elapsed: 00:00:10.29
orclz>

Nearly four times as slow! And that is just one simple numeric index. I see tables with twenty or thirty indexes: wide compound indexes, complex function based indexes. The effects on high volume DML may be devastating. Of course this simple not very scientific test may not apply to your environment, but it serves to emphasize the point that indexes have a cost. Be sure that you really need them.
Tests done using DB release 12.1.0.2, Windows 10, Dell laptop with SSD disc.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com