Re: 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 This is a calculated cost of the query plan being described. It has no meaning to us humans, but the engine evaluated several possible query plans and assigned a cost to each one, then chose the one with the lowest cost. Of course, the engine can only assign an accurate cost if the information in the catalog tables accurately reflects the contents of the database, so you have to run UPDATE STATISTICS in the correct manner. Check your release notes for details. > Estimated # of Rows Returned: 5 How many rows the engine thinks will be returned by the query. This estimate is influenced by the data in the sysdistrib table, which is set when you run UPDATE STATISTICS with either the MEDIUM or HIGH keywords. > Temporary Files Required For: Order By Since you specified that you want the data in a specific order, the engine must be able to either use an index on dist_name or sort the result set to return the data in the order you specify. In this case, it must sort the result set. Temporary files may also be required for queries with GROUP BY or HAVING clauses. > 1) db.district: INDEX PATH > > (1) Index Keys: co_code dist_code > Lower Index Filter: db.district.co_code = 38 In this query, the engine is using the index on co_code to quickly retrieve the rows you want. It does this because it believes the index is highly selective, in other words, given a single value, you find only one or a few matching rows. This index seems to be non-unique, since the estimated number of rows is > 1. Note that this is just an estimate, based on the information in sysdistrib. > 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 In this query, no temporary file is necessary because there was an index on the column you listed in your ORDER BY. The engine will read that index sequentially, retrieving the data rows in that order, then return them in the result set. The estimated cost is higher here because of the larger amount of I/O. The estimated number of rows probably matches the value of nrows in systables, which was updated the last time you ran UPDATE STATISTICS, regardless of what level. Mark Collins mcollins@us.dhl.com Dilbert is a documentary.