RE: {Spam?} RE: Creating index with upper function

> Date: Sun, 1 Feb 2009 17:27:12 +0000 > From: obnoxio@serendipita.com > CC: informix-list@iiug.org > Subject: Re: {Spam?} RE: Creating index with upper function > > Ian Michael Gumby wrote: > > > > > > > Date: Sun, 1 Feb 2009 15:58:45 +0000 > > > From: obnoxio@serendipita.com > > > > > > Does that make sense or am I missing something? > > > > > > Do you have to make a comment on every post, whether it adds value or > > > not? :o) > > > > > > -- > > > Cheers, > > > Obnoxio The Clown > > > > > No, > > > > I'll leave the 'Have you tried UPDATE STATISTICS? ' question for you. :-) > > > > I think part of the problem is that I see people asking questions on how > > to do something without thinking about the actual problem they are > > trying to solve. > > And functional indexes solve a lot of problems. Almost every client I > have shown the feature to wants to use a functional index on UPPER, but > can't because it's VARIANT. > So if there is a functional index on a column then the optimizer will always use the functional index and never do a sequential scan? I'd say that you should talk with the guys writing the optimizer, just to be sure. An alternative is to create a column which is an UPPER() of the other column and then create an index on that column. It will work. And of course, storage is 'cheap'. ;-) _________________________________________________________________ Windows Live™ Hotmail®…more than just e-mail. http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_hm_justgotbetter_howitworks_012009

