Re: Sequential scan vs Index

Dieter Becker writes: |> select unique patient.* from stationaer, patient |> where stationaer.izahl = patient.izahl |> and stationaer.station = ? |> and stationaer.e_datum >= ? |> and stationaer.a_datum <= ? |> order by 2, 4 |> |> 1) db.patient: SEQUENTIAL SCAN |> |> 2) db.stationaer: INDEX PATH |> Filters: (db.stationaer.station = 'M306' |> AND (db.stationaer.e_datum >= '03/04/1994' |> AND db.stationaer.a_datum <= '03/04/1994' ) ) |> (1) Index Keys: izahl |> Lower Index Filter: db.stationaer.izahl = db.patient.izahl |> stationaer: |> Index name Owner Type Cluster Columns |> stat_c1 db dupls No station |> e_datum |> ix296_1 db dupls No izahl |> and patient: |> Index name Owner Type Cluster Columns |> pn_iz db unique No izahl I suppose you'd prefer that stationaer was accessed via the index on (station, e_datum), then the izahl value from that be used to do an index read of patient. Given that pn_iz is a unique index, this would be a logical choice. Bear in mind, however, that the optimizer is cost-based and makes its decisions based on what it computes to be the least-costly access method. For this reason, update statistics are important. Have you updates the stats recently? That will enable the optimizer to decide the relative selectivity of the available indexes and thus make a better choice. If update stats results in no change in the access, you could "fake out" the optimizer by adding "AND patient.izahl > 0", which should force the use of index pn_iz. Dave Kosenko Informix Software, Inc. Disclaimer: The opinions expressed in this message are not those of Informix Software, its partners or lackeys. Anyone who says otherwise is itching for a fight. **************************************************************************** "I look back with some satisfaction on what an idiot I was when I was 25, but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney