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%.


SQL Performance Tuning
SQL Performance Tuning
ISBN: 0201791692
EAN: 2147483647
Year: 2005
Pages: 125

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net