Re: SEQ search faster than INDEX ?

Posted in 1995

Partha Dutta — Thu, 25 May 1995 05:33:08 -0500 (CDT) — source: Informix-list mailing list archive (1991-1998)

tilh@sin-co.sin-ro.DHL.COM (Ti Lian Hwang) 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 >----------------------------------- Try update statistis on the table, and then try the 3 queries over again. A lot of time, the optimizer gets confused about how to perform even the simplest of queries. If the update statistics doesn't work, try dropping the index and recreating it.