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

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

   索引唯一扫描：

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