IDS Forum

Index inquiry

Posted By: Kennedy, Randy
Date: Tuesday, 29 May 2007, at 3:01 p.m.

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

Messages In This Thread

• Index inquiry
  Kennedy, Randy -- Tuesday, 29 May 2007, at 3:01 p.m.
  • Re: Index inquiry Â
    vze2qjg5@verizon.net -- Tuesday, 29 May 2007, at 3:22 p.m.
    • RE: Index inquiry Â
      Kennedy, Randy -- Tuesday, 29 May 2007, at 3:28 p.m.
  • Re: Index inquiry
    ART KAGEL, BLOOMBERG/ 731 LEXIN -- Tuesday, 29 May 2007, at 4:19 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.