Oracle 23ai— The INMEMORY(ALL) and NO INMEMORY(ALL) Clauses

As you know, the In-Memory feature can be enabled or disabled at the column level:

SQL> create table usef.tb(c1 number,c2 number,c3 number,c4 number,c5 number,c6 number);
Table created.
SQL> ALTER TABLE usef.tb INMEMORY NO INMEMORY (c1,c2);
Table altered.
SQL> SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION
FROM V$IM_COLUMN_LEVEL
WHERE TABLE_NAME = 'TB'
ORDER BY COLUMN_NAME;  
TABLE_NAME COLUMN_NAME     INMEMORY_COMPRESSION
---------- --------------- --------------------------
TB         C1              NO INMEMORY
TB         C2              NO INMEMORY
TB         C3              DEFAULT
TB         C4              DEFAULT
TB         C5              DEFAULT
TB         C6              DEFAULT
6 rows selected.
SQL> select inmemory from dba_tables where TABLE_NAME = 'TB';
INMEMORY
--------
ENABLED

When a table contains many columns, the above command text can become lengthy. Therefore, this command requires more flexibility to easily apply desired changes by defining exclusion and inclusion lists.

This minor enhancement was introduced in Oracle 23ai. In this version, the clauses INMEMORY (ALL) and NO INMEMORY (ALL) can be used, as illustrated in the following examples:

SQL> ALTER TABLE usef.tb INMEMORY(c2,c5) NO INMEMORY (ALL);
Table altered.
SQL> SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION
FROM V$IM_COLUMN_LEVEL
WHERE TABLE_NAME = 'TB'
ORDER BY COLUMN_NAME;  2    3    4
TABLE_NAME COLUMN_NAME     INMEMORY_COMPRESSION
---------- --------------- --------------------------
TB         C1              NO INMEMORY
TB         C2              DEFAULT
TB         C3              NO INMEMORY
TB         C4              NO INMEMORY
TB         C5              DEFAULT
TB         C6              NO INMEMORY
6 rows selected.

SQL> ALTER TABLE usef.tb INMEMORY(ALL) NO INMEMORY (c4);
Table altered.
SQL>  SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION
FROM V$IM_COLUMN_LEVEL
WHERE TABLE_NAME = 'TB'
ORDER BY COLUMN_NAME;  2    3    4
TABLE_NAME COLUMN_NAME     INMEMORY_COMPRESSION
---------- --------------- --------------------------
TB         C1              DEFAULT
TB         C2              DEFAULT
TB         C3              DEFAULT
TB         C4              NO INMEMORY
TB         C5              DEFAULT
TB         C6              DEFAULT
6 rows selected.

SQL> ALTER TABLE usef.tb INMEMORY NO INMEMORY (ALL);
Table altered.
SQL> SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION
FROM V$IM_COLUMN_LEVEL
WHERE TABLE_NAME = 'TB'
ORDER BY COLUMN_NAME;
  TABLE_NAME COLUMN_NAME     INMEMORY_COMPRESSION
---------- --------------- --------------------------
TB         C1              NO INMEMORY
TB         C2              NO INMEMORY
TB         C3              NO INMEMORY
TB         C4              NO INMEMORY
TB         C5              NO INMEMORY
TB         C6              NO INMEMORY
6 rows selected.

SQL> ALTER TABLE usef.tb INMEMORY INMEMORY (ALL);
Table altered.
SQL> SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION
FROM V$IM_COLUMN_LEVEL
WHERE TABLE_NAME = 'TB'
ORDER BY COLUMN_NAME;  
TABLE_NAME COLUMN_NAME     INMEMORY_COMPRESSION
---------- --------------- --------------------------
TB         C1              DEFAULT
TB         C2              DEFAULT
TB         C3              DEFAULT
TB         C4              DEFAULT
TB         C5              DEFAULT
TB         C6              DEFAULT
6 rows selected.

The above clauses can also be used during table creation:

SQL> create table usef.tb(c1 number,c2 number,c3 number,c4 number,c5 number,c6 number) INMEMORY(ALL) NO INMEMORY (c4);
Table created.