SQL> create table test(id int,name char(10)); Table created. SQL> begin 2 for i in 1 .. 10000 3 loop 4 insert into test values(i,'a'||i); 5 end loop; 6 commit; 7 end ; 8 / PL/SQL procedure successfully completed. SQL> select count(*) from test; COUNT(*) ---------- 10000 SQL> create index test_idx1 on test(id); Index created. BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST', tabname => 'TEST', estimate_percent => 100, method_opt => 'for all columns size skewonly', no_invalidate => FALSE, degree => 1, cascade => TRUE); END; SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from test; COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) --------------------------------------------------- 28 SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from test where id<300; COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) --------------------------------------------------- 1 总共存放在28个块里,其中id<300的存放在一个块里 SQL> select * from test where id<300; 299 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2624864549 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 299 | 4485 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 299 | 4485 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TEST_IDX1 | 299 | | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"<300) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 43 consistent gets 0 physical reads 0 redo size 8853 bytes sent via SQL*Net to client 628 bytes received via SQL*Net from client 21 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 逻辑读是43 那43是怎么来的呢? SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------- SQL_ID 7vthtgk378xdk, child number 1 ------------------------------------- select * from test where id<300 Plan hash value: 2624864549 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 299 |00:00:00.01 | 43 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 299 | 299 |00:00:00.01 | 43 | |* 2 | INDEX RANGE SCAN | TEST_IDX1 | 1 | 299 | 299 |00:00:00.01 | 22 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"<300) 19 rows selected. 查看下索引的高度: SQL> select index_name, PREFIX_LENGTH, BLEVEL, LEAF_BLOCKS from user_indexes where index_name='TEST_IDX1'; INDEX_NAME PREFIX_LENGTH BLEVEL LEAF_BLOCKS ------------------------------ ------------- ---------- ----------- TEST_IDX1 1 21 可以看到索引高度为1,叶子节点有21个块 BLEVEL* NUMBER B*-Tree level: depth of the index from its root block to its leaf blocks. A depth of 0 indicates that the root block and leaf block are the same. 最底层的索引块(叶块(leaf block))存储了被索引的数据值,以及对应的 rowid。一个leaf block存储多个被索引的数据值 因为ID<300的表数据都在一个块里,那么是不是回表就不会增加逻辑读,也就是说访问表的逻辑读始终为1,那么推测访问索引的逻辑读为42 那42是怎么来的呢?表示疑问? 从set autot trace看是索引读了1+21=22个,回表后逻辑读变为43 那回表都干了些什么呢 增加了21个逻辑读?21个逻辑读难道是正好回表21次,那不符合前面的逻辑?