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.