Re: order by
Create a function index. See expample below.
IDS 9 or higher.
create function "informix".upper_index(char_in char(40))
returning char(40) with (not variant);
define char_out char(40);
let char_out = upper(char_in) ;
return char_out;
end function;
create table tcust
(
cust_id serial not null ,
cust_dt_on_sys date not null ,
cust_origin integer,
cust_proper_name char(36),
cust_first_name char(15) not null ,
cust_last_name char(20) not null ,
cust_middle_name char(15) not null ,
cust_email_addr char(40) not null ,
cust_svc_maint char(1)
);
create index "informix".upper_index_tcust on "ngpp".tcust
(upper_index(cust_email_addr))
using btree in ecomdevdbs01 ;
select * from tcust where upper_index(cust_email_addr) ="ROY.MERCER@GMAIL.COM"
output from sqexplain.out
QUERY:
------
select * from tcust where upper_index(cust_email_addr) ="ROY.MERCER@GMAIL.COM"
Estimated Cost: 5
Estimated # of Rows Returned: 116987
1) ngpp.tcust: INDEX PATH
(1) Index Keys: informix.upper_index(cust_email_addr)
informix.upper_index(c
ust_last_name) (Serial, fragments: ALL)
Lower Index Filter:
informix.upper_index(ngpp.tcust.cust_email_addr )= '
ROY.MERCER@GMAIL.COM'
UDRs in query:
--------------
UDR id : 234
UDR name: upper_index
UDR id : 234
UDR name: upper_index
UDR id : 234
UDR name: upper_index