Oracle Tips  by Burleson Consulting

The Data Warehouse Development Life Cycle

Oracle Features for the Data Warehouse

Turning on the table cache option

1. That the table is defined as cached. (alter table CUSTOMER cache;)

2. That the table is smaller than the value of CACHE_SIZE_THRESHOLD.

3. That the table is being accessed with a full-table scan.

In summary, the table CACHE option is useful in cases where a small, frequently referenced table needs to remain in the buffer cache for a longer period than traditional table scans. This occurs when the table is frequently re-read by many transactions. It is implemented with alter table commands or cache hints, and it only works when the table is small, and is read with a full table scan.

---

This is an excerpt from "High Performance Data Warehousing". To learn more about Oracle, try "Oracle Tuning: The Definitive Reference", by Donald K. Burleson.  You can buy it direct from the publisher at 30% off here:
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm