Enabling Objects for In-Memory Population

4 Enabling Objects for In-Memory Population

This chapter explains how to enable and disable objects for population in the IM column store, including setting compression and priority options.

This chapter contains the following topics:

4.1 About In-Memory Population

In-Memory population (population) occurs when the database reads existing row-format data from disk, transforms it into columnar format, and then stores it in the IM column store.

Only objects with the INMEMORY attribute are eligible for population.

This section contains the following topics:

4.1.1 Purpose of In-Memory Population

The IM column store does not automatically load all objects in the database into the IM column store.

If you do not use DDL to specify any objects as INMEMORY, then the IM column store remains empty. Population is necessary to transform rows from user-specified INMEMORY objects into columnar format, so that they are available for analytic queries.

Population, which transforms existing data on disk into columnar format, is different from repopulation, which loads new data into the IM column store. Because IMCUs are read-only structures, Oracle Database does not populate them when rows change. Rather, the database records the row changes in a transaction journal, and then creates new IMCUs as part of repopulation.

Related Topics

4.1.2 How In-Memory Population Works

You can specify that the database populates objects in the IM column store either at database instance startup or when INMEMORY objects are accessed.

The population algorithm also varies depending on whether you use single-instance or Oracle RAC.

This section contains the following topics:

4.1.2.1 Prioritization of In-Memory Population

DDL statements include an INMEMORY PRIORITY subclause that provides more control over the population queue.

Note:

The INMEMORY PRIORITY subclause controls the priority of population, but not the speed of population.

The priority level setting applies to an entire table, partition, or subpartition, not to different column subsets. Setting the INMEMORY attribute on an object means that this object is a candidate for population in the IM column store. It does not mean that the database immediately populates the object.

Note:

If a segment on disk is 64 KB or less, then it is not populated in the IM column store. Therefore, some small database objects that were enabled for the IM column store might not be populated.

Oracle Database manages prioritization as follows:

On-demand population

By default, the INMEMORY PRIORITY parameter is set to NONE. In this case, the database only populates the object when it is accessed through a full table scan. If the object is never accessed, or if it is accessed only through an index scan or fetch by rowid, then population never occurs.
Priority-based population

When PRIORITY is set to a value other than NONE, Oracle database automatically populates the objects using an internally managed priority queue. In this case, a full scan is not a necessary condition for population. The database does the following:
- Populates columnar data in the IM column store automatically after the database instance restarts
- Queues population of INMEMORY objects based on the specified priority level
  
  For example, a table altered with INMEMORY PRIORITY CRITICAL takes precedence over a table altered with INMEMORY PRIORITY HIGH, which in turn takes precedence over a table altered with INMEMORY PRIORITY LOW. If the IM column store has insufficient space, then Oracle Database does not populate additional objects until space is available.
- Waits to return from ALTER TABLE or ALTER MATERIALIZED VIEW statements until the changes to the object are recorded in the IM column store

After a segment is populated in the IM column store, the database only evicts it when the segment is dropped or moved, or the segment is updated with the NO INMEMORY attribute. You can evict a segment manually or by means of an ADO policy.

Example 4-1 Population of an Object in the IM Column Store

Before completing this example, the IM column store must be enabled for the database.

SELECT cust_id, cust_last_name, cust_first_name 
FROM   sh.customers 
WHERE  cust_city = 'Hyderabad' 
AND    cust_income_level LIKE 'C%' 
AND    cust_year_of_birth > 1960;

Display the execution plan for the query:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'+ALLSTATS'));
SQL_ID  frgk9dbaftmm9, child number 0
-------------------------------------
SELECT cust_id, cust_last_name, cust_first_name FROM   sh.customers
WHERE  cust_city = 'Hyderabad' AND    cust_income_level LIKE 'C%' AND
 cust_year_of_birth > 1960

Plan hash value: 2008213504

-------------------------------------------------------------------------------
| Id| Operation         | Name      |Starts|E-Rows|A-Rows|   A-Time   |Buffers|
-------------------------------------------------------------------------------
|  0| SELECT STATEMENT  |           |     1|      |    6 |00:00:00.01 |   1523|
|* 1|  TABLE ACCESS FULL| CUSTOMERS |     1|    6 |    6 |00:00:00.01 |   1523|
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("CUST_CITY"='Hyderabad' AND "CUST_YEAR_OF_BIRTH">1960 AND
              "CUST_INCOME_LEVEL" LIKE 'C%'))

Enable the sh.customers table for population in the IM column store:
```
ALTER TABLE sh.customers INMEMORY;
```
The preceding statement uses the default priority of NONE. A full scan is required to populate objects with no priority.
To determine whether data from the sh.customers table has been populated in the IM column store, execute the following query (sample output included):
```
SELECT SEGMENT_NAME, POPULATE_STATUS 
FROM   V$IM_SEGMENTS 
WHERE  SEGMENT_NAME = 'CUSTOMERS';

no rows selected
```
In this case, no segments are populated in the IM column store because the sh.customers table has not yet been scanned.

Query sh.customers using the same statement as in Step 1:

SELECT cust_id, cust_last_name, cust_first_name 
FROM   sh.customers 
WHERE  cust_city = 'Hyderabad' 
AND    cust_income_level LIKE 'C%' 
AND    cust_year_of_birth > 1960;

Querying the cursor shows that the database performed a full scan and accessed the IM column store:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'+ALLSTATS'));
SQL_ID  frgk9dbaftmm9, child number 0
-------------------------------------
SELECT cust_id, cust_last_name, cust_first_name FROM   sh.customers
WHERE  cust_city = 'Hyderabad' AND    cust_income_level LIKE 'C%' AND
 cust_year_of_birth > 1960

Plan hash value: 2008213504

---------------------------------------------------------------------------------
| Id| Operation           | Name            |Starts|E-Rows|A-Rows|A-Time|Buffers|
---------------------------------------------------------------------------------
|  0| SELECT STATEMENT           |           |    1|     | 6 |00:00:00.02| 1523 |
|* 1|  TABLE ACCESS INMEMORY FULL| CUSTOMERS |    1|    6| 6 |00:00:00.02| 1523 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - inmemory(("CUST_CITY"='Hyderabad' AND "CUST_YEAR_OF_BIRTH">1960 AND
              "CUST_INCOME_LEVEL" LIKE 'C%'))
       filter(("CUST_CITY"='Hyderabad' AND "CUST_YEAR_OF_BIRTH">1960 AND
              "CUST_INCOME_LEVEL" LIKE 'C%'))

Query V$IM_SEGMENTS again (sample output included):

COL SEGMENT_NAME FORMAT a20

SELECT SEGMENT_NAME, POPULATE_STATUS 
FROM   V$IM_SEGMENTS 
WHERE  SEGMENT_NAME = 'CUSTOMERS';

SEGMENT_NAME         POPULATE_STATUS
-------------------- ---------------
CUSTOMERS            COMPLETED

The value COMPLETED in POPULATE_STATUS means that the table is populated in the IM column store.

The DBA_FEATURE_USAGE_STATISTICS view confirms that the database used the IM column store to retrieve the results:

COL NAME FORMAT a25
SELECT ul.NAME, ul.DETECTED_USAGES 
FROM   DBA_FEATURE_USAGE_STATISTICS ul 
WHERE  ul.VERSION= (SELECT MAX(u2.VERSION) 
                    FROM   DBA_FEATURE_USAGE_STATISTICS u2 
                    WHERE  u2.NAME = ul.NAME 
                    AND    ul.NAME LIKE '%Column Store%');

NAME                      DETECTED_USAGES
------------------------- ---------------
In-Memory Column Store    1

Oracle Database SQL Language Reference to learn about the INMEMORY PRIORITY clause

4.1.2.2 How Background Processes Populate IMCUs

During population, the database reads data from disk in its row format, pivots the rows to create columns, and then compresses the data into In-Memory Compression Units (IMCUs).

Worker processes (Wnnn) populate the data in the IM column store. Each worker process operates on a subset of database blocks from the object. Population is a streaming mechanism, simultaneously compressing the data and converting it into columnar format.

The INMEMORY_MAX_POPULATE_SERVERS initialization parameter specifies the maximum number of worker processes to use for IM column store population. By default, the setting is one half of CPU_COUNT. Set this parameter to an appropriate value for your environment. More worker processes result in faster population, but they use more CPU resources. Fewer worker processes result in slower population, which reduces CPU overhead.

Note:

If INMEMORY_MAX_POPULATE_SERVERS is set to 0, then population is disabled.

See Also:

Oracle Database Reference for more information about the INMEMORY_MAX_POPULATE_SERVERS initialization parameter

4.1.3 Controls for In-Memory Population

Use the INMEMORY clause in DDL statements to specify which objects are eligible for population into the IM column store. You can enable tablespaces, tables (internal and external), partitions, and materialized views.

This section contains the following topics:

4.1.3.1 The INMEMORY Subclause

INMEMORY is a segment-level attribute, not a column-level attribute. However, you can apply the INMEMORY attribute to a subset of columns within a specific object.

To enable or disable an object for the IM column store, specify the INMEMORY clause in DDL statements for tablespaces, tables, and materialized views. The INMEMORY column in the DBA_TABLES view indicates which tables have the INMEMORY attribute set (ENABLED) or not set (DISABLED).

The following objects are not eligible for population in the IM column store:

Indexes
Index-organized tables
Hash clusters
Objects owned by the SYS user and stored in the SYSTEM or SYSAUX tablespace

This section contains the following topics:

4.1.3.1.1 In-Memory Tables

To make heap-organized tables eligible for population, specify INMEMORY on the CREATE TABLE or ALTER TABLE statements.

By default, the IM column store populates all nonvirtual columns in the table. You can specify all or some columns of an internal table. For example, you might exclude the weight_class and catalog_url columns in oe.product_information from eligibility.

For a partitioned table, you can populate all or some partitions in the IM column store. By default, all partitions in a partitioned table inherit the INMEMORY attribute.

On Oracle Exadata Storage Server, the CELLMEMORY keyword (default) enables the flash cache to store data in the In-Memory format. You can use ALTER TABLE to choose FOR QUERY or FOR CAPACITY compression. Specifying NO CELLMEMORY disables columnar storage in the flash cache.

If you enable a table for the IM column store and it contains any of the following types of columns, then they will not be populated in the IM column store:

Out-of-line columns (varrays, nested table columns, and out-of-line LOBs)

Note:

For inline LOB columns, the IM column store allocates up to 4 KB of contiguous buffer storage, and up to 32 KB when the inline LOBs contain OSON (binary JSON) data. For out-of-line LOBs, the IM column store allocates up to 40 bytes for the locator, but does not store the LOB itself.
Columns that use the LONG or LONG RAW data types
Extended data type columns

Example 4-2 Specifying a Table as INMEMORY

Assume that you are connected to the database as user sh. You enable the customers table for population in the IM column store, using the default compression level of FOR QUERY LOW:

SQL> SELECT TABLE_NAME, INMEMORY FROM USER_TABLES WHERE TABLE_NAME = 'CUSTOMERS';

TABLE_NAME INMEMORY
---------- --------
CUSTOMERS  DISABLED

SQL> ALTER TABLE customers INMEMORY;

Table altered.

SQL> SELECT TABLE_NAME, INMEMORY, INMEMORY_COMPRESSION FROM USER_TABLES WHERE TABLE_NAME='CUSTOMERS';

TABLE_NAME INMEMORY INMEMORY_COMPRESS
---------- -------- -----------------
CUSTOMERS  ENABLED  FOR QUERY LOW

See Also:

"In-Memory Compression"
"Enabling the IM Column Store for a Database"
Oracle Exadata System Software User's Guide to learn more about ALTER TABLE ... CELLMEMORY
Oracle Database SQL Language Reference for information about INMEMORY clause of the CREATE TABLE statement

4.1.3.1.2 In-Memory External Tables

To make external tables eligible for population, specify INMEMORY on the CREATE TABLE ... EXTERNAL or ALTER TABLE ... EXTERNAL statements.

Purpose of In-Memory External Tables

In-Memory external tables are useful in the following cases:

Short-term data that must be scanned repeatedly in a short time span and does not require retention in Oracle Database
External data that must be joined to relational data for fast analytic processing
Data that is accessed by analytic queries in both Oracle Database and external tools, and which does not need to be materialized in database storage

Restrictions for In-Memory External Tables

The IM column store manages the data for external tables in the same way as for heap-organized tables, with the following differences:

Automatic population and repopulation are not supported: you must use DBMS_INMEMORY.REPOPULATE.
Some INMEMORY subclauses for external tables are not valid, including the column clause, distribute clause, and priority clause.
Only the ORACLE_LOADER and ORACLE_DATAPUMP access drivers are supported.
In-Memory Optimized Arithmetic does not support external tables.
In-Memory external tables cannot be accessed by parallel execution.
Partitioning is not supported for In-Memory external tables.
Join groups are not supported for In-Memory external tables.
IM expressions are not supported for In-Memory external tables.

Note:

Sessions that query In-Memory external tables must have the initialization parameter QUERY_REWRITE_INTEGRITY set to stale_tolerated. If an external table is modified, then the results from the IM column store are undefined.

See Also:

"Creating and Populating External Tables: Examples"
Oracle Database SQL Language Reference for information about INMEMORY clause of the CREATE TABLE ... EXTERNAL statement
Oracle Database Licensing Information User Manual for details on which editions and services support In-Memory external tables

4.1.3.1.3 In-Memory Materialized Views

