Query plan / table partitioning

[Justin]

I have set up a test table with range partitioning. The partitions
are by date (recdate):
1) Pre-2004
2) 2004
3) 2005
4) 2006
5) 2007
6) 2008

When executing the query: select * from analysis_data where
year(recdate) = 2006, I would expect that the query plan would utilize
only the partition with 2006 data. However, it seems to do a TBSCAN
across the entire table.

There are no indexes setup on the table. DB2 Version 9.1 fp 4a

[w.l.fischer]

On Aug 27, 11:07*pm, Justin <[e-mail]:

I have set up a test table with range partitioning. *The partitions
are by date (recdate):
1) Pre-2004
2) 2004
3) 2005
4) 2006
5) 2007
6) 2008

When executing the query: select * from analysis_data where
year(recdate) = 2006, I would expect that the query plan would utilize
only the partition with 2006 data. *However, it seems to do a TBSCAN
across the entire table.

There are no indexes setup on the table. *DB2 Version 9.1 fp 4a

Don't know about this, but just an idea: A one-dimensional MDC would
cost almost no space and stop DB2 from scanning the whole table.

[ChrisC]

On Aug 27, 2:07*pm, Justin <[e-mail]:

I have set up a test table with range partitioning. *The partitions
are by date (recdate):

Did you partition by date (i.e., date between '2005-01-01' and
'2005-12-31' or by YEAR(date) = 2005?
-Chris