Finding the Cost of an Index

Building and maintaining an index structure can be expensive, and it can consume resources such as disk space, CPU, and I/O capacity.

According to the Oracle Database Performance Tuning Guide 10g Release 2 (10.2): "...each index maintained by an INSERT, DELETE, or UPDATE of the indexed keys requires about three times as much resource as the actual DML operation on the table. What this means is that if you INSERT into a table with three indexes, then it will be approximately 10 times slower than an INSERT into a table with no indexes".

So let’s attempt to demonstrate this estimate. Let’s start out with one of the largest tables in the Oracle E-Business Suite; ONT.OE_ORDER_LINES_ALL the table stores sales order lines for the Order Management module. I have recreated the table and associated indexes from a 11.5.10.2 installation on a Oracle 10.2 XE database.

Some facts on my testing:

• The ONT.OE_ORDER_LINES_ALL table contains 340 columns.
• Standard indexes consists of 1 unique reverse key index and 17 non unique indexes, together they index 26 columns.
• There are 14 custom non unique indexes covering 35 columns (9 function based).

Test 1: Insert 20000 rows using Insert with select sub query.

With no indexes: Avg 2.5 Seconds.
With standard indexes: Avg 6 Seconds.
With standard and custom indexes: Avg 18 Seconds.

Test 2: Insert 20000 rows using Bulk Collect Cursor and For Loop with Insert:

With standard indexes: Avg 8 Seconds.
With standard and custom indexes: Avg 22 Seconds.

I did not experience the 10 times slower estimate made by the Oracle Performance Tuning guide but the custom indexes significantly degraded insert performance.

So what is custom indexing doing to your Oracle EBS performance?