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 

 

https://databaseinternalmechanism.com/oracle-database-internals/index-lookup-unique-scanrange-scan-full-scan-fast-full-scan-skip-scan/

 

 

相关文章