Re: fragmentation and sqexplain
Siebe de Klaver wrote:
>
> 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) ?
These are the fragment numbers that were scanned. The records you are
looking for are in the first fragment (number 0) in your first example,
and in the second fragment (number 1) in your second example.
Hope that helps,
--
Mark.
+----------------------------------------------------------+-----------+
|Mark D. Stock - Informix SA http://www.informix.com |//////// /|
|mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //|
| +-----------------------------------+//// / ///|
| Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////|
| Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////|
|Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////|
+----------------------+-----------------------------------+-----------+