第二個特點，CACHE儲存引數無效：
SQL> CREATE TABLE T3 STORAGE(BUFFER_POOL KEEP) AS SELECT * FROM T2;
Table created.
SQL> ALTER TABLE T2 CACHE;
Table altered.
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
334022
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')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
--------------------------
T xcur 8096
SQL> SELECT COUNT(*) FROM T2;
COUNT(*)
----------
167011
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')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
---------------------------
T xcur 3267
T2 xcur 4829

SQL> SELECT COUNT(*) FROM T3;
COUNT(*)
----------
167011
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')
5 GROUP BY OBJECT_NAME, A.STATUS;

OBJECT_NAME STATU COUNT(*)
--------------------------
T2 xcur 3267
T3 xcur 4829
SQL> SELECT COUNT(*) FROM T WHERE ROWNUM < 100000;
COUNT(*)
----------
99999
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')
5 GROUP BY OBJECT_NAME, A.STATUS;

OBJECT_NAME STATU COUNT(*)
------------------------------
T xcur 3696
T3 xcur 4400

透過測試可以發現，CACHE選項沒有起作用，其實這也不難理解，既然放到單獨的KEEP池中，那麼必然打
算將這個物件快取，因此Oracle對所有KEEP池中的物件採用了預設CACHE的方式。而忽略物件本身的CACHE和NOCACHE選項[@more@]

oracle cache table(6)

相關文章