You can make materialized views eligible for population by specifying INMEMORY on the CREATE MATERIALIZED VIEW or ALTER MATERIALIZED VIEW statements.

For a partitioned materialized view, you can populate all or a subset of the partitions in the IM column store.

See Also:

Oracle Database SQL Language Reference for ALTER MATERIALIZED VIEW syntax and semantics

4.1.3.1.4 In-Memory Tablespaces

You can make permanent tablespaces eligible for population by specifying INMEMORY on the CREATE TABLESPACE or ALTER TABLESPACE statements.

By default, all tables and materialized views in the tablespace are enabled for the IM column store. Individual tables and materialized views in the tablespace may have different INMEMORY attributes. The attributes for individual database objects override the attributes for the tablespace.

Note:

Temporary tablespaces are not eligible for In-Memory population.

See Also:

Oracle Database SQL Language Reference for ALTER TABLESPACE syntax and semantics

4.1.3.2 Priority Options for In-Memory Population

When you enable an object for the IM column store, you can either let Oracle Database control when the object is populated (default), or you can specify a level that determines the priority of the object in the population queue.

Oracle SQL includes an INMEMORY PRIORITY clause that provides more control over the queue for population. For example, it might be more important or less important to populate a database object's data before populating the data for other database objects.

Video:

The following table describes the supported priority levels.

Table 4-1 Priority Levels for Populating a Database Object in the IM Column Store

CREATE/ALTER Syntax	Description
`PRIORITY NONE`	The database populates the object on demand only. A full scan of the database object triggers the population of the object into the IM column store. This is the default level when `PRIORITY` is not included in the `INMEMORY` clause.
`PRIORITY LOW`	The database assigns the object a low priority and populates it after startup based on its position in the queue. Population does not depend on whether the object is accessed. The object is populated in the IM column store before database objects with the following priority level: `NONE`. The database object's data is populated in the IM column store after database objects with the following priority levels: `MEDIUM`, `HIGH`, or `CRITICAL`.
`PRIORITY MEDIUM`	The database assigns the object a medium priority and populates it after startup based on its position in the queue. Population does not depend on whether the object is accessed. The database object is populated in the IM column store before database objects with the following priority levels: `NONE` or `LOW`. The database object's data is populated in the IM column store after database objects with the following priority levels: `HIGH` or `CRITICAL`.
`PRIORITY HIGH`	The database assigns the object a high priority and populates it after startup based on its position in the queue. Population does not depend on whether the object is accessed. The database object's data is populated in the IM column store before database objects with the following priority levels: `NONE`, `LOW`, or `MEDIUM`. The database object's data is populated in the IM column store after database objects with the following priority level: `CRITICAL`.
`PRIORITY CRITICAL`	The database assigns the object a low priority and populates it after startup based on its position in the queue. Population does not depend on whether the object is accessed. The database object's data is populated in the IM column store before database objects with the following priority levels: `NONE`, `LOW`, `MEDIUM`, or `HIGH`.

When more than one database object has a priority level other than NONE, Oracle Database queues the data for objects to be populated based on priority level. Database objects with the CRITICAL priority level are populated first; database objects with the HIGH priority level are populated next, and so on. If no space remains in the IM column store, then no additional objects are populated in it until space becomes available.

Note:

If you specify all objects as CRITICAL, then the database does not consider any object as more critical than any other.

When a database is restarted, all of the data for database objects with a priority level other than NONE are populated in the IM column store during startup. For a database object with a priority level other than NONE, an ALTER TABLE or ALTER MATERIALIZED VIEW DDL statement involving the database object does not return until the DDL changes are recorded in the IM column store.

Note:

The priority level setting must apply to an entire table or to a table partition. Specifying different IM column store priority levels for different subsets of columns in a table is not permitted.
If a segment on disk is 64 KB or less, then it is not populated in the IM column store. Therefore, some small database objects that were enabled for the IM column store might not be populated.

See Also:

"Prioritization of In-Memory Population"
Oracle Database SQL Language Reference for CREATE TABLE ... INMEMORY PRIORITY syntax and semantics

4.1.3.3 IM Column Store Compression Methods

Depending on your requirement, you can compress In-Memory objects at different levels.

Typically, compression is a space-saving mechanism. However, the IM column store can compress data using a new set of algorithms that also improve query performance. If the columnar data is compressed using the FOR DML or FOR QUERY options, then SQL queries execute directly on the compressed data. Thus, scanning and filtering operations execute on a much smaller amount of data. The database only decompresses data when it is required for the result set.

Video:

The V$IM_SEGMENTS and V$IM_COLUMN_LEVEL views indicate the current compression level. You can change compression levels by using the appropriate ALTER command. If a table is currently populated in the IM column store, and if you change any INMEMORY attribute of the table other than PRIORITY, then the database evicts the table from the IM column store. The repopulation behavior depends on the PRIORITY setting.

The following table summarizes the data compression methods supported in the IM column store.

Table 4-2 IM Column Store Compression Methods

CREATE/ALTER Syntax	Description
`NO MEMCOMPRESS`	The data is not compressed.
`MEMCOMPRESS FOR DML`	This method results in the best DML performance. This method compresses IM column store data the least, with the exception of `NO MEMCOMPRESS`. Note: This compression method is not supported for `CELLMEMORY` storage on Exadata flash cache.
`MEMCOMPRESS FOR QUERY LOW`	This method results in the best query performance. This method compresses IM column store data more than `MEMCOMPRESS FOR DML` but less than `MEMCOMPRESS FOR QUERY HIGH`. This method is the default when the `INMEMORY` clause is specified without a compression method in a `CREATE` or `ALTER` SQL statement or when `MEMCOMPRESS FOR QUERY` is specified without including either `LOW` or `HIGH`.
`MEMCOMPRESS FOR QUERY HIGH`	This method results in good query performance, and saves space. This method compresses IM column store data more than `MEMCOMPRESS FOR QUERY LOW` but less than `MEMCOMPRESS FOR CAPACITY LOW`.
`MEMCOMPRESS FOR CAPACITY LOW`	This method balances space saving and query performance, with a bias toward space saving. This method compresses IM column store data more than `MEMCOMPRESS FOR QUERY HIGH` but less than `MEMCOMPRESS FOR CAPACITY HIGH`. This method applies a proprietary compression technique called Oracle Zip (OZIP) that offers extremely fast decompression that is tuned specifically for Oracle Database. That data must be decompressed before it can be scanned. This method is the default when `MEMCOMPRESS FOR CAPACITY` is specified without including either `LOW` or `HIGH`.
`MEMCOMPRESS FOR CAPACITY HIGH`	This method results in the best space saving. This method compresses IM column store data the most.

In a SQL statement, the MEMCOMPRESS keyword must be preceded by the INMEMORY keyword.

See Also:

