数据库基本功之索引介绍

最新推荐文章于 2023-07-30 21:45:07 发布

DB架构

最新推荐文章于 2023-07-30 21:45:07 发布

阅读量664

点赞数

分类专栏： Oracle 数据库语言基础文章标签：数据结构 oracle

本文链接：https://blog.csdn.net/u011868279/article/details/129744733

版权

Oracle 数据库语言基础专栏收录该内容

13 篇文章 0 订阅

订阅专栏

1.索引结构及特点

1.1 B-树索引结构

B-树索引结构有3个基本组成部分:根节点、分支节点和叶子节点.其中根节点位于索引结构的顶端,而叶子节点位于索引结构的最底端,中间为分支节点。

1.1.1 根节点(Branch node)

一个B树索引只有一个根节点,它实际就是位于树的顶端的分支节点

1.1.2 分支节点(Branch node)包括根节点块

包含的条目指向索引里其他的分支节点或者是叶子节点.(即不止三层,一般效率最高的是三层)

a) 其所包含的索引条目都是按照顺序排列的(缺省是升序排列,也可以在创建索引时指定为降序排列).

b) 每个索引条目(也可以叫做每条记录)都具有两个字段.第一个字段表示当前该分支节点块下面所链接的索引块中所包含的最小键值;第二个字段为四个字节,表示所链接的索引块的地址,该地址指向下面一个索引块.

c) 在一个分支节点块中所能容纳的记录行数由数据块大小以及索引键值的长度决定.比如从上图一可以看到,对于根节点块来说,包含三条记录,分别为(0 B1)、(500 B2)、(1000 B3),它们指向三个分支节点块.其中的0、500和1000分别表示这三个分支节点块所链接的键值的最小值.而B1、B2和B3则表示所指向的三个分支节点块的地址。

1.1.3 叶子节点(Leaf node)

包含条目直接指向表里的数据行.

a) B树索引所有的叶子块位于同一层上,这是由B树的数据结构定义的.Oracle 设计的B树索引结构保证了B 树索引从根到叶子都有相等的分支节点,保证了B树索引的平衡,这样就不会因为基表的数据插入后删除操作造成B树索引变得不平衡,从而影响索引的性能.因此,从根块到达任何一个叶子块的遍历代价都是相同的;

索引高度是指从根块到达叶子块时所遍历的数据块的个数.通常,大多数的B树索引的高度都是2到3,也就意味着,即使表中有上百万条记录,从索引中定位一个键值只需要2或3次I/O,索引越高,性能越差;
b) 叶子节点所包含的索引条目与分支节点一样,都是按照顺序排列的(缺省是升序排列,也可以在创建索引时指定为降序排列)

c) 每个索引条目(也可以叫做每条记录)也具有两个字段.第一个字段表示索引的键值,对于单列索引来说是一个值;而对于多列索引来说则是多个值组合在一起的.第二个字段表示键值所对应的记录行的ROWID,该ROWID是记录行在表里的物理地址.ROWID 是唯一的Oracle 指针,指向该行的物理位置,使用ROWID 是Oracle 数据库中访问行最快的方法.

d) 叶子节点是一个双向链表,每个叶子节点包含一个指向下一个和上一个叶子点的指针,这在一定范围内便利索引以搜索需要的记录.

