oracle-常見的執行計劃（一）

来源:http://www.cnblogs.com/gull/archive/2016/06/12/5574548.html
-Advertisement-
Play Games

一、表訪問方式 CBO基礎概念中有講到，訪問表的方式有兩種：全表掃描和ROWID掃描。 全表掃描的執行計劃：TABLE ACCESS FULL ROWID掃描對應執行計劃：TABLE ACCESS BY USER ROWID 或 TABLE ACCESS BY INDEX ROWID 通過例子說明 ...


一、表訪問方式

CBO基礎概念中有講到，訪問表的方式有兩種：全表掃描和ROWID掃描。

全表掃描的執行計劃：TABLE ACCESS FULL

ROWID掃描對應執行計劃：TABLE ACCESS BY USER ROWID 或 TABLE ACCESS BY INDEX ROWID

通過例子說明

（一）、全表掃描方式

select empno,ename from scott.emp
      
select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  6c0fp61y99tuw, child number 0
-------------------------------------
select empno,ename from scott.emp
 
Plan hash value: 3956160932
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

（二）、rowid訪問方式

1、TABLE ACCESS BY USER ROWID

select empno,ename from scott.emp where rowid='AAASZHAAEAAAACXAAA'
  
select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  99f9cvxt33nzy, child number 0
-------------------------------------
select empno,ename from scott.emp where rowid='AAASZHAAEAAAACXAAA'
 
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 |    22 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

2、TABLE ACCESS BY INDEX ROWID

select empno,ename from scott.emp where empno=7521
  
select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  69nxfycyppq7m, child number 0
-------------------------------------
select empno,ename from scott.emp where empno=7521
 
Plan hash value: 2949544139
 
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    10 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------
...
 

二、B*樹訪問方式

索引唯一掃描、索引範圍掃描、索引全掃描、索引快速掃描、索引跳躍式掃描

以上這些執行計劃執行計劃相對應

  • 索引唯一掃描：INDEX UNIQUE SCAN
  • 索引範圍掃描：INDEX RANGE SCAN
  • 索引全掃描：INDEX FULL SCAN
  • 索引快速全掃描：INDEX FAST FULL SCAN
  • 索引跳躍式掃描：INDEX SKIP SCAN

例子說明

（一）、索引唯一掃描：INDEX UNIQUE SCAN

select empno,ename from scott.emp where empno=7521
  
select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  69nxfycyppq7m, child number 0
-------------------------------------
select empno,ename from scott.emp where empno=7521
 
Plan hash value: 2949544139
 
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    10 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

通過唯一索引的方式獲取rowid訪問表中的以rowid的方式

（二）、索引範圍掃描：INDEX RANGE SCAN

select empno,ename from scott.emp where empno>=7521 and empno<=8521
  
select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  95m0uw0yxc10w, child number 0
-------------------------------------
select empno,ename from scott.emp where empno>=7521 and empno<=8521
 
Plan hash value: 169057108
 
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |    11 |   110 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PK_EMP |    11 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EMPNO">=7521 AND "EMPNO"<=8521)

謂詞中存在大於、小於的訪問方式時，並且這個謂詞建議過索引，基本採用索引範圍掃描的方式

（三）、索引全掃描：INDEX FULL SCAN

select empno,ename from scott.emp order by empno

select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  3bt7b5h1rxh6z, child number 0
-------------------------------------
select empno,ename from scott.emp order by empno
 
Plan hash value: 4170700152
 
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   140 |     2   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

當索引列的定義可以為null

create index ind_EMP_JOB  ON scott.emp(JOB); 
         
select empno,ename from scott.emp order by job
      
select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  cnpptzn6mshrb, child number 0
-------------------------------------
select empno,ename from scott.emp order by job
 
Plan hash value: 150391907
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     4 (100)|          |
|   1 |  SORT ORDER BY     |      |    14 |   252 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   252 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

發現如果JOB列定義可以為空的話，order by 是不會走索引的。

調整列的屬性，不能為空，在查看執行計劃

alter table scott.emp modify(job not null)
          
select empno,ename from scott.emp order by job
      
select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  cnpptzn6mshrb, child number 0
-------------------------------------
select empno,ename from scott.emp order by job
 
Plan hash value: 157317628
 
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP         |    14 |   252 |     2   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | IND_EMP_JOB |    14 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

（四）、索引快速全掃描：INDEX FAST FULL SCAN

select /*+index_ffs(a ind_EMP_JOB)*/ job  from scott.emp a
      
select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  3hu16hz75qkhu, child number 0
-------------------------------------
select /*+index_ffs(a ind_EMP_JOB)*/ job  from scott.emp a
 
Plan hash value: 2520590889
 
------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |     2 (100)|          |
|   1 |  INDEX FAST FULL SCAN| IND_EMP_JOB |    14 |   112 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

這裡使用HINT，強制提示優化器走fast索引的方式

（五）、索引跳躍式掃描：INDEX SKIP SCAN

用於複合索引中的，非索引前導列的訪問

create index ind_EMP_JENAME  ON scott.emp(JOB,ename); 
          
select empno,ename from scott.emp where ename='ALLEN'

SQL_ID  bdfu46xwtg0qk, child number 0
-------------------------------------
select empno,ename from scott.emp where ename='ALLEN'
 
Plan hash value: 878294805
 
----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    10 |     2   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IND_EMP_JENAME |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ENAME"='ALLEN')
       filter("ENAME"='ALLEN')

三、點陣圖索引訪問的方式

適用於數據倉庫，不適用OLTP系統，物理存儲結構類似B*數索引，對應rowid的上限、rowid的下限、點陣圖段。

對於oracle資料庫中的點陣圖索引而言，他是沒有行鎖這個概念的，要鎖就鎖索引行的整個點陣圖段，而多個數據行可能對應同一個索引行的點陣圖段，這個鎖的粒度就決定了點陣圖索引不適用於高併發並頻繁修改的OLTP系統,在OLTP系統中，很容易產生死鎖。

  • 點陣圖索引單鍵值掃描：BITMAP INDEX SINGLE VALUE
  • 點陣圖索引範圍掃描: BITMAP INDEX RANGE SCAN
  • 點陣圖索引全掃描: BITMAP INDEX FULL SCAN
  • 點陣圖索引快速全掃描: BITMAP INDEX FAST FULL SCAN
  • 點陣圖按位與: BITMAP  AND
  • 點陣圖按位或: BITMAP OR
  • 點陣圖按位減: BITMAP MINUS

（一）、構造一張表，測試點陣圖索引

Create table test_normal (empno number(10), ename varchar2(30), sal number(10)) TABLESPACE GLL01;

Begin
For i in 1..1000000
Loop
   Insert into test_normal 
   values(i, dbms_random.string('U',30), dbms_random.value(1000,7000));
   If mod(i, 10000) = 0 then
   Commit;
  End if;
End loop;
End;

create bitmap index normal_empno_bmx on test_normal(empno) TABLESPACE GLL01;

create bitmap index normal_empno_sal on test_normal(sal) TABLESPACE GLL01;

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SYS','TEST_NORMAL',CASCADE=>TRUE);

（二）、點陣圖索引單鍵值掃描：BITMAP INDEX SINGLE VALUE

select * from test_normal where empno=1000

select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  343wc0yvq4cc9, child number 0
-------------------------------------
select * from test_normal where empno=1000
 
Plan hash value: 4267925254
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID | TEST_NORMAL      |     1 |    40 |     3   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                  |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | NORMAL_EMPNO_BMX |       |       |            |          |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("EMPNO"=1000)

（三）、點陣圖索引範圍掃描: BITMAP INDEX RANGE SCAN

select * from  test_normal where empno>=50 and  empno<=2000

select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  34p69q6q4wqxx, child number 0
-------------------------------------
select * from  test_normal where empno>=50 and  empno<=2000
 
Plan hash value: 641040856
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |       |       |   362 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID | TEST_NORMAL      |  1952 | 78080 |   362   (0)| 00:00:05 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                  |       |       |            |          |
|*  3 |    BITMAP INDEX RANGE SCAN   | NORMAL_EMPNO_BMX |       |       |            |          |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("EMPNO">=50 AND "EMPNO"<=2000)

（四）、點陣圖索引全掃描: BITMAP INDEX FULL SCAN

select /*+index(a normal_empno_bmx)*/  a.empno  from  test_normal a

select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  cmxdf5ry1gvw1, child number 0
-------------------------------------
select /*+index(a normal_empno_bmx)*/  a.empno  from  test_normal a
 
Plan hash value: 220257735
 
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |       |       |  3496 (100)|          |
|   1 |  BITMAP CONVERSION TO ROWIDS|                  |  1000K|  4882K|  3496   (1)| 00:00:42 |
|   2 |   BITMAP INDEX FULL SCAN    | NORMAL_EMPNO_BMX |       |       |            |          |
------------------------------------------------------------------------------------------------

（五）、點陣圖索引快速全掃描: BITMAP INDEX FAST FULL SCAN

select /*+index_ffs(a normal_empno_bmx)*/  a.empno  from  test_normal a

