SEQ search faster than INDEX ?

Posted in 1995

Ti Lian Hwang — 24 May 1995 23:23:03 -0400 — source: Informix-list mailing list archive (1991-1998)

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 -----------------------------------