Oracle SQL Performance Tuning Tips-Influencing the Optimizer using HINTS AT a to z help

Oracle SQL Performance Tuning Tips-Influencing the Optimizer using HINTS
 

•Hints are special instructions to Optimizer. You can change the Optimization goal for an individual statement by using Hint. Some commonly used Hints are: CHOOSE, RULE, FULL(table_name), INDEX(table_name index_name), USE_NL, USE_HASH(table_name), PARALLEL(table_name parallelism) etc. 

 

   SELECT    /*+RULE*/   NAME,

   ACCT_ALLOCATION_PERCENTAGE

  FROM ACCOUNTS WHERE ACCOUNT_ID = 1200

•The above SQL statement will be processed using the RULE based optimizer.

 

  SELECT /*+ INDEX(A, ACCT_ID_IND) */  NAME,

   ACCT_ALLOCATION_PERCENTAGE

  FROM ACCOUNTS A

  WHERE ACCOUNT_ID = :ACCT_ID 

  AND CLIENT_ID= :CLIENT_ID

•In the above SQL statement, an Index Hint has been used to force the use of a particular index.

Note : Provide your comments by clicking below options! Thanks ! :)

Tweet