Syntax Issues with INMEMORY Column Clause

With Oracle Database In-Memory, it is possible to populate individual columns of a table into the In-Memory Column Store. This is very useful for large tables, if only the frequently used columns should be populated to safe memory. The SQL syntax to define this seems to be straight-forward, but does not always work as expected.

Let’s assume we want to create a new table SALES_IM (as a copy of the SH.SALES table. The table should be configured as INMEMORY, except the rarely used columns CHANNEL_ID and PROMO_ID. The syntax diagrams of the CREATE TABLE and ALTER TABLE statements show us several options (Source: Oracle Database 12c Release 2, Database SQL Language Reference):

Inmemory syntax

The inmemory_table_clause defines whether the table is populated to the In-Memory Columns Store (IMCS) or not. With the inmemory_column_clause, we can turn individual columns on or off. At least, that’s what I expect. For the specific case above, I use the following statement:

 

CREATE TABLE sales_im INMEMORY

NO INMEMORY (channel_id, promo_id)

AS SELECT * FROM sh.sales;

 

Here, we already have the first surprise: Instead of a new table SALES_IM, we will get an error message. It seems that the syntax of the inmemory_column_clause is not supported in the CREATE TABLE statement, neither in Oracle 12.1.0.2 nor in 12.2.0.1.

 

ORA-00922: missing or invalid option

 

Fortunately, this issue can be solved with two separate commands. First, the whole table must be set to INMEMORY, and in a second step, the unneeded columns are excluded. The following statements work fine:

 

CREATE TABLE sales_im INMEMORY

AS SELECT * FROM sh.sales;

 

ALTER TABLE sales_im NO INMEMORY (channel_id, promo_id);

 

With a query on V$IM_COLUMN_LEVEL, we can verify that the table is now populated correctly. All columns except CHANNEL_ID and PROMO_ID are available in the IMCS.

 

SELECT column_name, inmemory_compression

  FROM v$im_column_level

 WHERE table_name = ‘SALES_IM’;

 

COLUMN_NAME          INMEMORY_COMPRESSION

——————– ——————–

PROD_ID              DEFAULT             

CUST_ID              DEFAULT             

TIME_ID              DEFAULT             

CHANNEL_ID           NO INMEMORY         

PROMO_ID             NO INMEMORY         

QUANTITY_SOLD        DEFAULT             

AMOUNT_SOLD          DEFAULT             

 

With the keywords INMEMORY and NO INMEMORY, it is possible to change the columns in the IMCS whenever you want. To show this, we turn off INMEMORY for column TIME_ID, and in a second step, turn it on for column CHANNEL_ID. Note that the first INMEMORY keyword in both statements is on table level, the second [NO] INMEMORY on column level. Actually, the first one is optional, because the table is already set to INMEMORY.

 

ALTER TABLE sales_im INMEMORY NO INMEMORY (time_id);

 

ALTER TABLE sales_im INMEMORY INMEMORY (channel_id);

 

As expected, TIME_ID is not in the IMCS anymore, but CHANNEL_ID is now populated:

 

COLUMN_NAME          INMEMORY_COMPRESSION

——————– ——————–

PROD_ID              DEFAULT             

CUST_ID              DEFAULT             

TIME_ID              NO INMEMORY         

CHANNEL_ID           DEFAULT             

PROMO_ID             NO INMEMORY         

QUANTITY_SOLD        DEFAULT             

AMOUNT_SOLD          DEFAULT             

 

This example shows that it is always possible to switch the INMEMORY behavior for each individual column of a table that is set to INMEMORY. But does it work the other way, too? I do not want to populate the whole table to IMCS, but only the three columns PROD_ID, CUST:ID and TIME_ID. Based on the syntax diagrams, the following command should be allowed:

 

ALTER TABLE sales_im NO INMEMORY INMEMORY (prod_id, cust_id, time_id);

 

In Oracle 12.1.0.2, this statement fails with an error message. The message is clear: This feature works only if the table is set to INMEMORY. So, we first must populate the whole table and then exclude the columns we don’t want to have in the IMCS.

 

ORA-64361: column INMEMORY clause may only be specified for an inmemory table

  

In Oracle 12.2.0.1, this restriction seems to be gone, the statement above can be executed without an error. But when we look at the result in V$IM_COLUMN_LEVEL, it’s not what we would expect:

 

SELECT column_name, inmemory_compression

  FROM v$im_column_level

 WHERE table_name = ‘SALES_IM’;


no rows selected

 

When the table is set to NO INMEMORY, it is not possible to populate individual columns to the IMCS. That’s very sad.

The workaround is to set INMEMORY on table level and then specify all columns of the table either in the INMEMORY column clause or the NO INMEMORY column clause. This works in 12.1 and 12.2:

 

ALTER TABLE sales_im INMEMORY

   INMEMORY (prod_id, cust_id, time_id)

NO INMEMORY (promo_id, channel_id, amount_sold, quantity_sold);

 

The result is now what we wanted to define: The first three columns of the table are in IMCS, all other columns are not.

 

SELECT column_name, inmemory_compression

  FROM v$im_column_level

 WHERE table_name = ‘SALES_IM’;

 

COLUMN_NAME          INMEMORY_COMPRESSION

——————– ——————–

PROD_ID              DEFAULT             

CUST_ID              DEFAULT             

TIME_ID              DEFAULT             

CHANNEL_ID           NO INMEMORY         

PROMO_ID             NO INMEMORY         

QUANTITY_SOLD        NO INMEMORY         

AMOUNT_SOLD          NO INMEMORY         

 

 

Summary

Although it is possible to define the INMEMORY clause for individual columns of a table, not all syntax combinations are allowed. With CREATE TABLE, only the whole table can be populated. With ALTER TABLE, is is allowed to define the INMEMORY or NO INMEMORY clause on column level, but only if the table is set to INMEMORY. If NO INMEMORY is set on table level, the behavior depends on the database version. In version 12.1, we get an error message (which is bad). In version 12.2, the column definitions for INMEMORY are just ignored (which is even worse).

Hopefully, these issues will be fixed in the next versions, either with supporting all combinations, or at least with correcting the syntax diagrams in the documentation.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s