ACCESSORACLE Index Lookup索引访问路径总结

** 

在ORACLE中,索引访问/查找(Index
Lookup)路径有五种植办法,分别吗INDEX UNIQUE SCAN、INDEX RANGE SCAN、INDEX FULL SCAN、INDEX FAST FULL SCAN
、INDEX SKIP
SCAN。下面通过一些案例介绍、总结一下就五种索引访问路径。本文是总结就地方的知识点,所以文中部分地方参考、引用了参考资料中的局部内容。详细、具体资料可参照官方资料Index
Scans

 

 

 

 

目录唯一扫描(INDEX UNIQUE
SCAN)

 

 

目录唯一扫描只发在唯一性索引(UNIQUE
INDEX)上,它只适用于WHERE 条件中凡等值查询的SQL,因为对唯一索引,等值查询到多单会返回一久记下。对于构成唯一索引来说,WHERE条件需要包含有的索引列才会采取索引唯一扫描(INDEX UNIQUE
SCAN)。

 

 

SQL> SET AUTOTRACE TRACEONLY;

SQL> SELECT * FROM SCOTT.EMP

  2  WHERE EMPNO=7788;

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2949544139

 

--------------------------------------------------------------------------------------

| 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    |     1 |    38 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO"=7788)

 

 

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          2  consistent gets

          0  physical reads

          0  redo size

        891  bytes sent via SQL*Net to client

        512  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

 

 

目唯一扫描(INDEX UNIQUE
SCAN)仅仅适用于WHERE条件中凡是等值查询的SQL万一是询问条件是一个间距范围,则无会见动索引唯一扫描。如下所示,执行计划成了目录范围扫描(INDEX RANGE
SCAN)。

 

 

 

SQL> SET AUTOTRACE TRACEONLY;

SQL> SELECT * FROM SCOTT.EMP

  2  WHERE EMPNO>=788 AND EMPNO <=7788;

 

8 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 169057108

 

--------------------------------------------------------------------------------------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |    11 |   418 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |    11 |   418 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | PK_EMP |    11 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO">=788 AND "EMPNO"<=7788)

 

 

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

       1383  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          8  rows processed

 

 

 

 

小心:如下所示,如果查询条件也DEPTNO下的等值查询,由于字段DEPTNO上之目为IX_DEPTNO(非唯一索引),所以不见面面世索引唯一扫描。也就是说索引唯一扫描只生在唯一索引上

 

 

SQL> SELECT * FROM SCOTT.EMP

  2  WHERE DEPTNO=10;

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1198124189

 

-----------------------------------------------------------------------------------------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |           |     5 |   190 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP       |     5 |   190 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IX_DEPTNO |     5 |       |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("DEPTNO"=10)

 

 

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          4  consistent gets

          1  physical reads

          0  redo size

       1159  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          3  rows processed

 

SQL> 

 

 

对此构成唯一索引,WHERE条件需要包含有的索引列才能够运用索引唯一扫描。否则即见面下索引范围扫描(INDEX RANGE SCAN)

 

 

 

SQL> CREATE TABLE SCOTT.EMP_TEST   

  2  AS

  3  SELECT * FROM SCOTT.EMP;

 

Table created.

 

SQL> CREATE UNIQUE INDEX SCOTT.IDX_EMP_TEST_U ON SCOTT.EMP_TEST(EMPNO, ENAME);

 

Index created.

 

SQL> SET AUTOTRACE ON EXPLAIN;

SQL> SELECT * FROM SCOTT.EMP_TEST

  2  WHERE EMPNO=7788;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 206749441

 

----------------------------------------------------------------------------------------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |     1 |    87 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEST       |     1 |    87 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_EMP_TEST_U |     1 |       |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO"=7788)

 

Note

-----

   - dynamic sampling used for this statement (level=2)

 

SQL> SELECT * FROM SCOTT.EMP_TEST

  2  WHERE EMPNO=7788 AND ENAME='SCOTT';

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2899991080

 

----------------------------------------------------------------------------------------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |     1 |    87 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEST       |     1 |    87 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | IDX_EMP_TEST_U |     1 |       |     0   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO"=7788 AND "ENAME"='SCOTT')

 

SQL> 

ACCESS 1

 

 

 

于构成唯一索引,如果索引前导列不在WHERE条件中,那么执行计划路径就是会见倒全表扫描而不见面走索引围观,如下所示:

 

 

 

SQL> SET AUTOTRACE ON EXPLAIN;

SQL> SELECT * FROM SCOTT.EMP_TEST

  2  WHERE ENAME='SCOTT';

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3124080142

 

------------------------------------------------------------------------------

| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |          |     1 |    38 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP_TEST |     1 |    38 |     3   (0)| 00:00:01 |

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("ENAME"='SCOTT')

 

SQL> 

 

 

 

