Author: kmaus (create
2003-6-4
)
Mail: kamus@itpub.net
Copyright Notice
Reprinted please indicate the author and the source
Cause (why you need cache table):
By default, if a table is read into buffer cache by Full Table Scan (FTS), then these blocks will be immediately placed in the end of the LRU LIST's Leastly Used. Oracle's purpose is to prevent large full-mete scans that other Block in Buffer Cache is extruded into buffer cache.
However, this processing method has led to an optimized place to take special attention, especially when using the CBO optimizer.
If the CBO optimizer finds a list of frequently used and relatively small, CBO usually uses full table scans to read the data of this table. Because it is a full surface scan, as mentioned above, the read block will be placed immediately in the end of the LRU List, which will soon be squeezed out of the next time you read. Buffer Cache, to the next use At this table, Oracle has to read data from the disk to Buffer Cache, which is undoubtedly a relatively expensive operation.
Solution:
Designed such a small table as a cached table. When the cached table is scanned, it will not be placed at the end of Least Recessly Used, but is placed at the end of the MOST Recessly Used. This ensures that such data can stay in buffer cache longer.
Implementation:
1. Specify when CREATE TABLE:
SQL> CREATE TABLE A
2 ID Number,
3 other varchar2 (10))
4 TableSpace App1
5 Storage (Initial 50K Next 50k Pctincrease 0)
6 [color = red] cache [/ color];
2. After creating, use the alter table to modify:
SQL> ALTER TABLE A [Color = Red] Cache [/ color];
3. Use hint
SQL> SELECT [color = red] / * cache * / [/ color] ID, Other from A;
Note: This operation is only valid for the current query, and the other to the table's retrieval will still use the default cache way of the table.
Check information about Cache Table
SQL> SELECT OWNER, TABLE_NAME
2 from DBA_TABLES
3 WHERE LTRIM (CACHE) = 'Y';
