Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18

    Question Unanswered: DB2 10.1 adaptive-compression question

    In DB2 v10.1 LUW with adaptive-compression I noticed that my single-column primary key indexes that are datatype bigint are not compressed when the table has 'value compression compress yes adaptive' in its 'create table' DDL.

    This seems logical as there cannot be duplicates, so nothing to gain from compression of that index.
    Something about eligibility for compression.

    Two questions arise, maybe I'm looking at it from the wrong angle:

    (1) Why does DB2 allow a unique index (single column, also bigint) to be compressed?
    Should'nt the same logic apply (i.e. no duplicates to compress)?
    I would expect an error or warning from the 'create unique index' if the datatype of the column(s) was a whole-number (integer, bigint etc) and the index was specified with compression.

    (2) Since the primary-key index with data-type bigint are not-compressed
    should all FK indexes (that are non-unique) that refer to PK be also not-compressed?
    In other words, is it best-practice to keep the FK-index and PK-index having the same compression details? Does anyone have any metrics for this, particularly with 'SET INTEGRITY' or enforcing FK constraints?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't understand why a PK column cannot be compressed. A bigint typically has a lot binary zeros in it.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    I did not write thatt they cannot be compressed.
    I find that IF I create a table with the relevant compression clauses then I found that none of the PK indexes were compressed, and I wondered why, and that led on to the question of matching the compression-detail between FK-indexes and PK-indexes.
    Also: does'nt compression of a colum work on the whole column value rather than a piece of it?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Firstly, adaptive compression relates to data only.

    Secondly, index compression has several aspects, not just the key value compression, and they all are taken into account when deciding if index compression is beneficial. Also, key values are taken as bit sequences for index compression purposes, not as numbers.

    Thirdly, as index key comparisons (e.g. when joining a PK with an FK) are performed on uncompressed values, it makes no difference whether they are both compressed or not compressed. If an FK index benefits from compression you should compress it, even if the corresponding PK is not.
    Last edited by n_i; 05-14-12 at 11:28.

  5. #5
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Thanks n_i for clarifying, though I'm still missing some details.

    In the docs it mentions "In an index where there is a high degree of commonality in the prefixes of the index keys, the database manager can apply compression based on the similarities in prefixes of index keys. "

    Does this mean for a single-column (bigint) primary-key that DB2 can compress the unique index (in my case the key values were monotonically increasing numbers)?

    I need to find out why it chose not to do it in my case, there might be something else happening.

    What do you mean "...when DB2 decides if index compression is beneficial"?
    (that is to say, at what point does DB2 make such a decision?)

    I understood that it is the DBA who must decide whether index-compression is beneficial (based on the tradeoff between performance and storage-cost-reduction). Can you clarify?

    If the index-comparison happens always on the uncompressed values, then there must be a measureable difference between the elapsed time to complete an 'alter table ... alter foreign key ...enforced' action on billions of rows when the PK/FK-indexes are both compressed versus when they are both uncompressed. I'm just trying to get a handle on that aspect.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2mor View Post

    Does this mean for a single-column (bigint) primary-key that DB2 can compress the unique index (in my case the key values were monotonically increasing numbers)?
    Yes it does, although the effect may not be as pronounced. I would not use the words "primary key" in this context though, it is just a unique index.

    Quote Originally Posted by db2mor View Post
    I need to find out why it chose not to do it in my case, there might be something else happening.
    If a table is created as compressed, each index created subsequently will be also compressed, unless you specify otherwise. I'm guessing that, if you declare a PK in the table definition itself, the index that is created for it does not follow that rule as the transaction that creates the table has not completed yet and therefore DB2 may not realize it needs to create that index compressed. Just speculating - you may want to test this.

    Quote Originally Posted by db2mor View Post
    What do you mean "...when DB2 decides if index compression is beneficial"?
    (that is to say, at what point does DB2 make such a decision?)
    I misspoke. It's the human being that makes this decision when creating the table and each index.

    Quote Originally Posted by db2mor View Post
    If the index-comparison happens always on the uncompressed values, then there must be a measureable difference between the elapsed time to complete an 'alter table ... alter foreign key ...enforced' action on billions of rows when the PK/FK-indexes are both compressed versus when they are both uncompressed. I'm just trying to get a handle on that aspect.
    There probably will be measurable difference, but not necessarily one that you expect. The CPU cost of compression is more than offset by the I/O savings on compressed objects.

  7. #7
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    OK thanks again n_i.

    Some things are now clearer.

    More testing upcoming!

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by n_i View Post
    There probably will be measurable difference, but not necessarily one that you expect. The CPU cost of compression is more than offset by the I/O savings on compressed objects.
    That is fine, so long as the server is not CPU bound, which can happen with very high bufferpool hit ratios.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •