In Oracle land each time the optimizer sees the work SELECT
that is a new query block within the query.
Each query block gets a name even when you don’t name them. But they are typical system type generated names;
they aren’t very informative by themselves. They get names like SEL$1 and SEL$2
and so on. But we can them is such a way that they do
make sense to us humans.
It’s really a good idea to do this. These names for the query blocks are also
used in the 10053 trace files. And again
it can just be easier to read things if you name them in more human
understandable ways. Here’s a quick couple
of examples to show this hint in action.
select /*+ QB_NAME(mainblock) */ a.username,
(select /*+ QB_NAME(cntblock) */
count(*)
from
scalar_allobjects b where b.owner = a.username) cnt
from
scalar_allusers a;
This is a simple enough query to demonstrate the point. Note there are two query blocks. The main query and then a sub query to get a
count. So I named them MAINBLOCK
and CNTBLOCK. Pretty tricky eh?
Now when I run the query and use DBMS_XPLAN.DISPLAY_CURSOR
to see the plan I can use the ALIAS format option to see where
in the plan the query blocks are getting evaluated.
SQL> SELECT
PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR
2
('g7pt8bj8jjy8r',0,'ALLSTATS LAST ALIAS'))
3 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
SQL_ID g7pt8bj8jjy8r, child number 0
-------------------------------------
select /*+
QB_NAME(mainblock) */ a.username,
(select /*+
QB_NAME(cntblock)
*/ count(*) from scalar_allobjects b where
b.owner =
a.username) cnt from scalar_allusers a
Plan hash value:
2801865168
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time
| Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 43 |00:00:00.01 | 6 |
| 1 |
SORT AGGREGATE | | 43 |
1 | 43 |00:00:00.26 | 65059 |
|* 2 |
TABLE ACCESS FULL| SCALAR_ALLOBJECTS | 43 |
2805 | 52617 |00:00:00.25 | 65059 |
| 3 |
TABLE ACCESS FULL | SCALAR_ALLUSERS
| 1 | 43 |
43 |00:00:00.01 | 6 |
--------------------------------------------------------------------------------------------------
Query Block Name
/ Object Alias (identified by operation id):
-------------------------------------------------------------
1 - CNTBLOCK
2 - CNTBLOCK
/ B@CNTBLOCK
3 - MAINBLOCK / A@MAINBLOCK
Predicate
Information (identified by operation id):
---------------------------------------------------
2 -
filter("B"."OWNER"=:B1)
29 rows selected.
Of course nothing is perfect. If you query block gets merged into another
one, then some names get lost. Rats!
In this query there are several query blocks, but because the
query block ORDERED_STDS gets merged into MAIN we lose the MAIN
name and it gets a new name SEL$DDAF0A40. Even with this, it’s
still easier to see which lines of the plan go to which subqueries. Also this little example uses the good old
explain plan to show they are used with explain plan as well.
SQL> explain plan set statement_id =
'EX2_2_1' for
2 select /*+ QB_NAME(MAIN)
*/ student_id, last_name
3 from (select /*+ QB_NAME
(ORDERED_STDS) */ * from student order
by student_id) s
4 where (select /*+
QB_NAME(ENROLL_CNT) */ count(*)
5 from enrollment e
6 where s.student_id
= e.student_id) >
7 (select /*+
QB_NAME(ENROLL_AVG) */ avg(count(*))
8 from
enrollment
9 group by
student_id);
Explained.
SQL>
SQL> SELECT PLAN_TABLE_OUTPUT FROM
TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','EX2_2_1','TYPICAL ALIAS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
Plan hash value: 3191484131
---------------------------------------------------------------------------------------
| Id |
Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 |
SELECT STATEMENT | |
268 | 3216 | 3
(0)| 00:00:01 |
| 1
| TABLE ACCESS BY INDEX ROWID| STUDENT
| 268 |
3216 | 2 (0)| 00:00:01 |
|* 2
| INDEX FULL SCAN | STU_PK | 13
| | 1
(0)| 00:00:01 |
| 3
| SORT AGGREGATE | |
1 | 4 | | |
|* 4
| INDEX RANGE SCAN | ENR_PK | 1
| 4 | 1
(0)| 00:00:01 |
| 5
| SORT AGGREGATE | |
1 | 4 | 1
(0)| 00:00:01 |
| 6
| SORT GROUP BY | |
1 | 4 | 1
(0)| 00:00:01 |
| 7
| INDEX FULL SCAN | ENR_PK | 226
| 904 | 1
(0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified
by operation id):
-------------------------------------------------------------
1 -
SEL$DDAF0A40 / STUDENT@ORDERED_STDS
2 -
SEL$DDAF0A40 / STUDENT@ORDERED_STDS
3 -
ENROLL_CNT
4 -
ENROLL_CNT / E@ENROLL_CNT
5 -
ENROLL_AVG
7 -
ENROLL_AVG / ENROLLMENT@ENROLL_AVG
Predicate Information (identified by
operation id):
---------------------------------------------------
2 -
filter( (SELECT /*+ QB_NAME ("ENROLL_CNT") */ COUNT(*) FROM
"ENROLLMENT" "E" WHERE
"E"."STUDENT_ID"=:B1)> (SELECT /*+ QB_NAME
("ENROLL_AVG") */ AVG(COUNT(*)) FROM "ENROLLMENT"
"ENROLLMENT" GROUP BY
"STUDENT_ID"))
4 -
access("E"."STUDENT_ID"=:B1)
SQL>
