oracle cache table,oracle cache table(6)

第二个特点，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@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18921899/viewspace-1017604/，如需转载，请注明出处，否则将追究法律责任。