[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 24 09:55:43 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show user;
USER is "SYS"
SQL> alter session set container=PDB1;

Session altered.

SQL> 
SQL> conn scott/tiger@PDB1
Connected.
SQL> 
SQL> set pagesize 200 linesize 200
SQL> 
SQL> select rowid,sal from emp;

ROWID                     SAL
------------------ ----------
AAAR1HAAMAAAACTAAA        800
AAAR1HAAMAAAACTAAB       1600
AAAR1HAAMAAAACTAAC       1250
AAAR1HAAMAAAACTAAD       2975
AAAR1HAAMAAAACTAAE       1250
AAAR1HAAMAAAACTAAF       2850
AAAR1HAAMAAAACTAAG       2450
AAAR1HAAMAAAACTAAH       3000
AAAR1HAAMAAAACTAAI       1500
AAAR1HAAMAAAACTAAJ       1100
AAAR1HAAMAAAACTAAK        950
AAAR1HAAMAAAACTAAL       3000
AAAR1HAAMAAAACTAAM       1300
AAAR1HAAMAAAACTAAN       5000

14 rows selected.

SQL>

1.1.4 知识点

叶块之间使用双向链连接,(为了范围索引index range scan).
删除表行时,索引叶块也会更新,但只是逻辑更改,并不做物理的删除叶块.
索引叶块中不保存表行键值的null信息

1.2 位图索引结构

位图索引适用于离散度较低的列(如男、女)
它的叶块中存放key, start rowid-end rowid,并应用一个函数把位图bitmap中相应key置1,位图索引在逻辑or时效率最高.
如图中四种值(Blue\Green\Red\Yellow),Red在表中对应的bitmap值1.
左边的Index图也是根节点、分支节电、叶子结点

SQL> 
SQL> select * from emp1;

     EMPNO ENAME                          JOB                                MGR HIREDATE               SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------
      7369 SMITH                          CLERK                             7902 17-DEC-80             1600        300         20
      7499 ALLEN                          SALESMAN                          7698 20-FEB-81             1600        300         30
      7521 WARD                           SALESMAN                          7698 22-FEB-81             1250        500         30
      7566 JONES                          MANAGER                           7839 02-APR-81             2975                    20
      7654 MARTIN                         SALESMAN                          7698 28-SEP-81             1250       1400         30
      7698 BLAKE                          MANAGER                           7839 01-MAY-81             2850                    30
      7782 CLARK                          MANAGER                           7839 09-JUN-81             1500        300         10
      7788 SCOTT                          ANALYST                           7566 24-JAN-87             3000                    20
      7844 TURNER                         SALESMAN                          7698 08-SEP-81             1500          0         30
      7876 ADAMS                          CLERK                             7788 02-APR-87             1100                    20
      7900 JAMES                          CLERK                             7698 03-DEC-81              950                    30
      7902 FORD                           ANALYST                           7566 03-DEC-81             3000                    20
      7934 MILLER                         CLERK                             7782 23-JAN-82             1300                    10
      7839 KING                           PRESIDENT                         8000 17-NOV-81             5000          0         10

14 rows selected.

SQL> 
SQL> create bitmap index job_bitmap on emp1(job);

Index created.

SQL> select count (*) from emp1 where job = 'CLERK' or job = 'MANAGER';

  COUNT(*)
----------
         7

SQL>

SQL> create bitmap index job_bitmap on emp1(job);
值/行 1 2 3 4 5 6 7 8 9 10 11 12 13 14
---------------------------------------------------------------
ANALYST   0 0 0 0 0 0 0 1 0 0 0 0 1 0
CLERK   1 0 0 0 0 0 0 0 0 0 1 1 0 1
MANAGER   0 0 0 1 0 1 1 0 0 0 0 0 0 0
PRESIDENT   0 0 0 0 0 0 0 0 1 0 0 0 0 0
SALESMAN   0 1 1 0 1 0 0 0 0 0 0 0 0 0
SQL> select count (*) from emp1 where job = 'CLERK' or job = 'MANAGER';
值/行 1 2 3 4 5 6 7 8 9 10 11 12 13 14
---------------------------------------------------------------
CLERK   1 0 0 0 0 0 0 0 0 0 1 1 0 1
MANAGER   0 0 0 1 0 1 1 0 0 0 0 0 0 0
---------------------------------------------------------------
-- or的结果
1 0 0 1 0 1 1 0 0 0 1 1 0 1

以上操作使用autotrace可以看到优化器使用了bitmap,

B树索引要比位图索引应用更广泛,下面我们重点关注B树索引.索引是一个与表相关的可选结构,在逻辑和物理上都独立于表的数据,索引能优化查询,不能优化DML操作,Oracle自动维护索引,频繁的DML操作反而会引起大量的索引维护.

如果SQL语句只访问被索引的列*,那么数据库只需从索引中读取数据,*而不用读取表,如果该语句同时还要访问除索引列之外的列,那么,数据库会使用rowid来查找表中的行,通常,为检索表数据,数据库以交替方式先读取索引块,然后读取相应的表块

2. 适用环境

3. B树索引的访问

当oracle进程需要访问数据文件里的数据块时，oracle会有两种类型的I/O操作方式：
1）随机访问，每次读取一个数据块（通过等待事件“db file sequential read”体现出来）。
2）顺序访问，每次读取多个数据块（通过等待事件“db file scattered read”体现出来）。
第一种方式则是访问索引里的数据块，而第二种方式的I/O操作属于全表扫描。这里顺带有一个问题，为何随机访问会对应到db file sequential read等待事件，而顺序访问则会对应到db file scattered read等待事件呢？这似乎反过来了，随机访问才应该是分散（scattered）的，而顺序访问才应该是顺序（sequential）的。其实，等待事件主要根据实际获取物理I/O块的方式来命名的，而不是根据其在I/O子系统的逻辑方式来命名的。下面对于如何获取索引数据块的方式中会对此进行说明。
事实上在B树索引虽然为一个树状的立体结构，但其对应到数据文件里的排列当然还是一个平面的形式，也就是像下面这样。
/根/分支/分支/叶子/…/叶子/分支/叶子/叶子/…/叶子/分支/叶子/叶子/…/叶子/分支/.....
因此，当oracle需要访问某个索引块的时候，势必会在这个结构上跳跃的移动。
当oracle需要获得一个索引块时，首先从根节点开始，根据所要查找的键值，从而知道其所在的下一层的分支节点，然后访问下一层的分支节点，再次同样根据键值访问再下一层的分支节点，如此这般，最终访问到最底层的叶子节点。可以看出，其获得物理I/O块时，是一个接着一个，按照顺序，串行进行的。在获得最终物理块的过程中，我们不能同时读取多个块，因为我们在没有获得当前块的时候是不知道接下来应该访问哪个块的。因此，在索引上访问数据块时，会对应到 db file sequential read等待事件，其根源在于我们是按照顺序从一个索引块跳到另一个索引块，从而找到最终的索引块的。
那么对于全表扫描来说，则不存在访问下一个块之前需要先访问上一个块的情况。全表扫描时，oracle知道要访问所有的数据块，因此唯一的问题就是尽可能高效的访问这些数据块。因此，这时oracle可以采用同步的方式，分几批，同时获取多个数据块。这几批的数据块在物理上可能是分散在表里的，因此其对应到db file scattered read等待事件。

4. 常用B树索引类型

4.1 Unique or non_unique

SQL> 
SQL> show user
USER is "SCOTT"
SQL> -- 唯一或非唯一索引 ,唯一索引指键值不重复
SQL> create unique index empno_idx on emp1(empno);

Index created.

SQL> -- 设置相同的empno.则报错
SQL> update emp1 set empno=7788 where empno=7902;
update emp1 set empno=7788 where empno=7902
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.EMPNO_IDX) violated


