1. Database In-Memory Guide
  2. Configuring and Populating the IM Column Store
  3. Enabling Objects for In-Memory Population Manually

5 Enabling Objects for In-Memory Population Manually

If INMEMORY_AUTOMATIC_LEVEL is not set to HIGH, then you must manually enable and disable objects for population and set compression and priority options.

Note:

If INMEMORY_AUTOMATIC_LEVEL is set to HIGH, and if INMEMORY_FORCE is not set to BASE_LEVEL, then the database automatically enables all objects for In-Memory population, and also populates and evicts them as needed. Manually specifying the INMEMORY clause in DDL statements is not necessary.

5.1 About Manually Enabling Objects for In-Memory Population

Only objects with the INMEMORY clause are eligible for population into the IM column store. To apply this clause manually, you must use DDL statements such as CREATE TABLE or ALTER TABLE.

5.1.1 Purpose of Enabling Objects for In-Memory Population

Unless objects have the INMEMORY attribute, they are not eligible for population.

When an object has the INMEMORY attribute, it can potentially reside in the IM column store. In-Memory population is a separate step that 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.

Note:

Population, which transforms existing data on disk into columnar format, is different from repopulation, which transforms new data into columnar format. 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.

Before Oracle Database 21c, all objects were implicitly NO INMEMORY by default. Starting in Oracle Database 21c, when the INMEMORY_AUTOMATIC_LEVEL initialization parameter is set to HIGH, all objects are INMEMORY by default, and therefore automatically eligible for population. No manual DDL statements to specify individual objects as INMEMORY are necessary. When INMEMORY_AUTOMATIC_LEVEL is not HIGH, then you must specify the INMEMORY clause manually.

Related Topics

5.1.2 Controls for In-Memory Objects

You can enable tablespaces, tables (internal and external), partitions, and materialized views for In-Memory access. You can also specify options such as compression and population priority.

5.1.2.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

5.1.2.1.1 In-Memory Tables

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

Columns Eligible for Population

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.

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

Starting in Oracle Database 21c, the INMEMORY TEXT clause enables you specify In-Memory full text columns. These are CHAR, VARCHAR2, CLOB, BLOB, or JSON columns that support fast In-Memory queries using the CONTAINS() or JSON_TEXTCONTAINS() operators. The IM column store stores the column data, such as a text, JSON, or XML document, in its domain-specific IM format.

In-Memory Partitioned Tables

For a partitioned table, you can specify the INMEMORY clause at the table level. Partitioned tables can have only external partitions, only internal partitions, or a hybrid mixture of both internal and external partitions. By default, all partitions in a partitioned table inherit the table-level INMEMORY clause. You can also specify this clause on individual partitions.

Column Storage in the Flash Cache on Oracle Exadata Storage Server

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.

Example 5-1 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:

5.1.2.1.2 In-Memory External Tables

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

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

How In-Memory External Tables Work

The IM column store manages the data for external tables in the same way as for heap-organized tables. For example, a full table scan populates both internal tables and external tables into the IM column store. The same drivers supported for external tables are supported for In-Memory external tables.

User Interface for In-Memory External Tables

You can specify the INMEMORY clause at the top-level of a partitioned external or hybrid partitioned table. This clause is inherited by every partition. You can also specify INMEMORY for an individual partition, which enables different partitions within an external table to have different In-Memory specifications.

Note:

If you specify INMEMORY clauses on a hybrid table that are not supported by IM external tables, then those attributes are only be inherited by the internal partitions.

Note the following restrictions for In-Memory external tables:

  • Subpartitions are not supported for In-Memory external tables.

  • Some INMEMORY subclauses for external tables are not valid, including the column clause, distribute clause, and priority clause.

  • Join groups are not supported for In-Memory external tables.

  • In-Memory Optimized Arithmetic does not support external tables.

  • IM expressions are not supported for In-Memory external tables.

  • In-Memory external tables do not support the DISTRIBUTE ... FOR SERVICE clause for Oracle Active Data Guard instances.

The following dictionary views for external tables have an INMEMORY and INMEMORY_COMPRESSION column:

  • DBA_XTERNAL_PART_TABLES

  • DBA_XTERNAL_TAB_PARTITIONS

  • DBA_XTERNAL_TAB_SUBPARTITIONS

Note:

Sessions that query In-Memory external tables must have the initialization parameter QUERY_REWRITE_INTEGRITY set to stale_tolerated.

It is important to keep in mind that if an external table is modified, then the results from the IM column store are undefined. Results are also undefined if a partition is altered (by dropping or adding values). This may lead to differences in results between IM and non-IM based scans. You can run DBMS_INMEMORY.REPOPULATE to refresh the IM store so that it is resynchronized with the table data.

See Also:

5.1.2.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

5.1.2.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

5.1.2.2 Priority Options for the Population of In-Memory Objects

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 5-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:

5.1.2.3 Compression Levels for In-Memory Objects

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 algorithms that also improve query performance. If the columnar data uses the MEMCOMPRESS FOR DML or MEMCOMPRESS FOR QUERY options, then SQL queries execute directly on the compressed data. Thus, scanning and filtering operations execute on a smaller amount of data. The database only decompresses data when 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.

If the INMEMORY_FORCE initialization parameter is set to BASE_LEVEL, then INMEMORY objects and columns automatically use QUERY LOW compression. The data dictionary views may continue to show pre-existing compression settings, but the Base Level always transparently compresses objects and columns at the QUERY LOW level.

The following table summarizes the valid INMEMORY MEMCOMPRESS clauses.

Table 5-2 IM Column Store Compression Levels

CREATE/ALTER … INMEMORY Syntax Description

NO MEMCOMPRESS

The data is not compressed.

MEMCOMPRESS FOR DML

This level results in the best DML performance.

This level compresses IM column store data the least, with the exception of NO MEMCOMPRESS.

Note: This compression level is not supported for CELLMEMORY storage on Exadata flash cache.

MEMCOMPRESS FOR QUERY LOW

This level results in the best query performance.

This level compresses IM column store data more than MEMCOMPRESS FOR DML but less than MEMCOMPRESS FOR QUERY HIGH.

This level is the default in the following scenarios:

  • The INMEMORY clause is specified without a compression level in a CREATE or ALTER SQL statement.

  • MEMCOMPRESS FOR QUERY is specified without including either LOW or HIGH.

  • The INMEMORY_FORCE initialization parameter is set to BASE_LEVEL. In this case, the IM column store transparently applies the QUERY LOW level regardless of any manual compression settings.

MEMCOMPRESS FOR QUERY HIGH

This level results in good query performance, and saves space.

This level compresses IM column store data more than MEMCOMPRESS FOR QUERY LOW but less than MEMCOMPRESS FOR CAPACITY LOW.

MEMCOMPRESS FOR CAPACITY LOW

This level balances space saving and query performance, with a bias toward space saving.

This level compresses IM column store data more than MEMCOMPRESS FOR QUERY HIGH but less than MEMCOMPRESS FOR CAPACITY HIGH. This level 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 level is the default when MEMCOMPRESS FOR CAPACITY is specified without including either LOW or HIGH.

MEMCOMPRESS FOR CAPACITY HIGH

This level results in the best space saving.

This level compresses IM column store data the most.

MEMCOMPRESS AUTO

This level only applies when INMEMORY_AUTOMATIC_LEVEL is set to HIGH. In this case, the database automatically enables all segments with MEMCOMPRESS AUTO. However, you may decide to manually set specific segments to NO INMEMORY. If you later want to reapply the INMEMORY attribute to these NO INMEMORY segments, then specify the INMEMORY MEMCOMPRESS AUTO clause.

See Also:

5.1.2.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

5.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.

5.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 CDB or PDB".

To enable a new table for the IM column store:

  1. Log in to the database as a user with the necessary privileges to create the table.

  2. Run a CREATE TABLE statement with an INMEMORY clause.

See Also:

5.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 CDB or PDB".

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

  1. Log in to the database as a user with ALTER TABLE privileges.

  2. Run an ALTER TABLE statement with an INMEMORY clause or a NO INMEMORY clause.

  3. Optionally, to view metadata (size, priority, compression level) about the In-Memory segment, query V$IM_SEGMENTS.

See Also:

5.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.

See Also:

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

5.2.3.1 Creating an In-Memory Table: Example

This example creates the test_inmem table and enables it for the IM column store.

In SQL*Plus, log in to the database as the user who will own the table, and then execute the following SQL statement:

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

The preceding statement uses the defaults for the INMEMORY clause: MEMCOMPRESS FOR QUERY and PRIORITY NONE. Because PRIORITY is NONE, the database will not automatically populate the table.

5.2.3.2 Creating a Table with In-Memory Partitions: Example

This example creates a partitioned table named range_sales, specifying a subset of the partitions as INMEMORY.

Log in to SQL*Plus as the user that will own the new table, and then execute the following DDL statement:

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));

The preceding SQL specifies a different compression level for the first three partitions in the IM column store. The last two partitions are not eligible for population in the IM column store.

5.2.3.3 Creating an In-Memory External Table: Example

This example creates an external table with the INMEMORY option.

This example assumes that the host has the directories /tmp/data/, /tmp/log/, and /tmp/bad/.

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 TRIMSPOOL ON
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 the admin_ext_sales table 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.

See Also:

5.2.3.4 Creating an In-Memory Partitioned External Table: Example

This example creates an external partitioned table with the INMEMORY option.

