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

Relevant Pages

  • Re: Creating index with upper function
    ... and don't have to waste storage (you use it for the index, ... The basic question still is relevant: Why the hell is UPPER variant? ... the cost of running an UPPER each time you do an INSERT or UPDATE. ... The fix would probably be easy. ...
    (comp.databases.informix)
  • Re: Please help extracting records
    ... we are trying to find out the "Na" level (per patient) that is higher than ... I made a calculated field to calculate 0 (if Na value was less than Upper ... I am not able to run the query this way, ...
    (microsoft.public.access.formscoding)
  • Re: Convert Names to Upper Lower format
    ... > I have names/addresses in either all upper or all lower or mixed,. ... > Somehow at one point I figures it out in an expression in a query but can ... In a query you can use ... However, in all probability, by changing van den Steen to Van Den ...
    (microsoft.public.access.queries)
  • Re: duplicates but different case
    ... > your suggestion worked but following query returns(the query uses sample ... > Any ideas Anders ... >> upper case but where one isn't upper case. ... >> case sensitieve ...
    (microsoft.public.fox.vfp.queries-sql)
  • Re: CAPS and Date/Time formatting
    ... Just a question about UCase. ... In a query where there is a join, ... The date/time formatting was perfect! ... The> in the Format property merely displays the data in upper case, ...
    (microsoft.public.access.reports)