Sqlexplain output

How to read sqlexplain out e.g. from following output - QUERY: ------ SELECT dist_name FROM district WHERE co_code = 38 ORDER BY dist_name Estimated Cost: 4 Estimated # of Rows Returned: 5 Temporary Files Required For: Order By 1) db.district: INDEX PATH (1) Index Keys: co_code dist_code Lower Index Filter: db.district.co_code = 38 QUERY: ------ select * from ecbegbal order by fund_code Estimated Cost: 10822 Estimated # of Rows Returned: 132178 1) db.ecbegbal: INDEX PATH (1) Index Keys: fund_code TIA Mukund

Shevkar, Mukund wrote: > > How to read sqlexplain out e.g. from following output - > > QUERY: > ------ > SELECT dist_name FROM district WHERE co_code = 38 ORDER BY dist_name> The original query, of course > Estimated Cost: 4 This number is for comparison purposes only. This number has no direct relation to the time it takes for the query to execute. > Estimated # of Rows Returned: 5 . . . based on statistics > Temporary Files Required For: Order By Since there is no way to determine if the rows returned are in order, Informix will sort the returned rows using a temporary sort file. > > 1) db.district: INDEX PATH > > (1) Index Keys: co_code dist_code > Lower Index Filter: db.district.co_code = 38 > Informix is using an index to read district; the index that it chose is (co_code, dist_code). The index will be filtered by the lead column, co_code, with a definite value. > QUERY: > ------ > select * from ecbegbal > order by fund_code> Once again, the query > Estimated Cost: 10822 See above . . . > Estimated # of Rows Returned: 132178 See above again . . . > > 1) db.ecbegbal: INDEX PATH > > (1) Index Keys: fund_code Informix chose this index to select the data from the table ecbegbal since you're ordering by fund_code. Notice that there is no listing for a file used for a temporary sort. Hope this helps John Carlson Informix DBA WHSmith USA

"Shevkar, Mukund" wrote: > > How to read sqlexplain out e.g. from following output - > > QUERY: > ------ > SELECT dist_name FROM district WHERE co_code = 38 ORDER BY dist_name This is an echo of your query. > Estimated Cost: 4 This an indicative of the I/O cost of the query using the selected query path. > Estimated # of Rows Returned: 5 A guestimate, based on the statistics and data distributions collected by UPDATE STATISTICS commands if run. Otherwise mostly a guess. > Temporary Files Required For: Order By Indicates that the optimizer has decided to physically sort the result set to satisfy the ORDER BY clause you specified. If you have an index beginning with dist_name the optimizer has decided another index path or even a sequential scan is faster. See below for details. > 1) db.district: INDEX PATH > > (1) Index Keys: co_code dist_code OK the optimizer chose an indexed search of the table using an index you have created whose keys are as above. > Lower Index Filter: db.district.co_code = 38 The optimizer has recognized that it can reduce the number of rows returned by filtering based on this index and your filter criterion. > QUERY: > ------ > select * from ecbegbal > order by fund_code> > Estimated Cost: 10822 > Estimated # of Rows Returned: 132178 Notice no "Temporary Files Required For: Order By" line here because below you will notice that the index selected for the search begins with the ORDER BY column. This is unusual because there is no filter and the optimizer knows it has to read all the data pages anyway. It will usually do this if the data record is very small so that there are relatively few data pages involved or if the sort key is relatively large. This may indicate that you need to UPDATE STATISTICS as the sorting that Informix does is normally MUCH faster than indexed reads or significant numbers of rows so this seems like a poor decision, unless this is an OL5.xx engine which always favored indexed reads to sorts (you do not specify version). > 1) db.ecbegbal: INDEX PATH > > (1) Index Keys: fund_code No filter criteria in the WHERE clause, indeed no WHERE clause, so no "Lower Index Filter". Art S. Kagel