This example assumes that the host has the directories /tmp/data/, /tmp/log/, and /tmp/bad/.

  1. Log in to the database as user sh.

  2. Run following SQL script creates the comma-delimited flat files /tmp/data/sh_sales_98.csv and /tmp/data/sh_sales_99.csv from the sh.sales table. 

    SET ECHO OFF
SET HEAD OFF
SET TAB OFF
SET PAGES 0
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET TERMOUT OFF
SPOOL /tmp/data/sh_sales_98.csv
SELECT prod_id       || ',' || cust_id  || ',' || time_id || ',' || 
       channel_id    || ',' || promo_id || ',' || 
       quantity_sold || ',' || amount_sold 
FROM   sales
WHERE  time_id < TO_DATE('1999-01-01','SYYYY-MM-DD','NLS_CALENDAR=GREGORIAN');
SPOOL OFF
SPOOL /tmp/data/sh_sales_99.csv
SELECT prod_id       || ',' || cust_id  || ',' || time_id || ',' || 
       channel_id    || ',' || promo_id || ',' || 
       quantity_sold || ',' || amount_sold 
FROM   sales
WHERE  time_id > TO_DATE('1998-12-31','SYYYY-MM-DD','NLS_CALENDAR=GREGORIAN')
AND    time_id < TO_DATE('2000-01-01','SYYYY-MM-DD','NLS_CALENDAR=GREGORIAN');
SPOOL OFF

  3. Run the following SQL script to create the external table sh.admin_ext_pt_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 partitioned table
