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.

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 ?

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.

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?

Thanks for the response. I will retry with a good partitioning key.