4 Enabling Objects for In-Memory Population

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

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.

Example 4-16 Creating an Eviction Policy

In this example, you create a policy specifying that oe.order_items table is evicted from the IM column store if it has not been accessed in three days. An ADO IM column store policy must be a segment-level policy.

ALTER TABLE oe.order_items ILM ADD POLICY
   NO INMEMORY SEGMENT
   AFTER 3 DAYS OF NO ACCESS;

Example 4-17 Executing an ILM Policy Using DBMS_ILM

You can also evaluate and executes policies manually. Thus, you can programmatically decide when you want an object compressed or tiered. The following example manually executes an ADO task for sh.sales:

DECLARE
  v_executonid NUMBER;
BEGIN
  DBMS_ILM.EXECUTE_ILM ( owner           => 'SH', 
                         object_name     => 'SALES',
                         execution_mode  => DBMS_ILM.ILM_EXECUTION_OFFLINE,
                         task_id         => v_executionid);
END;
/