4.访问索引的方法
1.索引唯一性扫描
仅适用于where条件里是等值查询的目标SQL，因为扫描的对象是唯一性索引，所以索引唯一性扫描的结果之多只会返回一条记录
2.索引范围扫描
目标SQL的where条件一定是范围查询（谓词条件为BETWEEN,<,>等）,当扫描的对象是非唯一性索引时，对目标SQL的where条件没有限制。索引范围扫描的结果可能会返回多条记录，其实就是索引范围扫描中范围，在同等条件下，当目标索引的索引行的数量大于1时，索引范围扫描所耗费的逻辑读至少会比响应的索引唯一性扫描的逻辑读多1
scott@ORCLPDB01 2023-04-02 10:15:22> create table emp_temp as select * from emp;
Table created.
Elapsed: 00:00:00.14
scott@ORCLPDB01 2023-04-02 10:15:45> create unique index idx_emp_temp on emp_temp(empno);
Index created.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 10:19:44> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP_TEMP',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.34
--清空库缓存
sys@ORCL 2023-04-02 10:15:30> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.16
sys@ORCL 2023-04-02 10:16:41> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-02 10:19:52> set autot trace
scott@ORCLPDB01 2023-04-02 10:20:20> select * from emp_temp where empno = 7369;
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 3451700904
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IDX_EMP_TEMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7369)
Statistics
----------------------------------------------------------
150 recursive calls
43 db block gets
217 consistent gets
32 physical reads
6604 redo size
961 bytes sent via SQL*Net to client
613 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
1 rows processed
scott@ORCLPDB01 2023-04-02 10:20:34> drop index IDX_EMP_TEMP;
Index dropped.
Elapsed: 00:00:00.12
scott@ORCLPDB01 2023-04-02 10:20:52> create index idx_emp_temp on emp_temp(empno);
Index created.
Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-02 10:21:02> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP_TEMP',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.26
--清空库缓存
sys@ORCL 2023-04-02 10:15:30> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.16
sys@ORCL 2023-04-02 10:16:41> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-02 10:21:23> select * from emp_temp where empno = 7369;
Elapsed: 00:00:00.10
Execution Plan
----------------------------------------------------------
Plan hash value: 1638992559
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP_TEMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP_TEMP | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7369)
Statistics
----------------------------------------------------------
169 recursive calls
0 db block gets
275 consistent gets
36 physical reads
0 redo size
1105 bytes sent via SQL*Net to client
407 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
27 sorts (memory)
0 sorts (disk)
1 rows processed3.索引全扫描
扫描目标索引所有叶子快的所有索引行。
scott@ORCLPDB01 2023-04-02 10:32:41> set autot trace
scott@ORCLPDB01 2023-04-02 10:32:46> select empno from emp;
14 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 179099197
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
710 bytes sent via SQL*Net to client
383 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processedsorts（memory）和sorts（disk）的值均为0，说明已经按照empno排序好了
4.索引快速全扫描
4.1索引快速扫描只适用于CBO
4.2索引快速全扫描可以使用多块读，也可以并行执行
4.3索引快速全扫描的执行结果并不一定是有序的
scott@ORCLPDB01 2023-04-02 10:42:19> create table emp_test(empno number,col1 char(2000),col2 char(2000),col3 char(2000));
Table created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 10:42:29> alter table emp_test add constraint pk_emp_test primary key (empno,col1,col2,col3);
Table altered.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 10:42:37> insert into emp_test select empno,ename,job,'A' from emp;
14 rows created.
Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-02 10:43:13> insert into emp_test select empno,ename,job,'B' from emp;
14 rows created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 10:43:20> insert into emp_test select empno,ename,job,'C' from emp;
14 rows created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 10:43:28> insert into emp_test select empno,ename,job,'D' from emp;
14 rows created.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 10:43:33> insert into emp_test select empno,ename,job,'E' from emp;
14 rows created.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 10:43:37> insert into emp_test select empno,ename,job,'F' from emp;
14 rows created.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 10:43:46> insert into emp_test select empno,ename,job,'G' from emp;
14 rows created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 10:43:49> commit;
Commit complete.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 10:43:52> select count(*) from emp_test;
COUNT(*)
----------
98
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 10:44:08> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP_TEST',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.31
scott@ORCLPDB01 2023-04-02 10:45:40> set autot on;
scott@ORCLPDB01 2023-04-02 10:48:20> select /* index_ffs(emp_test pk_emp_test) */ empno from emp_test;
EMPNO
----------
7499
7521
7369
7839
7844
7566
7654
7698
7782
7788
7876
7900
7934
7369
7499
7902
7521
7566
7654
7698
7782
7788
7876
7839
7844
7900
7902
7934
7369
7499
7654
7566
7521
7698
7782
7844
7839
7788
7876
7900
7369
7499
7521
7934
7902
7698
7876
7782
7788
7839
7844
7566
7654
7902
7934
7369
7900
7521
7499
7782
7788
7839
7844
7876
7566
7900
7654
7902
7698
7934
7934
7844
7934
7698
7369
7369
7499
7521
7499
7876
7566
7782
7654
7521
7698
7782
7900
7788
7566
7788
7839
7844
7876
7902
7900
7654
7839
7902
98 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3550420785
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 392 | 28 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| PK_EMP_TEST | 98 | 392 | 28 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
251 consistent gets
0 physical reads
0 redo size
2296 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
98 rows processed5.索引跳跃扫描
5.1.扫描该索引时跳过了它的前导列，直接从该索引的非前导列开始扫描
scott@ORCLPDB01 2023-04-02 10:50:00> create table employee(gender varchar2(1),employee_id number);
Table created.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 10:50:34> alter table employee modify(employee_id not null);
Table altered.
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 10:51:06> create index idx_employee on employee(gender,employee_id);
Index created.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 10:51:42> 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.09
scott@ORCLPDB01 2023-04-02 10:52:35> 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.18
scott@ORCLPDB01 2023-04-02 10:54:11> set autot trace;
scott@ORCLPDB01 2023-04-02 10:54:15> select * from employee where employee_id = 10;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2119105728
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEE | 1 | 15 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=10)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
49 consistent gets
0 physical reads
0 redo size
631 bytes sent via SQL*Net to client
411 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
scott@ORCLPDB01 2023-04-02 10:54:31> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMPLOYEE',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
scott@ORCLPDB01 2023-04-02 10:55:39> select * from employee where employee_id = 10;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 461756150
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IDX_EMPLOYEE | 1 | 6 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPLOYEE_ID"=10)
filter("EMPLOYEE_ID"=10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
631 bytes sent via SQL*Net to client
411 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
