Oracle里的常見(jiàn)執(zhí)行計(jì)劃有哪些

小編給大家分享一下Oracle里的常見(jiàn)執(zhí)行計(jì)劃有哪些，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

1. 與表訪問(wèn)相關(guān)的執(zhí)行計(jì)劃

   Oracle數(shù)據(jù)庫(kù)里面與表訪問(wèn)有關(guān)的的兩種方法：全表掃描和ROWID掃描。反應(yīng)在執(zhí)行計(jì)劃上，與全表掃描對(duì)應(yīng)的執(zhí)行計(jì)劃中的關(guān)鍵字是“TABLE ACCESS FULL”,與ROWID掃描對(duì)應(yīng)的執(zhí)行計(jì)劃中的關(guān)鍵字是"TABLE ACCESS BY USER ROWID"或“TABLE ACCESS BY INDEX ROWID”.

   我們來(lái)看一下與表訪問(wèn)的相關(guān)的執(zhí)行計(jì)劃，先執(zhí)行如下SQL:

   
   

SQL> select empno,ename,rowid from emp where ename='TURNER';

     EMPNO ENAME      ROWID

---------- ---------- ------------------

      7844 TURNER     AAAVREAAEAAAACXAAJ

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID3bjd8ps607cau, child number 0

-------------------------------------

select empno,ename,rowid from emp where ename='TURNER'

Plan hash value: 3956160932

--------------------------------------------------------------------------

| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  | | | |     3 (100)| |

|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    22 |     3   (0)| 00:00:01 |

從上述顯示內(nèi)容中可以看出，目標(biāo)sql的執(zhí)行計(jì)劃走的是對(duì)表EMP的全表掃描，全表掃描在執(zhí)行計(jì)劃中對(duì)應(yīng)的關(guān)鍵字就是“TABLE ACCESS FULL”

將上述sql改寫(xiě)成以指定的ROWID的方式執(zhí)行：

SQL> select empno,ename from emp where rowid='AAAVREAAEAAAACXAAJ';

     EMPNO ENAME

---------- ----------

      7844 TURNER

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------

SQL_ID8n08pmh26ud05, child number 0

-------------------------------------

select empno,ename from emp where rowid='AAAVREAAEAAAACXAAJ'

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 |

    從上述顯示內(nèi)容可以看出，現(xiàn)在該sql的執(zhí)行計(jì)劃走的對(duì)表emp的rowid掃描，其對(duì)應(yīng)的執(zhí)行計(jì)劃關(guān)鍵字“TABLE ACCESS BY USER ROWID”。

    注意，ROWID掃描所對(duì)應(yīng)的的執(zhí)行計(jì)劃的關(guān)鍵字還有可能是“TABLE ACCESS BY INDEX ROWID”,這取決于訪問(wèn)表時(shí)的ROWID來(lái)源。如果ROWID是來(lái)源于用戶手工指定（例如上述指定“select empno,ename from emp where rowid='AAAVREAAEAAAACXAAJ'”），則對(duì)應(yīng)的執(zhí)行計(jì)劃關(guān)鍵字是“TABLE ACCESS BY USER ROWID”；如果是ROWID是來(lái)源于索引，則對(duì)應(yīng)的執(zhí)行計(jì)劃關(guān)鍵字是“TABLE ACCESS BY INDEX ROWID”

    表EMP的主鍵是列EMPNO（即列EMPNO上有主鍵索引），我們將目標(biāo)sql改寫(xiě)成如下形式后執(zhí)行：

SQL> select empno,ename from emp where empno=7369;

     EMPNO ENAME

---------- ----------

      7369 SMITH

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------

SQL_ID6yzqcfbz5xz3c, child number 0

-------------------------------------

select empno,ename from emp where empno=7369

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 |

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------

|*  2 |   INDEX UNIQUE SCAN    | PK_EMP |   1 |     |   0   (0)|     |

--------------------------------------------------------------------------------------

 從上述顯示內(nèi)容可以看出，此時(shí)目標(biāo)sql的執(zhí)行計(jì)劃在訪問(wèn)表emp走的是對(duì)表emp的rowid掃描，因?yàn)檫@里的rowid是來(lái)源于索引PK_emp,所以其對(duì)應(yīng)的執(zhí)行計(jì)劃關(guān)鍵字是“TABLE ACCESS BY INDEX ROWID”.

