12C新特性：INMEMORY实战（5）对列进行inmemory的操作

创建测试表

SQL> create table test2 as select OWNER,OBJECT_NAME,OBJECT_ID from dba_objects;

Table created.

SQL> COL TABLE_NAME FORMAT a20
SQL> COL COLUMN_NAME FORMAT a20
SQL> SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION FROM   V$IM_COLUMN_LEVEL WHERE  TABLE_NAME = 'TEST2' ORDER BY COLUMN_NAME;

no rows selected

测试把单独的列放到inmemory

SQL> alter table test2 inmemory(OBJECT_NAME);

Table altered.

查看结果，所有的列都放入inmemory，得出结论：无法仅对某列直接放入inmemory

SQL> COL TABLE_NAME FORMAT a20
SQL> COL COLUMN_NAME FORMAT a20
SQL> set linesize 200
SQL> SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION FROM   V$IM_COLUMN_LEVEL WHERE  TABLE_NAME = 'TEST2' ORDER BY COLUMN_NAME;

TABLE_NAME           COLUMN_NAME          INMEMORY_COMPRESSION
-------------------- -------------------- ------------------------------------------------------------------------------
TEST2                OBJECT_ID            DEFAULT
TEST2                OBJECT_NAME          DEFAULT
TEST2                OWNER                DEFAULT

对列进行no inmemory

SQL> alter table test2 no inmemory(OBJECT_NAME);

Table altered.

SQL> SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION FROM   V$IM_COLUMN_LEVEL WHERE  TABLE_NAME = 'TEST2' ORDER BY COLUMN_NAME;

TABLE_NAME           COLUMN_NAME          INMEMORY_COMPRESSION
-------------------- -------------------- ------------------------------------------------------------------------------
TEST2                OBJECT_ID            DEFAULT
TEST2                OBJECT_NAME          NO INMEMORY
TEST2                OWNER                DEFAULT

修改列的压缩策略

SQL> alter table test2 INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (OBJECT_ID);

Table altered.

SQL> SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION FROM   V$IM_COLUMN_LEVEL WHERE  TABLE_NAME = 'TEST2' ORDER BY COLUMN_NAME;

TABLE_NAME           COLUMN_NAME          INMEMORY_COMPRESSION
-------------------- -------------------- ------------------------------------------------------------------------------
TEST2                OBJECT_ID            FOR CAPACITY HIGH
TEST2                OBJECT_NAME          NO INMEMORY
TEST2                OWNER                DEFAULT