SQL> drop index empno_idx;

Index dropped.

SQL> create index empno_idx on emp1(empno);

Index created.

SQL> set autotrace on
SQL> 
SQL> select * from emp1 where empno=7788;

     EMPNO ENAME                          JOB                                MGR HIREDATE               SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------
      7788 SCOTT                          ANALYST                           7566 24-JAN-87             3000                    20


Execution Plan
----------------------------------------------------------
Plan hash value: 3213420274

-------------------------------------------------------------------------------------------------
| 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| EMP1      |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMPNO_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=7788)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       1107  bytes sent via SQL*Net to client
        397  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace off
SQL>

4.2 Composite

组合索引
绑定了两个或更多列的索引.

SQL> 
SQL> show user
USER is "SCOTT"
SQL> 
SQL> create index job_deptno_idx on emp1(job, deptno);

Index created.

SQL> col index_name format a15;
SQL> col table_name format a15;
SQL> col column_name format a15;
SQL> select * from user_ind_columns;

INDEX_NAME      TABLE_NAME      COLUMN_NAME     COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND      COLLATED_COLUMN_ID
--------------- --------------- --------------- --------------- ------------- ----------- ------------ ------------------
EMPNO_IDX       EMP1            EMPNO                         1            22           0 ASC
IDX_DEPTNO_TEMP EMP_TEMP        DEPTNO                        1            22           0 ASC
IDX_EMP_MGR     EMP             MGR                           1            22           0 ASC
IDX_MGR_TEMP    EMP_TEMP        MGR                           1            22           0 ASC
I_SNAP$_EMP_AGG EMP_AGGR_MV     SYS_NC00003$                  1            23           0 ASC
R_MV

