Beware of over-creating Indexes!!

Author: Momen

Page: 1 2

All of us know that indexes are optional structures that are associated with tables and clusters. Indexes allow SQL statements to execute more quickly and therefore they are used to enhance expensive queries so that they can run more quickly.

Affect of Indexes on DML Statements:

Although indexes execute SQL statements quickly by having faster access path to table data but on the other hand some trade-off is associated in using indexes. Since all indexes on the table will be updated with DML statements are attempted so the DML statements will consume more time.

Below simple test will help you identify the affect of indexes on 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.

Create Table without Indexes:

First of all we will create a test table with three columns. No indexes will be created on this table.

SQL> set serveroutput on
SQL>
SQL> drop table MYTABLE_WITHOUTINDEX purge;
Table dropped.

SQL> create table MYTABLE_WITHOUTINDEX(a number, b varchar2(30), c date);
Table created.

Insert Data:

Now we will insert 100,000 records in the table and measure the time taken in executing DML statements for these 100,000 records.

SQL> declare
x number;
begin
x := dbms_utility.get_time;
for i in 1..100000 loop
insert into MYTABLE_WITHOUTINDEX values(i, 'value = ' || i, sysdate + mod(i,365));
end loop;
dbms_output.put_line('Time taken for DML statements on table without indexes : ' ||to_char(dbms_utility.get_time - x));
end;
/

Time taken for DML statements on table without indexes: 475
PL/SQL procedure successfully completed.

Create Table with Indexes:

Now we will create another table. This time we will create indexes on all the three columns of our test table.

SQL> drop table MYTABLE_WITHINDEX purge;
Table dropped.

SQL> create table MYTABLE_WITHINDEX (a number, b varchar2(30), c date);
Table created. Continued...

Page: 1 2

 More Oracle Database Articles
   Database Security: Step by step guideline
   Why Stored Procedures?
   SQL Injection Attacks - Are you safe?
   Oracle SQL Resource Usage, Some Concerns
   The Secrets of Oracle Scalar subqueries!!