常见的几种索引扫描类型

一、如何查看SQL语句的执行计划

1、在SQL*PLUS环境中   执行SET AUTOTRACE ON 可以打开启动自动跟踪功能，如下(下面的红色字体是敲入的命令)： ChenZw> set autotrace on ChenZw> select * from emp;        EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------       7369 SMITH      CLERK           7902 17-12月-80            800                    20       7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30       7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30       7566 JONES      MANAGER         7839 02-4月 -81           2975                    20       7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30       7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30       7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10       7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20       7839 KING       PRESIDENT            17-11月-81           5000                    10       7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30       7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20       7900 JAMES      CLERK           7698 03-12月-81            950                    30       7902 FORD       ANALYST         7566 03-12月-81           3000                    20       7934 MILLER     CLERK           7782 23-1月 -82           1300                    10   已选择14行。     执行计划 ---------------------------------------------------------- Plan hash value: 3956160932   -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 | |   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------     统计信息 ----------------------------------------------------------         476  recursive calls           0  db block gets          90  consistent gets           9  physical reads           0  redo size        1423  bytes sent via SQL*Net to client         416  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           7  sorts (memory)           0  sorts (disk)          14  rows processed     如同describe命令可以省略为desc一样，set autotrace off命令也可以省略为set autot off。如果，执行计划中的行太宽的话，可以执行如下命令，将行的宽度设置为200，命令如下： ChenZw> set linesize 200 ChenZw>   2、在PL/SQL Developer软件中   在PL/SQL Developer窗口中，点击“文件”的“新建”菜单中选择“SQL窗口”,在里面输入自己要执行的SQL，例如上面的 SELECT * FROM EMP; 然后点击“F5”按钮，系统会自动弹出“解释计划窗口”，并且将该SQL的执行计划在“解释计划窗口”中显示出来 或者直接创建“解释计划窗口”也可以进入解释计划窗口。     现在回过头来看这个SQL语句，使用的是“TABLE ACCESS FULL”的方式进行查询，那么优化器为什么会使用全表查询呢？有这样几个情况下，数据库会选择全表索引的。 第一、高度并发的时候，如果在DBA_TABLES视图里面的DEGREE（The number of threads per instance for scanning the table）字段的值比较高的时候，优化器会选择全表扫描的方式。 第二、在该表上，如果没有索引的时候，正所谓“巧妇难为无米之炊”，优化器也只能选择全表扫描FULL TABLE SCAN的方式了。 第三、查询大数据量的时候，比如上面的SQL语句，优化器一看后面一个WHERE条件都没有，或者优化器认为该次查询将会查询到大部分的数据，则会使用全表扫描，这种情况下，就算是该SQL能够用到索引，也不会使用的。 第四种，查询小标的时候，在初始化参数里面有一个DB_FILE_MULTIBLOCK_READ_COUNT，这个参数指定了每次读数据块的数量，但是如果发现当前要查询的表的数据表小于该参数的值，优化器也会选择全表扫描的。   二、SQL语句的几种常见的索引扫描类型       首先，有一个概念，就是除了获取索引本身的值之外，使用索引最终还是要使用表的ROWID进行获取数据的。也就是TABLE ACCESS BY ROWID。什么是ROWID呢？下面的查询：   ChenZw> select t.rowid,t.* from emp t;   ROWID              EMPNO ENAME  JOB       MGR HIREDATE    SAL  COMM DEPTNO ------------------ ----- ------ ------------- --------------- ----- ------ AAAQ+jAAEAAAAAeAAA  7369 SMITH  CLERK    7902 17-12月-80  800        20 AAAQ+jAAEAAAAAeAAB  7499 ALLEN  SALESMAN 7698 20-2月 -81 1600   300  30 AAAQ+jAAEAAAAAeAAC  7521 WARD   SALESMAN 7698 22-2月 -81 1250   500  30 AAAQ+jAAEAAAAAeAAD  7566 JONES  MANAGER  7839 02-4月 -81 2975        20 AAAQ+jAAEAAAAAeAAE  7654 MARTIN SALESMAN 7698 28-9月 -81 1250  1400  30 AAAQ+jAAEAAAAAeAAF  7698 BLAKE  MANAGER  7839 01-5月 -81 2850        30 AAAQ+jAAEAAAAAeAAG  7782 CLARK  MANAGER  7839 09-6月 -81 2450        10 AAAQ+jAAEAAAAAeAAH  7788 SCOTT  ANALYST  7566 19-4月 -87 3000        20 AAAQ+jAAEAAAAAeAAI  7839 KING   PRESIDENT     17-11月-81 5000        10 AAAQ+jAAEAAAAAeAAJ  7844 TURNER SALESMAN 7698 08-9月 -81 1500     0  30 AAAQ+jAAEAAAAAeAAK  7876 ADAMS  CLERK    7788 23-5月 -87 1100        20 AAAQ+jAAEAAAAAeAAL  7900 JAMES  CLERK    7698 03-12月-81  950        30 AAAQ+jAAEAAAAAeAAM  7902 FORD   ANALYST  7566 03-12月-81 3000        20 AAAQ+jAAEAAAAAeAAN  7934 MILLER CLERK    7782 23-1月 -82 1300        10       ROWID字段中保存了很多信息，例如所在数据块、数据文件、行在数据块中的信息等等，可以非常方便的通过ROWID的信息找到该记录所在的位置，在oracle中，使用ROWID来查找某条记录是最快的。在使用索引的时候，如果不是查询索引本身的值（例如主键），而是查询主键所在行的其他字段信息的话，Oracle也是首先得到ROWID的信息，然后再去找到其他字段的信息，并且返回。   在网上找到这样几种最常见的扫描的类型：   第一种：index unique  scan     索引唯一扫描，当可以优化器发现某个查询条件可以利用到主键、唯一键、具有外键约束的列，或者只是访问其中某行索引所在的数据的时候，优化器会选择这种扫描类型。   第二种：index range scan     索引范围扫描，当优化器发现在UNIQUE列上使用了大于、小于、大于等于、小于等于以及BETWEEN等就会使用范围扫描，在组合列上只使用部分进行查询，导致查询出多行数据。对非唯一的索引列上进行任何活动都会使用index range scan。   第三种：index full scan     全索引扫描，如果要查询的数据可以全部从索引中获取，则使用全索引扫描。   第四种：index fast full scan     索引快速扫描，扫描索引中的全部的数据块，与全索引扫描的方式基本上类似。两者之间的明显的区别是，索引快速扫描对查询的数据不进行排序，数据返回的时候不是排序的。“在这种存取方法中，可以使用多块读功能，也可以使用并行读入，从而得到最大的吞吐量和缩短执行时间”。   三、几种常见的索引类型的模拟   创建测试表以及测试数据的SQL：   CREATE TABLE TBL(     USERID NUMBER(20),     NAME VARCHAR2(20),     DEPTNO NUMBER(20) ) ; --模拟数据 BEGIN     FOR I IN 0..1000 LOOP         INSERT INTO TBL VALUES(         I,CONCAT('TBL',I),MOD(I,2));     END LOOP; END;   SELECT * FROM TBL;   1）TABLE ACCESS BY USER ROWID 执行下面的语句得到几个ROWID： ChenZw> select rowid,tbl.* from tbl where rownum<10;   ROWID                  USERID NAME                     DEPTNO ------------------ ---------- -------------------- ---------- AAAUhAAAEAAAAIcAAA        796 TBL796                        0 AAAUhAAAEAAAAIcAAB        797 TBL797                        1 AAAUhAAAEAAAAIcAAC        798 TBL798                        0 AAAUhAAAEAAAAIcAAD        799 TBL799                        1 AAAUhAAAEAAAAIcAAE        800 TBL800                        0 AAAUhAAAEAAAAIcAAF        801 TBL801                        1 AAAUhAAAEAAAAIcAAG        802 TBL802                        0 AAAUhAAAEAAAAIcAAH        803 TBL803                        1 AAAUhAAAEAAAAIcAAI        804 TBL804                        0   然后带着rowid去查询，看这个时候的执行计划： ChenZw> select * from tbl where rowid = 'AAAUhAAAEAAAAIcAAA'; 执行计划 ---------------------------------------------------------- Plan hash value: 2623095911   ----------------------------------------------------------------------------------- | Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------------- |   0 | SELECT STATEMENT           |      |     1 |    50 |     1   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY USER ROWID| TBL  |     1 |    50 |     1   (0)| 00:00:01 | ----------------------------------------------------------------------------------- 上面的TABLE ACCESS BY USER ROWID是一种效率最高的获取数据的方法。   2）index unique  scan 修改上面的表，增加一个主键，等于自动会增加一个唯一键约束： ALTER TABLE TBL ADD CONSTRAINT TBLID PRIMARY KEY (USERID); 执行下面的SQL语句： ChenZw> select * from tbl where userid = 100; 执行计划 ---------------------------------------------------------- Plan hash value: 1167568666   ------------------------------------------------------------------------------------- | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |       |     1 |    38 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| TBL   |     1 |    38 |     2   (0)| 00:00:01 | |*  2 |   INDEX UNIQUE SCAN         | TBLID |     1 |       |     1   (0)| 00:00:01 | -------------------------------------------------------------------------------------   从上面的执行计划可以看出，优化器首先是根据为刚才建立的索引TBLID来找到100的ROWID，然后根据ROWID去找到100所在的行数据。   2）index full scan 为上面的表增加一个联合索引，在TBL.NAME和TBL.DEPTNO两个列上，SQL如下： CREATE INDEX INDEX_TBL_NAME_DEPTNO ON TBL(NAME,DEPTNO);   3）index range scan 执行下面的语句： ChenZw> select * from tbl where userid between 10 and 100; 已选择91行。 执行计划 ---------------------------------------------------------- Plan hash value: 2314926374   ------------------------------------------------------------------------------------- | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |       |    91 |  3458 |     3   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| TBL   |    91 |  3458 |     3   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | TBLID |    91 |       |     2   (0)| 00:00:01 | ------------------------------------------------------------------------------------- 在索引列上使用大于等于，小于等于，between等的时候，优化器会首先在索引列上进行操作，进行一个索引范围扫描得到ROWID之后，再根据ROWID来找到相应的数据。   这个里面有一个很有意思的问题： ChenZw> select * from tbl where userid between 10 and 100 order by 1; 已选择91行。 执行计划 ---------------------------------------------------------- Plan hash value: 2314926374   ------------------------------------------------------------------------------------- | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |       |    91 |  3458 |     3   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| TBL   |    91 |  3458 |     3   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | TBLID |    91 |       |     2   (0)| 00:00:01 | ------------------------------------------------------------------------------------- 看执行计划，我们虽然在SQL语句指定了需要将结果集进行排序，但是在执行计划中，并没有进行排序的动作，这个是为什么呢？ ChenZw> select * from tbl where userid between 10 and 100 order by 1 desc; 已选择91行。 执行计划 ---------------------------------------------------------- Plan hash value: 4193471386   -------------------------------------------------------------------------------------- | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |       |    91 |  3458 |     3   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID | TBL   |    91 |  3458 |     3   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN DESCENDING| TBLID |    91 |       |     2   (0)| 00:00:01 | -------------------------------------------------------------------------------------- 这个执行计划和上面的执行计划有什么区别呢？其中第一个执行计划是索引范围扫描（INDEX RANGE SCAN），但是第二个执行计划是索引范围降序扫描（INDEX RANGE SCAN DESCENDING）。由此可以看出对于唯一索引，ORACLE是已经进行了正序排列了。也就是唯一索引在进行排序的时候消耗不是很大的，因为它在保存的时候就按照升序进行保存的。   4）index fast full scan 假如我们只需要得到所有的USERID的时候，优化器会如何进行查询呢？我们要查的USERID是全部在索引里面的，执行一下下面的语句，得到下面的执行计划： ChenZw> select userid from tbl; 已选择100001行。 执行计划 ---------------------------------------------------------- Plan hash value: 3798421822   ------------------------------------------------------------------------------ | Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT     |       |   115K|  1471K|    63   (2)| 00:00:01 | |   1 |  INDEX FAST FULL SCAN| TBLID |   115K|  1471K|    63   (2)| 00:00:01 | ------------------------------------------------------------------------------ 看到上面的执行计划是索引快速扫描（INDEX FAST FULL SCAN），因为需要得到的所有的数据都是在索引里面的，因此只需要将所需要的数据从索引中取出来就ok了。