oracle sql调优学习笔记（四）各种index access的方式

select /*+ index(employee IDX_EMP_1) */employee_id from employee;

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 10000 | 40000 |    16   (7)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEE | 10000 | 40000 |    16   (7)| 00:00:01 |
------------------------------------------------------------------------------

计算成本，全盘扫描更合适

alter table employee modify(employee_id not null);

select employee_id from employee;

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           | 10000 | 40000 |     6   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| IDX_EMP_1 | 10000 | 40000 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------------

select /*+ index(employee IDX_EMP_1) */employee_id from employee;

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           | 10000 | 40000 |    21   (5)| 00:00:01 |
|   1 |  INDEX FULL SCAN | IDX_EMP_1 | 10000 | 40000 |    21   (5)| 00:00:01 |
------------------------------------------------------------------------------

select /*+ index_ffs(employee IDX_EMP_1) */employee_id from employee;对指定的表执行快速全索引扫描,而不是全表扫描的办法.

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           | 10000 | 40000 |     6   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| IDX_EMP_1 | 10000 | 40000 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------------

drop index idx_emp_1;

create index idx_emp_2 on employee(gender,employee_id);

select * from employee where employee_id=101; SKIP SCAN是当索引的第一列不是where条件里的列,但索引的确包含了where条件里的列名,那么这时候就会用到skip scan,字面意思理解就是跳过没用到的列,抵达where条件引用的列名上!

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |     5 |     3   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | IDX_EMP_2 |     1 |     5 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

select * from employee where employee_id=100;

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

select * from employee where employee_id is null;

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |       |       |    15 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMPLOYEE |     2 |    30 |    15   (0)| 00:00:01 |