Hints

Most DBMSs provide some non-standard way to override the optimizer if you judge that its estimates or formulas are wrong. Commonly the mechanism is a hint, which often looks like a comment or parenthetical clause in the SQL statement but in fact is a direction to the optimizer. Here's an example from Oracle:

 SELECT /*+ INDEX(Table1 Index1) */        column1, column2    FROM Table1    WHERE column1 >55 

And here's one from Sybase:

 SELECT column1, column2   FROM Table1 (INDEX Index1 PREFETCH 16)   WHERE column1 > 55 

Hints are important enough to warrant mention, but there is no such thing as a portable hint; every DBMS (except IBM) has its own optimizer-specific hint set. We'll therefore limit ourselves to listing the most common hints, without getting into syntax.

• The predicate that follows is probably true .

• Stop trying to optimize if time-to-estimate is greater than time-to-execute.

• Use rule-based optimizer instead of cost-based optimizer.

• Prefer index X on table Y .

DBA note: Administrators can influence some DBMS's plans more generally with a manual override of the statistics, for example, by declaring that the selectivity of an index is 80% rather than 40%.