EXPLAIN

The non-standard EXPLAIN statement (see Table 17-1) is the vital way to find out what the optimizer has done. We haven't mentioned it up to now because this book's primary goal has been to show what you can do before the fact. But EXPLAIN is the way to measure whether your estimates correspond to DBMS reality. In many shops , it's customary to get an EXPLAIN for every SQL statement before submitting it for execution. That is quite reasonable. What's perhaps less reasonable is the custom of trying out every transformation one can think of and submitting them all for explanation. That is mere floundering. Understanding principlesin other words, estimating what's best before the factis more reliable and less time consuming. So don't flounderread this book!

Here is an example of a typical EXPLAIN output, from Informix:

 SET EXPLAIN ON QUERY: SELECT column1, column2 FROM Table1; Estimated cost: 3 Estimated # of rows returned: 50 1) Owner1.Table1 : SEQUENTIAL SCAN 

With most DBMSs, the EXPLAIN result goes to a table or file so you can select the information you need. In many cases, the EXPLAIN statement's output is much harder to follow than the short example we show here, which is why graphic tools like IBM's Visual EXPLAIN are useful.