索引唯一扫描：index unique scan
scott@ORCLPDB01 2023-04-02 22:44:32> create table employee(gender varchar2(1),employee_id number);
Table created.
Elapsed: 00:00:00.05
scott@ORCLPDB01 2023-04-02 22:45:05> insert into employee values('F','99');
1 row created.
Elapsed: 00:00:00.03
scott@ORCLPDB01 2023-04-02 22:46:56> insert into employee values('F','100');
1 row created.
Elapsed: 00:00:00.10
scott@ORCLPDB01 2023-04-02 22:47:02> insert into employee values('M','101');
1 row created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:09> insert into employee values('M','102');
1 row created.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 22:47:12> insert into employee values('M','103');
1 row created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:15> insert into employee values('M','104');
1 row created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:17> insert into employee values('M','105');
1 row created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:22> commit;
Commit complete.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:25> create unique index idx_uni_emp on employee(employee_id);
Index created.
Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-02 22:50:13> select * from employee where employee_id = 100;
G EMPLOYEE_ID
- -----------
F 100
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:50:40> set autot trace;
scott@ORCLPDB01 2023-04-02 22:50:52> select * from employee where employee_id = 100;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1887894887
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 15 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IDX_UNI_EMP | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
491 bytes sent via SQL*Net to client
397 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed索引范围扫描：index range scan
scott@ORCLPDB01 2023-04-02 22:53:11> select * from employee where employee_id = 100;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3410127368
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEE | 1 | 15 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_UNI_EMP | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
635 bytes sent via SQL*Net to client
412 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed索引全扫描：index full scan
scott@ORCLPDB01 2023-04-02 22:53:13> truncate table employee;
Table truncated.
Elapsed: 00:00:00.04
scott@ORCLPDB01 2023-04-02 22:54:00> 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.
Elapsed: 00:00:00.08
scott@ORCLPDB01 2023-04-02 22:54:42> 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.
Elapsed: 00:00:00.08
scott@ORCLPDB01 2023-04-02 22:55:45> select gender,count(*) from employee group by gender;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1624656943
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 20000 | 8 (13)| 00:00:01 |
| 1 | HASH GROUP BY | | 10000 | 20000 | 8 (13)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEE | 10000 | 20000 | 7 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
684 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
scott@ORCLPDB01 2023-04-02 22:56:20> set autot off;
scott@ORCLPDB01 2023-04-02 22:56:30> select gender,count(*) from employee group by gender;
G COUNT(*)
- ----------
M 5000
F 5000
Elapsed: 00:00:00.00
22:58:37> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMPLOYEE',estimate_percent=>100,cascade=>true,no_invalidate=>false,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.31
scott@ORCLPDB01 2023-04-02 22:58:48> set autot trace
scott@ORCLPDB01 2023-04-02 22:59:02> select employee_id from employee;
10000 rows selected.
Elapsed: 00:00:00.03
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 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
689 consistent gets
0 physical reads
0 redo size
179660 bytes sent via SQL*Net to client
7724 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
scott@ORCLPDB01 2023-04-02 22:59:19> select /*+ index(employee idx_uni_emp) */ employee_id from employee;
10000 rows selected.
Elapsed: 00:00:00.04
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 |
------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / EMPLOYEE@SEL$1
U - index(employee idx_uni_emp)
Statistics
----------------------------------------------------------
43 recursive calls
0 db block gets
745 consistent gets
0 physical reads
0 redo size
179660 bytes sent via SQL*Net to client
7976 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
10000 rows processed索引快速全扫描：index fast full scan
scott@ORCLPDB01 2023-04-02 23:01:08> alter table employee modify(employee_id not null);
Table altered.
Elapsed: 00:00:00.05
scott@ORCLPDB01 2023-04-02 23:03:53> select employee_id from employee;
10000 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 114952492
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 40000 | 7 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| IDX_UNI_EMP | 10000 | 40000 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
709 consistent gets
0 physical reads
0 redo size
179660 bytes sent via SQL*Net to client
7724 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10000 rows processed
scott@ORCLPDB01 2023-04-02 23:04:07> select /*+ index(employee idx_uni_emp) */ employee_id from employee;
10000 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3257444309
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 40000 | 20 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | IDX_UNI_EMP | 10000 | 40000 | 20 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
685 consistent gets
0 physical reads
0 redo size
179660 bytes sent via SQL*Net to client
7755 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed索引跳跃式扫描：index skip scan
scott@ORCLPDB01 2023-04-02 23:05:32> create index idx_emp on employee(gender,employee_id);
Index created.
Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-02 23:06:15> select * from employee where employee_id = 101;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1432429342
----------------------------------------------------------------------------
| 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 | 6 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPLOYEE_ID"=101)
filter("EMPLOYEE_ID"=101)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
13 physical reads
0 redo size
632 bytes sent via SQL*Net to client
412 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed