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 on 

Published 1st January 2022