Re: Creating index with upper function

I believe this message was not sent... Sorry if it appears as duplicate. Ian Michael Gumby wrote: > On Feb 1, 7:42 pm, Fernando Nunes <domusonl...@gmail.com> wrote: >> Ian Michael Gumby wrote: >> > [SNIP] >> WHERE my_upper(column_with_name) = my_upper(?) >> >> and don't have to waste storage (you use it for the index, but not for the new >> column you propose), and you don't even have to waste time talking to the >> optimizer team... let them keep up the good work :) >> >> The basic question still is relevant: Why the hell is UPPER variant? >> I don't see a reason, and there's an open issue for that... > > You kind of missed the point. As usual with you... People here are also used to that... don't worry... > > Ok, > > So if you use the : > WHERE my_func(x) = my_func(?) > > your problem is 'solved'... sort of. > > But what's the cost? On the query? None. On the index maintenance? the cost of running an UPPER each time you do an INSERT or UPDATE. > > It looks like the issue is that the OP wants to perform a case > insensitive query, yet may want to also retain the case sensitive > value. > > By creating the extra column, you don't have to run your my_upper() > function on each time you want to run a query. Twice actually. > So which is going to hurt performance more? A wider table, or having > to call the extra function? As everybody knows, the cost on the query is not worth the effort I did to write this line... The real question is the cost of maintaining the index... running the UPPER on each INSERT/UPDATE > Think about it. Care to join me? > > With 1 TB SATA drives now available on desktops, or ~150GB 2.5" SAS, > how expensive is it going to be to have a copy of the column that has > an UPPER function on it? The column plus the index... > Note that UPPER is only called on the insert of a row. Or you just Nice... Same as with a function index. You save the UPDATE... > store the data in uppercase and ignore the idea of maintaining the > case sensitive initial value. And then you're missing the OP's point (in your own words)... Easy to happen... believe me... happens to me all the time... at least that's what people say... > As I have demonstrated, you have a viable work around. Sure... you save an UPDATE and waste a lot of space. Oh... let me think... One real life situation for this is to store peoples names... How often do you update a person name? Frequently probably... > > Now, Gumby you ask, why is this important? > Not really... Risking to miss the point, I would never ask that... Just because I know you'll answer before I ask... > Simple junior, if you have a viable work around to a problem which is > not going to impact database sales, the problem you are facing becomes > a lower priority. I already said to you once, that if my age bothers you, I'll get over it... in time... Don't worry... > Now if you were on the 'chat with the labs' call, you would have heard > Jerry Keesee's response to the question as to why IBM IM is reluctant > to do a published benchmark on IDS. I was... They allow juniors to listen to that! Amazing isn't it?! > If IBM won't put skin in the game on a benchmark, what makes you think > that they'll spend money to fix a non-issue problem? Maybe the fact that is used to work (since there was a bug about it not working with derived types), or maybe the fact that there were some bugs associated with it (won't explain how they were closed, because people who can do something about it can easily check), or simply because it bothers me when Informix doesn't do something right (which I would say is the case, but I maybe wrong).... So, it's a real issue, having real customers complaints. It has a relatively easy workaround, but it's still annoying. The fix (if there is a reason to fix) would probably be easy (the impacts would have to be careful checked). Lot's of small issues are solved. There is a roadmap to implement, full of fancy and useful features, but that never stopped the fixing of "trivial" issues. > So why don't you go back to school and try and teach a next generation > of young'ns to use IDS? Because I'm too busy working with IDS and other IBM products, and I waste too much time with you. But I would be willing to do it if you were among the youngsters... It would be good for you. Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

> From: domusonline@gmail.com > Subject: Re: Creating index with upper function > Date: Thu, 5 Feb 2009 00:38:10 +0000 > To: informix-list@iiug.org > > I believe this message was not sent... Sorry if it appears as duplicate. > > Ok, > > > > So if you use the : > > WHERE my_func(x) = my_func(?) > > > > your problem is 'solved'... sort of. > > > > But what's the cost? > > On the query? None. > On the index maintenance? the cost of running an UPPER each time you do an > INSERT or UPDATE. I don't think that's 100% true. Let me clarify. In a simple filter, yes you'll use the functional index. But in a compound filter, you may use a different index. Unlike XPS and not until a future release of IDS, you can only use one index. So if the optimizer chooses the other index, you're then going to be doing a sequential scan using your function my_func() on each row. Yeah it sucks and its not just an IDS problem but also an Oracle and DB2 problem as well. Ran in to this on a large geospatial warehouse where the optimizer used the other column's index first and then ran the geospatial filter in a sequential scan of the collection. To get better performance, we had to split the query using an indexed temp table, so we could use the spatial index. Unfortunately this was Oracle. IDS actually does temp tables right. BTW, ever try applying a spatial filter on a million row collection without an index? Very, very painful. Look, UPPER() is a fairly simple function. When you get in to a more complex function, it will get painful fast. (Try doing a software only AES encryption/decryption function on a million rows) -G _________________________________________________________________ Windows Live™: E-mail. Chat. Share. Get more ways to connect. http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_HM_AE_Faster_022009

Ian Michael Gumby wrote: > > > > From: domusonline@gmail.com > > Subject: Re: Creating index with upper function > > Date: Thu, 5 Feb 2009 00:38:10 +0000 > > To: informix-list@iiug.org > > > > I believe this message was not sent... Sorry if it appears as duplicate. > > > > Ok, > > > > > > So if you use the : > > > WHERE my_func(x) = my_func(?) > > > > > > your problem is 'solved'... sort of. > > > > > > But what's the cost? > > > > On the query? None. > > On the index maintenance? the cost of running an UPPER each time you > do an > > INSERT or UPDATE. > > I don't think that's 100% true. > > Let me clarify. > > In a simple filter, yes you'll use the functional index. > But in a compound filter, you may use a different index. > > Unlike XPS and not until a future release of IDS, you can only use one > index. Not exactly true, although I understand your concern. Try something with and OR between two indexed columns and you'll possibly see what I mean. Even on v10. Never saw it in previous versions. Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...