下面繼續說明KEEP池和DEFAULT的一些不同之處。
首先，建立T表的一個索引：
SQL> CREATE INDEX IND_T_NAME ON T (NAME) STORAGE (BUFFER_POOL KEEP);
Index created.
SQL> ALTER TABLE T MODIFY NAME NOT NULL;
Table altered.
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
---------------
120 recursive calls
0 db block gets
9682 consistent gets
9655 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
4 sorts (memory)
0 sorts (disk)
1 rows processed
下面看KEEP池和DEFAULT池快取的另一個區別：
SQL> CREATE INDEX IND_T_NAME ON T (NAME) STORAGE (BUFFER_POOL KEEP);
Index created.
SQL> ALTER TABLE T MODIFY NAME NOT NULL;
Table altered.
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
-----------------
120 recursive calls
0 db block gets
9682 consistent gets
9655 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
4 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;

OBJECT_NAME STATU COUNT(*)
-----------------------
T xcur 8096
SQL> SELECT /*+ INDEX (T) */ COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
----------------
0 recursive calls
0 db block gets
538 consistent gets
538 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;

OBJECT_NAME STATU COUNT(*)
---------------------------
T xcur 7558
IND_T_NAME xcur 538
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
------------
0 recursive calls
0 db block gets
9666 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
----------------------------
T xcur 8096
透過測試可以看到，在KEEP池中，索引掃描引起的快取不在擁有高於全表掃描快取的優先順序。
根據上一篇文章中描述的KEEP池的快取使用特點也可以推出這個結果。由於KEEP池不存在LRU連結串列，
因此對索引掃描和全表掃描產生的快取採用一視同仁的態度。[@more@]

oracle cache table(5)

相關文章