JOB_BITMAP      EMP1            JOB                           1             9           9 ASC
JOB_DEPTNO_IDX  EMP1            JOB                           1             9           9 ASC
JOB_DEPTNO_IDX  EMP1            DEPTNO                        2            22           0 ASC
PK_DEPT         DEPT            DEPTNO                        1            22           0 ASC
PK_EMP          EMP             EMPNO                         1            22           0 ASC
SYS_C008020     TAB2            ID                            1            22           0 ASC
SYS_C008859     EXAMPLE         ID                            1            22           0 ASC

12 rows selected.

SQL> -- 如果select查询非第一列则不会用索引.即全盘扫描
SQL> select * from emp1;

     EMPNO ENAME                          JOB                                MGR HIREDATE               SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------
      7369 SMITH                          CLERK                             7902 17-DEC-80             1600        300         20
      7499 ALLEN                          SALESMAN                          7698 20-FEB-81             1600        300         30
      7521 WARD                           SALESMAN                          7698 22-FEB-81             1250        500         30
      7566 JONES                          MANAGER                           7839 02-APR-81             2975                    20
      7654 MARTIN                         SALESMAN                          7698 28-SEP-81             1250       1400         30
      7698 BLAKE                          MANAGER                           7839 01-MAY-81             2850                    30
      7782 CLARK                          MANAGER                           7839 09-JUN-81             1500        300         10
      7788 SCOTT                          ANALYST                           7566 24-JAN-87             3000                    20
      7844 TURNER                         SALESMAN                          7698 08-SEP-81             1500          0         30
      7876 ADAMS                          CLERK                             7788 02-APR-87             1100                    20
      7900 JAMES                          CLERK                             7698 03-DEC-81              950                    30
      7902 FORD                           ANALYST                           7566 03-DEC-81             3000                    20
      7934 MILLER                         CLERK                             7782 23-JAN-82             1300                    10
      7839 KING                           PRESIDENT                         8000 17-NOV-81             5000          0         10

14 rows selected.

SQL>

4.3 Reverse

反向键索引
将字节倒置后组织键值.当使用序列产生主键索引时,可以防止叶节点出现热块现象(知识点).

SQL> 
SQL> create index mgr_idx on emp1(mgr) reverse;

Index created.

SQL> select * from user_ind_columns;

INDEX_NAME      TABLE_NAME      COLUMN_NAME     COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND      COLLATED_COLUMN_ID
--------------- --------------- --------------- --------------- ------------- ----------- ------------ ------------------
PK_DEPT         DEPT            DEPTNO                        1            22           0 ASC
PK_EMP          EMP             EMPNO                         1            22           0 ASC
IDX_EMP_MGR     EMP             MGR                           1            22           0 ASC
JOB_BITMAP      EMP1            JOB                           1             9           9 ASC
JOB_DEPTNO_IDX  EMP1            JOB                           1             9           9 ASC
EMPNO_IDX       EMP1            EMPNO                         1            22           0 ASC
JOB_DEPTNO_IDX  EMP1            DEPTNO                        2            22           0 ASC
MGR_IDX         EMP1            MGR                           1            22           0 ASC
I_SNAP$_EMP_AGG EMP_AGGR_MV     SYS_NC00003$                  1            23           0 ASC
R_MV

