Re: Help on table indexing needed
}From: becker@ukh840.med-in.uni-sb.de (Dieter Becker)
}Subject: Help on table indexing needed
}Date: 8 Oct 1993 05:48:36 GMT
}X-Informix-List-Id: <news.4538>
}
}Sirs,
}
}i have a informix (online) table with the following scheme.
}It contains the data of patient's stay in our hospital.
}
} (
} izahl char(10) not null, # the identification of the patient
} station char(4) not null, # the ward
} a_datum date not null, # the start-date in the hospital
} e_datum date, # the leaving date
} ..... some other stuff deleted .......
} );
}
}My main query is to ask:
}which person were at a day or at a time-interval on station xx
}In Informix 4GL the code is:
} let ss = "select * from stationaer",
} " where ",
} " (stationaer.e_datum is null or stationaer.e_datum >= ?)",
} " and stationaer.a_datum <= ? ",
} " and stationaer.station = ?"
}
}This code is rather slow and I don't not know how to set the indices.
I haven't conducted the tests, but I'd create an index:
create index i1_stationaer on stationaer(station, a_datum);
One change which would simplify things would be to have some date such as
31/12/9999 as the e_datum value for people still in hospital. The SELECT
then simplifies to:
select * from stationaer
where e_datum >= ?
and a_datum <= ?
and station = ?
and you might get some value out of extending i1_stationaer to include
e_datum.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>