Sunday, May 15, 2011

How Adding An Index Can Affect Your Performance

(This post was tested on an Oracle 9 database.)

I read about this on pages 34-35 of Oracle 9i Performance Tuning Tips & Techniques by Richard J. Niemiec. He says that an index will generally degrade the performance of INSERT statements. This is because each time a row is added to the table, an entry has to go in the index too. I decided to verify this myself.









First create the structure of a table:

SQL> CREATE TABLE MY_OBJECTS AS
  2  SELECT * FROM DBA_OBJECTS
  3  WHERE 1=2
  4  /

Table created.

SQL>

See which datafile contains the table (I happen to know there is only one):

SQL> SELECT FILE# FROM V$DATAFILE A,
  2                 DBA_DATA_FILES B,
  3                 DBA_TABLES C
  4  WHERE A.NAME = B.FILE_NAME
  5    AND B.TABLESPACE_NAME = C.TABLESPACE_NAME
  6    AND C.TABLE_NAME = 'MY_OBJECTS'
  7  /

     FILE#
----------
        46

SQL>

Insert data into the table and see how many writes take place (425):

SQL> ALTER SYSTEM CHECKPOINT
  2  /

System altered.

SQL> SELECT PHYWRTS FROM V$FILESTAT
  2  WHERE FILE# = 46
  3  /

   PHYWRTS
----------
      7092

SQL> INSERT INTO MY_OBJECTS
  2  SELECT * FROM DBA_OBJECTS
  3  /

31526 rows created.

SQL> ALTER SYSTEM CHECKPOINT
  2  /

System altered.

SQL> SELECT PHYWRTS FROM V$FILESTAT
  2  WHERE FILE# = 46
  3  /

   PHYWRTS
----------
      7517

SQL>

Truncate the table:

SQL> TRUNCATE TABLE MY_OBJECTS
  2  /

Table truncated.

SQL>

Add an index to the table:

SQL> CREATE INDEX MY_OBJECTS_INDEX
  2  ON MY_OBJECTS(OBJECT_NAME)
  3  /

Index created.

SQL>

Insert data into the table again and see how many writes take place (618):

SQL> ALTER SYSTEM CHECKPOINT
  2  /

System altered.

SQL> SELECT PHYWRTS FROM V$FILESTAT
  2  WHERE FILE# = 46
  3  /

   PHYWRTS
----------
      7945

SQL> INSERT INTO MY_OBJECTS
  2  SELECT * FROM DBA_OBJECTS
  3  /

31527 rows created.

SQL> ALTER SYSTEM CHECKPOINT
  2  /

System altered.

SQL> SELECT PHYWRTS FROM V$FILESTAT
  2  WHERE FILE# = 46
  3  /

   PHYWRTS
----------
      8563

SQL>

In this example, inserting the same data twice required the following number of physical writes:

Without an index: 425
With an index: 618.

So the book was right.

The observant among you will notice a slight flaw in the test. The first INSERT created 31526 rows whereas the second created 31527 because it included the index I had just created. Hopefully that will not have changed the results too much!  

No comments: