Labeling Your Query Blocks with Hints

SQL can be hard to read. Execution plans can be EVEN HARDER to read. It doesn’t get any easier when you are joining a table to itself. Or maybe you are having a hard time finding the portion of a plan to correlates to a subquery.

We do have a solution!

QB_NAME Hint (Docs)

/*+ QB_NAME ( qb_name ) */

So why am I talking about this today? Well one of our SQL Advocates, Connor McDonald, recorded a nice video on this technique. To make it easy for you to follow along, I’ve included it and his Twitter profile below. Be a dear and watch the video and add Connor to your ‘Follow’ list.

Tweets by @connor_mc_d

The Video

My take, in a nutshell…

…if you don’t know if your code is self-documenting or not, assume it isn’t and comment it. Or to put it more simply, try to always comment it. Even if the SQL itself is self-documenting, add comments as to WHY you wrote the SQL in the first place. What’s the business requirement, yada yada yada.

Anyways, with all of that taken care of I wanted to show you very quickly how to see the named query blocks in your SQL Developer execution plans.

I changed Connor’s code – to go against HR.EMPLOYEES and HR.DEPARTMENTS vs SCOTT.EMP and SCOTT.DEPT. I THINK I translated it OK, but hard to tell since he didn’t document it 😉 Sorry, couldn’t resist that Connor.

But anyways, the query text itself is moot here, this is about the hints.

SELECT employees.*
FROM employees, 
 ( SELECT /*+ QB_NAME(Connor1) */
     trunc(hire_date, 'YYYY'), MAX(employee_id) employee_id
   FROM employees
   WHERE employee_id > 0
   GROUP BY trunc(hire_date, 'YYYY')) x,
 ( SELECT /*+ QB_NAME(Connor2) */
     department_id, avg(salary)
   FROM employees
   GROUP BY department_id) y
WHERE x.employee_id = employees.employee_id
AND y.department_id = employees.department_id;

Now I go to run an Explain or an AutoTrace (I like AutoTrace for the stats and increased accuracy over Explain):

If you don’t see this info in your plan, check your preferences: