from$_subquery$_NNN

from$_subquery$_NNN
Filed under: Execution plans,Oracle — Jonathan Lewis @ 4:26 pm BST May 12,2020

这是对我写的，一篇关于阅读执行计划的长文中的，评论部分的一个问题的引用：

在执行计划的HQuery Block Name / Object Alias部分，你如何解释类似 from$_subquery$_001@SEL$1的内容。

简单的回答是，如果你在查询的FROM子句中使用了内联视图，并且你没有给内联视图指定一个别名。那么优化器必须为其生成一个别名–就是你看到的这个样子。

以下是一个演示脚本，该脚本创建了两张表，然后运行一个两个内联视图做连接的查询（使用ANSI风格的SQL写法），并对内联视图进行了各种命名：

rem
rem     Script:         from_subquery.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2020
rem
rem     Last tested 
rem             19.3.0.0
rem             12.2.0.1
rem
create table t1
as
select  * 
from    all_objects
where   rownum <= 100
; 
create table t2
as
select  *
from    all_objects
where   rownum <= 100
;
set serveroutput off
prompt  =========================
prompt  Neither inline view named
prompt  =========================
select 
        count(*)
from    (select /*+ no_merge */ * from t1)
join 
        (select /*+ no_merge */ * from t2)
using
        (object_id)
;
select * from table(dbms_xplan.display_cursor(null,null,'alias'));
prompt  ============================
prompt  Only first inline view named
prompt  ============================
select 
        count(*)
from    (select /*+ no_merge */ * from t1) v1
join 
        (select /*+ no_merge */ * from t2)
using
        (object_id)
;
select * from table(dbms_xplan.display_cursor(null,null,'alias'));
prompt  =============================
prompt  Only second inline view named
prompt  =============================
select 
        count(*)
from    (select /*+ no_merge */ * from t1)
join 
        (select /*+ no_merge */ * from t2) v2
using
        (object_id)
;
select * from table(dbms_xplan.display_cursor(null,null,'alias'));
prompt  =======================
prompt  Both inline views named
prompt  =======================
select 
        count(*)
from    (select /*+ no_merge */ * from t1) v1
join 
        (select /*+ no_merge */ * from t2) v2
using
        (object_id)
;
select * from table(dbms_xplan.display_cursor(null,null,'alias'));

在全部四个示例中，我为内联视图添加了 no_merge 的HINT；如果我不这样做，优化器只会将每个查询简化为连接两个表的单个查询块。（四个示例的输出）如下图所示，生成的执行计划（稍作修饰性编辑）报告了计划哈希值、计划、大纲和谓词：

