Functional Index & sizing

Posted in 2010

Topics: Storage & Space Management, Stored Procedures & SPL, Data Types & Schema Design, Triggers, Constraints & Referential Integrity, Platform-Specific Issues

Tom Lehr — Tue, 29 Jun 2010 13:42:15 -0700 (PDT) — source: Usenet: comp.databases.informix

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;

↪ replying to Tom Lehr

Obnoxio The Clown — Tue, 29 Jun 2010 21:48:42 +0100 — source: Usenet: comp.databases.informix

Tom Lehr wrote: > 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) ; Just a guess, but I think the index extent is being sized on #rows * 255 (or thereabouts). But because the returned string is, on average, much shorter, it's not using all of it. > 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; > > > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

↪ replying to Obnoxio The Clown

Tom Lehr — Wed, 30 Jun 2010 06:37:48 -0700 (PDT) — source: Usenet: comp.databases.informix

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!