CACHE | NOCACHE | CACHE READS

CACHE | NOCACHE | CACHE READS

Use the CACHE clauses to indicate how Oracle Database should store blocks in the buffer cache. If you specify neither CACHE nor NOCACHE, then:

In a CREATE TABLE statement, NOCACHE is the default

In an ALTER TABLE statement, the existing value is not changed.

CACHE For data that is accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables.

As a parameter in the LOB_storage_clause, CACHE specifies that the database places LOB data values in the buffer cache for faster access.

Restriction on CACHE You cannot specify CACHE for an index-organized table. However, index-organized tables implicitly provide CACHE behavior.

NOCACHE For data that is not accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. NOCACHE is the default for LOB storage.

As a parameter in the LOB_storage_clause, NOCACHE specifies that the LOB value either is not brought into the buffer cache or is brought into the buffer cache and placed at the least recently used end of the LRU list. The latter is the default behavior.

Restriction on NOCACHE You cannot specify NOCACHE for an index-organized table.

CACHE READS CACHE READS applies only to LOB storage. It specifies that LOB values are brought into the buffer cache only during read operations but not during write operations.

parallel_clause

The parallel_clause lets you parallelize creation of the table and set the default degree of parallelism for queries and the DML INSERT, UPDATE, DELETE, and MERGE after table creation.

Note:

The syntax of the parallel_clause supersedes syntax appearing in earlier releases of Oracle. Superseded syntax is still supported for backward compatibility but may result in slightly different behavior from that documented.
NOPARALLEL Specify NOPARALLEL for serial execution. This is the default.

PARALLEL Specify PARALLEL if you want Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.

PARALLEL integer Specification of integer indicates the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.

MONITORING | NOMONITORING

In earlier releases, you could use these clauses to start or stop the collection of modification statistics on this table. These clauses have been deprecated.

If you formerly collected modification statistics on tables by using the DBMS_STATS package in GATHER AUTO or GATHER STALE mode, then you no longer have to do anything. Oracle Database now collects these statistics automatically, and the MONITORING and NOMONITORING keywords in existing code will not cause an error.

If, for performance reasons, you do not want to collect modification statistics on any tables, then you should set the STATISTICS_LEVEL initialization parameter to BASIC. Be aware, however, that doing so disables a number of manageability features.

source : http://docs.oracle.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s