"About Repopulation of the IM Column Store"
Oracle Exadata System Software User's Guide to learn more about ALTER TABLE ... CELLMEMORY
Oracle Database SQL Language Reference for CREATE TABLE ... INMEMORY PRIORITY syntax and semantics

4.1.3.4 Oracle Compression Advisor

Oracle Compression Advisor estimates the compression ratio that you can realize using the MEMCOMPRESS clause. The advisor uses the DBMS_COMPRESSION interface.

When you run DBMS_COMPRESSION.GET_COMPRESSION_RATIO for a table, Oracle Database analyzes a sample of the rows. For this reason, Oracle Compression Advisor provides a good estimate of the compression results that a table achieves after it is populated into the IM column store.

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn about DBMS_COMPRESSION.GET_COMPRESSION_RATIO

4.2 Enabling and Disabling Tables for the IM Column Store

Enable a table for the IM column store by including an INMEMORY clause in a CREATE TABLE or ALTER TABLE statement. Disable a table for the IM column store by including a NO INMEMORY clause in a CREATE TABLE or ALTER TABLE statement.

This section contains the following topics:

4.2.1 Enabling New Tables for the In-Memory Column Store

You enable a new table for the IM column store by including an INMEMORY clause in a CREATE TABLE statement.

You can enable either internal or external tables for the IM column store. Some INMEMORY subclauses, including the columns and priority subclauses, are not valid for external tables.

Prerequisites

Ensure that the IM column store is enabled for the database. See "Enabling the IM Column Store for a Database".

To enable a new table for the IM column store:

Log in to the database as a user with the necessary privileges to create the table.
Run a CREATE TABLE statement with an INMEMORY clause.

See Also:

"Enabling and Disabling Tables for the IM Column Store: Examples"
"Enabling a Subset of Columns for the IM Column Store: Example"
Oracle Database SQL Language Reference for information about INMEMORY clause of the CREATE TABLE statement

4.2.2 Enabling and Disabling Existing Tables for the IM Column Store

Enable or disable an existing table for the IM column store by including an INMEMORY or NO INMEMORY clause in an ALTER TABLE statement.

Prerequisites

Ensure that the IM column store is enabled for the database. See "Enabling the IM Column Store for a Database".

To enable or disable an existing table for the IM column store:

Log in to the database as a user with ALTER TABLE privileges.
Run an ALTER TABLE statement with an INMEMORY clause or a NO INMEMORY clause.
Optionally, to view metadata (size, priority, compression level) about the In-Memory segment, query V$IM_SEGMENTS.

See Also:

"Enabling and Disabling Tables for the IM Column Store: Examples"
"Enabling a Subset of Columns for the IM Column Store: Example"
Oracle Database SQL Language Reference for information about the ALTER TABLE statement
Oracle Database Reference for information about the V$IM_SEGMENTS view

4.2.3 Enabling and Disabling Tables for the IM Column Store: Examples

The following examples illustrate how to enable or disable tables for the IM column store.

Example 4-3 Creating a Table and Enabling It for the IM Column Store

The following example creates the test_inmem table and enables it for the IM column store:

CREATE TABLE test_inmem (
     id        NUMBER(5) PRIMARY KEY,
     test_col  VARCHAR2(15))
  INMEMORY;

This example uses the defaults for the INMEMORY clause: MEMCOMPRESS FOR QUERY and PRIORITY NONE.

Example 4-4 Enabling a Table for the IM Column Store

The following DDL statement enables the sh.sales table for the IM column store:

ALTER TABLE sh.sales INMEMORY;

The preceding statement uses the defaults for the INMEMORY clause: MEMCOMPRESS FOR QUERY and PRIORITY NONE.

The following query causes a full scan of sales, which populates the table into the IM column store:

SELECT /*+ FULL(sales) NO_PARALLEL(sales) */ COUNT(*) FROM sh.sales;

The following query shows the population status of sales (sample output included):

COL OWNER FORMAT a3
COL NAME FORMAT a10
COL STATUS FORMAT a20

SELECT OWNER, SEGMENT_NAME NAME, 
       POPULATE_STATUS STATUS
FROM   V$IM_SEGMENTS;

OWN NAME       STATUS
--- ---------- --------------------
SH  SALES      COMPLETED

The following query calculates the compression ratio. The query assumes that the tables are not further compressed on disk.

COL OWNER FORMAT a5
COL SEGMENT_NAME FORMAT a5
SET PAGESIZE 50000

SELECT v.OWNER, v.SEGMENT_NAME, v.BYTES ORIG_SIZE,
       v.INMEMORY_SIZE IN_MEM_SIZE,
       ROUND(v.BYTES / v.INMEMORY_SIZE, 2) COMP_RATIO
FROM   V$IM_SEGMENTS v
ORDER BY 4;

OWNER SEGME  ORIG_SIZE IN_MEM_SIZE COMP_RATIO
----- ----- ---------- ----------- ----------
SH    SALES     851968     1310720        .65
SH    SALES     835584     1310720        .64
SH    SALES     925696     1310720        .71
SH    SALES     958464     1310720        .73
SH    SALES     950272     1310720        .73
SH    SALES     786432     1310720         .6
SH    SALES     876544     1310720        .67
SH    SALES     753664     1310720        .58
SH    SALES    1081344     1310720        .83
SH    SALES     901120     1310720        .69
SH    SALES     925696     1310720        .71
SH    SALES     933888     1310720        .71
SH    SALES     843776     1310720        .64
SH    SALES     999424     1310720        .76
SH    SALES     581632     1507328        .39
SH    SALES     696320     1507328        .46

16 rows selected.

Example 4-5 Enabling a Table for the IM Column Store with FOR CAPACITY LOW Compression

The following DDL statement enables the oe.product_information table for the IM column store and specifies the compression method FOR CAPACITY LOW:

ALTER TABLE oe.product_information 
  INMEMORY 
  MEMCOMPRESS FOR CAPACITY LOW;

The preceding DDL statement uses the default for the PRIORITY clause, which is NONE. The following query scans the oe.product_information table to populate it (sample output included):

SELECT /*+ FULL(p) NO_PARALLEL(p) */ COUNT(*) 
FROM   oe.product_information p;

  COUNT(*)
----------
       288

The following query calculates the compression ratio (sample output included):

COL OWNER FORMAT a5
COL SEGMENT_NAME FORMAT a19
SET PAGESIZE 50000

SELECT v.OWNER, v.SEGMENT_NAME, v.BYTES ORIG_SIZE,
       v.INMEMORY_SIZE IN_MEM_SIZE,
       ROUND(v.BYTES / v.INMEMORY_SIZE, 2) COMP_RATIO
FROM   V$IM_SEGMENTS v
WHERE  SEGMENT_NAME LIKE 'P%'
ORDER BY 4;

OWNER SEGMENT_NAME         ORIG_SIZE IN_MEM_SIZE COMP_RATIO
----- ------------------- ---------- ----------- ----------
OE    PRODUCT_INFORMATION      98304     1310720        .08