Ian Michael Gumby wrote: > > > > Date: Sun, 1 Feb 2009 17:27:12 +0000 > > From: obnoxio@serendipita.com > > CC: informix-list@iiug.org > > Subject: Re: {Spam?} RE: Creating index with upper function > > > > Ian Michael Gumby wrote: > > > > > > > > > > Date: Sun, 1 Feb 2009 15:58:45 +0000 > > > > From: obnoxio@serendipita.com > > > > > > > > Does that make sense or am I missing something? > > > > > > > > Do you have to make a comment on every post, whether it adds value or > > > > not? :o) > > > > > > > > -- > > > > Cheers, > > > > Obnoxio The Clown > > > > > > > No, > > > > > > I'll leave the 'Have you tried UPDATE STATISTICS? ' question for > you. :-) > > > > > > I think part of the problem is that I see people asking questions > on how > > > to do something without thinking about the actual problem they are > > > trying to solve. > > > > And functional indexes solve a lot of problems. Almost every client I > > have shown the feature to wants to use a functional index on UPPER, but > > can't because it's VARIANT. > > > > So if there is a functional index on a column then the optimizer will > always use the functional index and never do a sequential scan? > I'd say that you should talk with the guys writing the optimizer, just > to be sure. > > An alternative is to create a column which is an UPPER() of the other > column and then create an index on that column. > It will work. And of course, storage is 'cheap'. ;-) That would work if you used the UPPER case as argument in the where clause. Something like: WHERE my_storage_waste = UPPER(?) or WHEEW my_storage_waste = "?" and made sure ? = "SOMETHING IN CAPS" If you use a custom NON VARIANT function (let's call it my_upper) you can do this: 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... Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

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. Ok, So if you use the : WHERE my_func(x) = my_func(?) your problem is 'solved'... sort of. But what's the cost? 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? Think about it. 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? Note that UPPER is only called on the insert of a row. Or you just store the data in uppercase and ignore the idea of maintaining the case sensitive initial value. As I have demonstrated, you have a viable work around. Now, Gumby you ask, why is this important? 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. 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. 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? So why don't you go back to school and try and teach a next generation of young'ns to use IDS?

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. > > Ok, > > So if you use the : > WHERE my_func(x) = my_func(?) > > your problem is 'solved'... sort of. > > But what's the cost? > > 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? You haven't got a fucking clue about functional indexes, do you? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

On Feb 2, 1:37 am, Obnoxio The Clown <obno...@serendipita.com> wrote: > 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. > > > Ok, > > > So if you use the : > > WHERE my_func(x) = my_func(?) > > > your problem is 'solved'... sort of. > > > But what's the cost? > > > 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? > > You haven't got a fucking clue about functional indexes, do you? > Actually I do. Here's the two options... option 1: You have a character column foo, and some functional index using my_func(x) WHERE my_func(foo) = my_func(?) where my_func is some function. In this case UPPER() When this statement runs, even before an index is used, you have to run my_func() twice. option 2: You have a character column bar which is set to UPPER (foo) on inserts and updated as foo is updated. You then have an index on column bar. Now you can do two things... where bar = ? -- Assuming that the application has done or bar = UPPER(?) -- you're taking the input and assuming nothing. Then your index on bar will be used and everyone is happy. But getting back to the point I was trying to make ... Because there is a work around, any quirk in behavior is going to take a lower priority than adding a new feature which will drive business, and a product defect for which there are no work around options. If the company is unwilling to spend money on projects like benchmarks, then where do you think this issue will fall?

On Feb 1, 11:30 pm, Ian Michael Gumby <im_gu...@hotmail.com> 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. > > Ok, > > So if you use the : > WHERE my_func(x) = my_func(?) > > your problem is 'solved'... sort of. > > But what's the cost? > > 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? > > Think about it. > > 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? > Note that UPPER is only called on the insert of a row. Or you just > store the data in uppercase and ignore the idea of maintaining the > case sensitive initial value. > > As I have demonstrated, you have a viable work around. > > Now, Gumby you ask, why is this important? > > 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. > > 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. > 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? > > So why don't you go back to school and try and teach a next generation > of young'ns to use IDS? What does update statistics do with functional indexes? I would think it would compile statistics on the index just like anything else, but a little test similar to the above test, but with everyone having a last name of "JOHNSON" used the index instead of scanning which is counter to what I would like. I couldn't find documentation to tell me what update statistics does with functional indexes. I actually used the above extra column method because we ran into an issue with functional indexes which we didn't find out about until right before we released. It was a quick hack and the special in row trigger syntax is a blessing for this kind of thing. We haven't changed it to a functional index because it isn't really causing us a problem and we had a hard time finding the issue in the first place, so we are afraid that it will only show up in production. Here is another tidbit from the manual: The function must be a user-defined function. You cannot create a functional index on any built-in function of SQL. You can, however, create a functional index on a user-defined function that calls a built in function and uses the value returned by the built-in function as the index key of a functional index.

Ian Michael Gumby wrote: > On Feb 2, 1:37 am, Obnoxio The Clown <obno...@serendipita.com> wrote: >> 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. >>> Ok, >>> So if you use the : >>> WHERE my_func(x) = my_func(?) >>> your problem is 'solved'... sort of. >>> But what's the cost? >>> 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? >> You haven't got a fucking clue about functional indexes, do you? >> > Actually I do. > > Here's the two options... > > option 1: You have a character column foo, and some functional index > using my_func(x) > > WHERE my_func(foo) = my_func(?) > > where my_func is some function. In this case UPPER() > > When this statement runs, even before an index is used, you have to > run my_func() twice. Why? You're making all sorts of assumptions about the optimiser here. But even if it is, woo! Phear the CPU costs. > option 2: You have a character column bar which is set to UPPER (foo) > on inserts and updated as foo is updated. > You then have an index on column bar. > > Now you can do two things... > > where bar = ? -- Assuming that the application has done > or bar = UPPER(?) -- you're taking the input and assuming nothing. > > Then your index on bar will be used and everyone is happy. Everyone except the guy who has to make absolutely sure that bar does, in fact, equal UPPER(foo). Whereas, if you use a functional index, you don't have to worry about that. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

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...

bozon wrote: > Here is another tidbit from the manual: > > The function must be a user-defined function. You cannot create a > functional index on any built-in function of SQL. You can, however, > create a functional index on a user-defined function that calls a > built in function and uses the value returned by the built-in function > as the index key of a functional index. > There are references to this subject way back to 1999 :) Apparently the fact is that it doesn't support the usage of built in functions. There is a feature request to change that. I believe the manual reference you mention was a fix into 11.10 docs. The error can be misleading... The issue may be the fact that UPPER is builtin and not the fact that it's variant or non-variant. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

On Feb 2, 7:24 pm, Fernando Nunes <domusonl...@gmail.com> wrote: > bozon wrote: > > Here is another tidbit from the manual: > > > The function must be a user-defined function. You cannot create a > > functional index on any built-in function of SQL. You can, however, > > create a functional index on a user-defined function that calls a > > built in function and uses the value returned by the built-in function > > as the index key of a functional index. > > There are references to this subject way back to 1999 :) > Apparently the fact is that it doesn't support the usage of built in functions. > There is a feature request to change that. I believe the manual reference you > mention was a fix into 11.10 docs. > > The error can be misleading... The issue may be the fact that UPPER is builtin > and not the fact that it's variant or non-variant. > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... I was looking at 11.50 documentation. I should have mentioned that in the citation.