与B树索引相关的执行计划
索引唯一扫描，索引范围扫描，索引全扫描，索引快速全扫描和索引跳跃式扫描。
创新互联建站长期为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树索引相关的执行计划
URL网址：http://myzitong.com/article/iehooh.html