select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  9rgzkasky186v, child number 0
-------------------------------------
select /*+index_ffs(a normal_empno_bmx)*/  a.empno  from  test_normal a
 
Plan hash value: 2075344169
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |       |       |  3141 (100)|          |
|   1 |  BITMAP CONVERSION TO ROWIDS |                  |  1000K|  4882K|  3141   (1)| 00:00:38 |
|   2 |   BITMAP INDEX FAST FULL SCAN| NORMAL_EMPNO_BMX |       |       |            |          |
-------------------------------------------------------------------------------------------------

（六）、點陣圖按位與: BITMAP AND、BITMAP OR

select * from  test_normal where empno=3969  and  sal in (1008,1011)

select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  cyf5th2ts2z7j, child number 0
-------------------------------------
select * from  test_normal where empno=3969  and  sal in (1008,1011)
 
Plan hash value: 640003492
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |       |       |     5 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID  | TEST_NORMAL      |     1 |    40 |     5   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS |                  |       |       |            |          |
|   3 |    BITMAP AND                 |                  |       |       |            |          |
|   4 |     BITMAP OR                 |                  |       |       |            |          |
|*  5 |      BITMAP INDEX SINGLE VALUE| NORMAL_EMPNO_SAL |       |       |            |          |
|*  6 |      BITMAP INDEX SINGLE VALUE| NORMAL_EMPNO_SAL |       |       |            |          |
|*  7 |     BITMAP INDEX SINGLE VALUE | NORMAL_EMPNO_BMX |       |       |            |          |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("SAL"=1008)
   6 - access("SAL"=1011)
   7 - access("EMPNO"=3969)

（七）、點陣圖按位減: BITMAP MINUS

select /*+index_ffs(test_normal normal_empno_sal)*/  * from  test_normal where empno>=50 and  empno<=20000 and  sal not in (1008)

select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  dn3yq5vrp1524, child number 0
-------------------------------------
select /*+index_ffs(test_normal normal_empno_sal)*/  * from  
test_normal where empno>=50 and  empno<=20000 and  sal not in (1008)
 
Plan hash value: 3977516083
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |       |       |  1385 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID  | TEST_NORMAL      | 19949 |   779K|  1385   (1)| 00:00:17 |
|   2 |   BITMAP CONVERSION TO ROWIDS |                  |       |       |            |          |
|   3 |    BITMAP MINUS               |                  |       |       |            |          |
|   4 |     BITMAP MINUS              |                  |       |       |            |          |
|   5 |      BITMAP MERGE             |                  |       |       |            |          |
|*  6 |       BITMAP INDEX RANGE SCAN | NORMAL_EMPNO_BMX |       |       |            |          |
|*  7 |      BITMAP INDEX SINGLE VALUE| NORMAL_EMPNO_SAL |       |       |            |          |
|*  8 |     BITMAP INDEX SINGLE VALUE | NORMAL_EMPNO_SAL |       |       |            |          |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("EMPNO">=50 AND "EMPNO"<=20000)
   7 - access("SAL"=1008)
   8 - access("SAL" IS NULL)

列值為NULL，點陣圖索引是記錄，以上這個例子中也把列為NULL剔除

 

四、表連接的訪問方式

  • 嵌套迴圈連接：NESTED LOOPS
  • 哈希連接：hash join
  • 排序合併連接：sort join和merge join
  • 反連接：nested loops anti、hash join anti、merge join anti
  • 半連接：nested loop semi、hash join semi、merge join semi

（一）、嵌套迴圈連接：NESTED LOOPS

select /*+leading(a) use_nl(b)*/* from scott.emp a , scott.dept b  where a.deptno=b.deptno

select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  c6xax626nhn8k, child number 0
-------------------------------------
select /*+leading(a) use_nl(b)*/* from scott.emp a , scott.dept b  
where a.deptno=b.deptno
 
Plan hash value: 3625962092
 
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |    17 (100)|          |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |    14 |   798 |    17   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    19 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("A"."DEPTNO"="B"."DEPTNO")

（二）、哈希連接：hash join

select /*+leading(a) use_hash(b)*/* from scott.emp a , scott.dept b  where a.deptno=b.deptno

select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  fq65sryy1d9dw, child number 0
-------------------------------------
select /*+leading(a) use_hash(b)*/* from scott.emp a , scott.dept b  
where a.deptno=b.deptno
 
Plan hash value: 1123238657
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     7 (100)|          |
|*  1 |  HASH JOIN         |      |    14 |   798 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |     3 |    57 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."DEPTNO"="B"."DEPTNO")

