Creating index with upper function
Q: why can't you build a functional index like CREATE INDEX idx ON tab(upper(col)) in Informix 11? Answer: the built-in UPPER is flagged VARIANT, and functional indexes require a NOT VARIANT, non-built-in function. The workaround given (with set explain output proving the index is used) is to wrap it in an SPL function, e.g. CREATE FUNCTION my_upper(...) ... WITH (NOT VARIANT) RETURN upper(s), run UPDATE STATISTICS FOR PROCEDURE, then index my_upper(col) and use that function in queries. Posters called the restriction illogical and suggested filing a feature request.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Informix 11
Why doesn't Informix allow creating indexes with upper function?
Something like
create index idx_last on table(upper(column));
On Jan 30, 2:49 pm, Mo <mohitanch...@gmail.com> wrote:
> Informix 11
>
> Why doesn't Informix allow creating indexes with upper function?
> Something like
>
> create index idx_last on table(upper(column));
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.
Mo wrote:
> Informix 11
>
> Why doesn't Informix allow creating indexes with upper function?
> Something like
>
> create index idx_last on table(upper(column));
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.
On Jan 31, 3:31 am, Obnoxio The Clown <obno...@serendipita.com> wrote:
> Mo wrote:
> > Informix 11
>
> > Why doesn't Informix allow creating indexes with upper function?
> > Something like
>
> > create index idx_last on table(upper(column));>
> 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.
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" .
On Jan 31, 2:31 am, Obnoxio The Clown <obno...@serendipita.com> wrote:
> 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?
Ian Michael Gumby wrote:
> On Jan 31, 2:31 am, Obnoxio The Clown <obno...@serendipita.com> wrote:
>
>> 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?
Do you have to make a comment on every post, whether it adds value or
not? :o)
--
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.