Example 4-6 Enabling a Table for the IM Column Store with HIGH Data Population Priority

The following DDL statement enables the oe.product_information table for the IM column store and specifies PRIORITY HIGH for populating the table data in the IM column store:

ALTER TABLE 
  oe.product_information 
  INMEMORY 
  PRIORITY HIGH;

Example 4-7 Enabling a Table for the IM Column Store with FOR CAPACITY HIGH Compression and LOW Data Population Priority

The following query shows the priority and compression setting for the oe.product_information table:

COL OWNER FORMAT a5
COL SEGMENT_NAME FORMAT a19
SET PAGESIZE 50000

SELECT v.OWNER, v.SEGMENT_NAME, v.INMEMORY_PRIORITY,
       v.INMEMORY_COMPRESSION
FROM   V$IM_SEGMENTS v
WHERE  SEGMENT_NAME LIKE 'P%';

OWNER SEGMENT_NAME        INMEMORY INMEMORY_COMPRESS
----- ------------------- -------- -----------------
OE    PRODUCT_INFORMATION HIGH     FOR CAPACITY LOW

The following DDL statement alters oe.product_information to use FOR CAPACITY HIGH table compression and PRIORITY LOW:

ALTER TABLE oe.product_information 
  INMEMORY 
  MEMCOMPRESS FOR CAPACITY HIGH 
  PRIORITY LOW;

Example 4-8 Enabling a Partitioned Table for the IM Column Store

This following DDL statement creates a partitioned table named range_sales and specifies a different compression level for the first three partitions in the IM column store. The last two partitions are not populated in the IM column store.

CREATE TABLE range_sales
    ( prod_id        NUMBER(6)
    , cust_id        NUMBER
    , time_id        DATE
    , channel_id     CHAR(1)
    , promo_id       NUMBER(6)
    , quantity_sold  NUMBER(3)
    , amount_sold    NUMBER(10,2)
    ) 
PARTITION BY RANGE (time_id)
  (PARTITION SALES_Q4_1999 
      VALUES LESS THAN (TO_DATE('01-JAN-2015','DD-MON-YYYY'))
      INMEMORY MEMCOMPRESS FOR DML,
   PARTITION SALES_Q1_2000 
      VALUES LESS THAN (TO_DATE('01-APR-2015','DD-MON-YYYY'))
      INMEMORY MEMCOMPRESS FOR QUERY,
   PARTITION SALES_Q2_2000 
      VALUES LESS THAN (TO_DATE('01-JUL-2015','DD-MON-YYYY'))
      INMEMORY MEMCOMPRESS FOR CAPACITY,
   PARTITION SALES_Q3_2000 
      VALUES LESS THAN (TO_DATE('01-OCT-2015','DD-MON-YYYY'))
      NO INMEMORY,
   PARTITION SALES_Q4_2000 
      VALUES LESS THAN (MAXVALUE));

Example 4-9 Disabling a Table for the IM Column Store

To disable a table for the IM column store, specify the NO INMEMORY clause. The following example disables the oe.product_information table for the IM column store:

ALTER TABLE oe.product_information NO INMEMORY;

You can query the V$IM_SEGMENTS view to list the database objects that are populated in the IM column store.

Example 4-10 Disabling Columnar Format on Flash Cache

By default, Exadata Smart Flash Cache compresses data using the level MEMCOMPRESS FOR CAPACITY LOW. To change the compression level or disable the columnar format altogether, use the ALTER TABLE ... NO CELLMEMORY statement.

The following example disables the columnar format for oe.product_information on flash cache storage:

ALTER TABLE oe.product_information NO CELLMEMORY;

4.2.4 Creating and Populating External Tables: Examples

This example creates an external table with the INMEMORY option, populates it in the IM column store, and then refreshes it.

The following code examples assume that you have created the directories /tmp/data/, /tmp/log/, and /tmp/bad/ on a Linux or UNIX host.

Example 4-11 Creating an External Table with the INMEMORY Attribute

The following SQL script creates the comma-delimited flat file /tmp/data/sh_sales.csv from the sh.sales table. Execute the script as user sh.

SET HEAD OFF
SET PAGES 0
SET FEEDBACK OFF
SET TERMOUT OFF
SPOOL /tmp/data/sh_sales.csv
SELECT prod_id       || ',' || cust_id  || ',' || time_id || ',' || 
       channel_id    || ',' || promo_id || ',' || 
       quantity_sold || ',' || amount_sold 
FROM   sales;
SPOOL OFF

Using the sh_sales.csv file, the following SQL script creates the external table sh.admin_ext_sales with the INMEMORY option:

CONNECT  /  AS SYSDBA;
-- Set up directories and grant access to sh 
CREATE OR REPLACE DIRECTORY admin_dat_dir
    AS '/tmp/data'; 
CREATE OR REPLACE DIRECTORY admin_log_dir 
    AS '/tmp/log'; 
CREATE OR REPLACE DIRECTORY admin_bad_dir 
    AS '/tmp/bad'; 
GRANT READ ON DIRECTORY admin_dat_dir TO sh; 
GRANT WRITE ON DIRECTORY admin_log_dir TO sh; 
GRANT WRITE ON DIRECTORY admin_bad_dir TO sh;
-- sh connects. Provide the user password (sh) when prompted.
CONNECT sh
-- create the external table
DROP TABLE admin_ext_sales;
CREATE TABLE admin_ext_sales
                   (prod_id           NUMBER, 
                    cust_id           NUMBER,
                    time_id           DATE, 
                    channel_id        NUMBER,
                    promo_id          NUMBER,
                    quantity_sold     NUMBER(10,2),
                    amount_sold       NUMBER(10,2)
                   ) 
     ORGANIZATION EXTERNAL 
     ( 
       TYPE ORACLE_LOADER 
       DEFAULT DIRECTORY admin_dat_dir 
       ACCESS PARAMETERS 
       ( 
         records delimited by newline 
         badfile admin_bad_dir:'empxt%a_%p.bad' 
         logfile admin_log_dir:'empxt%a_%p.log' 
         fields terminated by ',' 
         missing field values are null 
         ( prod_id, cust_id,
           time_id char date_format date mask "dd-mon-yy", 
           channel_id, promo_id, quantity_sold, amount_sold 
         ) 
       ) 
       LOCATION ('sh_sales.csv') 
     ) 
     REJECT LIMIT UNLIMITED
     INMEMORY;

The following query of ALL_EXTERNAL_TABLES shows that admin_ext_sales is enabled for INMEMORY:

COL OWNER FORMAT A10
COL TABLE_NAME FORMAT A15

SELECT OWNER, TABLE_NAME, 
       INMEMORY, INMEMORY_COMPRESSION 
FROM   ALL_EXTERNAL_TABLES 
WHERE  TABLE_NAME = 'ADMIN_EXT_SALES';

