Re: Fragment elimination on datetime field

I had this same problem. I was even using a datetime field and trying to cast it to a month to month datetime field. I thought the casting might work around the "function" issue. The only way I could think of was to add another column that gets updated with a trigger which is the month. You then fragment on this field. I haven't tried it so I don't know if you would have a bigger problem. you could use a smallint so you wouldn't add too much space to a table. When you implement the trigger use the "function into" form of the trigger, I believe this is optimized to update a field in the current record. Check a posting I gave last week on another issue for the syntax. Ben Thompson wrote: > 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.