Re: fragmentation and sqexplain
Siebe,
First of all, this is not a direct answer to your question. But if you
want to search data faster using the tableschema you presented it would
be better to alter the index to a clustered index.
It might help,
with kind regards,
Vincent Schakel
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