OWNER      TABLE_NAME      INMEMORY INMEMORY_COMPRESS
---------- --------------- -------- -----------------
SH         ADMIN_EXT_SALES ENABLED  FOR QUERY LOW

Related views include ALL_XTERNAL_PART_TABLES, ALL_XTERNAL_TAB_PARTITIONS, and ALL_XTERNAL_TAB_SUBPARTITIONS.

Example 4-12 Populating an External Table into the IM Column Store

The admin_ext_sales table is enabled for the IM column store, but is not yet populated. The following query, executed as user sh, populates the table. Note that queries of In-Memory external tables must have the QUERY_REWRITE_INTEGRITY initialization parameter set to stale_tolerated.

ALTER SESSION SET QUERY_REWRITE_INTEGRITY=stale_tolerated;
EXEC DBMS_INMEMORY.POPULATE('SH', 'ADMIN_EXT_SALES');

The following query indicates that the admin_ext_sales table has been populated:

COL OWNER FORMAT a3
COL NAME FORMAT a15
COL STATUS FORMAT a9

SELECT OWNER, SEGMENT_NAME NAME, 
       POPULATE_STATUS STATUS
FROM   V$IM_SEGMENTS;

OWN NAME            STATUS
--- --------------- ---------
SH  ADMIN_EXT_SALES COMPLETED

Example 4-13 Refreshing an External Table in the IM Column Store

Assume that a record is added to /tmp/data/sh_sales.csv as follows:

echo "148,8787,23-NOV-01,2,999,1,23.43" >> /tmp/data/sh_sales.csv

Unlike internal tables, external tables do not use the automatic repopulation mechanism. To refresh external tables, you must use the DBMS_INMEMORY.REPOPULATE procedure. A full scan will not populate the table.

The following example forces the IM column store to populate admin_ext_sales:

EXEC DBMS_INMEMORY.REPOPULATE('SH', 'ADMIN_EXT_SALES');

No queries of admin_ext_sales will succeed until the table is fully populated.

See Also:

"Controls for Repopulation of the IM Column Store"
"In-Memory Views"
Oracle Database PL/SQL Packages and Types Reference to learn more about DBMS_INMEMORY.REPOPULATE
Oracle Database Reference to learn about QUERY_REWRITE_INTEGRITY, ALL_EXTERNAL_TABLES, and the other external table views

4.3 Enabling and Disabling Columns for In-Memory Tables

You can specify the INMEMORY clause for individual columns in an internal table. External tables do not support specifying INMEMORY at the column level.

For internal tables, both In-Memory virtual columns (IM virtual columns) and nonvirtual columns are eligible for IM population. For external tables, only nonvirtual columns can be populated.

This section contains the following topics:

4.3.1 About IM Virtual Columns

An IM virtual column is like any other column, except that its value is derived by evaluating an expression.

Storing the precalculated IM virtual column values in the IM column store can improve query performance. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions (DETERMINISTIC only). You cannot explicitly write to an IM virtual column.

Note:

A virtual column or IM expression counts toward the limit of 1000 columns per populated object.

To populate IM virtual columns in the IM column store, set the INMEMORY_VIRTUAL_COLUMNS initialization parameter to one of the following values:

MANUAL (default): If a table is enabled for the IM column store, then no IM virtual columns defined on this table are eligible for population, unless they are explicitly set as INMEMORY.
ENABLE: If a table is enabled for the IM column store, then all IM virtual columns defined on this table are eligible for population, unless they are explicitly set as NO INMEMORY.

By default, the compression level of the column in the IM column store is the same as the table or partition in which it is stored. However, when a different compression level is specified for the IM virtual column, it is populated at the specified compression level.

To specify that no IM virtual columns are populated in the IM column store, set this initialization parameter to DISABLE.

The underlying storage structures for IM virtual columns and IM expressions are the same. However, different mechanisms control IM expressions and IM virtual columns.

Note:

The IM column store only populates virtual columns for tables marked INMEMORY.
To populate IM virtual columns in the IM column store, the value for the initialization parameter COMPATIBLE must be set to 12.1.0 or higher.

See Also:

"Enabling and Disabling Tables for the IM Column Store"
"IM Expressions Infrastructure"
"In-Memory Initialization Parameters"
Oracle Database SQL Language Reference for the syntax and semantics of the INMEMORY clause

4.3.2 Enabling IM Virtual Columns

IM virtual columns improve query performance by avoiding repeated calculations. Also, the database can scan and filter IM virtual columns using techniques such as SIMD vector processing.

Prerequisites

To enable IM virtual columns, the following conditions must be true:

The IM column store is enabled for the database.

See "Enabling the IM Column Store for a Database".
The table that contains the virtual columns is enabled for the IM column store.

See "Enabling and Disabling Tables for the IM Column Store".
The INMEMORY_VIRTUAL_COLUMNS initialization parameter is not set to DISABLE.
The value for the initialization parameter COMPATIBLE is set to 12.1.0 or higher.

To enable IM virtual columns:

In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.
Either set the INMEMORY_VIRTUAL_COLUMNS initialization parameter to ENABLE, or enable specific virtual columns for the IM column store.

Example 4-14 Enabling Virtual Columns for the IM Column Store

In this example, you are logged in to the database as SYSTEM. The IM column store is enabled, but population of virtual columns is currently disabled:

SQL> SHOW PARAMETER INMEMORY_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
inmemory_size                        big integer 200M

SQL> SHOW PARAMETER INMEMORY_VIRTUAL_COLUMNS

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
inmemory_virtual_columns             string      DISABLE

You add a virtual column to the hr.employees table, and then specify that the table is INMEMORY:

SQL> ALTER TABLE hr.employees ADD (weekly_sal AS (ROUND(salary*12/52,2)));

Table altered.

SQL> ALTER TABLE hr.employees INMEMORY;

Table altered.

At this stage, weekly_sal is not eligible for population, although the non-virtual columns in hr.employees are eligible for population. The following statement enables weekly_sal, and any other virtual columns in hr.employees, to be populated:

SQL> ALTER SYSTEM SET INMEMORY_VIRTUAL_COLUMNS=ENABLE SCOPE=SPFILE;

System altered.

Example 4-15 Enabling a Specific IM Virtual Column for the IM Column Store

This example assumes that the INMEMORY_VIRTUAL_COLUMNS initialization parameter is set to MANUAL, which means that IM virtual columns must be added to the IM column store explicitly. This example first creates the hr.admin_emp table:

CREATE TABLE hr.admin_emp (
      empno      NUMBER(5) PRIMARY KEY,
      ename      VARCHAR2(15) NOT NULL,
      job        VARCHAR2(10),
      sal        NUMBER(7,2),
      hrly_rate  NUMBER(7,2) GENERATED ALWAYS AS (sal/2080),
      deptno     NUMBER(3) NOT NULL)
   INMEMORY;

