Index inquiry

IDS: 9.40.FC3 OS: HPUX 11.11 We have several hundred tables from a 3rd party application that was created in the 7.x (at least that old) era and many tables have what I think are redundant and unnecessary indices, but wanted to pose the question to the group to see if I understand the indices and their usage correctly. For example, below is the SQL to create a table and it's indices. Do I need the caseindex since the same fields are also in the actioncaseindex? Also, If I did a select on fields in the actioncaseindex and actiondateindex, will it do a sequential scan or will it use a combined index search? On another table, we have a couple of multi column indices and several single column indices. If I do a select with a couple of those single columns included, again will it do a sequential scan or combined index search? Last question, we have a few tables that have several multi column indices. If one index has columns that are completely included within another, larger index, do I need the smaller index (i.e.. one index has fields 1,2,3,4,6,7,10,18,20 and another index has 1,2,3,4,6,7)? Would it be better to keep the smaller index, drop the larger, and create a new index just on 10,18,20 or create 3 new indices on each column individually (if combined index searches are done)? create table 'informix'.archivedanddeletedcases ( id SERIAL not null, courttype CHAR(1) not null, courtid SMALLINT not null, casecategory CHAR(2) not null, casenumber INT not null, action INT not null, actiondate DATE, insertdate DATETIME YEAR TO MINUTE default CURRENT YEAR TO MINUTE not null, deleterecord boolean default 'f' not null, d1personnumber INT, p1personnumber INT ) in sccdbs extent size 8192 next size 1024 lock mode row; create index 'informix'.actioncaseidindex on 'informix'.archivedanddeletedcases ( action, casenumber, casecategory, courtid, courttype ); create index 'informix'.actiondateindex on 'informix'.archivedanddeletedcases ( actiondate, action ); create unique index 'informix'.caseidindex on 'informix'.archivedanddeletedcases ( casenumber, casecategory, courtid, courttype ); alter table 'informix'.archivedanddeletedcases add constraint primary key (id) constraint pk_archivedandd139; Thanks for any info/opinions you can provide, Randy

An index can (and will be used) if it's leading column (the first column in the index) is part of the where clause. If not, it will not be used. So index(a,b,c) will not be used for queries on 'b' or 'c', but it will be used for queries on a, a,b, or a,b,c - it may even be used for a,c. Something you might try is looking as systabio in the sysmaster database - from that you can see which indices are actually being used. But then, if they aren't causing a problem, why get rid of them? j. >From: "Kennedy, Randy" <RKennedy@scottsdaleaz.gov> >Date: 2007/05/29 Tue PM 02:01:13 CDT >To: ids@iiug.org >Subject: Index inquiry [9253] >IDS: 9.40.FC3 >OS: HPUX 11.11 > >We have several hundred tables from a 3rd party application that was >created in the 7.x (at least that old) era and many tables have what I >think are redundant and unnecessary indices, but wanted to pose the >question to the group to see if I understand the indices and their usage >correctly. > >For example, below is the SQL to create a table and it's indices. Do >I need the caseindex since the same fields are also in the >actioncaseindex? Also, If I did a select on fields in the >actioncaseindex and actiondateindex, will it do a sequential scan or >will it use a combined index search? On another table, we have a couple >of multi column indices and several single column indices. If I do a >select with a couple of those single columns included, again will it do >a sequential scan or combined index search? Last question, we have a >few tables that have several multi column indices. If one index has >columns that are completely included within another, larger index, do I >need the smaller index (i.e.. one index has fields 1,2,3,4,6,7,10,18,20 >and another index has 1,2,3,4,6,7)? Would it be better to keep the >smaller index, drop the larger, and create a new index just on 10,18,20 >or create 3 new indices on each column individually (if combined index >searches are done)? > >create table 'informix'.archivedanddeletedcases ( > >id SERIAL not null, > >courttype CHAR(1) not null, > >courtid SMALLINT not null, > >casecategory CHAR(2) not null, > >casenumber INT not null, > >action INT not null, > >actiondate DATE, > >insertdate DATETIME YEAR TO MINUTE default CURRENT YEAR TO MINUTE >not null, > >deleterecord boolean default 'f' not null, > >d1personnumber INT, > >p1personnumber INT >) >in sccdbs >extent size 8192 next size 1024 >lock mode row; > >create index 'informix'.actioncaseidindex on >'informix'.archivedanddeletedcases >( >action, >casenumber, >casecategory, >courtid, >courttype >); > >create index 'informix'.actiondateindex on >'informix'.archivedanddeletedcases >( >actiondate, >action >); > >create unique index 'informix'.caseidindex on >'informix'.archivedanddeletedcases >( >casenumber, >casecategory, >courtid, >courttype >); > >alter table 'informix'.archivedanddeletedcases add constraint primary >key >(id) >constraint pk_archivedandd139; > >Thanks for any info/opinions you can provide, >Randy > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.

