Hi all
We are looking at implementing a functional index and have noticed
that the size of the index looks to be larger than the table itself at
least the allocated size. We are wondering why the allocated index
size would be so large and yet the used size is small - ?
We are on version 10.FC9 currently on AIX 5.3
Thanks for any assistance - Tom


Table:
Allocated Table Size : 10348 pg - 40.422 MB
Used Table Size : 6094 pg - 23.805 MB
Free : 4254 pg - 16.617 MB

Allocated Detached Index Size: 15129 pg - 59.098 MB
Used Index Size: 1199 pg - 4.684 MB
Free: 13930 pg - 54.414 MB

our example
the table :
create table 'informix'.reference (
id SERIAL not null,
from_class_id INT not null,
from_id INT not null,
type_cid INT not null,
status_cid INT not null,
reference_num VARCHAR(50),
update_version SMALLINT default 1 not null,
created_by VARCHAR(20) default USER not null,
create_date DATETIME YEAR TO FRACTION default CURRENT YEAR TO
FRACTION not null,
updated_by VARCHAR(20) default USER not null,
update_date DATETIME YEAR TO FRACTION default CURRENT YEAR TO
FRACTION not null,
reference_img_id INT8
) extent size 41392 next size 4136
lock mode row;

--Functional Index
create index 'informix'.reference_function_num on 'informix'.reference
( get_search_string (reference_num) );

create index 'informix'.reference_i1 on 'informix'.reference
(status_cid, from_class_id, from_id, type_cid, reference_num);
create index 'informix'.reference_i2 on 'informix'.reference
(status_cid, type_cid, reference_num, from_class_id, from_id);
create index 'informix'.reference_i3 on 'informix'.reference
(from_id, from_class_id );
create index 'informix'.reference_i4 on 'informix'.reference
(type_cid, status_cid );
create index 'informix'.reference_i5 on 'informix'.reference
( update_date );
create index 'informix'.referenceix1 on 'informix'.reference
( reference_num );
alter table 'informix'.reference add constraint primary key
(id) constraint reference_pk_id;
alter table 'informix'.reference add constraint foreign key
(reference_img_id) references 'informix'.reference_img
(reference_img_id)
constraint fk_reference_img_id_to_reference;

----------------------------------------------------------------------------------------------------------------------
here is the function :
create procedure "informix".get_search_string
(i_string varchar(255))
returning varchar(255,0) with (not variant) ;

define BLANK char(1) ;
define wk_string varchar(255,0) ;

-- Error handling
on exception
return null ;
end exception

-- Debugging only
-- trace on ;

let BLANK = ' ' ;

let wk_string = upper(replace(i_string, BLANK, ''));

let wk_string = replace(wk_string, ',', '');
let wk_string = replace(wk_string, '@', '') ;
let wk_string = replace(wk_string, '#', '') ;
let wk_string = replace(wk_string, '$', '') ;
let wk_string = replace(wk_string, '%', '') ;
let wk_string = replace(wk_string, '^', '') ;
let wk_string = replace(wk_string, '&', '') ;
let wk_string = replace(wk_string, '*', '') ;
let wk_string = replace(wk_string, '(', '') ;
let wk_string = replace(wk_string, ')', '') ;
let wk_string = replace(wk_string, '-', '') ;
let wk_string = replace(wk_string, '/', '') ;
let wk_string = replace(wk_string, '?', '') ;
let wk_string = replace(wk_string, ';', '') ;
let wk_string = replace(wk_string, ':', '') ;
let wk_string = replace(wk_string, '''', '') ;
let wk_string = replace(wk_string, '"', '') ;
let wk_string = replace(wk_string, '.', '') ;

return wk_string ;
end procedure;

gotchya - ran some tests and sure enough, change the varchar(255) to
varchar(50) and the allocated size drops about a fifth - even though
the used space stays the same (very small). it seems inefficient in
the storage utilization sense but good to know this beforehand as the
production functional index will end up being quite large.

So long & Thanks for all the fish, Ob!