聚合国内IT技术精华文章,分享IT技术精华,帮助IT从业人士成长

enable Result Cache on Table level

2013-12-07 03:01 浏览: 1848811 次 我要评论(0 条) 字号:

Oracle 11g does support table level result cache. there’s a column “RESULT_CACHE” on dba_tables.

Table annotations affect the entire query, not query segments. The primary benefit of using table annotations is avoiding the necessity of adding result cache hints to queries at the application level. Because a table annotation has a lower precedence than a SQL result cache hint, you can override table and session settings by using hints at the query level.

SQL> select table_name,RESULT_CACHE from user_tables where rownum<=10;
TABLE_NAME                     RESULT_
—————————— ——-
BINZHANG_CR                    DEFAULT

SQL> alter table BINZHANG_CR RESULT_CACHE (MODE FORCE);
Table altered.

SQL>  select table_name,RESULT_CACHE from user_tables where rownum<=10;
TABLE_NAME                     RESULT_
—————————— ——-
BINZHANG_CR                    FORCE

SQL> select * from binzhang_cr;
ID
———-
1
SQL> set autotrace on
SQL> /
ID
———-
1
Execution Plan
———————————————————
| Id  | Operation          | Name                       |
———————————————————
|   0 | SELECT STATEMENT   |                            |
|   1 |  RESULT CACHE      | 3fm7wczz65dy7dbbwchk5hknh2 |
|   2 |   TABLE ACCESS FULL| BINZHANG_CR                |
———————————————————
Result Cache Information (identified by operation id):
——————————————————
1 - column-count=1; dependencies=(ORACLE.BINZHANG_CR); name=”select * from binzhang_cr”
Statistics
———————————————————-
0  recursive calls
0  db block gets
0  consistent gets
0  physical reads
0  redo size
520  bytes sent via SQL*Net to client
519  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

if query is using multiple table join, you need to enable force cache on the mutliple tables.

Reference :
query result cache in oracle 11g

Using Result Cache Table Annotations



网友评论已有0条评论, 我也要评论

发表评论

*

* (保密)

Ctrl+Enter 快捷回复