Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103

    Unanswered: Partitioned Table Performance

    DB2 9.1/AIX 5.3

    I used the new table partitioning feature available in DB2 9.1 based on a column which divides a big table into two big chunks as below.

    HTML Code:
    /tmp > db2 "select distinct(src_type), count(*) from cust_p group by src_type with ur"
    
    SRC_TYPE 2
    ---------------- -----------
    R                   58877755
    S                   70305552
    
      2 record(s) selected.
    I placed data in two differenct tablespaces one for 'R' and 'S'. My query has a filter on SRC_TYPE='R' still I do not see any major performance gain of partition elimination after partitioning this table.

    Any thoughts about this table partitioning feature on DB2 9 or is this not a good candidate ?

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    db2udbgirl, The more distinct you can make the partition keys, the fewer rows will be in each partition.

    Even though you have 2 partitions instead of one table, you are still going to be doing a partition scan of about 60 million rows. An Index on the column wouldn't be used as the optimizer can tell it would have to retrieve half the rows in the table anyway. SRC_TYPE by itself isn't unique enough to break the partition down to smaller chucks.

    As an example, Assume SRC_TYPE could be all 26 letters of the alphabet. Also the rows are evenly distributed across all letters (a bit of a stretch, I know), you would then have on average, 5 million rows per partition. This still won't be very fast but it should run in a little less than 1/10 the time.

    Another factor is what disks you are using and where the containers are defined. If you can spread the partitions on different disks, you can improve parallel I/O. Assuming the 26 SRC_TYPE values again, if you had something like SRC_TYPE IN('A', 'Z'), and the partitions are on different disks, two parallel tasks could be started to read both partitions at the same time cutting the query time in about 1/2.

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    I am preparing for partitioning in our environment and here is what I realized.

    It's simple logic: the indexes of partitioned tables are not partitioned (each index is still located in one TS) so for index scans there would not be any performance improvement. You might notice some improvement in index scan cases if you place indexe(s) in separate TS(s). But you could do it in prev. versions of DB2 too.

    Does it make sense?
    Last edited by MarkhamDBA; 05-27-09 at 10:47.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    Thanks for the response. I will retry with a good partitioning key.

Posting Permissions

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