Manage Learn to apply best practices and optimize your operations.

Managing the WHERE clause for better query performance

This tip discusses the effects of bad WHERE clauses.

In this tip, from the book "Practical SQL: The Sequel" (Addison-Wesley, 2001), Judith Bowman examines the effects of bad WHERE clauses:

WHERE clauses are a possible place to start to improve query performance. Often the place to look is your indexes. Of the possible problems with WHERE clauses, only two are under your control.

  1. If the optimizer does not have accurate information on data distribution, it may pick the wrong index or no index at all.
  2. If you include certain elements in the WHERE clause, you may make the indexes unavailable.

Data distribution statistics

First, check your system documentation to see if the DBA needs periodically to run a command to keep the optimizer current (UPDATE STATISTICS in Transact-SQL and Informix, ANALYZE in Oracle.) Microsoft SQL Server also supports a command that tells you when the command was last run (STAT_DATE). See below for a list of commands associated with index statistics. The SQL Anywhere ESTIMATE command is included in the table, but it works differently than the UPDATE STATISTICS or ANALYZE commands, allowing the user to give the optimizer hints on data distribution.

  • SQL Anywhere: ESTIMATE
  • Oracle: ANALYZE

Disabling an index with a bad WHERE

Don't disable a valid index by the way you construct your WHERE clause. The easiest elements to optimize are comparison operators (=, >, <, and variants) or operators that can be translated to comparison operators (BETWEEN and some LIKE clauses).

Anything else may make your indexes unavailable. Here are some suspicious areas to investigate. Since architecture and optimizers vary so much, you'll have to check your system documentation to find out just how these areas affect (or don't affect!) your queries.

  • Comparing columns in the same table
  • Choosing columns with low selectivity indexes
  • Doing math on a column before comparing it to a constant
  • Applying a function to column data before comparing it to a constant
  • Finding ranges with BETWEEN
  • Matching with LIKE
  • Comparing to NULL
  • Negating with NOT
  • Converting values
  • Using OR
  • Finding sets of values with IN
  • Using multicolumn indexes

To learn more about SQL, buy the book Practical SQL: The Sequel

For More Information

Dig Deeper on Oracle and SQL