SQL Performance Tuning

Types of Oracle Hints | What are Oracle Hints Examples?

Amit S March 4, 2020

In my previous articles I have given idea of performance tuning. In this article I would like to give you idea about Oracle Hints and Oracle Hints Examples in detail. I would like to start with Optimizer hints definition. Optimizer hints are nothing but the techniques which are used to optimize the plans of execution of query. Oracle Hints mainly provides the plan for to control optimizer or to give instruction to optimizer to choose certain execution plan.

Oracle Hints Types with Examples :

In this section I would like to give you Oracle Hints types and Oracle Hints Examples. I would like to give the different examples which are useful for SQL query performance tuning in depth.

1. Single Table Hints : Single Table Hints are hints which can be Specified on one table or view.

Examples of Single table hints :

1.1. Index Hints : Index hints are the hints which used to do the forceful index scanning for specified table.

Real Example :

Select Emp_No,Employee_Name,Salary from Employee where Salary <50000;

We can create index on salary.

Create Index IDX_Employee_Salary on Employee(Salary);

This will create the index named IDX_Employee_Salary.

We need to use that index forcefully.

Select Emp_No,

Employee_Name,

/*+ INDEX (Employee IDX_Employee_Salary)*/ Salary

from Employee where Salary < 50000;

Use_NL Hint :

The USE_NL hint gives instruction to the optimizer to join each specified table to another row source with a nested loops join, using the specified table as the inner table.

1.2.Multitable Hints :

Multitable hints are like single table hints but these are basically used in between two or more tables.

Example :

Leading Hints :

Leading Hints are hints which we are used in two or more table. The Leading hint instructs the optimizer to use the specified set of tables as prefix in the execution plan.

Example :

If you want to fetch the data for Students who are joined with start_date.

Select * from Student S,Standard St,Student_History h

where s.Roll_No =st.Roll_no

and S.Joining_date=h.Start_date;

So User needs to check the explain plan of the query. Lets say the cost of the above query is 100.

Select /*+ LEADING(s h) */ * from Student S,Standard St,Student_History h

where s.Roll_No =st.Roll_no

and S.Joining_date=h.Start_date;

The above lending link will help you for optimized execution of the query. In simple words when user uses the hints between one or more table those hints are Multitable hints.

1.3. Query Block Hints :

The Query block Hints are used in single query block.

Example :

Star Transformation Hints :

The best example for the Query block hint is Star transformation hint. Star transformation hint is used to fetch the best plan and it makes the optimizer to choose best plan.

Even if the hint is given there is no guarantee that transformation will take place. At that time optimizer will try to generate the subqueries. If the subqueries are not being generated the best plan is without using subquery and query will execute with best plan.

Example :

SELECT /*+ STAR_TRANSFORMATION */ * FROM sales_order s, times_order t, products-order p, channels_order c WHERE s.time_id = t.time_id AND s.prod_id = p.product_id AND s.channel_id = c.channel_id AND p.product_status = ‘Running’;

1.4. Statement Hints :

The statement hints are hints which are applied to whole SQL statement.

Example :

All_Rows Hint :

All_Rows hint used to choose the best query optimization approach where this hint will execute the query with best throughput and minimum total resource consumption.

Example :

SELECT /*+ ALL_ROWS */ employees_id, first_name, Employee_salary, FROM employees WHERE employees_id = 100;

These are multiple types of Oracle hints which are useful in SQL Performance tuning.

Some Most Important Oracle Hints useful for SQL Tuning :

In this section I would like to give you brief about most important oracle hints with examples.

1.Faster Insertion Hint / Append Hint :

If user wants to insert data fast the Append Hint is the most useful Hint.It instructs the optimizer to use direct path insert.With using this index data will append directly to the table.

Real Life Example :

Create table Employee as Select * from Employee1;

Insert /*+APPEND*/ Into Employee e

Select * from Employee1;

2.Faster Retrieval of data :

The Parallel indexes are mainly used if we require to retrieve data as fast as possible. User needs to specify the desired no of parallel servers.

Real Life Example :

Select /*+ parallel(2)*/ First_name,Last_name frm Employees;

These are the different types of Oracle Hints with examples. I would like to give the brief about different types of hints in next articles with examples. I hope you like this article. If you like this article or if you have some issues kindly comment in comments section.

42 Replies to “Types of Oracle Hints | What are Oracle Hints Examples?”

  2. Most Useful Amith , Thanks!

    I need to learn tuning in oracle fusion, could you please help me

      1. Hello Amit ,
        I have read most of your articles today. Thank You for such a great bunch of knowledge.
        Can you please share the notes of SQL tuning and Oracle developer interview questions for experienced.

  9. Thanks a lot Amit for sharing this information.
    Can you share notes for performance tuning . It will help in my career growth.

  10. Hi Amit,
    Could you please share me the interview questions and tuning notes as well.

  14. Hello Sir, can you please share me tuning notes and interview questions. Really helped me alot.

Comments are closed.