Database Reference
In-Depth Information
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | VIEW | |
| 3 | HASH GROUP BY | |
| 4 | TABLE ACCESS FULL| EMP |
| 5 | TABLE ACCESS FULL | DEPT |
-------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2
2 - SEL$1 / EMPS@SEL$2
3 - SEL$1
4 - SEL$1 / EMP@SEL$1
5 - SEL$2 / DEPT@SEL$2
The system-generated query block names are composed of a prefix followed by an alphanumeric string. The
prefix is based on the operation contained in the query block. Table
11-2
summarizes them. The alphanumeric string
is a numeration of the query blocks, based on their position (left to right) during the parse of the SQL statement. In the
previous example, the main query block is named
SEL$2
, and the subquery query block is named
SEL$1
.
Table 11-2.
Prefixes Used in Query Block Names
Prefix
Used For
CRI$
CREATE INDEX
statements
DEL$
DELETE
statements
INS$
INSERT
statements
MISC$
Miscellaneous SQL statements like
LOCK TABLE
MRC$
MERGE
statements
SEL$
SELECT
statements
SET$
Set operators like
UNION
and
MINUS
UPD$
UPDATE
statements
As shown here, the utilization of system-generated query block names isn't different from the utilization of
user-defined query block names:
WITH
emps AS (SELECT deptno, count(*) AS cnt
FROM emp
GROUP BY deptno)
SELECT /*+
full(@sel$2 dept) full(@sel$1 emp)
*/ dept.dname, emps.cnt
FROM dept, emps
