Oracle里的常见执行计划有哪些

58次阅读

共计 11814 个字符，预计需要花费 30 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下 Oracle 里的常见执行计划有哪些，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

与表访问相关的执行计划

Oracle 数据库里面与表访问有关的的两种方法：全表扫描和 ROWID 扫描。反应在执行计划上，与全表扫描对应的执行计划中的关键字是“TABLE ACCESS FULL”, 与 ROWID 扫描对应的执行计划中的关键字是 TABLE ACCESS BY USER ROWID 或“TABLE ACCESS BY INDEX ROWID”.

我们来看一下与表访问的相关的执行计划，先执行如下 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

————————————————————————–

————————————————————————–

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

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

从上述显示内容中可以看出，目标 sql 的执行计划走的是对表 EMP 的全表扫描，全表扫描在执行计划中对应的关键字就是“TABLE ACCESS FULL”

将上述 sql 改写成以指定的 ROWID 的方式执行：

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

———————————————————————————–

———————————————————————————–

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

| 1 | TABLE ACCESS BY USER ROWID| EMP |1 | 22 |1 (0)| 00:00:01 |

从上述显示内容可以看出，现在该 sql 的执行计划走的对表 emp 的 rowid 扫描，其对应的执行计划关键字“TABLE ACCESS BY USER ROWID”。

注意，ROWID 扫描所对应的的执行计划的关键字还有可能是“TABLE ACCESS BY INDEX ROWID”, 这取决于访问表时的 ROWID 来源。如果 ROWID 是来源于用户手工指定（例如上述指定“select empno,ename from emp where rowid= AAAVREAAEAAAACXAAJ”），则对应的执行计划关键字是“TABLE ACCESS BY USER ROWID”；如果是 ROWID 是来源于索引，则对应的执行计划关键字是“TABLE ACCESS BY INDEX ROWID”

表 EMP 的主键是列 EMPNO（即列 EMPNO 上有主键索引），我们将目标 sql 改写成如下形式后执行：

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

————————————————————————————–

————————————————————————————–

| 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)| |

————————————————————————————–

从上述显示内容可以看出，此时目标 sql 的执行计划在访问表 emp 走的是对表 emp 的 rowid 扫描，因为这里的 rowid 是来源于索引 PK_emp, 所以其对应的执行计划关键字是“TABLE ACCESS BY INDEX ROWID”.

2. 与 B 树索引相关的执行计划

常见的与 B 树索引访问相关的方法有：包括索引唯一扫描，索引范围扫描，索引全扫描，索引快速全扫描和索引跳跃式扫描，反应在执行计划上，与索引唯一扫描对应的关键字“INDEX UNIQUE SCAN”, 与索引范围扫描对应的关键字是“INDEX RANGE SCAN”, 与索引全扫描对应的关键字是“INDEX FULL SCAN”, 与索引快速全扫描对应的关键字是“INDEX FAST FULL SCAN”, 与索引跳跃式扫描对应的关键字是“INDEX SKIP SCAN”.

下面来看一下与 B 树索引访问相关的的执行计划实例。创建一个测试表 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

——————————————————————————————-

——————————————————————————————-

| 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)| |

——————————————————————————————-

从上述显示内容可以看出，此 sql 的执行计划走的是索引 IDX_UNI_EMP 的索引唯一扫描，索引唯一扫描在执行计划中对应的关键字就是“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

—————————————————————————————–

—————————————————————————————–

| 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 |

—————————————————————————————–

从上述显示内容中可以看出，现在改 sql 的执行计划走是对索引 idx_emp_1 的索引范围扫描，索引范围扫描在执行计划中对应的关键字就是“INDEX RANGE SCAN”。

truncate 表 EMPLOYEE 中的数据：

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

对表 EMPLOYEE 收集一下统计信息：

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

——————————————————————————

——————————————————————————

| 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 可以通过扫描索引 idx_emp_1 得到，但 oracle 依然选择了对 employee 的全表扫描。

此时就算我们使用 Hint 强制让 oracle 扫描索引 idx_emp_1，从如下结果可以看到，oracle 依然选择了对表 employee 的全表扫描（即 hint 失效了）

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

10000 rows selected.

Execution Plan

———————————————————-

Plan hash value: 2119105728

——————————————————————————

——————————————————————————

| 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

会出现上述现象是因为 oracle 无论如何总会保证目标 sql 结果的正确性，可能会得到错误结果的执行路径 orale 是不会考虑的。对于 idx_emp_1 而言，它是一个单键值的 B 树索引，索引 NULL 值不会存储在其中，那么一旦列 employee_id 中出现 null 值（虽然这里实际上并没有 null 值），则扫描索引 IDX_EMP_1 的结果就会漏掉那些 employee_id 为 NULL 的值，这也就意味这个如果 orale 在执行上述 sql 选择了扫描索引 idx_emp_1，那么执行结果就可能是不准的。这种情况下，oracle 当然不会考虑扫描 idx_emp_1，即使我们使用 Hint。

如果这里我们想让 oracle 在执行上述 sql 扫描索引 idx_emp_1，则必须将列 employee_id 的属性改成 not null。这就相当于告诉 oracle，这里列 employee_id 上不会有 null 值，你就放心的扫描 idx_emp_1 吧。

SQL select employee_id from employee;

10000 rows selected.

Execution Plan

———————————————————-

Plan hash value: 3918702848

———————————————————————————-

———————————————————————————-

| 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 |

———————————————————————————-

从上述显示内容可以看出，现在 sql 的执行计划走的是 idx_emp_1 的索引快速全扫描，索引快速全扫描在执行计划中对应的关键字就是“INDEX FAST FULL SCAN”

现在我们加上强制索引 IDX_EMP_1 的 hint，再次执行该 sql：

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

10000 rows selected.

Execution Plan

———————————————————-

Plan hash value: 438557521

——————————————————————————

——————————————————————————

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

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

——————————————————————————

从上述显示内容可以看出，现在 SQL 的执行计划走的是对索引 idx_emp_1 的索引全扫描，索引全扫描在执行计划中对应的关键字就是“INDEX FULL SCAN”.

DROP 掉单键值 B 树索引 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

——————————————————————————–

——————————————————————————–

| 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

从上述显示内容可以看出，现在 sql 的执行计划走的是对索引 IDX_EMP_2 的索引跳跃式扫描，索引跳跃式扫描在执行计划中对应的关键字就是“INDEX SKIP SCAN”.

以上是“Oracle 里的常见执行计划有哪些”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！

正文完

发表至：数据库

2023-07-20

转载说明：除特殊说明外本站除技术相关以外文章皆由网络搜集发布，转载请注明出处。

MySQL中怎么实现索引和锁

MySQL日志之redo log和binlog的区别是什么

redis除了缓存还可以用来做哪些事情

mysql日期函数的用法

Uber用Go重写Schemaless数据库的分片层分析