此外,在尽计划受到,索引唯一扫描的产同样步,你见面小心到闹或是“TABLE
ACCESS BY INDEX ROWID”或“SELECT
STATEMENT”部分。
“TABLE
ACCESS BY INDEX ROWID”这代表表数据尚未经过FTS操作访问,而是经rowid查找访问。
如果拥有必要的数量还驻留于目中,则表查找可能是勿必要之,您将视的仅是索引访问(没有表访问)。
在以下示例中,所有列(只有EMPNO列)都在目录中。
所以,它不会见进展表访问,而采取SELET * 就见面出表访问:

 

 

SQL> SET AUTOTRACE ON EXPLAIN;

SQL> SELECT EMPNO FROM SCOTT.EMP

  2  WHERE EMPNO=7788;

 

     EMPNO

----------

      7788

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 56244932

 

----------------------------------------------------------------------------

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |        |     1 |     4 |     0   (0)| 00:00:01 |

|*  1 |  INDEX UNIQUE SCAN| PK_EMP |     1 |     4 |     0   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("EMPNO"=7788)

 

SQL> SELECT * FROM SCOTT.EMP   

  2  WHERE EMPNO=7788;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2949544139

 

--------------------------------------------------------------------------------------

| 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    |     1 |    38 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO"=7788)

 

SQL> 

 

 

ACCESS 2

 

 

 

 

平凡,执行计划优先通过Index查找到数据对应的rowid值(对于非唯一索引可能回多单rowid值),然后因rowid直接由发明中获具体的数据,这种查询艺术叫做索引围观或索引查找(index loopup)。一个rowid唯一象征一行数,该行对应之数据块是通过同样不良I/O得到的,在这景下该次I/O只见面读取一个数据块。在目中,除了存储每个索引的价外,索引还蕴藏具有此值的履对应之ROWID的价。

 

检索引围观可以由2步结合:

 

扫描索引得到相应的ROWID值

透过找到的ROWID从表中读取具体的数码

 

每步都是单身的均等次等I/O,但是对索引,由于经常应用,绝大多数且曾CACHE到内存中,所以率先步之I/O经常也逻辑I/O,即数据好打内存中得到。但是对第2步来说,如果表比较深,则该数量不可能全以内存中,所以该I/O很有或是物理I/O,这是一个物理操作,相对逻辑I/O来说,是极其费时间的。所以要是对大表进行搜寻引围观,取有底数据如果过量总量的5%-10%,使用索引围观会效率降低多。但是只要查询的数码会全以目录中找到,就可避免进行第2步操作,避免了无必要之I/O,此时即通过寻找引围观取出的数较多,效率要非常高之。进一步说,如果SQL语句被对索引列进行排序,因为索引已经先排序好了,所以当实行计划受到无需再对索引列进行排序。

 

 

 

 

目范围扫描(INDEX RANGE SCAN)

 

 

 

目录范围扫描是均等种很普遍的表访问方式,索引范围扫描的出众气象下是以谓词(WHERE限制法)中利用限制操作符(<,>,<>,>=,<=,BEWTEEN)。下面是发出索引范围扫描的有的气象

 

 

1:
在唯一索引上采用限制查找操作符(>, <, <>, >=, <=,
BEWTEEN)等。

 

2:
在做唯一索引上,只使一些列进行询问(一定带有前导列leading
column),导致查询有多长长的记下(也闹或是平长记下)。

 

3:
在非唯一索引列上展开任何查询。

 

 

 

1:
在唯一索引上使范围查找操作符(>, <, <>, >=, <=,
BEWTEEN)等。

 

 

 

SQL> SET AUTOTRACE ON EXPLAIN;

SQL> SELECT * FROM SCOTT.EMP

  2  WHERE EMPNO >=7788;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7876 ADAMS      CLERK           7788 23-MAY-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

 

7 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 169057108

 

--------------------------------------------------------------------------------------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |     5 |   190 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     5 |   190 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | PK_EMP |     5 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO">=7788)

 

SQL> 

 

 

2:
在结合唯一索引上,只使有列进行询问(一定带有前导列leading column ),导致查询有一致长或多漫长记下

 

 

 

SQL> SET AUTOTRACE OFF;

SQL> SET AUTOTRACE ON EXPLAIN;

SQL> SELECT * FROM SCOTT.EMP_TEST

  2  WHERE EMPNO=7788;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 206749441

 

----------------------------------------------------------------------------------------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |     1 |    38 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEST       |     1 |    38 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_EMP_TEST_U |     1 |       |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO"=7788)

 

SQL> 

 

 

 

3:
在非唯一索引列上展开任何查询。

 

 

 

SQL> SET AUTOTRACE TRACEONLY;

SQL> SELECT * FROM SCOTT.EMP

  2  WHERE DEPTNO=20;

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1198124189

 