Space and extents/maintenance was the main issues to look at them. Several of our tables have between 5-8 millions rows (not huge by some standards, but I have a finite amount of drive space currently available. Thanks, Randy -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of vze2qjg5@verizon.net Sent: Tuesday, May 29, 2007 12:22 PM To: ids@iiug.org Subject: Re: Index inquiry  [9254] An index can (and will be used) if it's leading column (the first column in the index) is part of the where clause. If not, it will not be used. So index(a,b,c) will not be used for queries on 'b' or 'c', but it will be used for queries on a, a,b, or a,b,c - it may even be used for a,c. Something you might try is looking as systabio in the sysmaster database - from that you can see which indices are actually being used. But then, if they aren't causing a problem, why get rid of them? j. >From: "Kennedy, Randy" <RKennedy@scottsdaleaz.gov> >Date: 2007/05/29 Tue PM 02:01:13 CDT >To: ids@iiug.org >Subject: Index inquiry [9253] >IDS: 9.40.FC3 >OS: HPUX 11.11 > >We have several hundred tables from a 3rd party application that was >created in the 7.x (at least that old) era and many tables have what I >think are redundant and unnecessary indices, but wanted to pose the >question to the group to see if I understand the indices and their >usage correctly. > >For example, below is the SQL to create a table and it's indices. Do I >need the caseindex since the same fields are also in the >actioncaseindex? Also, If I did a select on fields in the >actioncaseindex and actiondateindex, will it do a sequential scan or >will it use a combined index search? On another table, we have a couple >of multi column indices and several single column indices. If I do a >select with a couple of those single columns included, again will it do >a sequential scan or combined index search? Last question, we have a >few tables that have several multi column indices. If one index has >columns that are completely included within another, larger index, do I >need the smaller index (i.e.. one index has fields 1,2,3,4,6,7,10,18,20 >and another index has 1,2,3,4,6,7)? Would it be better to keep the >smaller index, drop the larger, and create a new index just on 10,18,20 >or create 3 new indices on each column individually (if combined index >searches are done)? > >create table 'informix'.archivedanddeletedcases ( > >id SERIAL not null, > >courttype CHAR(1) not null, > >courtid SMALLINT not null, > >casecategory CHAR(2) not null, > >casenumber INT not null, > >action INT not null, > >actiondate DATE, > >insertdate DATETIME YEAR TO MINUTE default CURRENT YEAR TO MINUTE not >null, > >deleterecord boolean default 'f' not null, > >d1personnumber INT, > >p1personnumber INT >) >in sccdbs >extent size 8192 next size 1024 >lock mode row; > >create index 'informix'.actioncaseidindex on >'informix'.archivedanddeletedcases >( >action, >casenumber, >casecategory, >courtid, >courttype >); > >create index 'informix'.actiondateindex on >'informix'.archivedanddeletedcases >( >actiondate, >action >); > >create unique index 'informix'.caseidindex on >'informix'.archivedanddeletedcases >( >casenumber, >casecategory, >courtid, >courttype >); > >alter table 'informix'.archivedanddeletedcases add constraint primary >key >(id) >constraint pk_archivedandd139; > >Thanks for any info/opinions you can provide, Randy > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Unlike Informix XPS, IDS cannot use more than one index for filtering or joining to the same table in a single query. So, if you have an index on column a, another on column b and a third on column c, with a compound index on a,b,c for queries only filtering on a, or b, or c IDS will tend to use the singleton indexes that's appropriate, but for queries on a and b or a,b & c it will use the compound index. The optimizer is even capable of performing an index filter on 'c' using the compound index if a & c are specified as filter or join columns in a query in order to minimize the number of data pages returned. Specific to your questions, caseidindex is needed over the actioncaseidindex when the action is not specified. The optimizer cannot use the latter for such queries. However, if all queries include say casenumber, casecategory, courtid, and courttype while only some include action, you could successfully replace both with an index on (casenumber, casecategory, courtid, courttype, action) which can be used for both queries. Similarly if all contain casenumber and casecategory, some also include action, and others courtid and/or courttype you can probably get away with an index on (casenumber, casecategory, action, courtid, courttype) as long as queries without action but with one or both of courtid and courttype are less frequent than those with action included. On the other question, if you have an index on columns 1-7 plus 10, 18 & 20 you do not also need an index on 1-7 alone UNLESS that one is needed to support some referential integrity constraint like a primary or foreign key. Basically, as already mentioned by someone, IDS can use any index that begins with one or more of the filter/join columns, IDS can always use a leading contiguous subset of the columns for direct matching row lookup, and IDS can always use any non-contiguous subset of the keys for filtering rows at the index level without reading data pages as long as at least the first key column is in the filter. Art S. Kagel ----- Original Message ----- From: Randy Kennedy <ids@iiug.org> At: 5/29 15:01:53 IDS: 9.40.FC3 OS: HPUX 11.11 We have several hundred tables from a 3rd party application that was created in the 7.x (at least that old) era and many tables have what I think are redundant and unnecessary indices, but wanted to pose the question to the group to see if I understand the indices and their usage correctly. For example, below is the SQL to create a table and it's indices. Do I need the caseindex since the same fields are also in the actioncaseindex? Also, If I did a select on fields in the actioncaseindex and actiondateindex, will it do a sequential scan or will it use a combined index search? On another table, we have a couple of multi column indices and several single column indices. If I do a select with a couple of those single columns included, again will it do a sequential scan or combined index search? Last question, we have a few tables that have several multi column indices. If one index has columns that are completely included within another, larger index, do I need the smaller index (i.e.. one index has fields 1,2,3,4,6,7,10,18,20 and another index has 1,2,3,4,6,7)? Would it be better to keep the smaller index, drop the larger, and create a new index just on 10,18,20 or create 3 new indices on each column individually (if combined index searches are done)? create table 'informix'.archivedanddeletedcases ( id SERIAL not null, courttype CHAR(1) not null, courtid SMALLINT not null, casecategory CHAR(2) not null, casenumber INT not null, action INT not null, actiondate DATE, insertdate DATETIME YEAR TO MINUTE default CURRENT YEAR TO MINUTE not null, deleterecord boolean default 'f' not null, d1personnumber INT, p1personnumber INT ) in sccdbs extent size 8192 next size 1024 lock mode row; create index 'informix'.actioncaseidindex on 'informix'.archivedanddeletedcases ( action, casenumber, casecategory, courtid, courttype ); create index 'informix'.actiondateindex on 'informix'.archivedanddeletedcases ( actiondate, action ); create unique index 'informix'.caseidindex on 'informix'.archivedanddeletedcases ( casenumber, casecategory, courtid, courttype ); alter table 'informix'.archivedanddeletedcases add constraint primary key (id) constraint pk_archivedandd139; Thanks for any info/opinions you can provide, Randy ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.