=========================
Neither inline view named
=========================
Plan hash value: 1978226902
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE      |      |     1 |    26 |            |          |
|*  2 |   HASH JOIN          |      |   100 |  2600 |     4   (0)| 00:00:01 |
|   3 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |   100 |   500 |     2   (0)| 00:00:01 |
|   5 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T2   |   100 |   500 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$16C51A37
   3 - SEL$2        / from$_subquery$_001@SEL$1
   4 - SEL$2        / T1@SEL$2
   5 - SEL$3        / from$_subquery$_003@SEL$1
   6 - SEL$3        / T2@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("from$_subquery$_001"."OBJECT_ID"="from$_subquery$_003"."O
              BJECT_ID")
 
============================
Only first inline view named
============================
Plan hash value: 1978226902
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE      |      |     1 |    26 |            |          |
|*  2 |   HASH JOIN          |      |   100 |  2600 |     4   (0)| 00:00:01 |
|   3 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |   100 |   500 |     2   (0)| 00:00:01 |
|   5 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T2   |   100 |   500 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$16C51A37
   3 - SEL$2        / V1@SEL$1
   4 - SEL$2        / T1@SEL$2
   5 - SEL$3        / from$_subquery$_003@SEL$1
   6 - SEL$3        / T2@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("V1"."OBJECT_ID"="from$_subquery$_003"."OBJECT_ID")
 
 
=============================
Only second inline view named
=============================
 
Plan hash value: 1978226902
 
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE      |      |     1 |    26 |            |          |
|*  2 |   HASH JOIN          |      |   100 |  2600 |     4   (0)| 00:00:01 |
|   3 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |   100 |   500 |     2   (0)| 00:00:01 |
|   5 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T2   |   100 |   500 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$16C51A37
   3 - SEL$2        / from$_subquery$_001@SEL$1
   4 - SEL$2        / T1@SEL$2
   5 - SEL$3        / V2@SEL$1
   6 - SEL$3        / T2@SEL$3
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("from$_subquery$_001"."OBJECT_ID"="V2"."OBJECT_ID")
 
 
=======================
Both inline views named
=======================
 
Plan hash value: 1978226902
 
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE      |      |     1 |    26 |            |          |
|*  2 |   HASH JOIN          |      |   100 |  2600 |     4   (0)| 00:00:01 |
|   3 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |   100 |   500 |     2   (0)| 00:00:01 |
|   5 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T2   |   100 |   500 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$16C51A37
   3 - SEL$2        / V1@SEL$1
   4 - SEL$2        / T1@SEL$2
   5 - SEL$3        / V2@SEL$1
   6 - SEL$3        / T2@SEL$3
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("V1"."OBJECT_ID"="V2"."OBJECT_ID")

当你扫过你看到的这些全部相同的执行计划–只有一个命名的变化。V1 是from$_subquery$_001 的同义词，v2 是 from$_subquery$_003的同义词.

不要让我解释为何优化器会选择这样的名字，我曾经想过，也许我可以在优化器的跟踪文件的某处看到 from$_subquery$_002，但是除了这里展示的这两个别名外，我只是偶尔看到过from$_subquery$_005@sel$4的别名出现。

原文链接地址 https://jonathanlewis.wordpress.com/2020/05/12/from_subquery_nnn/

原文内容：
from$_subquery$_NNN
Filed under: Execution plans,Oracle — Jonathan Lewis @ 4:26 pm BST May 12,2020
This is a reference note for a question that came up as a comment on a lengthy note I wrote about reading execution plans.

How do you interpret something like: from$_subquery$_001@SEL$1 in the Query Block Name / Object Alias section of an execution plan.

The simple answer is that if you’ve got an inline view in the FROM clause of a query and you haven’t given the inline view an alias the optimizer will have to invent one – and this is what they look like.

As a quick demo here’s a script to create a couple of tables and then run a query that joins two inline views (using “ANSI”-style SQL), with variations on which of the inline views are named:

rem
rem     Script:         from_subquery.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2020
rem
rem     Last tested 
rem             19.3.0.0
rem             12.2.0.1
rem
create table t1
as
select  * 
from    all_objects
where   rownum <= 100
; 
create table t2
as
select  *
from    all_objects
where   rownum <= 100
;
set serveroutput off
prompt  =========================
prompt  Neither inline view named
prompt  =========================
select 
        count(*)
from    (select /*+ no_merge */ * from t1)
join 
        (select /*+ no_merge */ * from t2)
using
        (object_id)
;
select * from table(dbms_xplan.display_cursor(null,null,'alias'));
prompt  ============================
prompt  Only first inline view named
prompt  ============================
select 
        count(*)
from    (select /*+ no_merge */ * from t1) v1
join 
        (select /*+ no_merge */ * from t2)
using
        (object_id)
;
select * from table(dbms_xplan.display_cursor(null,null,'alias'));
prompt  =============================
prompt  Only second inline view named
prompt  =============================
select 
        count(*)
from    (select /*+ no_merge */ * from t1)
join 
        (select /*+ no_merge */ * from t2) v2
using
        (object_id)
;
select * from table(dbms_xplan.display_cursor(null,null,'alias'));
prompt  =======================
prompt  Both inline views named
prompt  =======================
select 
        count(*)
from    (select /*+ no_merge */ * from t1) v1
join 
        (select /*+ no_merge */ * from t2) v2
using
        (object_id)
;
select * from table(dbms_xplan.display_cursor(null,null,'alias'));

In all four examples I’ve added the /*+ no_merge */ hint to the inline views; if I hadn’t done that the optimizer would simply have reduced each query to a single query block joining two tables. As it is here are the resulting execution plans (with a little cosmetic editing) reporting the plan hash value, plan, outline and predicates:

=========================
Neither inline view named
=========================
Plan hash value: 1978226902
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE      |      |     1 |    26 |            |          |
|*  2 |   HASH JOIN          |      |   100 |  2600 |     4   (0)| 00:00:01 |
|   3 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |   100 |   500 |     2   (0)| 00:00:01 |
|   5 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T2   |   100 |   500 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$16C51A37
   3 - SEL$2        / from$_subquery$_001@SEL$1
   4 - SEL$2        / T1@SEL$2
   5 - SEL$3        / from$_subquery$_003@SEL$1
   6 - SEL$3        / T2@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("from$_subquery$_001"."OBJECT_ID"="from$_subquery$_003"."O
              BJECT_ID")
 

============================
Only first inline view named
============================
Plan hash value: 1978226902
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE      |      |     1 |    26 |            |          |
|*  2 |   HASH JOIN          |      |   100 |  2600 |     4   (0)| 00:00:01 |
|   3 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |   100 |   500 |     2   (0)| 00:00:01 |
|   5 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T2   |   100 |   500 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$16C51A37
   3 - SEL$2        / V1@SEL$1
   4 - SEL$2        / T1@SEL$2
   5 - SEL$3        / from$_subquery$_003@SEL$1
   6 - SEL$3        / T2@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("V1"."OBJECT_ID"="from$_subquery$_003"."OBJECT_ID")
 
 
=============================
Only second inline view named
=============================
 
Plan hash value: 1978226902
 
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE      |      |     1 |    26 |            |          |
|*  2 |   HASH JOIN          |      |   100 |  2600 |     4   (0)| 00:00:01 |
|   3 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |   100 |   500 |     2   (0)| 00:00:01 |
|   5 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T2   |   100 |   500 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$16C51A37
   3 - SEL$2        / from$_subquery$_001@SEL$1
   4 - SEL$2        / T1@SEL$2
   5 - SEL$3        / V2@SEL$1
   6 - SEL$3        / T2@SEL$3
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("from$_subquery$_001"."OBJECT_ID"="V2"."OBJECT_ID")
 
 
=======================
Both inline views named
=======================
 
Plan hash value: 1978226902
 
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE      |      |     1 |    26 |            |          |
|*  2 |   HASH JOIN          |      |   100 |  2600 |     4   (0)| 00:00:01 |
|   3 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |   100 |   500 |     2   (0)| 00:00:01 |
|   5 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T2   |   100 |   500 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$16C51A37
   3 - SEL$2        / V1@SEL$1
   4 - SEL$2        / T1@SEL$2
   5 - SEL$3        / V2@SEL$1
   6 - SEL$3        / T2@SEL$3
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("V1"."OBJECT_ID"="V2"."OBJECT_ID")

As you scan down the plans you can see that they are all the same – with only a change in naming where V1 is synonymous with from$_subquery$_001 and v2 is synonymous with from$_subquery$_003.

Don’t ask me to explain how the optimizer chooses the names – I had thought I might see a from$_subquery$_002 somewhere in the optimizer trace file, but apart from the two aliases I’ve shown here the only other alias I got was one occurrence of from$_subquery$_005@sel$4.