IDX_DEPTNO_TEMP EMP_TEMP        DEPTNO                        1            22           0 ASC
IDX_MGR_TEMP    EMP_TEMP        MGR                           1            22           0 ASC
SYS_C008859     EXAMPLE         ID                            1            22           0 ASC
SYS_C008020     TAB2            ID                            1            22           0 ASC

13 rows selected.

SQL>

4.4 Function base

函数索引
以索引列的函数值作为键值的组织索引

SQL> 
SQL> create index fun_idx on emp1(lower(ename));

Index created.

SQL> -- 如果查询ename='SCOTT',则是全盘扫描,不用索引.
SQL> set autotrace on
SQL> select * from emp1 where lower(ename)='SCOTT';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3091332103

-----------------------------------------------------------------------------------------------
| 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| EMP1    |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | FUN_IDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(LOWER("ENAME")='SCOTT')


Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        871  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>

4.5 Compress

压缩索引
重复键值只会存储一次,即重复的键值在叶块中只存一次,后跟所有与之匹配的rowid字符串.

SQL> 
SQL> show user;
USER is "SCOTT"
SQL> create index comp_idx on emp1(sal) compress;

Index created.

SQL> -- 键值和rowid存在叶块中
SQL>

4.6 Ascending or descending

升序或降序索引
叶节点中的键值排列默认是升序的.

SQL> 
SQL> show user;
USER is "SCOTT"
SQL> create index deptno_job_idx on emp1(deptno desc, job asc);

Index created.

SQL>

4.7 更改索引属性

alter index xxx ....

5. 索引相关的数据字典

5.1 USER_INDEXES

SQL> 
SQL> show user;
USER is "SCOTT"
SQL> col index_name format a10;
SQL> col index_type format a10;
SQL> col table_owner format a10;
SQL> select * from user_indexes;

-- 索引主要信息

5.2 USER_IND_COLUMNS

SQL> 
SQL> col index_name format a15;
SQL> col table_name format a15;
SQL> col column_name format a15;
SQL> select * from user_ind_columns;

INDEX_NAME      TABLE_NAME      COLUMN_NAME     COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND      COLLATED_COLUMN_ID
--------------- --------------- --------------- --------------- ------------- ----------- ------------ ------------------
PK_DEPT         DEPT            DEPTNO                        1            22           0 ASC
PK_EMP          EMP             EMPNO                         1            22           0 ASC
IDX_EMP_MGR     EMP             MGR                           1            22           0 ASC
JOB_BITMAP      EMP1            JOB                           1             9           9 ASC
JOB_DEPTNO_IDX  EMP1            JOB                           1             9           9 ASC
EMPNO_IDX       EMP1            EMPNO                         1            22           0 ASC
JOB_DEPTNO_IDX  EMP1            DEPTNO                        2            22           0 ASC
MGR_IDX         EMP1            MGR                           1            22           0 ASC
FUN_IDX         EMP1            SYS_NC00009$                  1            10          10 ASC
COMP_IDX        EMP1            SAL                           1            22           0 ASC
DEPTNO_JOB_IDX  EMP1            SYS_NC00010$                  1            34           0 DESC
DEPTNO_JOB_IDX  EMP1            JOB                           2             9           9 ASC
I_SNAP$_EMP_AGG EMP_AGGR_MV     SYS_NC00003$                  1            23           0 ASC
R_MV

IDX_DEPTNO_TEMP EMP_TEMP        DEPTNO                        1            22           0 ASC
IDX_MGR_TEMP    EMP_TEMP        MGR                           1            22           0 ASC
SYS_C008859     EXAMPLE         ID                            1            22           0 ASC
SYS_C008020     TAB2            ID                            1            22           0 ASC

17 rows selected.

SQL>

6. 优化器使用索引的扫描方式

Oracle的执行计划常见的四种索引扫描方式

6.1 索引唯一扫描(index unique scan)

通过唯一索引查找一个数值返回单个ROWID.对于唯一组合索引,要在where的谓词”=“后包含所有列的”布尔与”.即and.

