RE: Fragment elimination on datetime field

Laurie, I you modify (or redo) you fragmentation schema and make it 'single fragment-single day' buy using range partitioning, you'll get rid of your problems: - detach old fragment would run momentarily; - add new empty fragment would run even faster; - Informix would be able to do the fragment elimination for most range queries Please, if you plan to detach/add fragments, use local indexes (co-fragmented with a table), so that IDS could easily drop index fragments with table fragments. There is an ugly bug in IDS, related to adding non-empty fragment to a fragmented table (with local indexes): for some reason, IDS is not using parallel index build to create an index on a newly attached fragment. Index build takes forever... To add a fragment, it's faster to drop all indexes an a table, and rebuild them from scratch with PDQPRIORITY, or add an empty fragment to the indexed table, and populate it with SQL 'insert' with data from a detached fragment. -Alexey -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Laurie Gustin Sent: Friday, September 01, 2006 1:00 PM To: informix-list@iiug.org Subject: RE: Fragment elimination on datetime field Actually - I meant Day of the Month... like the 1st, 2nd, 3rd... etc >>> "Laurie Gustin" <lgustin@utah.gov> 09/01/06 10:34 AM >>> 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 _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list