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次,那不符合前面的逻辑？