2.與B樹(shù)索引相關(guān)的執(zhí)行計(jì)劃

    常見(jiàn)的與B樹(shù)索引訪問(wèn)相關(guān)的方法有：包括索引唯一掃描，索引范圍掃描，索引全掃描，索引快速全掃描和索引跳躍式掃描，反應(yīng)在執(zhí)行計(jì)劃上，與索引唯一掃描對(duì)應(yīng)的關(guān)鍵字“INDEX UNIQUE SCAN”,與索引范圍掃描對(duì)應(yīng)的關(guān)鍵字是“INDEX RANGE SCAN”,與索引全掃描對(duì)應(yīng)的關(guān)鍵字是“INDEX FULL SCAN”,與索引快速全掃描對(duì)應(yīng)的關(guān)鍵字是“INDEX FAST FULL SCAN”,與索引跳躍式掃描對(duì)應(yīng)的關(guān)鍵字是“INDEX SKIP SCAN”.

    下面來(lái)看一下與B樹(shù)索引訪問(wèn)相關(guān)的的執(zhí)行計(jì)劃實(shí)例。創(chuàng)建一個(gè)測(cè)試表EMPLOYEE:

SQL> create table employee(gender varchar2(1),employee_id number);

Table created.

SQL> insert into employee values('F','99');

1 row created.

SQL> insert into employee values('F','100');

1 row created.

SQL> insert into employee values('M','101');

1 row created.

SQL> insert into employee values('M','102');

1 row created.

SQL> insert into employee values('M','103');

1 row created.

SQL> insert into employee values('M','104');

1 row created.

SQL> insert into employee values('M','105');

1 row created.

SQL> commit;

Commit complete.

SQL> create unique index idx_uni_emp on employee(employee_id);

Index created.

SQL> select * from employee where employee_id=100;

G EMPLOYEE_ID

- -----------

F  100

SQL> select plan_table_output from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------

SQL_IDbum8qv24s6tqp, child number 0

-------------------------------------

select * from employee where employee_id=100

Plan hash value: 1887894887

-------------------------------------------------------------------------------------------

| Id  | Operation    | Name  | Rows  | Bytes | Cost (%CPU)| Time  |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |  |  |  |1 (100)|  |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE  |1 |    15 |1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------

|*  2 |   INDEX UNIQUE SCAN    | IDX_UNI_EMP |1 |  |0   (0)|  |

-------------------------------------------------------------------------------------------

    從上述顯示內(nèi)容可以看出，此sql的執(zhí)行計(jì)劃走的是索引IDX_UNI_EMP的索引唯一掃描，索引唯一掃描在執(zhí)行計(jì)劃中對(duì)應(yīng)的關(guān)鍵字就是“INDEX UNIQUE SCAN”.

  Drop掉上述唯一索引IDX_UNI_EMP

SQL> drop index idx_uni_emp;

Index dropped.

SQL> create index idx_emp_1 on employee(employee_id);

Index created.

SQL> select * from employee where employee_id=100;

G EMPLOYEE_ID

- -----------

F  100

SQL> select plan_table_output from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------

SQL_IDbum8qv24s6tqp, child number 0

-------------------------------------

select * from employee where employee_id=100

Plan hash value: 2428325319

-----------------------------------------------------------------------------------------

| Id  | Operation    | Name| Rows| Bytes | Cost (%CPU)| Time|

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    ||||     2 (100)||

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE|     1 |    15 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------

|*  2 |   INDEX RANGE SCAN    | IDX_EMP_1 |     1 ||     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

  從上述顯示內(nèi)容中可以看出，現(xiàn)在改sql的執(zhí)行計(jì)劃走是對(duì)索引idx_emp_1的索引范圍掃描，索引范圍掃描在執(zhí)行計(jì)劃中對(duì)應(yīng)的關(guān)鍵字就是“INDEX RANGE SCAN”。

    truncate表EMPLOYEE中的數(shù)據(jù)：

SQL> truncate table employee;

Table truncated.

更新插入10000條記錄：

SQL> begin

  2  for i in 1..5000 loop

  3  insert into employee values('F',i);

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> begin

  2  for i in 5001..10000 loop

  3  insert into employee values('M',i);

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> select gender,count(*) from employee group by gender;

G   COUNT(*)

- ----------

M5000

F5000

對(duì)表EMPLOYEE收集一下統(tǒng)計(jì)信息：

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMPLOYEE',estimate_percent=>100,cascade=>TRUE,no_invalidate=>false,method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly

SQL> show user

USER is "SCOTT"

SQL> select employee_id from employee;

10000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2119105728

------------------------------------------------------------------------------

| Id  | Operation  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |     | 10000 | 40000 |   7   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| EMPLOYEE | 10000 | 40000 |   7   (0)| 00:00:01 |

------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

 1  recursive calls

 0  db block gets

689  consistent gets

 0  physical reads

 0  redo size

     174308  bytes sent via SQL*Net to client

       7850  bytes received via SQL*Net from client

668  SQL*Net roundtrips to/from client

 0  sorts (memory)

 0  sorts (disk)

      10000  rows processed

 明明上述SQL查詢字段employee_id可以通過(guò)掃描索引idx_emp_1得到，但oracle依然選擇了對(duì)employee的全表掃描。

    此時(shí)就算我們使用Hint強(qiáng)制讓oracle掃描索引idx_emp_1，從如下結(jié)果可以看到，oracle依然選擇了對(duì)表employee的全表掃描（即hint失效了）

