Virtual Indexes: My 2nd Chance in DB2 Has talent 2012

I will share the story of my 2nd chance at DB2 has talent... A better experience and managed the time well... But everyone was at their best in that episode and for a moment I thought have a got a wildcard entry in final round of DB2 has talent... Lot of people discussed the interested topics and a few discussed some features/tools which are not well documented... I too had chosen a topic which is not much discussed: "Virtual Indexes"... A way by which you can evaluate your indexes without even creating those... So let me start with it (and I will keep a check on my typos)...

So how many times you have faced an issue of poor performing queries and you feel probably adding an index will help improve your queries performance... But then you realize one index will not be sufficient for all the queries and you are wondering which columns to be combined for indexing... The permutation combination is generally good enough for you to not try actual creation of indexes as that will take some time and if it is not good you need to drop and start over...



So I made up a story... I had a table design, pretty simple one...

Three tables, two of those mapped to each other in the third table by their key columns... The mapping table has a self referencing relationship of parent-child...

I had a query which was being executed on the tables, again a very simple query... Join between 2 tables through the third table and then the third table was also involved in a self-join... But still the query was taking a lot of time to be executed... I guessed probably the indexes are missing and though of using index Adviser...

I could have achieved the same by setting my current explain mode=advise indexes and then run the queries and see what are the new indexes advised in ADVISE_INDEX table... But I was surprised to see the result in the table...

I was confused, every other column in my select list or in my where clause made a place in one or the other advised index...

I was not able to figure out which index, I shall use and which one I shall ignore... The major problem was creating all these indexes will slow down performance for my CRUD operations... A dba won't have all the time in this world to try out all the indexes in every possible permutation and go into the loop of "create index"-"generate plan"-"check usage"-"drop index"-"repeat"...

I was wondering if there is a way to force optimizer to assume as if an index is there and then generate a plan using the index if possible... Then I came across an option in db2advis and a value for "current explain mode"...

Once you have got the index suggestions, you can set your current explain mode to evaluate indexes and then run the queries which you think will be benefited by suggested indexes... then use db2exfmt to format the explain info... Alternatively using -p option in db2advis you can force the adviser to store explain info for the suggested changes... Then use db2exfmt you can generated the explain plan and see if suggested indexes are used or not... But I was not satisfied with the plan generated for any of those suggestions, specially when I ran the update queries for those tables in "evaluate indexes" as current explain mode, I was more convinced not to create all of them...

I took a backup of suggested indexes and then deleted the rows from ADVISE_INDEX and repopulated them from backup in various permutations to check which combination is most effective but none of them were as convincing... So I decided to insert my own rows in ADVISE_INDEX indexes i thought will benefit the select queries without having much impact on updates and inserts...

Something which I could not discuss during the presentation is I had a some challenges in trying out my own indexes, cause initially the optimizer ignored the indexes I imagined (or virtually created)... Then I had to work out the proper values for NLEAFS i.e. no of leaf nodes for your index tree (roughly no of distinct values in your column combination), NLEVELS means no of level in your index tree (is one of most difficult parameters to decide), FULLKEYCARD i.e. distinct key values in your index (a little easy compared to other a select distinct should do), FIRSTKEYCARD/FIRST2KEYCARD/FIRST3KEYCARD are for cardinality in first column, 2nd column and 3rd column in your index (again a little easy)... NEVELS can be decided by equation 2^x=no of distinct values where x is nlevels... SYSSTAT views can help you in deciding these parameters... COLCOUNT defines the no of column in the index...COLNAMES will define the columns which are part of index... INDEXTYPE (CLUS/REG/DIM/BLOK ) defines the type of index...

Finally with a little struggle and little time I could decide the best indexes for my tables... On exploring more about this feature I realized that I can virtually drop an index and check how the query performance is impacted before I actually drop it... All I have to do is insert a row for an existing index USE_INDEX column set to ‘N’ and EXISTS as ‘Y’...
I wrapped up with putting a slide for advantages of this (I will just put the slide here and will not be explaining)...

To end this blog I will just mention that my DB2 z/OS folks can also use the same feature DSN_VIRTUAL_INDEXES ... Oracle user can also try the feature of Virtual indexes by using NOSEGMENT clause in index creation... I will sign off this blog entry... Might take a few days to return, I have to enhance my skills on HADR and DB2 Internals (process and memory model)... Might have a short blog for insert enhancements and piece of code that you can probably re-use...