Using Query Blocks in Hints

Introduction

Some hints require you to specify the query block that the hint applies too. This example, taken from the Oracle manual explains how to find the query block and apply it to the hint.

Example

1. A view is defined as follows

CREATE OR REPLACE VIEW v_emp_job_history AS
  SELECT e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
  FROM   employees e1, (SELECT * FROM employees e3) e2, job_history j
  WHERE  e1.employee_id = e2.manager_id
  AND    e1.employee_id = j.employee_id
  AND    e1.hire_date = j.start_date
  AND    e1.salary = ( SELECT max(e2.salary) 
                       FROM employees e2 
                       WHERE e2.department_id = e1.department_id ) 
  GROUP BY e1.first_name, e1.last_name, j.job_id
  ORDER BY total_sal;

2. Run an explain plan on a select from the view

• SQL> EXPLAIN PLAN FOR SELECT * FROM v_emp_job_history;

3. View the plan_table

• SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));

4. Find the full table scan operation in the plan output and use the line ID to discover the query block name

------------------------------------------------------------------------------
|Id| Operation                        |Name        |Rows|Bytes|Cost |Time
------------------------------------------------------------------------------
|0 | SELECT STATEMENT                 |            |1   |46   |9(34)|00:00:01|
.
.
.
|11|          TABLE ACCESS FULL       | EMPLOYEES  |107 |749  |3(0) |00:00:01|.  <----Operation ID for the Full Table Scan is 11.
.
.
.
-------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$2980E977 / V_EMP_JOB_HISTORY@SEL$1
   2 - SEL$2980E977
   8 - SEL$8F9407EC / VW_SQ_1@SEL$32F848CB
   9 - SEL$8F9407EC
  11 - SEL$8F9407EC / E2@SEL$4. <--- Use the operation ID 11, to find the query block name, which is SEL$4

5. Use the NO_UNNEST hint with the Query Block SEL$4

• SQL> SELECT /*+ NO_UNNEST( @SEL$4 ) */ * FROM v_emp_job_history;

---

Published 1st January 2022