Re: Query Optimizer

root@perk.jpr.com (Joseph A. Miele) writes: >The following two queries QUERY 1 and QUERY 2 are the output of SET EXPLAIN >ON. I do not understand why the query optimizer does a sequential scan on >root.alujobsh in QUERY 2. The only difference in the two queries are these >lines: >QUERY 1: atkliquh.inv_num = '12316' and >QUERY 2: atkliquh.inv_num >= '12316' and atkliquh.inv_num <= '12316' and I'm no Query Optimizer expert, by try: atkliquh.inv_num BETWEEN '12316' AND '12316' and and see if the sequential scan gets moved to a lower level again. ================ Dennis J. Pimple Informix CSE / Denver 303-850-0210 >QUERY 1 take 5 seconds to run while QUERY 2 may take 5 minutes to run. Why >does adding the additional and cluase onto QUERY 2 cause the optmizer to do >a sequential scan and not use the indexes being used in QUERY 1? >QUERY 1: >------ >select count(*) from ainvpost, strcustr, atkliquh, alujobsh, atkliqud where>atkliquh.inv_num = '12316' and >atkliquh.inv_num = ainvpost.inv_num and >ainvpost.c_num = strcustr.cust_code and >atkliquh.job_num = alujobsh.job_num and >atkliquh.tk_num = atkliqud.tk_num >Estimated Cost: 29137 >Estimated # of Rows Returned: 1 >1) root.atkliquh: INDEX PATH > (1) Index Keys: inv_num > Lower Index Filter: root.atkliquh.inv_num = '12316' >2) root.ainvpost: INDEX PATH > (1) Index Keys: inv_num > Lower Index Filter: root.ainvpost.inv_num = root.atkliquh.inv_num >3) root.strcustr: INDEX PATH > (1) Index Keys: cust_code > Lower Index Filter: root.strcustr.cust_code = root.ainvpost.c_num >4) root.atkliqud: INDEX PATH > (1) Index Keys: tk_num > Lower Index Filter: root.atkliqud.tk_num = root.atkliquh.tk_num >5) root.alujobsh: INDEX PATH > (1) Index Keys: job_num > Lower Index Filter: root.alujobsh.job_num = root.atkliquh.job_num >QUERY2 : >------ >select count(*) from ainvpost, strcustr, atkliquh, alujobsh, atkliqud where>atkliquh.inv_num >= '12316' and atkliquh.inv_num <= '12316' and >atkliquh.inv_num = ainvpost.inv_num and >ainvpost.c_num = strcustr.cust_code and >atkliquh.job_num = alujobsh.job_num and >atkliquh.tk_num = atkliqud.tk_num >Estimated Cost: 98119 >Estimated # of Rows Returned: 1 >1) root.alujobsh: SEQUENTIAL SCAN >2) root.atkliquh: INDEX PATH > Filters: (root.atkliquh.inv_num >= '12316' AND root.atkliquh.inv_num <= '12316' ) > (1) Index Keys: job_num > Lower Index Filter: root.atkliquh.job_num = root.alujobsh.job_num >3) root.ainvpost: INDEX PATH > (1) Index Keys: inv_num > Lower Index Filter: root.ainvpost.inv_num = root.atkliquh.inv_num >4) root.strcustr: INDEX PATH > (1) Index Keys: cust_code > Lower Index Filter: root.strcustr.cust_code = root.ainvpost.c_num >5) root.atkliqud: INDEX PATH > (1) Index Keys: tk_num > Lower Index Filter: root.atkliqud.tk_num = root.atkliquh.tk_num