What's In A Name? Oracle SQL Developer Can Help You Find Out

What’s in a query block name, that is! I was trying to figure out the intricacies of how the Oracle optimizer works with set operators and how to effectively reference query blocks with the QB_NAME() directive in each subquery. I also wanted to put all the actual hints in one place — at the top in the main SELECT statement.

A simple version of the query I was working on is below; the original has two very complex SELECT statements with a UNION ALL between them.

select count(*) rc1 from dti1
UNION ALL
select * from (select count(*) rc2 from dti2);

The goal was to ensure a full table scan on both RC1 and RC2, since I didn’t want an index scan on those tables but the optimizer did:

What I wanted to do instead was to force full table scans but keep all the hints in the main SELECT statement if at all possible. And it is possible, if you know where to look. The first step is to add a query block name to all of the SELECT subqueries by using the QB_NAME() tag in each hinted comment:

select 
      /*+ qb_name(rc1_main) */ 
   count(*) rc1
from dti1
UNION ALL
select 
      /*+ qb_name(rc2_main) */ *
from (select 
         /*+ qb_name(rc2_submain) */
      count(*) rc2 from dti2);

Now that I have all the query blocks identified, I want to put the FULL() hints in the main SELECT statement — and now my SELECT statement looks like this:

select 
  /*+ qb_name(rc1_main) 
          full(@rc1_main dti1@rc1_main) 
          full(@rc2_submain dti2@rc2_submain) */ 
   count(*) rc1
from dti1
UNION ALL
select /*+ qb_name(rc2_main) */ *
from (select /*+ qb_name(rc2_submain) */ count(*) rc2 from dti2);

How did I figure out what references to put in the FULL() hints? The query block names and the references are in the PLAN_TABLE from an EXPLAIN PLAN somewhere, but using SQL Developer made it easy, and even easier to verify. I’ll now get a full table scan for both parts of the query in question:

The moral of the story is — use all the (free) tools at your disposal like Oracle SQL Developer. And the optimizer provides a lot more information than you think, but you just have to know where to find it.