Example of Indexing for a Problem Delete

You may also like...

9 Responses

  1. Frederik Engelen says:

    > cascading deletes
    Thanks for the detailed blog entry. Note that simple foreign keys can cause exactly the same problems. Problems that you’ll never notice when just inserting.

  2. Manohar VIswanatha says:

    Can you post the the query you used to find the delete statement in the first entry of this post ……………

    • Ember Crooks says:

      It is actually querying my own set of custom history tables based on MON_GET_PKG_CACHE_STMT, so it wouldn’t nesecarily work against other tables.

  3. Manohar VIswanatha says:

    Its from the MON_GET_PKG_CACHE_STMT 9.7 , is there a way we can get this from 9.1 , i mean throught Admin VIews or some how……………….

    • Ember Crooks says:

      I believe you can get it using the SNAPDYN_SQL in db2 9.1 – most of it is in there. There’s a chart of methods for getting to that kind of information and the versions in my developerWorks article: http://www.ibm.com/developerworks/data/library/techarticle/dm-1211packagecache/

      • Manohar VIswanatha says:

        the interesting column i found is ROWS RETURNED which i cannot find in any of these view 9.1 , so i thought have i missed anything and thought of confirming with you …………Anyways how’s your trip to IDUG Orlando???????? Hope you enjoyed it a lot ……can sense it in your tweets…..

  4. Shivraj says:

    Thanks for the detailed post. Very helpful !

  5. Shashi Ranjan says:

    Hi Ember,

    Thanks a lot for all your posts which is very helpful in learning’s and resolving problems. In the example, we created here an index suggested by db2 adviser only. Sometime, we face issue like where indexes recommended by db2 also do not help in complex queries. As you said in post, don’t relay on db2 adviser output. Is it possible to explain with an example what to do and how to choose and decide which index we should create and what are other points we should consider while looking at explain plan.

    The other question – is it advisable always to include primary key columns combination while creating index to increase cardinality.

    • Ember Crooks says:

      Only include primary key columns if they will also be queried along with the columns you are indexing on. Artificially inflating cardinality does not improve things – in fact, it may make performance worse.

      If you have a low-cardinality index that you wish to create, and there are no high-cardinality columns that also make sense to include based on the queries run against the table, then you should consider making it a clustering index or a dimension for MDC. Or even if higher cardinality is introduced by join with another table, consider a statistical view.

      Beware of any low-cardinality indexes of 3-5% or less of the table cardinality, especially if the data is unevenly distributed over the values. I do my best not to add such indexes as they may be detrimental to performance.

Leave a Reply

Your email address will not be published. Required fields are marked *