Oracle Hints in SQL and PLSQL

Oracle hints we use in the SQL 's to improve performance. But some time it will reduce performance too so try test possibilities and use them properly.




Some of the more useful hints are:



The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).

ORDERED - usually with USE_NL to get Oracle to not hash join




INDEX(t index_name) - where Oracle chooses the wrong index over the correct one




NO_INDEX - prevent an index from being used




INDEX_COMBINE - merging bitmap indexes (use when Oracle does not merge bitmap indexes)




FIRST_ROWS(n) - when you only want the first few rows




PARALLEL - to force parallel query on certain specific queries




GATHER_PLAN_STATISTICS - used as a handy sql trace




DYNAMIC_SAMPLING - used as alternative to statistics for large-scale warehouse queries




OPT_PARAM - used to control optimizer behavior at query level (added in 10.2.0.3)




QB_NAME - specify query block name when tuning complex queries. It helps in finding a particular query for troubleshooting (10 and up)




CARDINALITY - give the optimizer better information

[edit]Some examples




Example


SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b) USE_NL (glcc glf) USE_MERGE (gp gsb) */ b.application_id , b.set_of_books_id , b.personnel_id, p.vendor_id Personnel, p.segment1 PersonnelNumber, p.vendor_name Name FROM jl_br_journals j, jl_br_balances b, gl_code_combinations glcc, fnd_flex_values_vl glf, gl_periods gp, gl_sets_of_books gsb, po_vendors p WHERE ...


Note that the hints could have been also been in this format: SELECT --+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b) USE_NL (glcc glf) USE_MERGE (gp gsb)
SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id FROM employees WHERE employee_id = 7566;







suggesting that a FULL TABLE SCAN method be used:

SELECT /*+ FULL(x) */ FROM tab1 x WHERE col1 = 10;




Suggest that Oracle uses a specific index:

SELECT /*+ INDEX(x emp_idx1) */ ... FROM scott.emp x...




Suggest that Oracle DOES NOT USE a specific index:

SELECT /*+ NO_INDEX(x emp_idx1) */ ... FROM scott.emp x...