（三）、排序合併連接：sort join和merge join

select /*+use_merge(a b)*/* from scott.emp a , scott.dept b  where a.deptno=b.deptno

select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  9w06suya2pdrn, child number 0
-------------------------------------
select /*+use_merge(a b)*/* from scott.emp a , scott.dept b  where 
a.deptno=b.deptno
 
Plan hash value: 844388907
 
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |     6 (100)|          |
|   1 |  MERGE JOIN                  |         |    14 |   798 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     3 |    57 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     3 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   532 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("A"."DEPTNO"="B"."DEPTNO")
       filter("A"."DEPTNO"="B"."DEPTNO")

（四）、反連接：nested loops anti

alter table scott.emp modify(deptno not  null)

select * from scott.emp  a WHERE A.DEPTNO NOT   IN (SELECT  /*+nl_aj*/  DEPTNO FROM scott.dept b where a.deptno=b.deptno)

select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  dh1c9mwpw9pjx, child number 0
-------------------------------------
select * from scott.emp  a WHERE A.DEPTNO NOT   IN (SELECT  /*+nl_aj*/  
DEPTNO FROM scott.dept b where a.deptno=b.deptno)
 
Plan hash value: 3496123964
 
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |     3 (100)|          |
|   1 |  NESTED LOOPS ANTI |         |     7 |   287 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   532 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     2 |     6 |     0   (0)|          |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("A"."DEPTNO"="B"."DEPTNO")

（五）、反連接：hash join anti

select * from scott.emp a WHERE A.DEPTNO NOT  IN (SELECT /*+hash_aj*/    DEPTNO FROM scott.dept b where a.deptno=b.deptno)

select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  dhq4hhqgqqn0n, child number 0
-------------------------------------
select * from scott.emp a WHERE A.DEPTNO NOT  IN (SELECT /*+hash_aj*/   
 DEPTNO FROM scott.dept b where a.deptno=b.deptno)
 
Plan hash value: 1958379418
 
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |     5 (100)|          |
|*  1 |  HASH JOIN ANTI    |         |     7 |   287 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   532 |     3   (0)| 00:00:01 |
|   3 |   INDEX FULL SCAN  | PK_DEPT |     3 |     9 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."DEPTNO"="B"."DEPTNO")

（六）、反連接：merge join anti

select * from scott.emp  a WHERE A.DEPTNO NOT   IN (SELECT  /*+merge_aj*/  DEPTNO FROM scott.dept b where a.deptno=b.deptno)

select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  1r60ktudp9vq2, child number 0
-------------------------------------
select * from scott.emp  a WHERE A.DEPTNO NOT   IN (SELECT  
/*+merge_aj*/  DEPTNO FROM scott.dept b where a.deptno=b.deptno)
 
Plan hash value: 4267419248
 
-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |       |       |     6 (100)|          |
|   1 |  MERGE JOIN ANTI    |         |     7 |   287 |     6  (34)| 00:00:01 |
|   2 |   SORT JOIN         |         |    14 |   532 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP     |    14 |   532 |     3   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE       |         |     3 |     9 |     2  (50)| 00:00:01 |
|   5 |    INDEX FULL SCAN  | PK_DEPT |     3 |     9 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("A"."DEPTNO"="B"."DEPTNO")
       filter("A"."DEPTNO"="B"."DEPTNO")

（七）、半連接：nested loop semi

select * from scott.emp a WHERE  EXISTS (SELECT /*+nl_sj*/  1 FROM scott.dept b where a.deptno=b.deptno)

select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  9htytj0pxjhkg, child number 0
-------------------------------------
select * from scott.emp a WHERE  EXISTS (SELECT /*+nl_sj*/  1 FROM 
scott.dept b where a.deptno=b.deptno)
 
Plan hash value: 3274513678
 
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |     3 (100)|          |
|   1 |  NESTED LOOPS SEMI |         |     7 |   287 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   532 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     2 |     6 |     0   (0)|          |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("A"."DEPTNO"="B"."DEPTNO")

（八）、半連接：hash join semi

select * from scott.emp a WHERE  EXISTS (SELECT /*+hash_sj*/  1 FROM scott.dept b where a.deptno=b.deptno)

select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  cjhjgkgs8q1fc, child number 0
-------------------------------------
select * from scott.emp a WHERE  EXISTS (SELECT /*+hash_sj*/  1 FROM 
scott.dept b where a.deptno=b.deptno)
 
