Oracle执行计划是什么
关于执行计划的概念，相信大家都有过基础的了解。这里不详细解释概念性东西，只需要知道。执行计划是Oracle根据SQL语句生成的一个执行步骤的表述即可。
执行计划分三部分：1.执行计划的SQL语句，SQL Id 以及对应的执行计划 hash value 。 2.执行计划主体部分。 3.执行计划补充说明。如下：
-- 这里是SQL语句 select empno,ename from emp 的执行计划 -- 第一部分 SQL_ID 78bd3uh4a08av, child number 0 ------------------------------------- select empno,ename from emp Plan hash value: 3956160932 -- 第二部分 （主体部分） -- 这里主要关注部分，可以看到执行时的执行步骤，执行顺序，对应的谓词信息、列信息， 消耗成本等 ------------------------------------------- | Id | Operation | Name | E-Rows | ------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | TABLE ACCESS FULL| EMP | 16 | ------------------------------------------- -- 第三部分 （补充说明） Note ----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level
Oracle执行计划怎么获取
获取SQL执行计划的方法有很多，最简单的是在PL/SQL 中直接按F5，就可以获得SQL的执行计划。结果如下：
explain plan 命令
使用explain plan 命令得到的结果跟直接按F5得到的结果一致，其操作过程为（以SQL select * from emp 为例）：
-- 1.执行 explain 命令SQL，这里的for后面为需要查看执行计划的SQL语句： explain plan for select * from emp; -- 2.执行如下语句 select * from table(dbms_xplan.display） -- 得到执行结果如下： Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 16 | 592 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMP | 16 | 592 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------
上面的SQL执行计划，虽然可以得到执行计划，但是不一定是刚执行过的SQL使用的执行计划。也就是说可能是不准确的。使用下面的方法可以得到准确的执行过的SQL的执行计划：
-- 这里的all和advanced都行，二选一。但是必须是从dos窗口中执行，它会取上一次执行的SQL的执行计划返回。 select * from table(dbms_xplan.display_cursor(null,null,'all/advanced')); -- 通过使用该SQL查询执行过的SQL语句的SQLid SELECT SQL_ID FROM v$SQL WHERE SQL_TEXT like'select * from emp' -- 查询该SQL的执行计划，需要在同一个session中执行。 select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'all/advanced'));
而且，通过以上两种方式得到的执行计划数据会更全一些，会包含执行计划的头部信息和补充说明信息。例如：
SQL_ID a2dk8bdn0ujx7, child number 0 ------------------------------------- select * from emp Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | TABLE ACCESS FULL| EMP | 16 | 592 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / EMP@SEL$1 Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10], "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
Oracle执行计划怎么看
执行顺序
通过执行计划，我们可以看出执行计划的执行步骤和执行类型。但是执行计划是怎么个执行顺序呢，先执行的哪一步后执行哪一步呢？有个原则，先右后下。简单的说，从上到下，执行计划如果有缩进，算是子过程。类似于目录结构，有向右缩进，就是当前的子过程。先执行完子过程，然后回到父过程。然后执行下一个。下面用个例子说明下执行过程的流程。
执行计划如下：
SQL_ID 22svdfk7watap, child number 0 ------------------------------------- select emp.ename,EMP_TEMP.EMPNO from emp,EMP_TEMP where emp.ename in (select ename from EMP_TEMP where empno > 100) Plan hash value: 1993712166 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 16 (100)| | |* 1 | HASH JOIN | | 194 | 4074 | 16 (7)| 00:00:01 | | 2 | MERGE JOIN CARTESIAN| | 182 | 2730 | 13 (8)| 00:00:01 | | 3 | SORT UNIQUE | | 14 | 154 | 3 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL | EMP_TEMP | 14 | 154 | 3 (0)| 00:00:01 | | 5 | BUFFER SORT | | 14 | 56 | 10 (10)| 00:00:01 | | 6 | TABLE ACCESS FULL | EMP_TEMP | 14 | 56 | 1 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | EMP | 16 | 96 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5DA710D3 4 - SEL$5DA710D3 / EMP_TEMP@SEL$2 6 - SEL$5DA710D3 / EMP_TEMP@SEL$1 7 - SEL$5DA710D3 / EMP@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("EMP"."ENAME"="ENAME") 4 - filter("EMPNO">100) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=1) "EMP"."ENAME"[VARCHAR2,10], "EMP_TEMP"."EMPNO"[NUMBER,22] 2 - "ENAME"[VARCHAR2,10], "EMP_TEMP"."EMPNO"[NUMBER,22] 3 - (#keys=1) "ENAME"[VARCHAR2,10] 4 - (rowset=256) "ENAME"[VARCHAR2,10] 5 - (#keys=0) "EMP_TEMP"."EMPNO"[NUMBER,22] 6 - (rowset=256) "EMP_TEMP"."EMPNO"[NUMBER,22] 7 - (rowset=256) "EMP"."ENAME"[VARCHAR2,10]
在这个执行计划中，先找到id为0的行，发现有子项目，然后跳到1→2→3→4然后发现id为4的步骤没有子步骤，那么数据库首先执行4这一行。执行完后，看是否存在当前同一级的执行步骤，发现没有，然后返回到步骤3执行步骤3.然后判断发现步骤3有同一级别的执行步骤，进一步判断步骤3是否具有子步骤。发现具有子步骤6，然后执行步骤6.继续之前的判断，执行步骤5然后没有同级步骤存在，执行步骤2，然后下一级步骤执行步骤7，跳上一级执行步骤1步骤0 。整体的执行顺序就是这个样子的。
常见执行类型
1.表扫描相关执行计划：全表扫描、.rowId扫描。SQL语句和执行结果如下：
-- 全表扫描：table access full ，扫描表格的全部内容。 -- SQL 语句 select * from emp -- 执行计划 SQL_ID a2dk8bdn0ujx7, child number 0 ------------------------------------- select * from emp Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | TABLE ACCESS FULL| EMP | 16 | 592 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / EMP@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "EMP"@"SEL$1") END_OUTLINE_DATA */ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10], "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
-- rowId扫描，直接通过rowid获取。 -- SQL语句 select * from emp where rowid = 'AAAtkkAAGAABqYmAAA' -- 执行计划 SQL_ID 5jqu54nycrbg8, child number 0 ------------------------------------- select * from emp where rowid = 'AAAtkkAAGAABqYmAAA' Plan hash value: 1116584662 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / EMP@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") ROWID(@"SEL$1" "EMP"@"SEL$1") END_OUTLINE_DATA */ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10], "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
2.B索引扫描，主要有唯一性索引扫描（index unique scan）、索引范围扫描（index range scan）、索引全部扫描（index full scan）、索引快速全扫描（index fast full scan）、索引跳跃扫描（index skip scan）。SQL与执行计划如下：
-- 唯一性索引扫描，要求索引是唯一索引（如主键），关键字是 INDEX UNIQUE SCAN select empno from emp where empno=7368; -- 执行计划 SQL_ID grw180q65y5yd, child number 0 ------------------------------------- select empno from emp where empno=7368 Plan hash value: 56244932 ----------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| |* 1 | INDEX UNIQUE SCAN| PK_EMP | 1 | 4 | 0 (0)| ----------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / EMP@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO")) END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("EMPNO"=7368) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "EMPNO"[NUMBER,22]
-- 索引范围扫描，谓语条件是索引一部分的时候，关键字是 INDEX RANGE SCAN -- SQL语句 select * from emp where empno > 7521 --这里如果谓语不是唯一索引，等于号也会是范围索引 -- 执行计划 SQL_ID 1kbvtjx2cff29, child number 0 ------------------------------------- select * from emp where empno > 7521 Plan hash value: 2787773736 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 11 | 407 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | PK_EMP | 11 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / EMP@SEL$1 2 - SEL$1 / EMP@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO")) BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "EMP"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO">7521) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10], "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22] 2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
-- 索引全扫描，系统对索引进行全部扫描 关键字是 INDEX FULL SCAN -- SQL语句 select empno from emp -- 执行计划 SQL_ID g6z8w0zj8fma2, child number 0 ------------------------------------- select empno from emp Plan hash value: 179099197 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | 1 | INDEX FULL SCAN | PK_EMP | 16 | 64 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / EMP@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO")) END_OUTLINE_DATA */ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "EMPNO"[NUMBER,22]
-- 索引快速全扫描，内容较多时才会出现，执行计划中的关键字为：INDEX FAST FULL SCAN -- SQL语句 -- 首先需要创建一个表 create table test (name varchar2(1),test_id number not null); -- 创建相应的索引 create index t_idx on test(test_id); -- 执行计划添加数据 begin for i in 1..100000 loop insert into test values ('A',i); end loop; commit; end; -- 执行查询SQL select test_id from test; -- 执行计划 SQL_ID gqs77pc0cba78, child number 0 ------------------------------------- select test_id from test Plan hash value: 3163761342 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 68 (100)| | | 1 | INDEX FAST FULL SCAN| T_IDX | 99726 | 1266K| 68 (0)| 00:00:01 | ------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / TEST@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_FFS(@"SEL$1" "TEST"@"SEL$1" ("TEST"."TEST_ID")) END_OUTLINE_DATA */ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "TEST_ID"[NUMBER,22] Note ----- - dynamic statistics used: dynamic sampling (level=2)
-- 索引跳跃扫描，当索引为组合索引，又要根据索引的后置字段匹配时，Oracle有时候会选择索引跳跃式扫描。关键字为：INDEX SKIP SCAN --SQL 语句 -- 首先，删除原来的索引，建立新索引 drop index t_idx ; create index t_idx_2 on test (namess,test_id); -- 执行SQL语句 select /*+ t_idx_2 */ * from test where test_id = 1000 -- 执行计划 SQL_ID 5mjpux6q2dqs7, child number 0 ------------------------------------- select /*+ t_idx_2 */ * from test where test_id = 10000 Plan hash value: 906579381 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | INDEX SKIP SCAN | T_IDX_2 | 1 | 6 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / TEST@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_SS(@"SEL$1" "TEST"@"SEL$1" ("TEST"."NAMESS" "TEST"."TEST_ID")) END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("TEST_ID"=10000) filter("TEST_ID"=10000) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "TEST"."NAMESS"[VARCHAR2,1], "TEST_ID"[NUMBER,22]
3.位图索引扫描，需要建立位图索引 bitMap。我应用不多，不献丑了。
4.表连接相关索引：排序合并连接、嵌套循环连接、哈希链接、反连接、半连接。
-- 在执行计划上对应的关键字为： SORT JOIN 和 MERGE JOIN --数据准备 CREATE TABLE t1(c1 number,c2 varchar2(1)); CREATE TABLE t2(c2 varchar2(1),c3 varchar2(2)); INSERT INTO t1 VALUES (1,'A'); INSERT INTO t1 VALUES (2,'B'); INSERT INTO t1 VALUES (3,'C'); INSERT INTO t1 VALUES (4,'D'); INSERT INTO t1 VALUES (5,'E'); INSERT INTO t2 VALUES ('A','A2'); INSERT INTO t2 VALUES ('B','B2'); INSERT INTO t2 VALUES ('C','C2'); INSERT INTO t2 VALUES ('D','D2'); INSERT INTO t2 VALUES ('E','E2'); -- sql 语句 select /*+ use_merge(t1 t2) */ t1.c1,t1.c2,t2.c3 from t1,t2 where t1.c2= t2.c2; -- 执行计划 SQL_ID 0d9sbrn5axmr9, child number 0 ------------------------------------- select /*+ use_merge(t1 t2) */ t1.c1,t1.c2,t2.c3 from t1,t2 where t1.c2= t2.c2 Plan hash value: 412793182 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 8 (100)| | | 1 | MERGE JOIN | | 5 | 100 | 8 (25)| 00:00:01 | | 2 | SORT JOIN | | 5 | 75 | 4 (25)| 00:00:01 | | 3 | TABLE ACCESS FULL| T1 | 5 | 75 | 3 (0)| 00:00:01 | |* 4 | SORT JOIN | | 5 | 25 | 4 (25)| 00:00:01 | | 5 | TABLE ACCESS FULL| T2 | 5 | 25 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$1 / T1@SEL$1 5 - SEL$1 / T2@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "T1"@"SEL$1") FULL(@"SEL$1" "T2"@"SEL$1") LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1") USE_MERGE(@"SEL$1" "T2"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("T1"."C2"="T2"."C2") filter("T1"."C2"="T2"."C2") Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "T1"."C2"[VARCHAR2,1], "T1"."C1"[NUMBER,22], "T2"."C3"[VARCHAR2,2] 2 - (#keys=1) "T1"."C2"[VARCHAR2,1], "T1"."C1"[NUMBER,22] 3 - "T1"."C1"[NUMBER,22], "T1"."C2"[VARCHAR2,1] 4 - (#keys=1) "T2"."C2"[VARCHAR2,1], "T2"."C3"[VARCHAR2,2] 5 - "T2"."C2"[VARCHAR2,1], "T2"."C3"[VARCHAR2,2] Note ----- - dynamic statistics used: dynamic sampling (level=2)
-- 在执行计划上对应的关键字为：NESTED LOOPS --数据准备同上 -- sql 语句 select /*+ use_nl(t1 t2) */ t1.c1,t1.c2,t2.c3 from t1,t2 where t1.c2= t2.c2; -- 执行计划 SQL_ID fmtusp6nx39uv, child number 0 ------------------------------------- select /*+ use_nl(t1 t2) */ t1.c1,t1.c2,t2.c3 from t1,t2 where t1.c2= t2.c2 Plan hash value: 1967407726 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 12 (100)| | | 1 | NESTED LOOPS | | 5 | 100 | 12 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| T1 | 5 | 75 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| T2 | 1 | 5 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 3 - SEL$1 / T2@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "T1"@"SEL$1") FULL(@"SEL$1" "T2"@"SEL$1") LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1") USE_NL(@"SEL$1" "T2"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("T1"."C2"="T2"."C2") Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "T1"."C1"[NUMBER,22], "T1"."C2"[VARCHAR2,1], "T2"."C3"[VARCHAR2,2] 2 - "T1"."C1"[NUMBER,22], "T1"."C2"[VARCHAR2,1] 3 - "T2"."C3"[VARCHAR2,2] Note ----- - dynamic statistics used: dynamic sampling (level=2)
-- 在执行计划上对应的关键字为：HASH JOIN --数据准备同上 -- sql 语句 select t1.c1,t1.c2,t2.c3 from t1,t2 where t1.c2= t2.c2; -- 执行计划 SQL_ID 1z4x7bp0xm2sa, child number 0 ------------------------------------- select t1.c1,t1.c2,t2.c3 from t1,t2 where t1.c2= t2.c2 Plan hash value: 1838229974 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 6 (100)| | |* 1 | HASH JOIN | | 5 | 100 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| T1 | 5 | 75 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| T2 | 5 | 25 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 3 - SEL$1 / T2@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "T1"@"SEL$1") FULL(@"SEL$1" "T2"@"SEL$1") LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1") USE_HASH(@"SEL$1" "T2"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."C2"="T2"."C2") Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=1) "T1"."C2"[VARCHAR2,1], "T1"."C1"[NUMBER,22], "T2"."C3"[VARCHAR2,2] 2 - (rowset=256) "T1"."C1"[NUMBER,22], "T1"."C2"[VARCHAR2,1] 3 - (rowset=256) "T2"."C2"[VARCHAR2,1], "T2"."C3"[VARCHAR2,2] Note ----- - dynamic statistics used: dynamic sampling (level=2)
-- 在执行计划上对应的关键字为：ANTI --数据准备同上 -- sql 语句 select * from t1 where c2 not in (select c2 from t2 where c3 = 'B2') -- 执行计划 SQL_ID awz9nmmzpd0jp, child number 0 ------------------------------------- select * from t1 where c2 not in (select c2 from t2 where c3 = 'B2') Plan hash value: 1275484728 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 6 (100)| | |* 1 | HASH JOIN ANTI NA | | 5 | 100 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| T1 | 5 | 75 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| T2 | 1 | 5 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5DA710D3 2 - SEL$5DA710D3 / T1@SEL$1 3 - SEL$5DA710D3 / T2@SEL$2 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$5DA710D3") UNNEST(@"SEL$2") OUTLINE(@"SEL$1") OUTLINE(@"SEL$2") FULL(@"SEL$5DA710D3" "T1"@"SEL$1") FULL(@"SEL$5DA710D3" "T2"@"SEL$2") LEADING(@"SEL$5DA710D3" "T1"@"SEL$1" "T2"@"SEL$2") USE_HASH(@"SEL$5DA710D3" "T2"@"SEL$2") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("C2"="C2") 3 - filter("C3"='B2') Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=1) "C2"[VARCHAR2,1], "T1"."C1"[NUMBER,22] 2 - "T1"."C1"[NUMBER,22], "C2"[VARCHAR2,1] 3 - "C2"[VARCHAR2,1] Note ----- - dynamic statistics used: dynamic sampling (level=2)
-- 在执行计划上对应的关键字为：SEMI --数据准备同上 -- sql 语句 select * from t1 where exists (select * from t2 where t1.c2= t2.c2 and c3='A2') -- 执行计划 SQL_ID fs5rxxz52a0hy, child number 0 ------------------------------------- select * from t1 where exists (select * from t2 where t1.c2= t2.c2 and c3='A2') Plan hash value: 1713220790 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 6 (100)| | |* 1 | HASH JOIN SEMI | | 1 | 20 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| T1 | 5 | 75 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| T2 | 1 | 5 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5DA710D3 2 - SEL$5DA710D3 / T1@SEL$1 3 - SEL$5DA710D3 / T2@SEL$2 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$5DA710D3") UNNEST(@"SEL$2") OUTLINE(@"SEL$1") OUTLINE(@"SEL$2") FULL(@"SEL$5DA710D3" "T1"@"SEL$1") FULL(@"SEL$5DA710D3" "T2"@"SEL$2") LEADING(@"SEL$5DA710D3" "T1"@"SEL$1" "T2"@"SEL$2") USE_HASH(@"SEL$5DA710D3" "T2"@"SEL$2") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."C2"="T2"."C2") 3 - filter("C3"='A2') Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=1) "T1"."C2"[VARCHAR2,1], "T1"."C1"[NUMBER,22] 2 - (rowset=256) "T1"."C1"[NUMBER,22], "T1"."C2"[VARCHAR2,1] 3 - "T2"."C2"[VARCHAR2,1] Note ----- - dynamic statistics used: dynamic sampling (level=2)