About Me

My photo
Singapore, Singapore, Singapore
I am currently working as a DB Consultant with Ashnik. We are into Open Source and Cloud based enterprise class solutions. Prior to this I was working as a DBA @ Misys India (Banking Services). Working on IBM DB2, Oracle Microsoft SQL Server. Selected as IBM Champion for Data Management 2011. I like bit of photography, cycling, poetry and I just love gadgets!!

Connect to me

See my profile on LinkedIn Visit my Ashnik Website

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...

6 comments:

  1. I am trying to virtually drop an index by updating USE_INDEX column set to ‘N’ and EXISTS as ‘Y’ but it doesn't do anything.

    Can you show the exact syntax you are using?

    ReplyDelete
  2. There is nothing specific about the EXPLAIN command which you need to do here. You need to set your current explain mode=advise indexes and then try EXPLAIN command to collect explain info and then format it.

    Can you make sure the index name, table name, schema etc are proper? I suggest that you populate this table from syscat.indexes using a INSERT INTO..SELECT.. statement. Then you can update EXISTS to 'Y' and USE_INDEX to 'N'

    ReplyDelete
  3. This is what I am doing:

    update ADVISE_INDEX set EXISTS='Y' where NAME='VIRTUAL_INDEX_NAME';
    update ADVISE_INDEX set USE_INDEX='N' where NAME='VIRTUAL_INDEX_NAME';

    set current explain mode recommend indexes;
    set current explain mode explain;

    sql statement;

    db2exfmt -1 -d DATABASE -o explain.out

    set current explain mode no;

    I have tried different permutations of 'current explain mode evaluate indexes', 'current explain mode recommend indexes', and 'current explain mode explain'. I have tried with true indexes and virtual indexes.
    But it still does not recognize that the indexes are 'virtually dropped' when I look at explain.out.

    I have db2 9.5fp4 if that makes a difference.

    What am I doing wrong?

    ReplyDelete
    Replies
    1. If I understood your comment correctly then, I guess your are trying combination of EXPLAIN MODE (?). See the moment you say:

      set current explain mode recommend indexes;
      set current explain mode explain;

      second statement will set explain mode to explain (which will override or subdue the effect of first statement).

      Just set it once and set it to evaluate indexes:

      set current explain mode evaluate indexes;

      It should help. And I have seen usage of ADVISE_INDEX being documented for evaluating virtual indexes even in DB2 8.2, so version (9.5) should not be an issue.

      Delete
  4. I still cannot get it to work.

    I have tried:

    set current explain mode evaluate indexes;
    sql statement;
    db2exfmt -1 -d DATABASE -o explain.out

    set current explain mode recommend indexes;
    sql statement;
    db2exfmt -1 -d DATABASE -o explain.out

    set current explain mode explain;
    sql statement;
    db2exfmt -1 -d DATABASE -o explain.out

    And they all look the same.

    Can you point me to some official documentation? I have found very little about this feature. You are the only place I have found that says it works via command line.

    When I asked an IBM rep about it they said I had to get Optim Query Workload Tuner to enable it. But I'm sure that was only to enable it in Data Studio.

    ReplyDelete
    Replies
    1. Hi Jack,

      That is the way to do it. There is nothing wrong in what you are doing:

      set current explain mode evaluate indexes;
      sql statement;
      db2exfmt -1 -d DATABASE -o explain.out

      But as you are trying to see if a dropped index is affecting a plan or not, in that case it will be important to see if the index was used at all for the queries. Have you confirmed that first? If it was not at all used, dropping it (actually/virtually) will not affect your plan.

      BTW optim or for that matter any plan analysis tool can not (rather should not) be generating planing of its own, they all depend on db2 optimizer and if there is a way they can simulate something (virtual indexes) then there must be a stub in db2 somewhere which is letting them do this. Sure, with optim or data studio your work is much more easier but it should work even without those (as I have explained in this blog).

      Can you please send me/post the values (may in form if insert statement) for the existing index (from syscat.index) and advise_index table.


      Regards
      Sameer

      Delete