例:索引唯一扫描(index unique scan)

SQL> 
SQL> show user;
USER is "SCOTT"
SQL> 
SQL> drop index empno_idx;

Index dropped.

SQL> create unique index emp1_idx on emp1(empno);

Index created.

SQL> set autotrace off
SQL> 
SQL> select empno from emp1 where empno=7788;

     EMPNO
----------
      7788

SQL> set autotrace on
SQL> select empno from emp1 where empno=7788;

     EMPNO
----------
      7788


Execution Plan
----------------------------------------------------------
Plan hash value: 1995401140

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     4 |     0   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| EMP1_IDX |     1 |     4 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMPNO"=7788)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        548  bytes sent via SQL*Net to client
        401  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

6.2 索引范围扫描(index range scan)

在非唯一索引上,可能返回多行数据,所以在非唯一索引上都使用索引范围扫描.

例:索引范围扫描(index range scan)

SQL>
SQL> drop index emp1_idx;

Index dropped.

SQL> create index emp1_idx on emp1(empno);

Index created.

SQL> select empno from emp1 where empno=7788;

EMPNO
----------
7788

Execution Plan
----------------------------------------------------------
Plan hash value: 253836959

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| EMP1_IDX | 1 | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("EMPNO"=7788)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
548 bytes sent via SQL*Net to client
401 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

使用index rang scan的3种情况:

(1) 在唯一索引列上使用了range操作符(> < <> >= <= between)
(2) 在唯一组合索引上,对组合索引使用部分列进行查询(含引导列),导致查询出多行
(3) 对非唯一索引列上进行的任何查询.不含’布尔或’

6.3 索引全扫描(index full scan)

对整个index进行扫描,并且顺序的读取其中数据.

全Oracle索引扫描只在CBO(基于成本的优化器)模式下才有效. CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时,才进行全Oracle索引扫描,而且此时查询出的数据都必须从索引中可以直接得到.

对比index full scan,当行数较多时,会使用index fast full scan
行数较多时index fast full scan 比index full scan计划成本要低的多,所以CBO优化器会优选 index fast full scan

两项再比较一下:

SQL> 
SQL> show user;
USER is "SCOTT"
SQL> drop index emp1_idx;

Index dropped.

SQL> create index empno_idx on emp1(empno);

Index created.

SQL> select empno from emp1;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7844
      7876
      7900
      7902
      7934
      7839

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2226897347

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |    56 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP1 |    14 |    56 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        710  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

没有启用索引扫描是因为键值(empno)存在空值

SQL> 
SQL> show user;
USER is "SCOTT"
SQL> -- 去空
SQL> alter table emp1 modify (empno not null);

Table altered.

SQL> -- 因为索引的叶子快不存空值,使字段非空
SQL> select empno from emp1;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876
      7900
      7902
      7934

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3564745122

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |    14 |    56 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | EMPNO_IDX |    14 |    56 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         11  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
        710  bytes sent via SQL*Net to client
        605  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

SQL> set autotrace off
SQL> insert into emp1 select * from emp1;

SQL> /

114688 rows created.

SQL> set autotrace traceonly explain

229376 rows created.

SQL> select empno from emp1;

458752 rows created.

SQL> SQL> 
Execution Plan
----------------------------------------------------------
Plan hash value: 3564745122

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |   458K|  5824K|     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | EMPNO_IDX |   458K|  5824K|     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: statistics for conventional DML

