12. Using the Query Optimizer : Summary for Evaluating QEPs
 
Share this page                  
Summary for Evaluating QEPs
The main points to check when evaluating a QEP are as follows:
Cart-prods can be caused by errors due to disjoint queries or queries that involve certain types of OR operations. Also, joins involving calculations, data type conversions, and non-equijoins can generate cart-prods. Alternative ways of posing the query is often advised under these circumstances.
The NU on the storage structure part in the orig node description is not a good sign if you believe the storage structure must have been used to restrict the number of rows being read.
Verify that the appropriate secondary indexes are being used. Running the optimization process to generate statistics on the indexed columns allows the query optimizer to better differentiate between the selectivity powers of the different indexes for a particular situation.
If there is little data in a table (for example, less than five pages) the query optimizer can consider a scan of the table rather than use any primary or secondary indexes, because little is to be gained from using them.
Check that row estimates are accurate on the QEP nodes. If not, run the optimization process to generate statistics on the columns in question.