浅谈Oracle B-TREE索引扫描方式（上）

1.Index Unique Scans

• 适用场景：主键或唯一索引的等值匹配，最多只会返回一行数据，是单块读
• 索引唯一扫描的工作方式

    

 

• 示例：

SYS@pudge> create table xiaom.test1(id number primary key,create_time date default sysdate);

Table created.

SYS@pudge> begin
  2  for i in 1 .. 100000 loop
  3     insert into xiaom.test1(id)
  4     values
  5     (i);
  6     end loop;
  7     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SYS@pudge> set autotrace on
SYS@pudge> select * from xiaom.test1 where id = 5000;

        ID CREATE_TIME
---------- -------------------
      5000 2020-08-19 15:49:27


Execution Plan
----------------------------------------------------------
Plan hash value: 3480502009

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    22 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1       |     1 |    22 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C005694 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   2 - access("ID"=5000)


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

SYS@pudge> /

2.Index Range Scans

• 适用场景：1.主键或唯一索引的范围查询 2.非唯一性索引的等值和范围查询
• 工作方式：1）读取根节点  2）读取分支节点 3）交替执行一下步骤  a.读取叶子块获取rowid  b.通过rowid读取数据行

 

• 示例(以唯一性索引的范围匹配查询为例)：

  SYS@pudge> select * from xiaom.test1 where id >=5000 and id < 5010;
  
          ID CREATE_TIME
  ---------- -------------------
        5000 2020-08-19 15:49:27
        5001 2020-08-19 15:49:27
        5002 2020-08-19 15:49:27
        5003 2020-08-19 15:49:27
        5004 2020-08-19 15:49:27
        5005 2020-08-19 15:49:27
        5006 2020-08-19 15:49:27
        5007 2020-08-19 15:49:27
        5008 2020-08-19 15:49:27
        5009 2020-08-19 15:49:27
  
  10 rows selected.
  
  Execution Plan
  ----------------------------------------------------------
  Plan hash value: 3210885398
  
  -------------------------------------------------------------------------------------------
  | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
  -------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT            |             |    10 |   220 |     3   (0)| 00:00:01 |
  |   1 |  TABLE ACCESS BY INDEX ROWID| TEST1       |    10 |   220 |     3   (0)| 00:00:01 |
  |*  2 |   INDEX RANGE SCAN          | SYS_C005694 |    10 |       |     2   (0)| 00:00:01 |
  -------------------------------------------------------------------------------------------
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     2 - access("ID">=5000 AND "ID"<5010)
  
  Note
  -----
     - dynamic sampling used for this statement (level=2)
  
  
  Statistics
  ----------------------------------------------------------
            9  recursive calls
            0  db block gets
           64  consistent gets
            0  physical reads
            0  redo size
          808  bytes sent via SQL*Net to client
          524  bytes received via SQL*Net from client
            2  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
           10  rows processed

  3.Index Full Scans

• 使用场景：按照顺序读取整个索引，是一种相对比较差劲的扫描方式（单块读）
• 工作方式：数据库读取根块，然后在索引的左侧向下导航（如果进行向下的完整扫描，则向右导航），直到到达叶块为止。然后数据库到达一个叶块，扫描按索引顺序遍历索引的底部，一次跨一个块。数据库使用单块I / O而不是多块I / O。

 

• 示例：

SYS@pudge> select id from xiaom.test1 order by id ;
Execution Plan
----------------------------------------------------------
Plan hash value: 181745691

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             | 97463 |  1237K|   245   (0)| 00:00:03 |
|   1 |  INDEX FULL SCAN | SYS_C005694 | 97463 |  1237K|   245   (0)| 00:00:03 |
--------------------------------------------------------------------------------

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


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
       6900  consistent gets
          0  physical reads
          0  redo size
    1829390  bytes sent via SQL*Net to client
      73850  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed