Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2010
    Posts
    67

    Unanswered: table partitioning

    Hi There,

    db2 9.7.7
    aix 7.1

    Recently we did partition one of large table and had better performance in "select count(*)" query . select count(*) query will scan entire of table and logically partition of table should NOT have advantage for this kind of queries , could you please describe what is the logic behind of this improving performance ?

    thank you in advance for your help.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if you would be more specific.. maybe we could answer the question
    partitioning can have advantages depending on the type of queries..
    if you select only data from 1 partition, this partition only will be scanned
    all depends on partitioning/key/index/ and query
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jan 2010
    Posts
    67
    Hi Guy Przytula,
    we created a partition table with year (timstamp) so we have 15 partition 2000-2015) with 200 mil records. we ran db2 select count(*) from table_name query and we got %50 improvement compare with non partition table. I think select count(*) will scan entire table so to me performance should be the same as non partition but it seems I was wrong

    1- db2 explain shows us db2 use index (could you please tell me why/how select count(*) db2 use index ?
    2- parallelism in both (partition and non partition) is NO
    3- why on partition table select count(*) is faster ?

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Check if DB2 is scanning the index partitions (assuming partitioned index) in parallel.
    Specify if row compression is active for the table and index(es) for both the measurements (i.e. with and without the range-partitions).
    Specify if the tablespace/container/bufferpool characteristics(all of them) are identical with both measurements.

  5. #5
    Join Date
    Jan 2010
    Posts
    67
    Hi db2mor,

    1- we do not use row compression.
    2- all indexes on both tables (partition and non partition) are identical.
    3- we created every partition (15) in separate tablespaces and same buferpool
    4- db2 select count(*) is using an index that key partition is part of that

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi mars,

    You should compare the io characteristics of both queries. I mean a number of phisical & logical reads per execution, for example. It might be a chance that partitioned table could better cached or non-partitioned one was fragmented.
    As for index use for this type of query, the explanation can be simple: you can get row count scanning index pages only. Often indexes has significantly less pages than data, so it's cheaper to scan some index than data.
    Regards,
    Mark.

  7. #7
    Join Date
    Nov 2011
    Posts
    334
    hi mars,
    Would you plz give the db2batch output of these 2 queries;
    db2batch -d <dbname> -i complete -o e yes p 5 -f <sqlfile> > db2batch.out
    and another thing i think is
    if each tablespace is backed by different physical disks,
    Separating 15 partitions into 15 different tablespaces will gain some io throughput.

  8. #8
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Quote Originally Posted by mars View Post
    Hi db2mor,

    1- we do not use row compression.
    2- all indexes on both tables (partition and non partition) are identical.
    3- we created every partition (15) in separate tablespaces and same buferpool
    4- db2 select count(*) is using an index that key partition is part of that
    You did not answer the question "Specify if the tablespace/container/bufferpool characteristics(all of them) are identical with both measurements."
    "both measurements" means the measure taken without range-partitioning and the measure taken with range-partitioning.
    If both measures are using the same bufferpool, how have you performed the test? (i.e to ensure that for both measurements the bufferpool was not pre-initialised by a previous run for the other measurement case which would skew the result).
    As the index includes the range-partitioning column, DB2 may find the cost of scanning that index to be cheaper than scanning the table (as there will be one index-entry for every row, depending on the number of index-keys per page).
    If the index is itself partitioned (as distinct from global) DB2 may parallelize the index scanning.

  9. #9
    Join Date
    Jan 2010
    Posts
    67
    Thank you mark.b/fengsun2/db2mor for your help and really appreciate for spending time.

    after reading your answers it seems to me the result of test that I was doing is related to so many factors that I have not mentioned about that. let me I review them and try again.

    Thank you again for your help

Posting Permissions

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