At this stage, the hrly_rate virtual column is not eligible for population. The following statement explicitly specifies the virtual column as INMEMORY:

ALTER TABLE hr.admin_emp INMEMORY(hrly_rate);

4.3.3 Enabling a Subset of Columns for the IM Column Store: Example

This example enables all columns in the oe.product_information table for the IM column store except weight_class and catalog_url.

The following statement also specifies different IM column store compression methods for the columns enabled for the IM column store:

ALTER TABLE oe.product_information  
   INMEMORY MEMCOMPRESS FOR QUERY (
      product_id, product_name, category_id, supplier_id, min_price)
   INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (
      product_description, warranty_period, product_status, list_price)
   NO INMEMORY (
      weight_class, catalog_url);

Specifically, the preceding statement specifies the following:

The columns product_id, product_name, category_id, supplier_id, and min_price are enabled for the IM column store with the MEMCOMPRESS FOR QUERY compression method.
The columns product_description, warranty_period, product_status, and list_price are enabled for the IM column store with the MEMCOMPRESS FOR CAPACITY HIGH compression method.
The weight_class and catalog_url columns are not enabled for the IM column store.
The table uses the default for the PRIORITY clause, which is PRIORITY NONE.

Note:

The priority level setting must apply to an entire table or partition. Specifying different IM column store priority levels for different subsets of columns in a table is not allowed.

To determine the selective column compression levels defined for a database object, query the V$IM_COLUMN_LEVEL view, as shown in the following example:

COL TABLE_NAME FORMAT a20
COL COLUMN_NAME FORMAT a20

SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION 
FROM   V$IM_COLUMN_LEVEL 
WHERE  TABLE_NAME = 'PRODUCT_INFORMATION'
ORDER BY COLUMN_NAME;

TABLE_NAME           COLUMN_NAME          INMEMORY_COMPRESSION
-------------------- -------------------- --------------------------
PRODUCT_INFORMATION  CATALOG_URL          NO INMEMORY
PRODUCT_INFORMATION  CATEGORY_ID          FOR QUERY LOW
PRODUCT_INFORMATION  LIST_PRICE           FOR CAPACITY HIGH
PRODUCT_INFORMATION  MIN_PRICE            FOR QUERY LOW
PRODUCT_INFORMATION  PRODUCT_DESCRIPTION  FOR CAPACITY HIGH
PRODUCT_INFORMATION  PRODUCT_ID           FOR QUERY LOW
PRODUCT_INFORMATION  PRODUCT_NAME         FOR QUERY LOW
PRODUCT_INFORMATION  PRODUCT_STATUS       FOR CAPACITY HIGH
PRODUCT_INFORMATION  SUPPLIER_ID          FOR QUERY LOW
PRODUCT_INFORMATION  WARRANTY_PERIOD      FOR CAPACITY HIGH
PRODUCT_INFORMATION  WEIGHT_CLASS         NO INMEMORY

See Also:

"In-Memory Compression"
Oracle Database Reference for more information about the V$IM_COLUMN_LEVEL view

4.3.4 Specifying INMEMORY Column Attributes on a NO INMEMORY Table: Example

Starting in Oracle Database 12c Release 2 (12.2), you can specify the INMEMORY clause at the column level on an object that is not yet specified as INMEMORY.

In previous releases, the column-level INMEMORY clause was only valid when specified on an INMEMORY table or partition. This restriction meant that a column could not be associated with an INMEMORY clause before the table or partition was associated with an INMEMORY clause.

Starting in Oracle Database 12c Release 2 (12.2), if you specify the INMEMORY clause at the column level, then the database records the attributes of the specified column. If the table is NO INMEMORY (default), then the column-level attributes do not affect how the table is queried until the table or partition is specified as INMEMORY. If you mark the table itself as NO INMEMORY, then the database drops any existing column-level attributes.

In this example, your goal is to ensure that column c3 in a partitioned table is never populated in the IM column store. You perform the following steps:

Create a partitioned table t as follows:

CREATE TABLE t (c1 NUMBER, c2 NUMBER, c3 NUMBER) 
  NO INMEMORY -- this clause specifies the table itself as NO INMEMORY
  PARTITION BY LIST (c1) 
    ( PARTITION p1 VALUES (0), 
      PARTITION p2 VALUES (1), 
      PARTITION p3 VALUES (2) );

Table t is NO INMEMORY. The table is partitioned by list on column c1, and has three partitions: p1, p2, and p3.

Query the compression of the columns in the table (sample output included):

COL TABLE_NAME FORMAT a20
COL COLUMN_NAME FORMAT a20

SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION
FROM   V$IM_COLUMN_LEVEL
WHERE  TABLE_NAME = 'T'
ORDER BY COLUMN_NAME;

no rows selected

As shown by the output, no column-level INMEMORY attributes are set.

To ensure that column c3 is never populated, apply the NO INMEMORY attribute to column c3:
```
ALTER TABLE t NO INMEMORY (c3);
```

Query the compression of the columns in the table (sample output included):

SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION
FROM   V$IM_COLUMN_LEVEL
WHERE  TABLE_NAME = 'T'
ORDER BY COLUMN_NAME;

TABLE_NAME           COLUMN_NAME          INMEMORY_COMPRESSION
-------------------- -------------------- --------------------
T                    C1                   DEFAULT
T                    C2                   DEFAULT
T                    C3                   NO INMEMORY

The database has recorded the NO INMEMORY attribute for c3. The other columns use the default compression.

Specify partition p3 as INMEMORY:
```
ALTER TABLE t 
  MODIFY PARTITION p3 
    INMEMORY PRIORITY CRITICAL;
```
Because column c3 was previously specified as NO INMEMORY, initial population of partition p3 will not include column c3.
Specify the entire table as INMEMORY:
```
ALTER TABLE t INMEMORY;
```

Query the compression of the columns in the table (sample output included):

SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION
FROM   V$IM_COLUMN_LEVEL
WHERE  TABLE_NAME = 'T'
ORDER BY COLUMN_NAME;

TABLE_NAME           COLUMN_NAME          INMEMORY_COMPRESSION
-------------------- -------------------- --------------------------
T                    C1                   DEFAULT
T                    C2                   DEFAULT
T                    C3                   NO INMEMORY

The database has retained the NO INMEMORY setting for column c3. The other columns use the default compression.

Apply different compression levels to columns c1 and c2:

ALTER TABLE t 
  INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (c1) 
  INMEMORY MEMCOMPRESS FOR CAPACITY LOW (c2);

Query the compression of the columns in the table (sample output included):

SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION
FROM   V$IM_COLUMN_LEVEL
WHERE  TABLE_NAME = 'T'
ORDER BY COLUMN_NAME;

TABLE_NAME           COLUMN_NAME          INMEMORY_COMPRESSION
-------------------- -------------------- --------------------------
T                    C1                   FOR CAPACITY HIGH
T                    C2                   FOR CAPACITY LOW
T                    C3                   NO INMEMORY

