oracle 全文扫描,ORACLE 索引全扫描逻辑读

全索引扫描 我以为是这样的情况 通过索引的叶节点的双向链表,走完全不索引叶块后,再去回表读数据!

SELECT id

FROM (SELECT MIN(BV_ID) id

FROM RBS.RBS_FIRST_FAILURE_BLACKLIST

GROUP BY BV_BE_ID, UPPER(BV_VALUE)

HAVING COUNT(1) > 1)

WHERE rownum <= 500;

执行计划

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

Plan hash value: 1883400118

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

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

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

|   0 | SELECT STATEMENT|      |   500 |  6500 |   511(0)| 00:00:07 |

|*  1 |  COUNT STOPKEY|      |       |       |   |      |

|   2 |   VIEW|      |  3014K|    37M|   511(0)| 00:00:07 |

|*  3 |    FILTER|      |       |       |   |      |

|   4 |     SORT GROUP BY NOSORT|      |  3014K|  2955M|   511(0)| 00:00:07 |

|   5 |      TABLE ACCESS BY INDEX ROWID| RBS_FIRST_FAILURE_BLACKLIST |  3014K|  2955M|   511(0)| 00:00:07 |

|   6 |       INDEX FULL SCAN| IX_FIRSBFAIL_BEID      |   500 |       |     4(0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter(ROWNUM<=500)

3 - filter(COUNT(*)>1)

Note

-----

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

统计信息

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

8  recursive calls

0  db block gets

3060089  consistent gets

0  physical reads

0  redo size

528  bytes sent via SQL*Net to client

520  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

select object_name,object_id,data_object_id

from USER_objects

where  object_name in ('RBS_FIRST_FAILURE_BLACKLIST','IX_FIRSBFAIL_BEID');

OBJECT_NAME                                     OBJECT_IDDATA_OBJECT_ID

IX_FIRSBFAIL_BEID                                75247         75247

RBS_FIRST_FAILURE_BLACKLIST75243        75244

75247=>125EF

75244=>125EC

ORADEBUG出来的TRC居然达到1.7GB

[oracle@132-MRBS-SZ trace]more 8580_stared_block.txt

ktrgtc2(): started for block <0x0007 : 0x018006c3> objd: 0x000125ef

ktrgtc2(): started for block <0x0007 : 0x01800ce1> objd: 0x000125ef

ktrget2(): started for block  <0x0007 : 0x018006c4> objd: 0x000125ef

ktrget2(): started for block  <0x0006 : 0x01446946> objd: 0x000125ec

.....................................................................

ktrget2(): started for block  <0x0007 : 0x018006c5> objd: 0x000125ef

从上面内容可知它依旧是读叶块,再读数据块,再读叶块

叶块读了6405次

[oracle@132-MRBS-SZ trace]cat 8580_stared_block.txt |grep 0x000125ef |wc -l

6405

数据块读了3053656次

[oracle@132-MRBS-SZ trace]cat 8580_stared_block.txt |grep 0x000125ec |wc -l

3053656

如果设置array 5000是否会改变呢?

[oracle@132-MRBS-SZ trace]cat 9520_stared_block.txt |grep 0x000125ef|wc -l

6405

[oracle@132-MRBS-SZ trace]cat 9520_stared_block.txt |grep 0x000125ec|wc -l

3053532

看样子次数不会减少很多