SQL> -- 可以加一行hint,强制oracle使用index full scan的执行计划,得到cost是100.
SQL> select /*+ index (emp1 emp1_idx) */ empno from emp1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3564745122

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |   458K|  5824K|     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | EMPNO_IDX |   458K|  5824K|     1   (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 / EMP1@SEL$1
         U -  index (emp1 emp1_idx) / index specified in the hint doesn't exist

Note
-----
   - dynamic statistics used: statistics for conventional DML

SQL>

可以看出:CBO在满足一定条件时会先选index fast full scan,而不是index full scan,因为前者的cost是29,比后者100低许多.

CBO优化器何时决定INDEX FULL SCAN 与 INDEX FAST FULL SCAN:
共性:当仅从索引表中就可以得到所要的查询结果,省去了第二步扫描表块.

个性:INDEX FAST FULL SCAN 可以使用多块读,多块读由参数db_file_multiblock_read_count指定,适用于表行多时IO效率更高,而对于索引列上order by之类的操作又几乎总是使用INDEX FULL SCAN.

6.4 索引快速扫描(index fast full scan)

6.4.1 概念

扫描索引中的所有的数据块,与 index full scan很类似,显著的区别是
full scan 是根据叶子块的双向列表顺序读取,读取的块是有顺序的,也是经过排序的,所以返回的列表也是排序的.
fast full scan 在读取叶子块时的顺序完全由物理存储位置决定,并采取多块读,每次读取DB_FILE_MULTIBLOCK_READ_COUNT值的块.

6.4.2 DB_FILE_MULTIBLOCK_READ_COUNT

SQL> 
SQL> conn / as sysdba
Connected.
SQL> show parameter mult;

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_file_multiblock_read_count        integer                           36
multishard_query_data_consistency    string                            strong
multishard_query_partial_results     string                            not allowed
parallel_adaptive_multi_user         boolean                           FALSE
SQL>

----一次读取36个块。

分析器是根据要访问的数据量和索引的聚簇因子等属性判断使用 RANGE SCAN 或 INDEX FULL SCAN

6.4.3 聚簇因子(CLUSTERING_FACTOR)

堆表表行的物理存储在数据块是无序的,这与插入一行记录首选空闲块的策略有关,而索引的键值是有序的,当这两者差异越大,聚簇因子的值就越高.

如果你的scott不能使用autotrace,做一下几步.

SQL> conn / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce
SQL> grant plustrace to scott, hr;
SQL>
SQL> create table emp1 as select * from emp;
SQL> set autotrace traceonly explain;
SQL> select empno from emp1;

7. 索引的碎片问题(不考)

由于对基表做DML操作,便导致对索引表的块自动更改操作,尤其是基表的delete操作会引起index表的index entries的逻辑删除.

注意,只有当一个索引块中的全部index entry 都被删除了,这个块才会被收回.
如果update基表索引列,则索引块会发生entry delete,再entry insert,这些动作都可能产生索引碎片.

SQL> create table t (id int);
SQL> create index ind_1 on t(id); 
SQL>
begin
for i in 1..1000000 loop
  insert into t values (i);
if mod (i, 100) =0 then
commit; 
end if;
end loop;
end;
/
-- 分析索引
SQL> analyze index ind_1 validate structure;
col name format a10;
col DEL_LF_ROWS/LF_ROWS format a5;
SQL> select name, HEIGHT, PCT_USED, DEL_LF_ROWS/LF_ROWS from index_stats;
NAME	HEIGHT	PCT_USED	DEL_LF_ROWS/LF_ROWS
----- 	------	--------	--------------------
IND_1	3		72			0
-- Hight	3指的是 下图的高度3;
-- PCT_USED	指的是索引用满了.100越高越好;

在 Oracle 文档里并没有清晰的给出索引碎片的量化标准,Oracle 建议通过 Segment Advisor(段顾问)解决表和索引的碎片问题.

如果自行解决,可以通过查看index_stats视图

以下三种情形之一发生时,说明积累的碎片应该整理了(仅供参考).
1.HEIGHT >=4
2.PCT_USED<50%(仅供参考)
3.DEL_LF_ROWS/LF_ROWS>0.2

联机重建索引通常比删除后再重建要更实用,Oracle9i和10g一直提供联机索引重建功能-rebuild online,但由于涉及到使用表的排他锁,通常一段时间内其他用户的DML操作需要等待.

在11g中有了改进,实现了最终意义上的联机索引重建(rebuild index online)的特性,重新设计了锁的模式,因此允许DML操作不受干扰.

8.删除索引

drop index index_name on table_name;
alter table table_name drop index index_name;
alter table table_name drop primary key;