When the Wrong Index Works Better

This week I saw a case where a poor choice of index may have actually improved performance…. temporarily.

An application with performance problems had been investigated and a new index had been implemented which reportedly improved reduced the relevant query’s execution time from 40 seconds to between 6-15 seconds.  However, there were still complaints about the variable length of the execution time, so I began to review the situation.

From V$SQL I could see hundreds of examples of the query that had run recently – after the index had been created.  The elapsed time for these took up to 103 seconds to complete.  The number of rows returned and physical/logical block gets also varied greatly.  I began to doubt the accuracy of the description of the new index’s impact, so I needed to verify that the new index was the best choice.

The queries were similar to this (altered for simplicity and confidentiality):

SELECT some_columns
FROM  dummy_table
WHERE FOREIGN_KEY = :b1
AND AMOUNT1 + AMOUNT2 <>0  
AND to_char(TIME_STAMP, 'yyyy-mm-dd HH24:MI:SS') <= '2009-08-18 17:03:54'
/

Each query had a literal for the TIME_STAMP comparison.
The first thing I noticed was the common developer error of putting the function on the column instead of the constant.  It should have been rewritten as:

TIME_STAMP <= to_date('2009-08-18 17:03:54','yyyy-mm-dd HH24:MI:SS');
This would allow Oracle to only convert between char and date once on the constant, rather than on the column for each row processed.  Also, it would allow the TIME_STAMP value to be used to access an index, if a suitable one existed.
These were the relevant indexes:
                                                   DIST KEYS       LEAF BLKS LAST ANALYZED
INDEX_NAME                                         (SELCTY%)   BPK (DEPTH)   (ESTIMATE %)   COLUMN (BYTES ASC/DESC)
-------------------------------------------------- ---------- ---- --------- -------------- ---------------------------------------
DUMMY_TABLE_IDX1                                   34K (0)    #### 243K (3)  15/08/09 (1)   FOREIGN_KEY (22 ASC)

DUMMY_TABLE_IDX2                                   346K (1)    108 283K (3)  15/08/09 (1)   TIME_STAMP (7 ASC)

DUMMY_TABLE_IDX3                                   25M (48)      1 192K (3)  15/08/09 (7)   FOREIGN_KEY (22 ASC)
                                                                                            TIME_STAMP (7 ASC)

DUMMY_TABLE_PK (U)                                 51M (100)     1 274K (2)  15/08/09 (1)   PRIMARY_KEY (22 ASC)
 DUMMY_TABLE_IDX3 was the newly created index that was supposed to improve performance.
Although the to_char function prevented the TIME_STAMP column from being used to access the index, it could still be used to filter rows before accessing the DUMMY_TABLE to get the rest of the columns.  The DUMMY_TABLE was big and could never reside completely in the cache, so avoiding unnecessary table accesses would boost performance.  But would filtering rows based on the TIME_STAMP achieve this aim?
I am always suspicious of date_column < :date_time_value because it is hardly ever very selective.  In this case I noticed that the date and time compared to the TIME_STAMP column was always within a second or two of the actual execution time.  As one would expect from the name, the TIME_STAMP column has only past times in it.
Select count(1) from dummy_table where time_stamp > sysdate;
...returned no rows.  So, the TIME_STAMP column is of no use in an index to improve the performance of this query.
So why was there an impression that the index was beneficial?  One possible answer is that the new index is compact (dense) because it hasn't had much/any data deleted from it, where as the index that would have previously been used by the query (DUMMY_TABLE_IDX1) had become sparse after a lot of data was deleted.  The index information above shows that the old index is 26% larger than the new index (confirmed by DBA_SEGMENTS) despite the new index having an extra column!  At first the smaller index (DUMMY_TABLE_IDX3), with less empty blocks would have been more efficient than the old one (see this post about index performance).  But, after time passes and data is deleted, DUMMY_TABLE_IDX3 will become larger and perform worse because it stores an extra column (TIME_STAMP) which adds nothing for performance because it doesn't filter any rows.
Simply coalescing the index would have provided more benefit than adding the new index, without the overhead.  Compressing it would have improved it even more.
An alternative to the above:

Replace DUMMY_TABLE_IDX3 with a compressed functional index on (FOREIGN_KEY, AMOUNT1 + AMOUNT2), because the criterion "AMOUNT1 + AMOUNT2 <> 0" does filter out a lot of rows for some of the queries, and so table accesses (sequential I/O) would be reduced.
	

Reduce Processing Time by Reducing the Index

Rebuilding Oracle indexes is nothing new, but there are contrasting ideas about when this action should be taken.  Some argue that there is usually no real benefit, and others set automatic jobs to rebuild indexes regularly as a rule.

I think that each case should be evaluated on its merits.  Without going into it (the topic is well covered elsewhere) some indexes naturally tend towards 75% capacity and others >90%.  Index capacities well under 75% are usually due to deletions.  The only general rule I’d suggest is that when data is deleted, especially by a few large operations, then the related indexes might be worth checking to see if a reorganisation is warranted.

Compact (dense) indexes mean less disk space, less memory for caching the same information, less I/O and less processing (CPU time).

Rebuilding, coalescing (Enterprise Edition) or shrinking space (10g+) can be big and easy wins.  There are many cases I could describe, but here is the most extreme example.

Purging Performance Problem

An organisation approached me after they been trying to purge years worth of data from an Oracle 9i database, but found that the performance was atrocious, and they were after that elusive go fast switch.

The application had a purging function built in, (rare but so beneficial!), so the code couldn’t be adjusted.  There were no application design changes possible, eg partition by time and efficiently drop partitions instead of deleting.

The application purged data by asking for a period to retain and one to purge, then looping – deleting more recent data in the first iteration followed by incrementally older data in the next iterations.  Here is a simplified example of what I believe it was doing from the description given to me:

delete where data_date between :retention_date-1 and :retention_date;
commit;
delete where data_date between :retention_date-2 and :retention_date;
commit;
delete where data_date between :retention_date-3 and :retention_date;
commit;
....

This was probably designed to reduce undo space requirements.

The data_date key was indexed, which avoided tablescans.  Oracle would handle the first deletion by entering the index at retention_date – N and then (range) scanning the index until it found the retention date, deleting as it went.

The problem is obvious to those who understand how Oracle indexes work.  The deleted index entries result in more and more empty leaf blocks between the data yet to be purged and the retention date.  These empty leaf blocks are not taken out of the index structure until they are reused elsewhere (by an insert or update).  The index range scan had to access more and more of these empty leaf blocks as the purging process continued, requiring more processing time for each subsequent delete.

The solution I suggested was to punctuate the purge cycles with manual index coalesce commands to remove the empty leaf blocks.  The first coalesce of the relevant index took several minutes, but took less time after that. This is the feedback we received when the index coalesce was added to the purging process:

“Wow……..what previously took 1 hour now takes about 25s.  Yes, I can now purge a day off the table in about 25s.   This is great.  A whole week in 1 minute. and a month in 10 minutes.”

In this case, it wasn’t even necessary to access the database to find the solution.  When one does have access to the database, evidence that the index should be rebuilt / shrunk / coalesced can come from:

• a high logical read count for a SQL statement, and
• a high logical read count for an index segment, and
• a low density shown by the results of analyze index .. validate structure, or
  
• a low density shown by the results of the segment advisor (10g), or
• a low estimated density from the key length x number of keys versus actual index size, or
• a tree dump of the index showing many empty (or mostly empty) leaf blocks in the structure