fragmentation and sqexplain
Hi everybody,
I have tested 2 fragmentation strategy's :
1.
create table "wxs".fragm1
( id serial,
tijd datetime year to second
) fragment by expression
tijd < '1997-03-01 00:00:00' and tijd >= '1997-01-01 00:00:00'
in sub_space1,
tijd < '1997-05-01 00:00:00' and tijd >= '1997-03-01 00:00:00'
in sub_space2,
tijd < '1997-07-01 00:00:00' and tijd >= '1997-05-01 00:00:00'
in wxs_space1,
tijd < '1997-09-01 00:00:00' and tijd >= '1997-07-01 00:00:00'
in wxs_space2,
tijd < '1997-11-01 00:00:00' and tijd >= '1997-09-01 00:00:00'
in sys_space1,
tijd < '1997-12-31 23:59:59' and tijd >= '1997-11-01 00:00:00'
in sys_space2 extent size 20 next size 10 lock mode row;
CREATE INDEX i_tijd3 on 'wxs'.fragm1(tijd);
2.
create table "wxs".fragm1
( id serial,
tijd datetime year to second
) fragment by expression
month(tijd) in (1,7)
in sub_space1,
month(tijd) in (2,8)
in sub_space2,
month(tijd) in (3,9)
in wxs_space1,
month(tijd) in (4,10)
in wxs_space2,
month(tijd) in (5,11)
in sys_space1,
month(tijd) in (6,12)
in sys_space2 extent size 20 next size 10 lock mode row;
CREATE INDEX i_tijd3 on 'wxs'.fragm1(tijd);
With sqexplain :
1.
QUERY:
------
select * from fragm1 where tijd ='1997-02-15 13:00:00'
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) wxs.fragm1: INDEX PATH
(1) Index Keys: tijd (Serial, fragments: 0)
Lower Index Filter: wxs.fragm1.tijd = datetime(1997-02-15
13:00:00) year to second
2.
QUERY:
------
select * from fragm1 where tijd ='1997-02-15 13:00:00'
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) wxs.fragm1: INDEX PATH
(1) Index Keys: tijd (Serial, fragments: 1)
Lower Index Filter: wxs.fragm1.tijd = datetime(1997-02-15
13:00:00) year to second
The Question : what does fragments: 0 vs. fragments: 1 mean ?
Has it something to do with eliminating fragments (maybe a problem by
using a function in the fragment expression) ?
Thank you for your help
Siebe