Creating index with upper function
Mo
Why doesn't Informix allow creating indexes with upper function?
Something like
create index idx_last on table(upper(column));
bozon
You can do this but it leaves me with a question. Why isn't the upper
function "NOT VARIANT" in the first place? You'll see what I mean in
my example.
create table test_emp(
first_name varchar(64),
last_name varchar(64),
mi varchar(64)
) ;
create unique index test_emp_0ux on test_emp( last_name, first_name,
mi ) ;
create function my_upper( s varchar(64) ) returning varchar(64) with
(not variant) ;
return upper(s) ;
end function;
update statistics for procedure my_upper;
create index test_emp_1x on test_emp(my_upper(last_name)) ;
-- Figure out how to get enough data in here any way you want. I
cheated.
-- insert into test_emp select first 5000 distinct first_name,
last_name, middle_initial from employee ;
update statistics for table test_emp;
update statistics high for table test_emp;
set explain on;
-- Does scan see sqexplain.out segment below
select * from test_emp where upper(last_name) >= "FRED"
-- Uses index see sqexplain.out segment below
select * from test_emp where my_upper(last_name) >= "FRED"
{
QUERY:
------
select * from test_emp where upper(last_name) >= "FRED"
Estimated Cost: 205
Estimated # of Rows Returned: 1648
1) informix.test_emp: SEQUENTIAL SCAN
Filters: UPPER(informix.test_emp.last_name ) >= 'FRED'
QUERY:
------
select * from test_emp where my_upper(last_name) >= "FRED"
Estimated Cost: 86
Estimated # of Rows Returned: 1648
1) informix.test_emp: INDEX PATH
(1) Index Keys: informix.my_upper(last_name) (Serial, fragments:
ALL)
Lower Index Filter: informix.my_upper
(informix.test_emp.last_name )>= 'FRED'
UDRs in query:
--------------
UDR id : 291
UDR name: my_upper
UDR id : 291
UDR name: my_upper
}
Here is the warning in the FM:
function User-defined function used as a key to this index Must be a
nonvariant function that does not return a
large object data type. Cannot be a built-in algebraic, exponential,
log,orhexfunction.“Identifier” on page 5-23
Here is the definition of variant:
Use the VARIANT and NOT VARIANT modifiers with C user-defined
functions
and SPL functions. A function is variant if it returns different
results when it is
invoked with the same arguments or if it modifies a database or
variable state. For
example, a function that returns the current date or time is a variant
function.
I am not sure why the upper function would be considered "VARIANT". I
sure hope it doesn't give me different output with the same arguments.
I know it might do different things based on the locale but I consider
that an implicit argument.
Obnoxio The Clown
UPPER isn't "NOT VARIANT". You'll have to create your own NOT VARIANT
function and use that.
Submit a feature request, I know it's being considered (because I said
it was stupid that this doesn't work, too!)
--
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.
bozon
Yes, because about the first thing you try when you here that Informix
has functional indexes is
create index employee_last_name_index on employee( upper(last_name) );
It of course doesn't work because the upper function is "broken" .
Ian Michael Gumby
> Submit a feature request, I know it's being considered (because I said
> it was stupid that this doesn't work, too!)
>
> --
Ok, color me silly.
Table 1:
Id , myStmt
1, 'Hello'
2,'HELLo'
3,'HELlo'
4,'HELLO'
Now create your UPPER index on the stmt column
How are you going to use it?
Select * from table_1 where myStmt = 'hello'
Whill the optimizer use the index? What will match?
Ok, more to the point, what is the end result that you are trying to
achieve?
I suspect that the OP wanted to do something like taking 'HelLO' and
'hello' and then storing that in the data base as 'HELLO' so that a
regular index will work.
So when write your query you'd write
Select * from table_1 where myStmt = UPPER(?)
Where the ? represents the user's input. SO that if he entered
'hello', all the rows with some permutation of 'hello' will be
returned.
Does that make sense or am I missing something?
Obnoxio The Clown
Do you have to make a comment on every post, whether it adds value or
not? :o)
Ian Michael Gumby
> > Does that make sense or am I missing something?
>
> not? :o)
>
> Cheers,
> Obnoxio The Clown
>
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.
Hotmail® goes where you go. On a PC, on the Web, on your phone. See how.
Obnoxio The Clown
>
>
> > Date: Sun, 1 Feb 2009 15:58:45 +0000
> > From: obn...@serendipita.com
>
> >
> > not? :o)
> >
> > Cheers,
> > Obnoxio The Clown
> >
>
> 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.
Ian Michael Gumby
> Date: Sun, 1 Feb 2009 17:27:12 +0000
> From: obn...@serendipita.com
> CC: inform...@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: obn...@serendipita.com
> >
> > >
> > > not? :o)
> > >
> > > Cheers,
> > > Obnoxio The Clown
> > >
> >
> > 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.
>
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. See how it works.
Fernando Nunes
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...
Ian Michael Gumby
> 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?
Obnoxio The Clown
>
>
> > Date: Sun, 1 Feb 2009 17:27:12 +0000
> > From: obn...@serendipita.com
> > CC: inform...@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: obn...@serendipita.com
> > >
> > > >
> > > > not? :o)
> > > >
> > > > Cheers,
> > > > Obnoxio The Clown
> > > >
> > >
> > > 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'. ;-)
Oh, FFS. Go RTFM or STFU.
Obnoxio The Clown
> 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
Ian Michael Gumby
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?
bozon
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.
Obnoxio The Clown
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.
Obnoxio The Clown
>
>
> > Date: Mon, 2 Feb 2009 14:46:06 +0000
> > From: obn...@serendipita.com
>
> > > Then your index on bar will be used and everyone is happy.
> >
> > don't have to worry about that.
> >
>
> Hmmm, a clown who can't pull the trigger? ;-)
> Or handle constraints?
>
> What happens when your offshore team forgets to use my_func()
> and writes the query...
>
> WHERE foo = UPPER(?) and expects it to work?
If it's coded into the application, it's hardly a problem. It will
happen once or twice, be felt by the user, moaned about and then get
fixed. But if your trigger has a problem, the problem might remain
hidden for years, gradually corrupting data and reducing the value of
the system to the business.
> For every solution there is a trade-off.
Indeed.
Fernando Nunes
> 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.
Fernando Nunes
> 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.
bozon
I was looking at 11.50 documentation. I should have mentioned that in
the citation.
Fernando Nunes
What I meant is that the clarification that we cannot use internal/builtin
functions was introduced in 11.10. So it's normal they're still on 11.50.
We could compare it with V10 or before.
Regards.
Fernando Nunes
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.
Ian Michael Gumby
> From: domus...@gmail.com
> Subject: Re: Creating index with upper function
> Date: Thu, 5 Feb 2009 00:38:10 +0000
> To: inform...@iiug.org
>
> I believe this message was not sent... Sorry if it appears as duplicate.
> >
> > 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.
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. Check it out.
Fernando Nunes
>
>
> > From: domus...@gmail.com
> > Subject: Re: Creating index with upper function
> > Date: Thu, 5 Feb 2009 00:38:10 +0000
> > To: inform...@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.