Hi all,
I am trying to achieve horizontal partitioning with Derby. My application stores *a lot* of data as time passes, and it needs to drop obsolete data. Table partitioning would be the ideal solution, but it is not implemented in Derby. So I create one table per period of time. All tables have the same columns, the first one being the data timestamp. Dropping obsolete data is then made easy: just drop old tables. Now, I need to query data without knowing how tables are organized. So, I create a view on all tables (the view is deleted/created every time a data table is created/dropped): fine. However, checking the runtime statistics, I notice ALL tables are scanned when I use the view! Note I added constraints on the Timestamp field. Sample SQL: create table t1 (ts timestamp, a int, b int, c int); create table t2 (ts timestamp, a int, b int, c int); create table t3 (ts timestamp, a int, b int, c int); alter table t1 add constraint t1_c check (ts >= '2007-07-16 09:00:00.0' and ts < '2007-07-16 09:01:00.0'); alter table t2 add constraint t2_c check (ts >= '2007-07-16 09:01:00.0' and ts < '2007-07-16 09:02:00.0'); alter table t3 add constraint t3_c check (ts >= '2007-07-16 09:02:00.0' and ts < '2007-07-16 09:03:00.0'); create view t_view as select * from t1 union select * from t2 union select * from t3; CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1); select * from t_view where ts >= '2007-07-16 09:01:30.0' and ts < '2007-07-16 09:02:00.0'; VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); The displayed execution plan shows t1, t2 and t3 are scanned. I expect only t2 is used. I also tried to force using constraints by defining the view this way, but it did not improve the behavior: create view t_view as select * from t1 --DERBY-PROPERTIES constraint=t1_c union select * from t2 --DERBY-PROPERTIES constraint=t2_c union select * from t3 --DERBY-PROPERTIES constraint=t3_c; Any idea on how to achieve horizontal partitioning? Thanks! Olivier.
