Hints in Oracle What are Hints? Hints give specific

1
Hints in Oracle
2
What are Hints?

• Hints give specific information that we know
  about our data and application.
• A way to override the default query optimization
  in the DBMS
• Influence the execution plan of query

3
Why Use Hints?

• Oracle optimizer may not always choose the best
  execution plan
• Using hints may improve the performance by
  changing the execution plan oracle takes.

4
Using Hints

• Hints can be used in the Select, Delete, and
  Update clauses.
• In each statement, the hint goes directly after
  the Select, Delete, or Update keyword. A few
  hints use Insert.
• Hints are placed in the / / tag, where the
  hint goes after the sign
• Ex SELECT / ALL_ROWS / From

5
Types of Hints

• Approach hints
• Access hints
• Join hints
• Misc. hints

6
Approach Hints

• ALL_ROWS Minimizes total resource consumption.
  Results will be returned only after all
  processing has been completed
• FIRST_ROWS(n) Minimized response time, or
  minimal resource usage to return the first n rows.

7
Access Hints

• FULL(table) Chooses a full table scan for the
  table, even if there is an index available.
• INDEX(table index index...) Chooses an Index
  scan for the table.

8
Index Hint Example

• CIS Department has far more males than females
• SELECT / FULL(s) / id, name
• FROM Student s
• WHERE sex m
• SELECT / Index(s sex_index) / id, name
• FROM Student s
• WHERE sex f

9
Join hints

• ORDERED tables are joined in the order in which
  they appear in the FROM clause.
• LEADING(table) specified table is the first
  table used in the join order.
• USE_HASH(table table ) Tables are joined
  using a hash join. Smaller table is used to make
  a hash table on join key. The larger table is
  scanned using hash table to find joined rows.

10
Join Hints cont.

• USE_NL(table table) Joins tables using nested
  loops join, using specified table as inner join.
  For every row in outer table, oracle accesses
  every row in inner table.
• USE_MERGE(table table) Joins tables using a
  sort-merge join. Sorted list are made and then
  merged together. Best if tables are already
  sorted.

11
Misc. Hints

• APPEND Data is appended to the end of table,
  rather then using existing space.
• CACHE(table) Blocks received are placed at the
  most recently used end of the LRU list in the
  buffer cache when a full table scan is preformed.
• NOCACHE(table) Blocks received are placed at the
  least recently used end of the LRU list in the
  buffer cache.

12
Sources

• Oracle documentation at http//oracle.cis.ksu.edu
  /oradocs/index.htm