Re: Optimize Query for slow responce

In article <38lia4$97d@emory.mathcs.emory.edu>, pp@q8.dk (Poul Pedersen) writes: |> 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 ? |> Hi Poul, I think part of your problem lies with using an arithmetic qualifier, the "<" sign against a character field. The optimizer is using the indexed column gd_kontonr the way it should, but forcing it to look at gd_prod_kode as a numeric value is possibly slowing it down. Just a guess. Anyone know for sure? -- Ron Rupert | I've read dozens of books about heroes rlr@alert.com/(303)488-7674 | and crooks, and I've learned much from Programmer Analyst | both of their styles. - Jimmy Buffet Alert Centre, Inc. |