-----------------------------------------------------------------------------------------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |           |     5 |   190 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP       |     5 |   190 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IX_DEPTNO |     5 |       |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("DEPTNO"=20)

 

 

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

       1241  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          5  rows processed

 

SQL> SELECT * FROM SCOTT.EMP

  2  WHERE DEPTNO >=10;

 

14 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1198124189

 

-----------------------------------------------------------------------------------------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |           |    14 |   532 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP       |    14 |   532 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IX_DEPTNO |    14 |       |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("DEPTNO">=10)

 

 

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

       1630  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         14  rows processed

 

SQL> 

 

 

目录降序范围扫描(INDEX RANGE SCAN
DESCENDING)

** 

 

以默认情况下,索引是按索引关键字升序来存放数据的。当SQL语句以ORDER BY
COLUMN_NAME DESC排序时,就会出现INDEX RANGE SCAN DESCENDING的访问路径

 

按部就班常理来讲,如果生ORDER BY COLUMN_NAME
DESC的条件,那么

 


需要事先从目录中读取数据;

 


再比如标准化被COLUMN_NAME字段做降序排序。

 

若挑选INDEX RANGE SCAN DESCENDING,则是直以目上按索引关键字降序查找数据(其实是找到对应索引最右侧边的纸牌块的率先行索引行,然后经叶子块之间的双向链表访问数),这样正是为了避免先随照索引来查找数据,然后再度举行同软降序排序的操作。如下测试所示:

 

 

SQL> SET LINESIZE 1200;

SQL> SET AUTOTRACE TRACEONLY;

SQL> SELECT * FROM SCOTT.EMP_TEST

  2  WHERE EMPNO >= 7788 

  3  ORDER BY EMPNO DESC;

 

7 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 4203867900

 

-----------------------------------------------------------------------------------------------

| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                |     5 |   190 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID | EMP_TEST       |     5 |   190 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_EMP_TEST_U |     5 |       |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO">=7788 AND "EMPNO" IS NOT NULL)

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

       1324  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          7  rows processed

 

SQL> 

 

 

 

摸索引全扫描(INDEX FULL SCAN)

 

 

查找引全扫描需要扫描目标索引所有叶子块的所有索引行。但是这不意味着索引全扫描需要扫描该索引的所有分支块。在默认情况下,索引全局扫描只待通过看必要的分支块定位及该索引最左边的纸牌块的率先行索引行。然后便好以该索引叶子块之间的双向指针链表,从左到右依次顺序扫描该索引所有叶子块的所有索引行了。索引全扫描的结果是板上钉钉的。它是依据目录的键值列来排序的。也就是说要走索引全局扫描能达标排序的作用。这样即使可以免对该索引的寻引键值再开排序操作。但是,也正是索引全扫描的有序性决定了寻引全扫描不可知并行执行。如果查询列普凡是目标索引的索引列,那么索引全扫描是无欲回表的。

 

 

SQL> CREATE INDEX SCOTT.IX_EMP_N1 ON SCOTT.EMP(EMPNO, ENAME);

 

Index created.

 

SQL> SET AUTOTRACE TRACEONLY;

 

SQL> select empno, ename from scott.emp order by empno,ename;

 

14 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 162731191

 

------------------------------------------------------------------------------

| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |           |    14 |   140 |     1   (0)| 00:00:01 |

|   1 |  INDEX FULL SCAN | IX_EMP_N1 |    14 |   140 |     1   (0)| 00:00:01 |

------------------------------------------------------------------------------

 

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          2  consistent gets

          0  physical reads

          0  redo size

        837  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         14  rows processed

 

SQL> 

 

 

 

 

目快速扫描(INDEX FAST FULL
SCAN)

 

 

目录快速扫描与目录全扫描一样,也待扫描目标索引所有的叶子块的所有索引行。它为适用于具有品种的B树索引(包括唯一性索引和非唯一性索引)。
索引快速全扫描和索引全扫描相比来以下三触及分别:

 

    1:
索引快速全扫描只适用于CBO模式,而寻引全扫描可以用来CBO也得以用于RBO。

    2:
索引快速全扫描可以动用多片读,也得以并行执行。这种存取方法中,可以以多片读功能,也得使并行读入,以便赢得无限老吞吐量和缩短执行时间

    3:
索引快速全扫描的实践结果未自然是一动不动的,因为索引快速全扫描是冲目录在磁盘上的物理存储顺序来围观的,而无是根据索引行的逻辑顺序来围观的。所以扫描结果不必然有序(对于单个索引叶子块被之搜索引行而言,其大体存储顺序与逻辑存储顺序是一样的;但对物理存储位置紧邻的目录叶子块而言,块以及片之间找引行的情理存储顺序则不自然当逻辑上平稳)

 

 

