Many New Index Options in DB2 - Version 9 DB2 Performance Features - Part 19

Index design is one of the most important items or maybe the most important item in database design. Sometimes it is better to enhance the database with more indexes instead of trying to fix the application SQL. Tuning SQL is always good but tuning many SQL statements takes too much time. When time is tight most of the problem application SQLs can be instantaneously fixed through better indexes.

DB2 Version 9 indexes have several new options that help DB2 performance. First is the enhancement that I have written about before in the blog (Expression Performance) is Index on Expression. The Index on Expression enhancement alone can make a huge performance impact by tailoring the index to the application WHERE clause predicates. This customization of an index design can be a huge DB2 performance improvement for many applications, especially applications using DATE, TIME and other SQL functions within the SQL.

Next, is Index Compression which cuts disk requirements for the index structure dramatically. Most of the time compression can yield over a 50% savings and sometimes over 80%. This is especially important for large BI/DW databases indexes that can sometimes be terabytes before compression.

Next, index page sizes can be set to a larger page size that helps reduce the overall number of pages and the number of page splits within the index structure. Minimizing page splits helps avoid contention, helps cache all the index entries and keeps index value entries near each other on the same index page.

Another improvement for indexes that have ascending/descending keys is the new key randomization feature. Using the RANDOM option during the CREATE or ALTER of the index causes the index entries to be stored at random places in the index tree. This random inserting cuts down on index contention and is especially important for robust data sharing systems that are inserting many ascending/descending index keys.

So next time an application has SQL or DB2 performance problems take a look at improving or defining a new index on the database. You might improve a huge number of applications instead of only a single application program and have time left to focus on other issues.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>