Table Cache Option
Oracle Tips by Burleson Consulting
The Data Warehouse Development Life Cycle
Oracle Features for the Data Warehouse
create table customer
Turning on the table cache option
There are two ways to activate the table CACHE option. The option
can be specified permanently with a CREATE TABLE or ALTER TABLE
command, or a cache hint can be used with the cost-based SQL
optimizer to direct the data blocks. A create and alter table
example is shown below:
storage (next . . . )
alter table orders cache;
Another method of invoking table caching can be done by adding CACHE
hints. The CACHE hint is generally used in conjunction with the FULL
hint to insure that a full-table scan is being performed against the
target table. In the following example, a reference table called
ZIP_CODE is cached into the buffer.
Select /*+ FULL(ZIP_CODE) CACHE(ZIP_CODE) */
zip_code, city_name, county_name, state_name
The main purpose of the CACHE option is to allow small tables that
are always read from front to back to remain in the data buffer.
These types of table might include small reference tables or any
tables that are intended to be read by using a full-table scan.
Remember, the CACHE option requires:
This is an excerpt from "High Performance
Data Warehousing", copyright 1997.
like Oracle tuning, you may enjoy the book
Oracle Tuning: The Definitive Reference , with
over 900 pages of BC's favorite tuning tips &
You can buy it
directly from the publisher and save 30%, and get
instant access to the code depot of Oracle tuning