Re: SEQ search faster than INDEX ?

Try: select mawb.flight_arr_date, mawb.flight_no, mawb.uplift_station, mawb.mawb_no,mawb.ship_mode from mawb where flight_arr_date BETWEEN '21/05/1995' AND '25/05/1995' order by mawb.flight_arr_date desc And see if performance doesn't improve with an index on flight_arr_date ======================================================================= Dennis J. Pimple dennisp@informix.com Opinions expressed Senior Consultant -------------------- are mine, and do not Informix Software Inc Voice: 303-850-0210 necessarily reflect Denver Colorado USA Fax: 303-779-4025 those of my employer. Ti Lian Hwang (tilh@sin-co.sin-ro.DHL.COM) wrote: : There seems to be a problem with index searches bases on dates. They seem to : required more resources than sequential searches. : The following shows the result of "set explain on" on 3 queries, : with different indices. : 1) costs 1249, using a combination index : 2) costs 4053, using a date index : 3) costs 1766, using a squential scan : Any ideas ? : ------------------------------------------ : OnLine RSAM Version 5.03.UC1 : OS HP-UX apollo A.09.04 : ------------------------------------------ : QUERY: : ------ : select mawb.flight_arr_date, mawb.flight_no, mawb.uplift_station, mawb.mawb_no, : mawb.ship_mode from mawb : where flight_arr_date >= '21/05/1995' AND flight_arr_date <= '25/05/1995' : order by mawb.flight_arr_date desc : Estimated Cost: 1249 : Estimated # of Rows Returned: 2908 : Temporary Files Required For: Order By : 1) gisadm.mawb: INDEX PATH : (1) Index Keys: flight_arr_date flight_no mawb_no : Lower Index Filter: gisadm.mawb.flight_arr_date >= '21/05/1995' : Upper Index Filter: gisadm.mawb.flight_arr_date <= '25/05/1995' : QUERY: : ------ : select mawb.flight_arr_date, mawb.flight_no, mawb.uplift_station, mawb.mawb_no, : mawb.ship_mode from mawb : where flight_arr_date >= '21/05/1995' AND flight_arr_date <= '25/05/1995' : order by mawb.flight_arr_date desc : Estimated Cost: 4053 : Estimated # of Rows Returned: 2908 : Temporary Files Required For: Order By : 1) gisadm.mawb: INDEX PATH : (1) Index Keys: flight_arr_date : Lower Index Filter: gisadm.mawb.flight_arr_date >= '21/05/1995' : Upper Index Filter: gisadm.mawb.flight_arr_date <= '25/05/1995' : QUERY: : ------ : select mawb.flight_arr_date, mawb.flight_no, mawb.uplift_station, mawb.mawb_no, : mawb.ship_mode from mawb : where flight_arr_date >= '21/05/1995' AND flight_arr_date <= '25/05/1995' : order by mawb.flight_arr_date desc : Estimated Cost: 1766 : Estimated # of Rows Returned: 2908 : Temporary Files Required For: Order By : 1) gisadm.mawb: SEQUENTIAL SCAN : Filters: (gisadm.mawb.flight_arr_date >= '21/05/1995' AND gisadm.mawb.flight_arr_date <= '25/05/1995' ) : ----------------------------------- : Ti Lian Hwang : email : tilh@sin-co.sin-ro.dhl.com : -----------------------------------