Plan hash value: 3753861400
 
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |     5 (100)|          |
|*  1 |  HASH JOIN SEMI    |         |     7 |   287 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   532 |     3   (0)| 00:00:01 |
|   3 |   INDEX FULL SCAN  | PK_DEPT |     3 |     9 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."DEPTNO"="B"."DEPTNO")

（九）、半連接：merge join semi

select * from scott.emp a WHERE  EXISTS (SELECT /*+merge_sj*/  1 FROM scott.dept b where a.deptno=b.deptno)

select *from table(dbms_xplan.display_cursor(null,null))

SQL_ID  f2zxcjpqvpsu5, child number 0
-------------------------------------
select * from scott.emp a WHERE  EXISTS (SELECT /*+merge_sj*/  1 FROM 
scott.dept b where a.deptno=b.deptno)
 
Plan hash value: 3011744318
 
-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |       |       |     6 (100)|          |
|   1 |  MERGE JOIN SEMI    |         |     7 |   287 |     6  (34)| 00:00:01 |
|   2 |   SORT JOIN         |         |    14 |   532 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP     |    14 |   532 |     3   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE       |         |     3 |     9 |     2  (50)| 00:00:01 |
|   5 |    INDEX FULL SCAN  | PK_DEPT |     3 |     9 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("A"."DEPTNO"="B"."DEPTNO")
       filter("A"."DEPTNO"="B"."DEPTNO")

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • T-SQL中的一些小陷阱
    1，當心ISNULL函數對你的邏輯引起BUG 有人喜歡或者習慣於(並不代表我推薦，甚至這種寫法沒有任何好處)用ISNULL處理變數這種方式寫查詢 比如：select * from TestISNULL where name = ISNULL(@name,name) @name相當於傳入到存儲過程中的 ...
  • SQL Server 關於列的許可權控制
    在SQL SERVER中列許可權（Column Permissions）其實真沒有什麼好說的，但是好多人對這個都不甚瞭解，已經被人問了幾次了，所以還是在這裡介紹一下，很多人都會問，我能否單獨對錶的某列授權給某個用戶？ 答案是可以，我們可以對錶中的列授予SELECT、UPDATE許可權，我們結合下麵的簡單... ...
  • 曲演雜壇--EXISTS語句
    通常在我寫EXISTS語句時,我會寫成IF EXISTS(SELECT TOP(1) 1 FROM XXX),也沒細細考究過為什麼要這麼寫,只是隱約認為這樣寫沒有啥問題,那今天就深究下吧！ 首先準備測試測試數據 其中需要註意下索引IDX_ID, 雖然ID已經是主鍵索引，但仍創建一個非聚集索引以供後續 ...
  • Hadoop 調研筆記
    由於從各光伏電站採集的數據量較大，必須解決海量數據的查詢、分析的問題。目前主要考慮兩種方式：1. Hadoop大數據技術；2. Oracle（數據倉庫）+BI； 本文僅介紹hadoop的技術要應用特征。 Hadoop 基本介紹 hadoop是一個平臺，是一個適合大數據的分散式存儲和計算的平臺。什麼是 ...
  • 控制文件管理
    控制文件是Oracle資料庫中一種非常重要的文件。 在Oracle資料庫中主要包括：數據文件、控制文件和重做日誌文件。在數據文件中存儲資料庫中的數據，包括各種資料庫對象及其數據。在重做日誌文件中存放用戶執行DML及DDL命令的記錄。 在控制文件中存放資料庫的結構信息。具體來說，在控制文件中包含以下重 ...
  • SQL Server 發佈訂閱 發佈類型詳解
    MicrosoftSQL Server 提供了三種複製類型。 每種複製類型都適合於不同應用程式的要求。 根據應用程式需要，可以在拓撲中使用一種或多種複製類型： 快照複製 事務複製 合併複製 為了幫助您選擇適當的複製類型，此主題提供了有關下列內容的信息： 複製方案 本部分簡要描述了複製的多種常用情況， ...
  • 資料庫查詢練習1
    1、 查詢Student表中的所有記錄的Sname、Ssex和Class列。 2. 查詢Student表的所有記錄。 3.查詢Score表中成績在60到80之間的所有記錄 4.查詢Score表中成績為85，86或88的記錄。 5.查詢Student表中“95031”班或性別為“女”的同學記錄。 6. ...
  • YourSQLDba版本升級總結
    在使用YourSQLDba做資料庫備份、維護時，像其它軟體一樣，版本升級是不可避免的。因為YourSQLDba一直在不停更新版本、擴展功能。下麵介紹一下升級YourSQLDba時的具體步驟和一些註意事項。下麵案例，YourSQLDba原版本為YourSQLDba version: 5.0.2 201... ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類