Query hints to override optimizer
Can we give query hints in Sybase like we do in Oracle? How?
Optimizer hints in Sybase like Oracle are overrides to the optimizer. They should be used with much caution. The ASE optimizer does a marvelous job at optimization when data modeling is done correctly, the necessary indexes exist, statistics are up to date and code is written with thought.
ASE allows you to specify optimizer overrides as follows:
[ SET FORCEPLAN ON ] SELECT columns FROM table ( [ INDEX indid or name | PREFETCH size | MRU | LRU ] ) WHERE columns = something UPDATE table SET columns = something FROM table ( [ INDEX indid or name | PREFETCH size | MRU | LRU ] ) WHERE columns = something DELETE table FROM table ( [ INDEX indid or name | PREFETCH size | MRU | LRU ] ) WHERE columns = something [ SET FORCEPLAN OFF ]
Specifying indexes
You can force the use of a particular index using the keyword INDEX and specifying either the indid or the index name. If you must force an index to be used you should always use the index name and not the indid. This is because the indid is assigned to many indexes in creation order. Routine database maintenance may cause the indid to change.
You can also specify a table scan by using ( INDEX 0 ). This indicates that ASE should perform a full table scan!
To force an index:
SELECT columns FROM table ( INDEX index_name ) WHERE columns = something UPDATE table SET columns = something FROM table ( INDEX index_name ) WHERE columns = something DELETE table FROM table ( INDEX index_name ) WHERE columns = something
To force a Full Table Scan:
SELECT columns FROM table ( INDEX 0 ) WHERE columns = something UPDATE table SET columns = something FROM table ( INDEX 0 ) WHERE columns = something DELETE table FROM table ( INDEX 0 ) WHERE columns = something
As we stated earlier using hints can cause application issues for both application maintenance and optimal optimization strategies. However, if you must force an index or table scan, do not do so within your application code. Create a view with the index or table scan forced and use the view as you would the table. This makes maintenance much more easier, and it exposes which objects have indexes or table scans forced.
Example:
CREATE VIEW vw_[index_name | scan]_test_table AS SELECT columns FROM table ( INDEX index_name | 0 ) go
The statements needing access to this table, having a forced a method, can now use the view.
Example:
SELECT columns FROM vw_[index_name | scan]_table WHERE columns = something UPDATE table SET columns = something FROM vw_[index_name | scan]_table WHERE columns = something DELETE table FROM vw_[index_name | scan]_table WHERE columns = something
Join orders
To force join order use SET FORCEPLAN [ ON | OFF ].
Warning!!! Forcing join orders can increase application maintenance. In addition, system evolution could create a more efficient path.
When you SET FORCEPLAN ON all queries which follow the setting of the option will use the join orders in the order they are specified in a query's FROM clause.
Example:
SET FORCEPLAN ON SELECT t1.columns FROM table1 t1, table2 t2 WHERE t1.column01 = t2.column01 SET FORCEPLAN OFF go
In the previous example, we have forced ASE to start its scan on test_table1 as the Outer Table. Depending on the statistics this could be a very, very bad thing. Only a highly skilled DBA should make the call about using such logic.