【IT168 技术文档】
RECYCLE池和DEFAULT池的算法在索引方面有相似之处：都是认为索引读产生的BLOCK应该被缓存。
首先，清掉RECYCLE池。
SQL> ALTER TABLESPACE NDMAIN OFFLINE;
Tablespace altered.
SQL> ALTER TABLESPACE NDMAIN ONLINE;
Tablespace altered.
利用T2和T3表将RECYCLE填满：
SQL> SELECT COUNT(*) FROM T2;
COUNT(*)
----------
167011
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4839 consistent gets
4829 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT COUNT(*) FROM T3;
COUNT(*)
----------
167011
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4839 consistent gets
4829 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'T3', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T2 xcur 4829
T3 xcur 3267
下面创建一个索引，测试索引扫描和表扫描的区别：
SQL> CREATE INDEX IND_T_NAME ON T (NAME) STORAGE (BUFFER_POOL RECYCLE);
Index created.
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9667 consistent gets
9656 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'T3', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 17
T2 xcur 4829
T3 xcur 3248
IND_T_NAME xcur 2
SQL> SELECT /**//*+ INDEX(T) */ COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
538 consistent gets
537 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'T3', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 1
T2 xcur 4829
T3 xcur 2727
IND_T_NAME xcur 539
SQL> SELECT /**//*+ INDEX(T) */ COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
538 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
很显然，索引BLOCK的CACHE和全表扫描的结果不同，索引的BLOCK全部被CACHE到RECYCLE池中。
