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

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

  1. 与表访问相关的执行计划

    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

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

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

    从上述显示内容可以看出,现在该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

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

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

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

    从上述显示内容可以看出,此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

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

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

– ———-

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

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

| 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可以通过扫描索引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 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

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

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

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

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

原创文章,作者:carmelaweatherly,如若转载,请注明出处:https://blog.ytso.com/205007.html

(0)
上一篇 2021年11月29日
下一篇 2021年11月29日

相关推荐

发表回复

登录后才能评论