1.146 INMEMORY_CLAUSE_DEFAULT
INMEMORY_CLAUSE_DEFAULT
enables you to specify a default In-Memory Column Store (IM column store) clause for new tables and materialized views.
Note:
The setting of his parameter has no effect on In-Memory external tables or hybrid partitioned tables.Property | Description |
---|---|
Parameter type |
String |
Syntax |
|
Syntax |
other-clauses::=
|
Syntax |
compression-clause::=
|
Syntax |
priority-clause::=
|
Syntax |
rac-clause::=
|
Syntax |
distribute-clause::=
|
Syntax |
duplicate-clause::=
|
Default value |
An empty string |
Modifiable |
|
Modifiable in a PDB |
Yes |
Basic |
No |
Oracle RAC |
All instances should use the same value |
Footnote 1 See Table 1-2 for more information about this clause.
If the INMEMORY_CLAUSE_DEFAULT
parameter is unset or set to an empty string (the default), only tables and materialized views explicitly specified as INMEMORY
will be populated into the IM column store. Setting the value of the INMEMORY_CLAUSE_DEFAULT
parameter to NO INMEMORY
has the same effect as setting it to the default value.
If the INMEMORY_CLAUSE_DEFAULT
parameter is set, then any newly created table or materialized view specified as INMEMORY
will inherit unspecified attributes from this parameter. This can force certain in-memory options by default that are not explicitly specified in the syntax. For example, if the INMEMORY_CLAUSE_DEFAULT
parameter is set to MEMCOMPRESS FOR CAPACITY LOW
and a table is created as INMEMORY PRIORITY HIGH
, then the table is treated as if it was declared as INMEMORY MEMCOMPRESS FOR CAPACITY LOW PRIORITY HIGH
.
If INMEMORY
is specified as part of this parameter, then all newly created tables and materialized views will be populated into the IM column store, except tables and materialized views explicitly specified as NO INMEMORY
. For example, if this parameter is set to INMEMORY MEMCOMPRESS FOR CAPACITY HIGH
, then all new tables will be created as if this clause were present in the SQL CREATE TABLE
statement. If there is a default INMEMORY
value for the tablespace for a given segment, then it will override the value for this parameter.
Table 1-2 Meaning of INMEMORY_CLAUSE_DEFAULT Parameter Values
Syntax | Description |
---|---|
|
Specifies that all newly-created tables and materialized views populate the IM column store unless they are specified as |
|
Specifies that only tables and materialized views explicitly specified as |
|
Specifies that in-memory compression should be used for the instance. Use the |
|
When |
|
|
|
When |
|
When |
|
When |
|
When |
|
When |
|
When |
|
When |
|
Specifies the priority to use when populating tables in the IM column store. Use the By default, the population of a table in the IM column store can be delayed until the database determines it is useful. On database instance startup, tables are populated in priority order. |
|
When This is the default value when no priority is specified. |
|
When |
|
When |
|
When |
|
When |
|
Specifies how tables in the IM column store will be managed among Oracle Real Application Clusters (Oracle RAC) instances. Use the For a non-Oracle RAC database, these settings have no effect, because the whole table or partition has to be on the single instance. |
|
Specifies how a table is distributed among Oracle RAC instances. |
|
Specifies that the database will automatically decide how to distribute tables in the IM column store across the Oracle RAC instances based on the type of partitioning and the value of the
|
|
Specifies that the tables in the IM column store will be distributed by rowid range to different Oracle RAC instances. |
|
Specifies how many copies of each In-Memory Compression Unit (IMCU) of the tables in the IM column store will be spread across all the Oracle RAC instances. Note: The |
|
Data is not duplicated across Oracle RAC instances. This is the default. |
|
Data is duplicated on another Oracle RAC instance, resulting in data existing on a total of two Oracle RAC instances.. |
|
Data is duplicated across all Oracle RAC instances. If you specify |
Examples
The following statement causes no tables to populate the IM column store:
alter system set INMEMORY_CLAUSE_DEFAULT='NO INMEMORY' scope=both;
The following statement causes new tables and materialized views (except those specified as NO INMEMORY
) to populate the IM column store at the high capacity compression level:
alter system set INMEMORY_CLAUSE_DEFAULT='INMEMORY MEMCOMPRESS FOR CAPACITY HIGH' scope=both;
The following statement causes new tables (even those specified as NO INMEMORY
) to populate the IM column store at the highest performance compression level at LOW
priority:
alter system set INMEMORY_CLAUSE_DEFAULT='INMEMORY MEMCOMPRESS FOR QUERY LOW PRIORITY LOW' scope=both;
The following statement causes new tables (even those specified as NO INMEMORY
) to populate the IM column store without any in-memory compression:
alter system set INMEMORY_CLAUSE_DEFAULT='INMEMORY NO MEMCOMPRESS' scope=both;
The following statement causes tables in the IM column store to be duplicated on every Oracle RAC instance, unless on a non-engineered system. For a non-engineered system, the duplicate-clause
(DUPLICATE ALL
) will be ignored and tables in the column store will be automatically distributed across the Oracle RAC instance, with only one copy of each IMCU in the tables in the IM column store:
alter system set INMEMORY_CLAUSE_DEFAULT='INMEMORY MEMCOMPRESS FOR QUERY DISTRIBUTE AUTO DUPLICATE ALL' scope=both;
The following statement sets the value of the INMEMORY_CLAUSE_DEFAULT
parameter back to its default value, the empty string:
alter system set INMEMORY_CLAUSE_DEFAULT='' scope=both;
See Also:
-
Oracle Database In-Memory Guide for an introduction to the IM column store
-
Oracle Database In-Memory Guide for more information about the IM column store
-
Oracle Database In-Memory Guide for more information on IM column store compression methods
-
Oracle Database In-Memory Guide for more information on priority levels for populating a database object in the IM column store
-
Oracle Database SQL Language Reference for more information on the CREATE TABLE statement
-
Oracle Database SQL Language Reference for more information on the CREATE MATERIALIZED VIEW statement