DBMS_INMEMORY

This package contains procedures for populating and repopulating the IM column store, and for dropping IM expressions from a specified table.

1. IME_DROP_EXPRESSIONS Procedure

This procedure drops a specified set of SYS_IME virtual columns from a table.

DBMS_INMEMORY.IME_DROP_EXPRESSIONS(

   schema_name    IN    VARCHAR2,

   table_name     IN    VARCHAR2,

   column_name    IN    VARCHAR2 DEFAULT NULL);  

column_name: The name of the SYS_IME column. By default this value is null, which specifies all SYS_IME columns in this table.

The maximum number of SYS_IME columns for a table, regardless of whether the attribute is INMEMORY or NO INMEMORY, is 50. After the 50-expression limit is reached for a table, the database will not add new SYS_IME columns.

To make space for new expressions, you must manually drop SYS_IME columns with the DBMS_INMEMORY.IME_DROP_EXPRESSIONS or DBMS_INMEMORY_ADMIN.IME_DROP_ALL_EXPRESSIONS procedures. To drop a specified SYS_IME column or all SYS_IME columns in the requested table, use DBMS_INMEMORY.IME_DROP_EXPRESSIONS .

To populate these segments again, either invoke the DBMS_INMEMORY.POPULATE procedure, or perform a full table scan.

1. POPULATE Procedure

This procedure forces population of the specified table, partition, or subpartition into the IM column store.

DBMS_INMEMORY.POPULATE(

   schema_name      IN    VARCHAR2,

   table_name       IN    VARCHAR2,

   subobject_name   IN    VARCHAR2 DEFAULT NULL);

1. REPOPULATE Procedure

This procedure forces repopulation of a table, partition, or subpartition that is currently populated in the IM column store.

DBMS_INMEMORY.REPOPULATE(

   schema_name      IN    VARCHAR2,

   table_name       IN    VARCHAR2,

   subobject_name   IN    VARCHAR2 DEFAULT NULL,

   force            IN    BOOLEAN DEFAULT FALSE);

Force: Whether to repopulate all IMCUs in the segment, just as in initial population.

The following values are possible for the force parameter:

FALSE — The database repopulates only IMCUs containing modified rows. This is the default.

TRUE — The database drops the segment, and then rebuilds it. The database increments the statistics and performs all other tasks related to initial population.

For example, IMCU 1 contains rows 1 to 500,000, and IMCU 2 contains rows 500,001 to 1,000,000. A statement modifies row 600,000. When force is FALSE, the database only repopulates IMCU 2. When force is TRUE, the database repopulates both IMCUs.

Consider further that the INMEMORY_VIRTUAL_COLUMNS initialization parameter is set to ENABLE, and an application creates a new virtual column. When force is FALSE, the database only repopulates IMCU 2 with the new column. When force is TRUE, the database repopulates both IMCUs with the new column.

1. SEGMENT_DEALLOCATE_VERSIONS Procedure

This procedure deallocates non-current IMCUs in the IM column store.

DBMS_INMEMORY.SEGMENT_DEALLOCATE_VERSIONS(

   SCHEMA_NAME      IN  VARCHAR2,

   TABLE_NAME       IN  VARCHAR2,

   PARTITION_NAME   IN  VARCHAR2 DEFAULT NULL,

   SPCPRESSURE      IN  BOOLEAN DEFAULT FALSE);

Spcpressure: Whether to force deallocation of non-current IMCUs (TRUE), or wait for the database to deallocate them automatically. By default, the database deallocates non-current IMCUs every two minutes.

During repopulation, the IM column store maintains both the current IMCU and non-current IMCU. This mechanism, which is called double buffering, ensures that queries do not decrease performance because an IMCU is unavailable during repopulation. After repopulation completes, the IM column store retains the non-current IMCU for a short time (2 minutes by default) to optimize queries with older SCNs. Typically, the default behavior is sufficient. However, you can force deallocation of non-current IMCUs by using the SEGMENT_DEALLOCATE_VERSIONS procedure.