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

  1. 索引唯一扫描,索引范围扫描,索引全扫描,索引快速全扫描和索引跳跃式扫描。

    创新互联建站长期为1000+客户提供的网站建设服务,团队从业经验10年,关注不同地域、不同群体,并针对不同对象提供差异化的产品和服务;打造开放共赢平台,与合作伙伴共同营造健康的互联网生态环境。为轮台企业提供专业的成都做网站、成都网站建设,轮台网站改版等技术服务。拥有10年丰富建站经验和众多成功案例,为您定制开发。

    索引唯一扫描:

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> insert into employee values('F',106);
1 row created.
SQL> commit;
Commit complete.
SQL> create unique index idx_unqi_emp on employee(employee_id);
Index created.
SQL> select * from employee where employee_id=100;
G EMPLOYEE_ID
- -----------
F  100
SQL> set lines 200 pagesize 1000
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: 1037614268
--------------------------------------------------------------------------------------------
| 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 |
|*  2 |   INDEX UNIQUE SCAN    | IDX_UNQI_EMP | 1 |   | 0   (0)|   |
---------------------------------------------------------------------------------
31 rows selected.

索引范围扫描:

SQL> drop index idx_unqi_emp;
Index dropped.
SQL> create index idx_unqi_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: 407794244
--------------------------------------------------------------------------------------------
| 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 |
|*  2 |   INDEX RANGE SCAN    | IDX_UNQI_EMP | 1 |   | 1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

索引快速全扫描:

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
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                tabname          => 'EMPLOYEE',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size repeat',
                                no_invalidate    => FALSE,
                                degree           => 8,
                                granularity      => 'ALL',
                                cascade          => TRUE);
 END;
PL/SQL procedure successfully completed.
SQL> set autot trace
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 |
------------------------------------------------------------------------------

提示走索引,无效,因为employee_id有null值:

SQL> create index idx_emp_1 on employee(employee_id);
Index created.
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 |
------------------------------------------------------------------------------

建立组合索引,或许把employee_id限制为非空:

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 |  25   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | IDX_EMP_1 | 10000 | 40000 |  25   (0)| 00:00:01 |
------------------------------------------------------------------------------

索引跳跃扫描:
SQL> create index idx_emp_1 on employee(gender,employee_id);
Index created.
SQL> select * from employee where employee_id=109;
Execution Plan----------------------------------------------------------Plan hash value: 2039022311
------------------------------------------------------------------------------| Id  | Operation  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0 | SELECT STATEMENT |      |    1 |    6 |    3   (0)| 00:00:01 ||*  1 |  INDEX SKIP SCAN | IDX_EMP_1 |    1 |    6 |    3   (0)| 00:00:01 |------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
   1 - access("EMPLOYEE_ID"=109)       filter("EMPLOYEE_ID"=109)
Statistics----------------------------------------------------------   1  recursive calls   0  db block gets   6  consistent gets   3  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

当前标题:与B树索引相关的执行计划
链接分享:http://bzwzjz.com/article/iehooh.html

其他资讯

Copyright © 2007-2020 广东宝晨空调科技有限公司 All Rights Reserved 粤ICP备2022107769号
友情链接: 营销网站建设 古蔺网站建设 专业网站设计 网站建设推广 成都企业网站设计 成都营销网站建设 网站制作 成都网站设计 成都网站建设 成都网站建设 网站建设 成都网站设计 四川成都网站建设 重庆网站制作 高端网站设计推广 响应式网站设计 成都网站建设 移动网站建设 成都网站设计 手机网站建设套餐 LED网站设计方案 定制网站设计