SQL> BEGIN

  2     FOR IndexLoop IN 0..1000 LOOP

  3             INSERT INTO SCOTT.EMP(EMPNO,ENAME) 

  4             VALUES(IndexLoop,CONCAT('TEST',IndexLoop));

  5     END LOOP;

  6  END;

  7  / 

PL/SQL procedure successfully completed.

 

SQL> execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>'EMP',

     estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

 

PL/SQL procedure successfully completed.

SQL> SELECT EMPNO FROM SCOTT.EMP;

 

1015 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 366039554

 

-------------------------------------------------------------------------------

| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |        |  1015 |  4060 |     3   (0)| 00:00:01 |

|   1 |  INDEX FAST FULL SCAN| PK_EMP |  1015 |  4060 |     3   (0)| 00:00:01 |

-------------------------------------------------------------------------------

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

         72  consistent gets

          0  physical reads

          0  redo size

      18050  bytes sent via SQL*Net to client

       1260  bytes received via SQL*Net from client

         69  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       1015  rows processed

 

 

 

目录跳跃扫描(INDEX SKIP SCAN)

 

 

 

当表有一个复合索引,而以查询中除索引中首先列外的别列作规范,并且优化器模式吗CBO,这时候查询计划即使发生或采用及目跳跃扫描Skip
scan会探测出索引前导列的唯一值个数,每个唯一值都见面当健康扫描的进口,在这基础及开同样糟搜索,最后合并这些查询

 

 

INDEX
SKIP SCAN,发生在多单列建立之复合索引上,如果SQL中称词条件只是包含索引中的部分列,并且这些列包含建立目录时的首先排列(前导列)时,就可能有INDEX SKIP
SCAN。索引跳跃扫描仅仅适用于那些目标索引前导列的DISTINCT值数量较少、后续非前导列的可选择性有非常好之情状下。

 

   

Oracle
10g的文档如下:

 

 

Index
Skip Scans

 

Index
skip scans improve index scans by nonprefix columns. Often, scanning
index

blocks
is faster than scanning table data blocks.

Skip
scanning lets a composite index be split logically into smaller
subindexes. In skip

scanning,
the initial column of the composite index is not specified in the query.
In

other
words, it is skipped.

The
number of logical subindexes is determined by the number of distinct
values in

the
initial column. Skip scanning is advantageous if there are few distinct
values in the

leading
column of the composite index and many distinct values in the
nonleading

key
of the index.

 

 

Example
13–5 Index Skip Scan

** 

Consider,
for example, a table employees (sex, employee_id, address) with
a

composite
index on (sex, employee_id). Splitting this composite index would
result

in
two logical subindexes, one for M and one for F.

For
this example, suppose you have the following index data:

(‘F’,98)

(‘F’,100)

(‘F’,102)

(‘F’,104)

(‘M’,101)

(‘M’,103)

(‘M’,105)

 

 

 

The
index is split logically into the following two subindexes:

 

 


The
first subindex has the keys with the value F.


The
second subindex has the keys with the value M.

 

Figure
13–2 Index Skip Scan Illustration

 

ACCESS 3

 

 

 

The
column sex is skipped in the following query:

SELECT
*

FROM
employees

WHERE
employee_id = 101;

A
complete scan of the index is not performed, but the subindex with the
value F is

searched
first, followed by a search of the subindex with the value M.

 

 

 

 

SQL> DROP TABLE SCOTT.OBJECT_TEST;

 

Table dropped.

 

SQL> CREATE TABLE SCOTT.OBJECT_TEST

  2  AS

  3     SELECT * FROM DBA_OBJECTS;

 

Table created.

 

SQL> CREATE INDEX SCOTT.IDX_OBJECT_TEST_N1 ON SCOTT.OBJECT_TEST(OWNER, OBJECT_ID, OBJECT_TYPE);

 

Index created.

 

SQL> execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>'OBJECT_TEST');

 

PL/SQL procedure successfully completed.

 

SQL>

SQL> SET AUTOTRACE TRACEONLY;

SQL> SELECT * FROM SCOTT.OBJECT_TEST

  2  WHERE OBJECT_ID=13;

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1063825859

 

--------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                    |     1 |    97 |    28   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| OBJECT_TEST        |     1 |    97 |    28   (0)| 00:00:01 |

|*  2 |   INDEX SKIP SCAN           | IDX_OBJECT_TEST_N1 |     1 |       |    27   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"=13)

       filter("OBJECT_ID"=13)

 

 

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

         18  consistent gets

          0  physical reads

          0  redo size

       1607  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL>  

** 

** 

 

参考资料:

 

http://blog.itpub.net/26736162/viewspace-2139246/

 

http://docs.oracle.com/cd/B19306_01/server.102/b14211/optimops.htm#i51571 

 

Index Lookup (Unique Scan,Range Scan, Full Scan, Fast Full Scan, Skip Scan)

 

 

相关文章