RE: Fragment elimination on datetime field

Ben,

My understanding is that month() SQL function is *just*
a function, like any other function, that can be created in SPL or C.

Optimizer doesn't know the internals of this function, specifically,
doesn't know, how to convert it's results into a date range.
This is why optimizer is unable to generate proper fragment
elimination query plan.

I think, that it is much more efficient to specify date
range (either '<' and ">=", or 'between', both work similarly)
for this type of table fragmentation.


-Alexey

-----Original Message-----
From: informix-list-bounces@xxxxxxxx
[mailto:informix-list-bounces@xxxxxxxx] On Behalf Of Ben Thompson
Sent: Thursday, August 31, 2006 8:05 AM
To: informix-list@xxxxxxxx
Subject: Fragment elimination on datetime field

Hi,

IDS 10.00.xc5
Multiple platforms

I am looking for a way of creating an index on a large table so that
fragment elimination will be achieved regularly. The index is simply on
a datetime field. I want the index to be maintenance free so I don't
want to use specific date ranges and risk that a date can be inserted
that doesn't match any of the fragments.

I have tried using the MONTH function as in

fragment by expression
(MONTH (dtfield ) = 1 ) in dbs1 ,
(MONTH (dtfield ) = 2 ) in dbs2 ,
(MONTH (dtfield ) = 3 ) in dbs3 ,
(MONTH (dtfield ) = 4 ) in dbs4 ....

(This is neat in that it splits things up into 12 which is about the
maximum number of threads I want to use for PDQ. Of course PDQ is
separate to fragment elimination.)

However this doesn't achieve fragment elimination unless you
specifically use the MONTH function in your SQL statement and even then
it doesn't always work if you filter on this column further. Most of the

SQL we run uses date ranges over a week or month period.

I suppose I am looking for a neat way of doing it like there is with the

MOD function for id type columns. Can anyone suggest anything?

Ben.
_______________________________________________
Informix-list mailing list
Informix-list@xxxxxxxx
http://www.iiug.org/mailman/listinfo/informix-list


.

Relevant Pages

  • RE: Fragment elimination on datetime field
    ... sense as to why there is no fragment elimination on the queries. ... Anyone have a better idea how to rotate this data, AND acheive fragment ... My understanding is that monthSQL function is *just* ... SQL we run uses date ranges over a week or month period. ...
    (comp.databases.informix)
  • RE: Fragment elimination on datetime field
    ... sense as to why there is no fragment elimination on the queries. ... Anyone have a better idea how to rotate this data, AND acheive fragment ... My understanding is that monthSQL function is *just* ... SQL we run uses date ranges over a week or month period. ...
    (comp.databases.informix)
  • Re: Fragment elimination on datetime field
    ... If you have a Global Indexon this table, it will be very slow to add or drop a fragment since Informix needs rebuilding whole index. ... sense as to why there is no fragment elimination on the queries. ... I want the index to be maintenance free so I don't want to use specific date ranges and risk that a date can be inserted that doesn't match any of the fragments. ... However this doesn't achieve fragment elimination unless you specifically use the MONTH function in your SQL statement and even then ...
    (comp.databases.informix)
  • RE: Fragment elimination on datetime field
    ... add new empty fragment would run even faster; ... Fragment elimination on datetime field ... My plan was to detach and add new fragments on a rotating basis ... My understanding is that monthSQL function is *just* ...
    (comp.databases.informix)
  • Re: SEQUENTIAL SCANS
    ... table A has another individual index on column t. ... then the optimizer might compute that doing the ... is it able to do some sort of fragment elimination? ...
    (comp.databases.informix)