• Oracle7 and later provides new approach for optimization: cost-based optimization (CBO). CBO evaluates the cost to, or impact on, your system of the execution path for each specific query and then select the lowest-cost path. The CBO was designed to save you the trouble of fiddling with your queries. Occasionally, it is not giving you the results you want and you have exhausted all other possible problem areas, you can specify hints to direct the CBO as it evaluates a query and creates an execution plan. If you have used hints before, you know that a hint starts with /*+ and ends with */. A hint applies only to the statement in which it resides; nested statements consider as separate statement and require their own hints. Furthermore, a hint currently has a 255-character limit. Since the use of hint is database-specific, we should make use of Database Functions to accomplish it.

• The most effective hints for use with the CBO are:

◦ FULL - tells the optimizer to perform a full table scan on the table specified in the hint

◦ SELECT /*+FULL(table_​name)*/ COLUMN1,COLUMN2.....

◦ INDEX - tells the optimizer to use one or more indexes for executing a given query.

◦ Note: If you just want to ensure the optimizer doesn't perform a table scan, use INDEX hint without specifying an index name and the optimizer will use the most restrictive index. A specific index should not be used as the actual index name may differ on the client's site.

◦ SELECT /*+INDEX(table_​name index_​name1 indexname2...) */

◦ COLUMN1, COLUM2

◦ ORDERED - tells the optimizer to access tables in particular order, based on the order in the query's FROM clause (often referred to as the driving order for a query)

◦ SELECT /*+ORDERED*/ COLUMN1, COLUMN2

◦ FROM TABLE1, TABLE2

◦ ALL_​ROWS - tells the optimizer to choose the fastest path for retrieving all the rows of a query, at the cost of retrieving a single row more slowly.

◦ SELECT /*+ALL_​ROWS*/ COLUMN1, COLUMN2...

◦ FIRST_​ROWS - tells the optimizer to choose the approach that returns the first row as quickly as possible.

◦ Note: the optimizer will ignore the first rows hint in DELETE and UPDATE statements and in SELECT statements that contain any of the following: set operators, group by clauses, for update clause, group functions, and the distinct operators.

◦ SELECT /*+FIRST_​ROWS*/ COLUMN1, COLUMN2...

◦ USE_​NL - tells the optimizer to use nested loops by using the tables listed in the hint as the inner (non-driving) table of the nested loop. Note: if you use an alias for a table in the statement, the alias name, not the table name, must appear in the hint, or the hint will be ignored.

◦ SELECT /*+USE_​NL(tableA table B) */ COLUMN1, COLUMN2...

• Hints are an Oracle specific feature and are not supported by the DB2 SQL syntax.

• If you need to add a hint to your SQL make sure that a different SQL version is used for DB2 where the hint is not used.

• Base product developers should not duplicate their SQL in this case but rather use the special database functions file "dbregex.txt". In this file you should add a new hint-code that in Oracle translates into the specific hint whereas in DB2 it translates into an empty string.