Each column now has a different compression level.

Specify the entire table as NO INMEMORY:
```
ALTER TABLE t NO INMEMORY;
```
Query the compression of the columns in the table (sample output included):
```
SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION
FROM   V$IM_COLUMN_LEVEL
WHERE  TABLE_NAME = 'T'
ORDER BY COLUMN_NAME;

no rows selected
```
Because the entire table was specified as NO INMEMORY, the database dropped all column-level INMEMORY attributes.

See Also:

Oracle Database SQL Language Reference for ALTER TABLE syntax and semantics

4.4 Enabling and Disabling Tablespaces for the IM Column Store

You can enable or disable tablespaces for the IM column store.

Enable a tablespace for the IM column store during tablespace creation with a CREATE TABLESPACE statement that includes the INMEMORY clause. You can also alter a tablespace to enable it for the IM column store with an ALTER TABLESPACE statement that includes the INMEMORY clause.

Disable a tablespace for the IM column store by including a NO INMEMORY clause in a CREATE TABLESPACE or ALTER TABLESPACE statement.

When a tablespace is enabled for the IM column store, all tables and materialized views in the tablespace are enabled for the IM column store by default. The INMEMORY clause is the same for tables, materialized views, and tablespaces. The DEFAULT storage clause is required before the INMEMORY clause when enabling a tablespace for the IM column store and before the NO INMEMORY clause when disabling a tablespace for the IM column store.

When a tablespace is enabled for the IM column store, individual tables and materialized views in the tablespace can have different in-memory settings, and the settings for individual database objects override the settings for the tablespace. For example, if the tablespace is set to PRIORITY LOW for populating data in memory, and if a table in the tablespace is set to PRIORITY HIGH, then the table uses PRIORITY HIGH.

Prerequisites

Ensure that the IM column store is enabled for the database. See "Enabling the IM Column Store for a Database".

To enable or disable tablespaces for the IM column store:

In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.
Run a CREATE TABLESPACE or ALTER TABLESPACE statement with an INMEMORY clause or a NO INMEMORY clause.

Example 4-16 Creating a Tablespace and Enabling It for the IM Column Store

The following example creates the users01 tablespace and enables it for the IM column store:

CREATE TABLESPACE users01 
   DATAFILE 'users01.dbf' SIZE 40M 
   ONLINE
   DEFAULT INMEMORY;

This example uses the defaults for the INMEMORY clause. Therefore, MEMCOMPRESS FOR QUERY is used, and PRIORITY NONE is used.

Example 4-17 Altering a Tablespace to Enable It for the IM Column Store

The following example alters the users01 tablespace to enable it for the IM column store and specifies FOR CAPACITY HIGH compression for the database objects in the tablespace and PRIORITY LOW for populating data in memory:

ALTER TABLESPACE users01 DEFAULT INMEMORY 
   MEMCOMPRESS FOR CAPACITY HIGH 
   PRIORITY LOW;

4.5 Enabling and Disabling Materialized Views for the IM Column Store

You can enable and disable materialized views for the IM column store.

Enable a materialized view for the IM column store by including an INMEMORY clause in a CREATE MATERIALIZED VIEW or ALTER MATERIALIZED VIEW statement. Disable a materialized view for the IM column store by including a NO INMEMORY clause in a CREATE MATERIALIZED VIEW or ALTER MATERIALIZED VIEW statement.

Prerequisites

Ensure that the IM column store is enabled for the database. See "Enabling the IM Column Store for a Database".

To enable or disable a materialized view for the IM column store:

In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.
Run a CREATE MATERIALIZED VIEW or ALTER MATERIALIZED VIEW statement with either an INMEMORY clause or a NO INMEMORY clause.

Example 4-18 Creating a Materialized View and Enabling It for the IM Column Store

The following statement creates the oe.prod_info_mv materialized view and enables it for the IM column store:

CREATE MATERIALIZED VIEW oe.prod_info_mv INMEMORY 
  AS SELECT * FROM oe.product_information;

This example uses the defaults for the INMEMORY clause: MEMCOMPRESS FOR QUERY LOW and PRIORITY NONE.

Example 4-19 Enabling a Materialized View for the IM Column Store with HIGH Data Population Priority

The following statement enables the oe.prod_info_mv materialized view for the IM column store:

ALTER MATERIALIZED VIEW oe.prod_info_mv INMEMORY PRIORITY HIGH;

This example uses the default compression: MEMCOMPRESS FOR QUERY LOW.

See Also:

Oracle Database SQL Language Reference to learn more about the CREATE or ALTER MATERIALIZED VIEW statements

4.6 Forcing Initial Population of an In-Memory Object: Tutorial

Enabling an object for In-Memory population does not immediately populate the object.

If you enabled an object with PRIORITY set to NONE, and if you want to populate it immediately, then you have the following options:

Force a full table scan
Use the DBMS_INMEMORY.POPULATE procedure

Assumptions

This tutorial assumes the following:

The IM column store is enabled.
You want to enable the sh.customers table for In-Memory population, using the default PRIORITY of NONE.
You want to force the immediate population of sh.customers into the IM column store.

To force population of an INMEMORY table:

In SQL*Plus or SQL Developer, log in to the database as a user with administrative privileges.
Apply the INMEMORY attribute to the table.

For example, enable sh.customers for IM population as follows:
```
ALTER TABLE sh.customers INMEMORY;
```
Optionally, to check the population status, query V$IM_SEGMENTS.

For example, use the following statement (sample output included):
```
COL OWNER FORMAT a10;
COL NAME FORMAT a25;
COL STATUS FORMAT a10;

SELECT OWNER, SEGMENT_NAME NAME,
       POPULATE_STATUS STATUS
FROM   V$IM_SEGMENTS
WHERE  SEGMENT_NAME = 'CUSTOMERS';

no rows selected
```
The preceding output shows that the object is not yet populated in the IM column store.
Execute the DBMS_INMEMORY.POPULATE procedure on the table.

For example, enable this procedure against sh.customers as follows:
```
EXEC DBMS_INMEMORY.POPULATE('SH', 'CUSTOMERS');
```

Optionally, to check the population status, query V$IM_SEGMENTS.

For example, use the following statement (sample output included):

SELECT OWNER, SEGMENT_NAME NAME,
       POPULATE_STATUS STATUS
FROM   V$IM_SEGMENTS
WHERE  SEGMENT_NAME = 'CUSTOMERS';

OWN NAME       STATUS
--- ---------- --------------------
SH  CUSTOMERS  COMPLETED

The object is now populated in the IM column store.

See Also:

Oracle Database Reference to learn about V$IM_SEGMENTS
Oracle Database PL/SQL Packages and Types Reference to learn about DBMS_INMEMORY.POPULATE