Slooooooo Query

Regards, Peter .-------------------------. | Peter Estabrook |___________________________________________ | User Technology Assoc. | Host : SEQUENT S2000/200 Dynix/ptx 1.4 / | 2121 Crystal Drive #103 | Fax : (703) 486-7179 / | Arlington, VA 22202 USA | Voice: (703) 486-7190 ( (-------------------------| Mail : estabroo@sea07s.navsea.navy.mil \\ (__________________________________________\\ We had a problem like that. I not sure if it will help be what we found is that the cost based optimizer is to smart for it's own good. We had a query "company_name like Name% and state = va" (for example). We had keys on both fields and because of this, Online did the "state = va" first because it was not a substring search and it would happen faster. The problem was that the "company_name like Name%" was executed as a non-indexed search after it found the state. The table has 648,800 rows and the number of hits for any one state could be 12,000 - 30,000. We kept the index on the state field but we modified the state query to "state like state%" and all was well. Hope it helps. > 26-Oct-1994 > } I can not understand the Informix Query Optimizer ! } --------------------------------------------------- } } } I have a fine large table and I create a query, where I select all fields from } the table. To lookup the records I have an unique index on a field called } 'gd_kontonr' and as a second want, I have a filter 'gd_prod_kode < 90000'. } } select * from ks_gdkonto } where gd_kontonr = "0003573508" } and gd_prod_kode < 90000; } } This one takes loooooooong time to run.................... } } } select * from ks_gdkonto } where gd_kontonr = "0003573508"; } } This one is very very fast, you can't start it before it is finished, but } I want to have 'gd_prod_kode < 90000' on the where part. } } } } Now look at the Optimizer Query Plan: } } } QUERY: (LOW) } ------ } select * from ks_gdkonto } where gd_kontonr = "0003573508" } and gd_prod_kode < 90000 } } } Estimated Cost: 2 } Estimated # of Rows Returned: 1 } } 1) kundedb.ks_gdkonto: INDEX PATH } } Filters: kundedb.ks_gdkonto.gd_kontonr = '0003573508' } } (1) Index Keys: gd_prod_kode } Upper Index Filter: kundedb.ks_gdkonto.gd_prod_kode < 90000 } } } } It is totally upside down of what I expected ! } } } } This is the table create command: } } } create table "kundedb".ks_gdkonto } ( } gd_seg_type char(3), } gd_kontonr char(10) not null, } gd_aftale_type char(1), } gd_prod_kode char(5), } gd_tankstorrel integer, } gd_tanktype char(2), } gd_naeste_gd integer, } gd_a_faktor smallfloat, } gd_b_faktor smallfloat, } gd_c_faktor smallfloat, } gd_fast_kfaktor char(1), } gd_akk_kvant float, } gd_akk_s_aar float, } gd_akk_f_aar float, } gd_opret_dato date, } gd_udgaaet date, } gd_rette_dato date, } gd_rette_tid datetime hour to second, } gd_rettet_af char(10), } gd_udg_kode char(1), } gd_2_kort char(1), } gd_2_dato date, } gd_4_opret date, } gd_4_lukket date } ); } revoke all on "kundedb".ks_gdkonto from "public"; } } create unique index "kundedb".ks_gdk1 on "kundedb".ks_gdkonto (gd_kontonr); } create index "kundedb".ks_gdk2 on "kundedb".ks_gdkonto (gd_aftale_type); } create index "kundedb".ix454_4 on "kundedb".ks_gdkonto (gd_prod_kode); } create index "kundedb".ks_gdk4 on "kundedb".ks_gdkonto (gd_tanktype); } create index "kundedb".ks_gdk5 on "kundedb".ks_gdkonto (gd_fast_kfaktor); } } } } Why can't the Optimizer use a better Query Plan on this simple select ? } } } } Regards } ----------------------------------------------------------------- } Databaseadministrator Poul Pedersen } Kuwait Petroleum (Danmark) A/S } Hummeltoftevej 49 Mail: pp@q8.dk } 2830 Virum Voice: +45 45 98 45 94 } Denmark Fax: +45 42 85 14 18 } -----------------------------------------------------------------