索引唯一扫描，索引范围扫描，索引全扫描，索引快速全扫描和索引跳跃式扫描。
索引唯一扫描：
?
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