Re: fragmentation and sqexplain

Siebe, Sorry if this has been answered already, but I missed the original posting apparently. The 'fragments: 0' vs. 'fragments: 1' only means that in the first query, the data will be found in the first fragment (no. 0) and in number 1 in the second. A quick scan of the data and fragmentation strategy will confirm that this is correct behavior. Also, unless the other respondent edited something out of the original post, I see nothing that would indicate a need for clustering the index. There may be reasons, but I don't see any evidence to support that notion. David Siebe de Klaver schreef: } 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