SQL> select /* index(employee idx_emp_1)*/employee_id from employee;

10000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2119105728

------------------------------------------------------------------------------

| Id  | Operation  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |     | 10000 | 40000 |   7   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| EMPLOYEE | 10000 | 40000 |   7   (0)| 00:00:01 |

------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

 1  recursive calls

 0  db block gets

689  consistent gets

 0  physical reads

 0  redo size

     174308  bytes sent via SQL*Net to client

       7850  bytes received via SQL*Net from client

668  SQL*Net roundtrips to/from client

 0  sorts (memory)

 0  sorts (disk)

      10000  rows processed

 會(huì)出現(xiàn)上述現(xiàn)象是因?yàn)閛racle無(wú)論如何總會(huì)保證目標(biāo)sql結(jié)果的正確性，可能會(huì)得到錯(cuò)誤結(jié)果的執(zhí)行路徑orale是不會(huì)考慮的。對(duì)于idx_emp_1而言，它是一個(gè)單鍵值的B樹(shù)索引，索引NULL值不會(huì)存儲(chǔ)在其中，那么一旦列employee_id中出現(xiàn)null值（雖然這里實(shí)際上并沒(méi)有null值），則掃描索引IDX_EMP_1的結(jié)果就會(huì)漏掉那些employee_id為NULL的值，這也就意味這個(gè)如果orale在執(zhí)行上述sql選擇了掃描索引idx_emp_1，那么執(zhí)行結(jié)果就可能是不準(zhǔn)的。這種情況下，oracle當(dāng)然不會(huì)考慮掃描idx_emp_1，即使我們使用Hint。

    如果這里我們想讓oracle在執(zhí)行上述sql掃描索引idx_emp_1，則必須將列employee_id的屬性改成not null。這就相當(dāng)于告訴oracle，這里列employee_id上不會(huì)有null值，你就放心的掃描idx_emp_1吧。

SQL> select employee_id from employee;

10000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3918702848

----------------------------------------------------------------------------------

| Id  | Operation     | Name | Rows  | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     | | 10000 | 40000 |     7   (0)| 00:00:01 |

|   1 |  INDEX FAST FULL SCAN| IDX_EMP_1 | 10000 | 40000 |     7   (0)| 00:00:01 |

----------------------------------------------------------------------------------

從上述顯示內(nèi)容可以看出，現(xiàn)在sql的執(zhí)行計(jì)劃走的是idx_emp_1的索引快速全掃描，索引快速全掃描在執(zhí)行計(jì)劃中對(duì)應(yīng)的關(guān)鍵字就是“INDEX FAST FULL SCAN”

現(xiàn)在我們加上強(qiáng)制索引IDX_EMP_1的hint，再次執(zhí)行該sql：

SQL> select /*+ index(employee idx_emp_1) */ employee_id from employee;

10000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 438557521

------------------------------------------------------------------------------

| Id  | Operation | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |     | 10000 | 40000 |  20   (0)| 00:00:01 |

|   1 |  INDEX FULL SCAN | IDX_EMP_1 | 10000 | 40000 |  20   (0)| 00:00:01 |

------------------------------------------------------------------------------

從上述顯示內(nèi)容可以看出，現(xiàn)在SQL的執(zhí)行計(jì)劃走的是對(duì)索引idx_emp_1的索引全掃描，索引全掃描在執(zhí)行計(jì)劃中對(duì)應(yīng)的關(guān)鍵字就是“INDEX FULL SCAN”.

DROP掉單鍵值B樹(shù)索引IDX_EMP_1；

SQL> drop index idx_emp_1;

Index dropped.

SQL> create index index_emp_2 on employee(gender,employee_id);

Index created.

SQL> set autot trace

SQL> select * from employee where employee_id=101;

Execution Plan

----------------------------------------------------------

Plan hash value: 2052968723

--------------------------------------------------------------------------------

| Id  | Operation | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |       |     1 |     6 |     3 (0)| 00:00:01 |

|*  1 |  INDEX SKIP SCAN | INDEX_EMP_2 |     1 |     6 |     3 (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("EMPLOYEE_ID"=101)

       filter("EMPLOYEE_ID"=101)

Statistics

----------------------------------------------------------

 0  recursive calls

 0  db block gets

 6  consistent gets

 0  physical reads

 0  redo size

600  bytes sent via SQL*Net to client

524  bytes received via SQL*Net from client

 2  SQL*Net roundtrips to/from client

 0  sorts (memory)

 0  sorts (disk)

 1  rows processed

從上述顯示內(nèi)容可以看出，現(xiàn)在sql的執(zhí)行計(jì)劃走的是對(duì)索引IDX_EMP_2的索引跳躍式掃描，索引跳躍式掃描在執(zhí)行計(jì)劃中對(duì)應(yīng)的關(guān)鍵字就是“INDEX SKIP SCAN”.

以上是“Oracle里的常見(jiàn)執(zhí)行計(jì)劃有哪些”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！