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 ->Reply-To: becker@ukh840.med-in.uni-sb.de (Dieter Becker) ->Organization: Medizinische Universitaetsklinik, D-6650 Homburg / Saar -> ->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 indexed on every of this 4 field, but I seem the query resolves in a ->sequential search. INFORMIX-VERSION is 4.1. ->Can You help me please. -> ->-- -> Dr. med. dipl.-math Dieter Becker Tel.: (0 / +49) 6841 - 16 3046 -> Medizinische Universitaets- und Poliklinik Fax.: (0 / +49) 6841 - 16 3369 -> Innere Medizin III -> D - 66421 Homburg / Saar Email: becker@med-in.uni-sb.de -> Hr. Dr. Becker, The "or" in your query is probably the culprit. "Ors" are handled badly by Informix and usually result in a sequential search of the entire table. One option is to rewrite your query as the UNION of two queries, one with "stationaer.e_datum is null" and one with "stationaer.e_datum >= ?". Another is to recast the "or" using a variation of DeMorgan's theorem: (A or B) == not (not A and not B) So I suppose this becomes: "not (stationaer.e_datum is not null and stationaer.e_datum < ?)" I have no idea which query would run faster. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, Tech Ops | / \\ alan@den.mmc.com | P.O. Box 179, M/S 5422 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\