Re: Fragment elimination on datetime field

HI, Laurie, I faced the similar challenge and talked to IBM technical support and got some positive response, ==== Frank This issue you are discussing with fragmentation was fixed in 10.00.C5. If this covers your issues as well can this PR be closed, upgrading should resolve your issue. It was caused by defect 175906 ADDING A FRAGMENT SCANS THE ENTIRE TABLE WHEN USING A BEFORE CLAUSE ==== We installed IDS10 CU5 and I have not tested it yet. Hopefully it can treat your problem to some extent. Another tips may be, If you have a Global Index(Not partitioned index) on this table, it will be very slow to add or drop a fragment since Informix needs rebuilding whole index. But if you only have Local (partitioned) index, it should be much faster to either drop or add a fragment. So try to design a table with only partitioned index may help. I know currently IDS has some limitations on fragmenting index. I am confident this will be improved because Fragmentation is more and more important for larger and larger database and data warehouse. Thanks, Frank Laurie Gustin wrote: >I am trying to do the same thing with the Day of the week. Now it makes >sense as to why there is no fragment elimination on the queries. > >My plan was to detach and add new fragments on a rotating basis >throughout the month, but the index builds take forever, so I am having >to drop my history data and rebuild the table from scratch once a >month. > >Anyone have a better idea how to rotate this data, AND acheive fragment >elimination on queries? > >Thanks > > >(IDS 10 FC4, HPUX 11) > >Laurie Gustin >IT Programmer Analyst >Department of Public Safety >lgustin@utah.gov >801-965-4410 > > > >>>>"Alexey Sonkin" <alexeys@cidc.com> 08/31/06 12:43 PM >>> >>>> >>>> >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@iiug.org >[mailto:informix-list-bounces@iiug.org] On Behalf Of Ben Thompson >Sent: Thursday, August 31, 2006 8:05 AM >To: informix-list@iiug.org >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@iiug.org >http://www.iiug.org/mailman/listinfo/informix-list > > >_______________________________________________ >Informix-list mailing list >Informix-list@iiug.org >http://www.iiug.org/mailman/listinfo/informix-list >_______________________________________________ >Informix-list mailing list >Informix-list@iiug.org >http://www.iiug.org/mailman/listinfo/informix-list > > > -- Yunyao "Frank" Qu Computer Sciences Corporation(CSC) NOAA/CLASS, (301)817-4696