Re: SQL Query Optmizer

In article <CM1FzM.1A0@perk.jpr.com>, news@perk.jpr.com (Joseph A. Miele / News Administrator) 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 |> |> 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 |> |> -- |> Unix at Home perk machine |> Joseph A. Miele jam@perk.jpr.com |> Dir MIS Spectraserv, Inc. 76207.1365@compuserve.com I think this is fine. In the second query you have 2 simple filters ( i.e. non-join) : atkliquh.inv_num >= '12316' and atkliquh.inv_num <= '12316' Cost of using index on this table would depend on selctivity of filter1 * selectivity of filter2 Which would be greater than cost for using index for one more selective filter. i.e atkliquh.inv_num = '12316'. So for first query overall cost of the query is less if atkliquh is scanned first. And as alujobsh has an index for join filter with atkliquh -so index is used. For second query optimizer decides that scanning atkliquh first is not cost effective and depending on other circumstances decides to scan alujobsh first which does not have any simple filter that can use index so index is not used. Try following on stores5 database using online: This is similar case as yours. Second query has redundant filter so best path is not chosen. Optimizer can check for "a <= b and a >= b" is equivalent to "a == b" But is it really required ? QUERY: ------ select * from customer c, orders o where c.customer_num = o.customer_num and o.order_num > 1020 and c.customer_num = 115 Estimated Cost: 3 Estimated # of Rows Returned: 1 1) darshan.c: INDEX PATH (1) Index Keys: customer_num Lower Index Filter: darshan.c.customer_num = 115 2) darshan.o: INDEX PATH Filters: darshan.o.order_num > 1020 (1) Index Keys: customer_num Lower Index Filter: darshan.o.customer_num = darshan.c.customer_num QUERY: ------ select * from customer c, orders o where c.customer_num = o.customer_num and o.order_num > 1020 and c.customer_num = 115 Estimated Cost: 3 Estimated # of Rows Returned: 1 1) darshan.c: INDEX PATH (1) Index Keys: customer_num Lower Index Filter: darshan.c.customer_num = 115 2) darshan.o: INDEX PATH Filters: darshan.o.order_num > 1020 (1) Index Keys: customer_num Lower Index Filter: darshan.o.customer_num = darshan.c.customer_num QUERY: ------ select * from customer c, orders o where c.customer_num = o.customer_num and o.order_num > 1020 and c.customer_num <= 115 and c.customer_num >= 115 Estimated Cost: 4 Estimated # of Rows Returned: 1 1) darshan.o: INDEX PATH (1) Index Keys: order_num Lower Index Filter: darshan.o.order_num > 1020 2) darshan.c: INDEX PATH Filters: (darshan.c.customer_num <= 115 AND darshan.c.customer_num >= 115 ) (1) Index Keys: customer_num Lower Index Filter: darshan.c.customer_num = darshan.o.customer_num