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 DEFAULTSQL> 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 FORCESQL> 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
网友评论已有0条评论, 我也要评论