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

小编给大家分享一下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 AAAVREAAEAAAACXAAJSQL> 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 |从上述显示内容中可以看出，目标sql的执行计划走的是对表EMP的全表扫描，全表扫描在执行计划中对应的关键字就是“TABLE ACCESS FULL”将上述sql改写成以指定的ROWID的方式执行：SQL> select empno,ename from emp where rowid=’AAAVREAAEAAAACXAAJ’; EMPNO ENAME———- ———- 7844 TURNERSQL> 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 |从上述显示内容可以看出，现在该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 SMITHSQL> 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=7369Plan 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)| |————————————————————————————–从上述显示内容可以看出，此时目标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 100SQL> 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=100Plan 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)| |——————————————————————————————-从上述显示内容可以看出，此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 100SQL> 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=100Plan 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 |—————————————————————————————– 从上述显示内容中可以看出，现在改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(*)- ———-M5000F5000对表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 traceonlySQL> show userUSER 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 gets689 consistent gets 0 physical reads 0 redo size 174308 bytes sent via SQL*Net to client 7850 bytes received via SQL*Net from client668 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——————————————————————————| 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 gets689 consistent gets 0 physical reads 0 redo size 174308 bytes sent via SQL*Net to client 7850 bytes received via SQL*Net from client668 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———————————————————————————-| 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 |———————————————————————————-从上述显示内容可以看出，现在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——————————————————————————| 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 |——————————————————————————从上述显示内容可以看出，现在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 traceSQL> 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 size600 bytes sent via SQL*Net to client524 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里的常见执行计划有哪些”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注百云行业资讯频道！

相关推荐: 什么是Redis以及其作用

什么是Redis以及其作用，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。Redis，英文全称是Remote Dictionary Server（远程字典服务），是一个开源的使用ANSI C语言编写…