DROP TABLE admin_ext_pt sales;
CREATE TABLE admin_ext_pt_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 
         ) 
       )  
     )
     REJECT LIMIT UNLIMITED INMEMORY
     PARTITION BY RANGE (time_id)
     ( PARTITION sales_1998 VALUES LESS THAN
        (TO_DATE('1996-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
           LOCATION('sh_sales_98.csv'),
       PARTITION sales_1999 VALUES LESS THAN
        (TO_DATE('1997-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
           LOCATION('sh_sales_99.csv')
     );

    Note:

    To apply the INMEMORY attribute to an individual partition rather than at the table level, place it directly after the PARTITION ... LOCATION('part_name') clause.

  4. Query ALL_EXTERNAL_TABLES to confirm that the admin_ext_pt_sales table is enabled for INMEMORY: 

    COL OWNER FORMAT A10
COL TABLE_NAME FORMAT A18

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

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

    Related views include ALL_XTERNAL_PART_TABLES, ALL_XTERNAL_TAB_PARTITIONS, and ALL_XTERNAL_TAB_SUBPARTITIONS.

  5. Populate admin_ext_pt_sales into the IM column store: 

    EXEC DBMS_INMEMORY.POPULATE('SH', 'ADMIN_EXT_PT_SALES');

  6. Query the population status of the admin_ext_pt_sales partitions: 

    COL OWNER FORMAT a3
COL NAME FORMAT a18
COL PARTITION FORMAT a13
COL STATUS FORMAT a9
COL BNP FORMAT 99999

SELECT OWNER, SEGMENT_NAME NAME, PARTITION_NAME PARTITION,
       POPULATE_STATUS STATUS, BYTES_NOT_POPULATED AS "BNP"
FROM   V$IM_SEGMENTS;

OWN NAME               PARTITION     STATUS       BNP
--- ------------------ ------------- --------- ------
SH  ADMIN_EXT_PT_SALES SALES_1998    COMPLETED      0
SH  ADMIN_EXT_PT_SALES SALES_1999    COMPLETED      0

    The query shows that only the two external partitions were populated.

See Also:

5.2.3.5 Creating and Populating a Hybrid External Table: Example

This example creates a hybrid external table with the INMEMORY option, and then populates both the internal and external partitions.

This example assumes the existence of the sh.sales table. The goal is to create a hybrid partitioned table sales_hpt with two internal partitions, one of which uses the data from sh.sales, and then add one external partition. When you apply the INMEMORY attribute to sales_hpt, this attribute applies to all partitions.

  1. In Linux, create a temporary directory, and then create a text file with one row of sales data.

    rm -rf /tmp/sales_data
mkdir /tmp/sales_data
echo "1002,110,19-MAR-2016,12,18,150,4800" > /tmp/sales_data/sales2016_data.txt

  2. In SQL*Plus, log is with administrator privileges, and then create a directory object for the sales data:

    CONNECT / AS SYSDBA
CREATE DIRECTORY sales_data AS '/tmp/sales_data';
GRANT READ,WRITE ON DIRECTORY sales_data TO sh;

  3. Log in as user sh, and then create the sales_hpt table: 

    CONNECT sh

DROP TABLE sales_hpt;
CREATE TABLE sales_hpt
  ( prod_id       NUMBER        NOT NULL,
    cust_id       NUMBER        NOT NULL,
    time_id       DATE          NOT NULL,
    channel_id    NUMBER        NOT NULL,
    promo_id      NUMBER        NOT NULL,
    quantity_sold NUMBER(10,2)  NOT NULL,
    amount_sold   NUMBER(10,2)  NOT NULL
  )
    EXTERNAL PARTITION ATTRIBUTES (
      TYPE ORACLE_LOADER 
      DEFAULT DIRECTORY sales_data
       ACCESS PARAMETERS(
         FIELDS TERMINATED BY ','
         (prod_id,cust_id,time_id DATE 'dd-mm-yyyy',channel_id,promo_id,quantity_sold,amount_sold)
       ) 
      REJECT LIMIT UNLIMITED
     ) 
    PARTITION BY RANGE (time_id)
    (
     PARTITION sales_2014 VALUES LESS THAN (TO_DATE('01-01-2015','dd-mm-yyyy')),
     PARTITION sales_2015 VALUES LESS THAN (TO_DATE('01-01-2016','dd-mm-yyyy')),
     PARTITION sales_2016 VALUES LESS THAN (TO_DATE('01-01-2017','dd-mm-yyyy')) 
       EXTERNAL LOCATION ('sales2016_data.txt')
);

    The preceding statement shows that the table has three partitions: sales_2014, sales_2015, and sales_2016. Only sales_2016 is designated as external.

  4. Query the data dictionary to confirm that the table is a hybrid (sample output included):

    COL TABLE_NAME FORMAT a25
SELECT TABLE_NAME, HYBRID FROM USER_TABLES WHERE HYBRID = 'YES';

TABLE_NAME                HYB
------------------------- ---
SALES_HPT                 YES

  5. Insert rows into the internal partitions sales_2014 and sales_2015: 

    INSERT INTO sh.sales_hpt (SELECT * FROM sales);
INSERT INTO sh.sales_hpt 
  VALUES (30, 21086, TO_DATE('2015-12-30','SYYYY-MM-DD'), 2, 999, 1, 10.19);
COMMIT;

    The first of the preceding statements inserts all rows from the sales table. All dates in sales are before 2002, so all rows from sales are inserted into the sales_2014 partition. The second statement inserts a single row into the sales_2015 partition.

  6. Query the partitions to confirm that the correct data exists:

    SQL> SELECT COUNT(*) FROM sales_hpt PARTITION(sales_2014);

  COUNT(*)
----------
    918843

SQL> SELECT COUNT(*) FROM sales_hpt PARTITION(sales_2015);

  COUNT(*)
----------
         1

SQL> SELECT COUNT(*) FROM sales_hpt PARTITION(sales_2016);

  COUNT(*)
----------
         1

  7. Apply the INMEMORY attribute at the table level, and then force the database to populate the table into the IM column store: 

    ALTER TABLE sales_hpt INMEMORY;
EXEC DBMS_INMEMORY.POPULATE('SH', 'SALES_HPT');

  8. Query the population status of the sales_hpt partitions: 

    COL OWNER FORMAT a3
COL SEGMENT FORMAT a18
COL PARTITION FORMAT a13
COL STATUS FORMAT a9
COL BNP FORMAT 99999

SELECT OWNER, SEGMENT_NAME SEGMENT, PARTITION_NAME PARTITION,
       IS_EXTERNAL AS EXT, POPULATE_STATUS STATUS, 
       BYTES_NOT_POPULATED AS "BNP"
FROM   V$IM_SEGMENTS
WHERE  SEGMENT_NAME = 'SALES_HPT'
ORDER BY PARTITION;

OWN SEGMENT            PARTITION     EXT   STATUS       BNP
--- ------------------ ------------- ----- --------- ------
SH  SALES_HPT          SALES_2014    FALSE COMPLETED      0
SH  SALES_HPT          SALES_2015    FALSE COMPLETED      0
SH  SALES_HPT          SALES_2016    TRUE  COMPLETED      0

    The query shows that all partitions, both internal and external, were populated.

5.2.3.6 Enabling an Existing Table for the IM Column Store: Example

This example enables the existing sh.sales table for the IM column store.

In SQL*Plus, log in to the database as the sh user, and then execute the following DDL statement: 

ALTER TABLE sales INMEMORY;

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

5.2.3.7 Setting In-Memory Compression to FOR CAPACITY LOW: Example

This example enables the existing oe.product_information table for the IM column store and specifies the compression method FOR CAPACITY LOW.

In SQL*Plus, log in to the database as the oe user, and then execute the following DDL statement: 

ALTER TABLE product_information 
  INMEMORY 
  MEMCOMPRESS FOR CAPACITY LOW;

The preceding statement uses the default for the PRIORITY clause of NONE. Populate the table by forcing a full table scan as follows (sample output included): 

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

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

In a separate session, log in as a user with administrative privileges, and then calculate the compression ratio by executing the following query (sample output included):

COL OWNER FORMAT a5
COL SEGMENT_NAME FORMAT a19
SET PAGESIZE 50000

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

OWNER SEGMENT_NAME         ORIG_SIZE IN_MEM_SIZE COMP_RATIO
----- ------------------- ---------- ----------- ----------
OE    PRODUCT_INFORMATION      98304     1310720        .08
5.2.3.8 Setting In-Memory Priority to HIGH: Example

This example 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.

In SQL*Plus, log in to the database as the oe user, and then execute the following DDL statement: 

ALTER TABLE 
  product_information 
  INMEMORY 
  PRIORITY HIGH;
5.2.3.9 Changing the Compression and Priority Settings for an In-Memory Table: Example

This example alters the oe.product_information table to use FOR CAPACITY HIGH table compression and a LOW priority setting.

In SQL*Plus, log in to the database as an administrative user, and then execute the following query to show the current 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;
5.2.3.10 Disabling a Table for the IM Column Store: Example

To disable a table for the IM column store, specify the NO INMEMORY clause.

Log in to the database as the user oe, and then execute the following statement to disable the product_information table for the IM column store: 

ALTER TABLE oe.product_information NO INMEMORY;

The V$IM_SEGMENTS view lists the database objects that are populated in the IM column store.

5.2.3.11 Disabling Columnar Format on Exadata Smart Flash Cache: Example

This example disables the columnar format for oe.product_information on Exadata Smart Flash Cache storage.

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.

Log in to the database as user oe, and execute the following DDL statement: 

ALTER TABLE product_information NO CELLMEMORY;

5.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.

5.3.1 About In-Memory Columns

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 are eligible.

5.3.1.1 Selective Columns

By default, all columns in an INMEMORY table are enabled for the IM column store and therefore eligible for population. To save memory, you may decide to make a subset of columns ineligible for the IM column store.

Note:

If the INMEMORY_FORCE initialization parameter is set to BASE_LEVEL, then INMEMORY objects and columns automatically use QUERY LOW compression. The data dictionary views may continue to show pre-existing compression settings, but the Base Level always transparently compresses objects and columns at the QUERY LOW level.

5.3.1.1.1 The NO INMEMORY Attribute

If some columns in an INMEMORY table are specified NO INMEMORY, then only the INMEMORY columns are eligible for population.

To apply the NO INMEMORY attribute to a subset of columns, specify ALTER TABLE table_name INMEMORY ... NO INMEMORY excluded_columns, where excluded_columns lists the NO INMEMORY columns. Only the columns that do not have the NO INMEMORY attribute, that is, columns that are not in the excluded columns list, inherit the segment-level INMEMORY attribute.

The following DDL statement enables all columns in employees for In-Memory, except for the salary column: 

ALTER TABLE hr.employees INMEMORY NO INMEMORY (salary);

The following query of V$IM_COLUMN_LEVEL shows that salary is NO INMEMORY: 

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 = 'EMPLOYEES'
ORDER BY COLUMN_NAME;

TABLE_NAME           COLUMN_NAME          INMEMORY_COMPRESSION
-------------------- -------------------- --------------------------
EMPLOYEES            COMMISSION_PCT       DEFAULT
EMPLOYEES            DEPARTMENT_ID        DEFAULT
EMPLOYEES            EMAIL                DEFAULT
EMPLOYEES            EMPLOYEE_ID          DEFAULT
EMPLOYEES            FIRST_NAME           DEFAULT
EMPLOYEES            HIRE_DATE            DEFAULT
EMPLOYEES            JOB_ID               DEFAULT
EMPLOYEES            LAST_NAME            DEFAULT
EMPLOYEES            MANAGER_ID           DEFAULT
EMPLOYEES            PHONE_NUMBER         DEFAULT
EMPLOYEES            SALARY               NO INMEMORY

See Also:

5.3.1.1.2 In-Memory Hybrid Scans

An In-Memory hybrid scan accesses a table in the IM column store when not all columns are populated.

Before Oracle Database 21c, if a query referenced any column with the NO INMEMORY setting, then the query accessed all data from the row store. Therefore, the table scan could not take advantage of columnar formats, predicate pushdown, and other In-Memory features. Starting in Oracle Database 21c, queries that reference both INMEMORY and NO INMEMORY columns can access columnar data.

In some cases, an In-Memory hybrid scan can improve performance by orders of magnitude. The greatest performance benefits occur when a query has selective filters. In this case, the IM column store can quickly filter out most rows so that the row store projects only a small number of rows.

To achieve optimal performance, the optimizer compares different access methods. If the optimizer chooses a table scan, then the storage engine automatically determines whether an In-Memory hybrid scan performs better than a regular row store scan. The optimizer considers hybrid scans when the following conditions are met:

  • The predicate contains only INMEMORY columns.

  • The SELECT list contains an arbitrary combination of INMEMORY and NO INMEMORY columns.

For example, assume that the salary and commission_pct columns in the employees table are specified as INMEMORY. The first_name column is NO INMEMORY because it is rarely referenced. The following queries are eligible for hybrid IM scans because they reference both NO INMEMORY and INMEMORY columns: 

SELECT first_name FROM employees WHERE salary=6000 ORDER BY first_name;

SELECT first_name, salary AS base_sal, ((salary*commission_pct)+salary) AS total_sal 
FROM   employees 
WHERE  commission_pct=.1
ORDER BY total_sal DESC;

An In-Memory hybrid scan logically divides the work into two: one part processes the query on the IM column store, and the other part processes the query on the row store. In the execution plan, the operation named TABLE ACCESS INMEMORY FULL (HYBRID) indicates a hybrid scan. Note that if runtime statistics indicate that performance will be faster by accessing the row store only, then the database can disable the In-Memory hybrid scan at runtime.

Example 5-2 In-Memory Hybrid Scan

In this example, you enable all sales columns for In-Memory access except for amount_sold: 

SH@21c:21c> ALTER TABLE sales INMEMORY NO INMEMORY (amount_sold);

Table altered.

You populate the table into the IM column store:

SH@21c:21c> SELECT /*+ FULL(sales) NO_PARALLEL(sales) */ COUNT(*) FROM sales;

  COUNT(*)
----------
    918843

You apply the SUM function to amount_sold, which is a NO INMEMORY column, and then reference only INMEMORY columns in the predicate: 

SELECT SUM(amount_sold) AS revenue 
FROM   sales 
WHERE  time_id >= TO_DATE('1994-01-01',  'YYYY-MM-DD') 
AND    prod_id BETWEEN 30 and 40
AND    quantity_sold < 2;

   REVENUE
----------
7695555.89

Step 3 of the following execution plan shows that the optimizer chose an In-Memory hybrid scan: 

SH@21c> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
SQL_ID  6nz5k0y07akx8, child number 0
-------------------------------------
SELECT SUM(amount_sold) as revenue FROM   sales WHERE  time_id >=
TO_DATE('1994-01-01',  'YYYY-MM-DD') AND    prod_id BETWEEN 30 and 40
AND    quantity_sold < 2

Plan hash value: 3519235612
-------------------------------------------------------------------------------------------
|Id | Operation                             |Name|Rows|Bytes|Cost (%CPU)|Time|Pstart|Pstop|
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                      |      |    |     |463 (100)|        |   |    |
| 1 |  SORT AGGREGATE                       |      |  1 |  20 |         |        |   |    |
| 2 |   PARTITION RANGE ALL                 |      |196K|3834K|463   (2)|00:00:01| 1 | 28 |
|*3 |    TABLE ACCESS INMEMORY FULL (HYBRID)| SALES|196K|3834K|463   (2)|00:00:01| 1 | 28 |
-------------------------------------------------------------------------------------------

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

   3 - filter(("PROD_ID"<=40 AND "PROD_ID">=30 AND 
       "TIME_ID">=TO_DATE(' 1994-01-01 00:00:00','syyyy-mm-dd hh24:mi:ss') 
       AND "QUANTITY_SOLD"<2))
5.3.1.2 IM Virtual Columns

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

Purpose of IM Virtual Columns

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.

Virtual columns have uses in a variety of contexts. For example, you can add virtual columns to an INMEMORY spatial table, and then use operators such as SDO_FILTER to query that table without using a spatial index.

User Interface for IM Virtual Columns

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.

Note:

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

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:

5.3.1.3 IM Full Text Columns

You specify an In-Memory full text column with the INMEMORY TEXT clause.

Purpose of IM Full Text Columns

In previous releases, the IM column store did not support predicates for non-scalar document objects such as text, XML, and JSON. These types have their own domain-specific predicate and projection query constructs, such as CONTAINS() for CLOB columns. Fast predicate evaluations required domain indexes such as Oracle full text index, XML Search Index, or JSON Search Index. Starting in Oracle Database 21c, optimized In-Memory searching occurs when a CONTAINS() or JSON_TEXTCONTAINS() operator appears in a predicate.

An IM table scan can evaluate both scalar and non-scalar data. When the IM column store contains both scalar and non-scalar columns, OLAP applications that access both types of data can avoid accessing the row store, thereby improving performance.

How Full Text Columns Work

Every domain-specific data object is stored in the IM column store in its domain-specific format. The IM full text feature supports the following data types:

  • CHAR

  • VARCHAR2

  • CLOB

  • BLOB

  • JSON

In previous releases, queries using CONTAINS() and JSON_TEXTCONTAINS() were only evaluated with a text index and JSON search index. Starting in Oracle Database 20, when the underlying columns that store the documents are specified as INMEMORY TEXT, queries evaluate these operators in SQL predicates. Domain-specific indexes are optional.

Both JSON and non-JSON columns support a custom indexing policy created with the CTX_DDL.CREATE_POLICY procedure, which requires the CTXAPP role or execute privileges on the CTXSYS.CTX_DDL package. If the column data type is JSON, then the IM full text version of this column enables path-aware search using JSON_TEXTCONTAINS() when the column uses either of the following:

  • A default policy

  • A custom policy with a PATH_SECTION_GROUP having JSON_ENABLED attribute set to TRUE

User Interface for Full Text Columns

Both CREATE TABLE and ALTER TABLE support the INMEMORY TEXT clause. The PRIORITY clause has the same effect on population of IM full text columns as standard In-Memory columns. The default priority is NONE. The MEMCOMPRESS clause is not valid with INMEMORY TEXT.

Table 5-3 INMEMORY TEXT Clause

Syntax Description

INMEMORY TEXT (col1, col2, …)

Specifies the list of columns to be enabled as IM full text. The columns must be of type CHAR, VARCHAR2, CLOB, BLOB, or JSON. JSON columns have JSON_TEXTCONTAINS() automatically enabled.

INMEMORY TEXT (col1 USING policy1, col2 USING policy2, …)

Specifies the list of columns to be enabled as IM full text along with custom indexing policies. The columns must be of type CHAR, VARCHAR2, CLOB, BLOB.

Table 5-4 Initialization Parameters Relating to IM Full Text

Syntax Description

MAX_STRING_SIZE

Controls the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL. IM full text columns require that you set to MAX_STRING_SIZE to EXTENDED, thus raising the byte limit to 32767.

INMEMORY_VIRTUAL_COLUMNS

Controls which user-defined virtual columns are stored as IM virtual columns. IM full text columns require that you set to INMEMORY_VIRTUAL_COLUMNS to ENABLE.

See Also:

5.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:

To enable IM virtual columns:

  1. In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.

  2. Either set the INMEMORY_VIRTUAL_COLUMNS initialization parameter to ENABLE, or enable specific virtual columns for the IM column store.

Example 5-3 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=BOTH;

System altered.

Note that you could also use SCOPE=SPFILE, but in that case the change will not take effect until the next database restart. When SCOPE=BOTH is used, the alteration takes place immediately. A restart is not required.

Example 5-4 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);

Example 5-5 Adding Virtual Columns to an In-Memory Spatial Table

In this example, create a table that contains location data, but initially does not include a spatial geometry column. Then, add a spatial geometry column. Update the table to populate geometry objects based on the existing latitude and longitude coordinates. Also update the spatial metadata. Finally, convert the table to INMEMORY, specifying the spatial column as INMEMORY SPATIAL to create the inmemory spatial index.

  1. Create the sample table city_points and insert a set of coordinates (latitude and longitude) for a location in or near each city. 

    CREATE TABLE city_points (
  city_id NUMBER PRIMARY KEY,
  city_name VARCHAR2(25),
  latitude NUMBER,
  longitude NUMBER);

INSERT INTO city_points (city_id, city_name, latitude, longitude)
  VALUES (1, 'Boston', 42.207905, -71.015625);
INSERT INTO city_points (city_id, city_name, latitude, longitude)
  VALUES (2, 'Raleigh', 35.634679, -78.618164);
INSERT INTO city_points (city_id, city_name, latitude, longitude)
  VALUES (3, 'San Francisco', 37.661791, -122.453613);
INSERT INTO city_points (city_id, city_name, latitude, longitude)
  VALUES (4, 'Memphis', 35.097140, -90.065918);
  2. Add a spatial geometry column to the city_points table.
    ALTER TABLE city_points ADD (shape SDO_GEOMETRY);
  3. Populate the new column with geometry objects based on the location coordinates that you inserted.
    UPDATE city_points SET shape = 
  SDO_GEOMETRY(
    2001,
    8307,
    SDO_POINT_TYPE(LONGITUDE, LATITUDE, NULL),
    NULL,
    NULL
   );
  4. Update the spatial metadata in the user_sdo_geom_metadata view.
    INSERT INTO user_sdo_geom_metadata VALUES (
  'city_points',
  'SHAPE', 
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('Longitude',-180,180,0.5), 
    SDO_DIM_ELEMENT('Latitude',-90,90,0.5)
  ), 
  8307
);
commit;
  5. Alter the city_points table to make it a candidate for population in the IM column store. Specify the table as INMEMORY. Also include the INMEMORY SPATIAL keywords since this table includes the shape spatial geometry column. 
    ALTER TABLE city_points INMEMORY PRIORITY high INMEMORY SPATIAL (shape);

    You can then use DBMS_INMEMORY.POPULATE to populate the city_points table in the IM column store. 

    EXEC DBMS_INMEMORY.POPULATE('chicago','city_points');

Note:

Virtual columns are created as part of the spatial geometry column. This is not related to the In-Memory feature. With the use of the INMEMORY SPATIAL keywords, one or more IME columns are created.

See Also:

The Spatial and Graph Developer's Guide, where this same example is used in a broader discussion of spatial concepts.

Example 5-6 Using Spatial Digest (MIN/MAX Value IME in IM Column Store)

SELECT city_name 
FROM city_points c 
where 
 sdo_filter(c.shape, 
            sdo_geometry(2001,8307,sdo_point_type(-122.453613,37.661791,null),null,null)
           ) = 'TRUE'; 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

Example 5-7 Using DBIM With no Spatial Enhancements

SELECT * FROM city_points c where c.shape.sdo_point.x = -122.453613; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

Example 5-8 Verifying Virtual Column Creation

col table_name format a20
col column_name format a30
col data_type format a20
col data_default format a30 word_wrapped;
select
 table_name, column_name, data_type, DATA_LENGTH, DATA_DEFAULT
from user_tab_cols 
 where column_name like 'SYS%';

SELECT * FROM city_points c;
If you want to clean up, delete the related metadata and then drop and purge the city_points sample table.
delete from user_sdo_geom_metadata where table_name = 'CITY_POINTS'; drop table city_points purge;

5.3.3 Enabling IM Full Text Columns

To enable IM full text columns, specify the INMEMORY TEXT clause on the CREATE TABLE and ALTER TABLE statement.

Prerequisites

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

  • The IM column store must be enabled for the database.

    See "Enabling the IM Column Store for a CDB or PDB".

  • IM virtual columns must be enabled.

    See "Enabling IM Virtual Columns".

  • The initialization parameter MAX_STRING_SIZE must be set to EXTENDED.

  • If you specify a custom indexing policy, then the policy must exist.

    You can create a policy with CTX_DDL.CREATE_POLICY. This procedure requires the CTXAPP role or be EXECUTE privileges on the CTXSYS.CTX_DDL package.

To enable IM full text columns:

  1. In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.

  2. Specify either the CREATE TABLE or ALTER TABLE statement with the INMEMORY TEXT clause, using either of the following forms:

    • INMEMORY TEXT (col1, col2, …)

    • INMEMORY TEXT (col1 USING policy1, col2 USING policy2, …)

Example 5-9 Enabling IM Full Text Columns

In this example, you log in with the biblio user account, which has the CTXAPP role. You create a table with two IM full text search columns: text_doc and json_doc: 


CREATE TABLE books (id NUMBER, createTime DATE, text_doc CLOB, json_doc JSON) 
   INMEMORY TEXT(text_doc, json_doc);

You create a custom policy for text search, and then apply it to the text_doc column as follows: 

EXEC CTX_DDL.CREATE_POLICY('book_search_policy'); 
ALTER TABLE books INMEMORY TEXT (text_doc USING 'book_search_policy');

Note that the books.json_doc column, which uses the JSON data type, uses a default policy.

Example 5-10 Replacing a Custom Policy on an INMEMORY TEXT Column

In this example, the table books is enabled with two IM full text search columns: text_doc and json_doc. The text_doc column uses the custom policy book_search_policy. Your goal is replace the existing policy with a policy named book_search_policy2. You must apply the NO INMEMORY attribute and then apply INMEMORY as follows: 

ALTER TABLE books NO INMEMORY TEXT(text_doc);
ALTER TABLE books INMEMORY TEXT (text_doc USING 'book_search_policy2');

See Also:

5.3.4 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);

Note 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. Consequently, any query that references these two columns, either in the SELECT list or in the predicate, must use the row store rather than 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:

5.3.5 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:

  1. 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.

  2. 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.

  3. To ensure that column c3 is never populated, apply the NO INMEMORY attribute to column c3: 

    ALTER TABLE t NO INMEMORY (c3);

  4. 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.

  5. 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.

  6. Specify the entire table as INMEMORY: 

    ALTER TABLE t INMEMORY;

  7. 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.

  8. Apply different compression levels to columns c1 and c2:
    ALTER TABLE t 
  INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (c1) 
  INMEMORY MEMCOMPRESS FOR CAPACITY LOW (c2);

  9. 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.

  10. Specify the entire table as NO INMEMORY: 

    ALTER TABLE t NO INMEMORY;

  11. 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

5.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 CDB or PDB".

To enable or disable tablespaces for the IM column store:

  1. In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.

  2. Run a CREATE TABLESPACE or ALTER TABLESPACE statement with an INMEMORY clause or a NO INMEMORY clause.

Example 5-11 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 5-12 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;

5.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 CDB or PDB".

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

  1. In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.

  2. Run a CREATE MATERIALIZED VIEW or ALTER MATERIALIZED VIEW statement with either an INMEMORY clause or a NO INMEMORY clause.

Example 5-13 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 5-14 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