8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
In-Memory Column Store in Oracle Database 12c Release 1 (12.1.0.2)
The In-Memory Column Store (IM column store) was the headline feature of the 12.1.0.2 patchset. This features allows you to store columns, tables, partitions and materialized views in memory in a columnar format, rather than the typical row format. The advantage of having data in memory is obvious, but the columnar storage lends itself extremely well to analytic queries found in business intelligence products.
The IM column store is a separately licensed option of Oracle Database Enterprise Edition.
- Introduction
- Enable In-Memory Column Store
- Disable In-Memory Column Store
- Managing Tables
- Managing Columns
- Managing Materialized Views
- Managing Tablespaces
- Views
Introduction
The In-Memory column store is a new section of the SGA, sized using the INMEMORY_SIZE
initialization parameter. You can choose to store specific groups of columns, whole tables, materialized views or table partitions in the store. Alternatively, you can enable IM column store at the tablespace level, so all tables and materialized views in the tablespace are automatically enabled for the IM column store. The following commands have been modified to include additional in-memory clauses.
CREATE TABLE ALTER TABLE CREATE TABLESPACE ALTER TABLESPACE CREATE MATERIALIZED VIEW ALTER MATERIALIZED VIEW
Basic examples of their use will be shown below.
The documentation claims the IM column store is good for the following.
- Large scans that apply "=", "<", ">" and "IN" filters.
- Queries that return a small number of columns from a table with a large number of columns.
- Queries that join small tables to large tables.
- Queries that aggregate data.
It also states it is not designed for the following.
- Queries with complex predicates.
- Queries that return a large number of columns.
- Queries that return large numbers of rows.
- Queries with multiple large table joins.
The important thing to remember here is *you* will be responsible for deciding which objects will benefit the most from inclusion in the IM column store. If you choose wisely you will see big improvements in performance. If you choose badly, you will waste a lot of memory that could be used by the buffer cache.
Full lists of initialization parameters, compression methods and population options are available from the documentation. I'm not going to repeat them here.
- Initialization Parameters Related to the IM Column Store
- IM Column Store Compression Methods
- IM Column Store Data Population Options
Enable In-Memory Column Store
Remember, the IM column store is part of the SGA, so the SGA must be capable of containing the amount of memory you want to assign to the INMEMORY_SIZE
parameter. In a multitenant environment, the INMEMORY_SIZE
parameter must be set in the CDB if any of the PDBs need access to the IM column store.
- If you are using AMM (MEMORY_TARGET), you will need to extend this to account for the
INMEMORY_SIZE
parameter value. - If you are using ASMM (SGA_TARGET), you will need to extend this to account for the
INMEMORY_SIZE
parameter value.
Assuming the COMPATIBLE
parameter is set to 12.1.0 or higher and there is enough room in the SGA to hold the IM column store, the following process will enable the IM column store. In this case I am setting the INMEMORY_SIZE
parameter to 2G.
ALTER SYSTEM SET SGA_TARGET=3G SCOPE=SPFILE; ALTER SYSTEM SET INMEMORY_SIZE=2G SCOPE=SPFILE; SHUTDOWN IMMEDIATE; STARTUP; ORACLE instance started. Total System Global Area 3221225472 bytes Fixed Size 2929552 bytes Variable Size 419433584 bytes Database Buffers 637534208 bytes Redo Buffers 13844480 bytes In-Memory Area 2147483648 bytes Database mounted. Database opened. SQL>
Notice the "In-Memory Area" line produced during the startup.
The current IM settings are shown below. With the exception of the size, all others are default values.
SQL> SHOW PARAMETER INMEMORY NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_clause_default string inmemory_force string DEFAULT inmemory_max_populate_servers integer 1 inmemory_query string ENABLE inmemory_size big integer 2G inmemory_trickle_repopulate_servers_ integer 1 percent optimizer_inmemory_aware boolean TRUE SQL>
The INMEMORY_SIZE
parameter setting is inherited by all PDBs unless it is explicitly set at the PDB level. Changing the INMEMORY_SIZE
parameter value at the PDB level does not require a restart of the instance or PDB.
CONN sys@pdb1 AS SYSDBA -- Disable IM column store in the PDB ALTER SYSTEM SET INMEMORY_SIZE=0; -- OR ALTER SYSTEM RESET INMEMORY_SIZE; -- Assign a PDB-specific size. ALTER SYSTEM SET INMEMORY_SIZE=1G;
Disable In-Memory Column Store
There are several ways to disable the IM column store, depending on what you are trying to achieve.
Setting the INMEMORY_FORCE
parameter to "OFF" means objects will not be maintained in the IM column store. Switching it back to "DEFAULT" returns to the default behaviour.
-- System level ALTER SYSTEM SET INMEMORY_FORCE=OFF; ALTER SYSTEM SET INMEMORY_FORCE=DEFAULT;
Setting the INMEMORY_QUERY
parameter to "DISABLE" means the optimiser will not consider the IM column store to optimise queries. Switching it back to "ENABLE" reverts it to the default functionality.
-- System level ALTER SYSTEM SET INMEMORY_QUERY=DISABLE; ALTER SYSTEM SET INMEMORY_QUERY=ENABLE; -- Session level ALTER SESSION SET INMEMORY_QUERY=DISABLE; ALTER SESSION SET INMEMORY_QUERY=ENABLE;
To disable the IM column store completely and release the memory, reset the INMEMORY_SIZE
parameter.
ALTER SYSTEM RESET INMEMORY_SIZE SCOPE=SPFILE; SHUTDOWN IMMEDIATE; STARTUP;
As described in the previous section, the PDB-specific settings can be altered without an instance or PDB restart.
Managing Tables
The CREATE TABLE
and ALTER TABLE
commands have been amended to allow you to determine if the table should be stored in the IM column store. Creating a table with the NO INMEMORY
clause is the same as not specifying the clause at all. The example below shows the creation of three tables using three variations on the syntax. The [DBA|ALL|USER]_TABLES
views have been amended to include IM-related information.
CONN test/test@pdb1 CREATE TABLE im_tab ( id NUMBER ) INMEMORY; CREATE TABLE noim_tab ( id NUMBER ) NO INMEMORY; CREATE TABLE default_tab ( id NUMBER ); COLUMN table_name FORMAT A20 SELECT table_name, inmemory, inmemory_priority, inmemory_distribute, inmemory_compression, inmemory_duplicate FROM user_tables ORDER BY table_name; TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL -------------------- -------- -------- --------------- ----------------- ------------- DEFAULT_TAB DISABLED IM_TAB ENABLED NONE AUTO FOR QUERY LOW NO DUPLICATE NOIM_TAB DISABLED 3 rows selected. SQL>
The ALTER TABLE
command can change the IM status of the objects. The following example flips the status.
ALTER TABLE IM_TAB NO INMEMORY; ALTER TABLE NOIM_TAB INMEMORY MEMCOMPRESS FOR CAPACITY LOW; ALTER TABLE DEFAULT_TAB INMEMORY PRIORITY HIGH; SELECT table_name, inmemory, inmemory_priority, inmemory_distribute, inmemory_compression, inmemory_duplicate FROM user_tables ORDER BY table_name; TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL -------------------- -------- -------- --------------- ----------------- ------------- DEFAULT_TAB ENABLED HIGH AUTO FOR QUERY LOW NO DUPLICATE IM_TAB DISABLED NOIM_TAB ENABLED NONE AUTO FOR CAPACITY LOW NO DUPLICATE 3 rows selected. SQL>
Managing Columns
The following example shows the syntax for including a subset of columns in the IM column store.
CREATE TABLE im_col_tab ( id NUMBER, col1 NUMBER, col2 NUMBER, col3 NUMBER, col4 NUMBER ) INMEMORY INMEMORY MEMCOMPRESS FOR QUERY HIGH (col1, col2) INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (col3) NO INMEMORY (id, col4);
The column settings are displayed using the V$IM_COLUMN_LEVEL
view.
CONN sys@pdb1 AS SYSDBA SELECT table_name, segment_column_id, column_name, inmemory_compression FROM v$im_column_level WHERE owner = 'TEST' and table_name = 'IM_COL_TAB' ORDER BY segment_column_id; TABLE_NAME SEGMENT_COLUMN_ID COLUMN_NAME INMEMORY_COMPRESSION -------------------- ----------------- ------------------------------- -------------------------- IM_COL_TAB 1 ID NO INMEMORY IM_COL_TAB 2 COL1 FOR QUERY HIGH IM_COL_TAB 3 COL2 FOR QUERY HIGH IM_COL_TAB 4 COL3 FOR CAPACITY HIGH IM_COL_TAB 5 COL4 NO INMEMORY 5 rows selected. SQL>
The IM settings can be changed using the ALTER TABLE command.
CONN test/test@pdb1 ALTER TABLE im_col_tab NO INMEMORY (col1, col2) INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (col3) NO INMEMORY (id, col4); CONN sys@pdb1 AS SYSDBA SELECT table_name, segment_column_id, column_name, inmemory_compression FROM v$im_column_level WHERE owner = 'TEST' and table_name = 'IM_COL_TAB' ORDER BY segment_column_id; TABLE_NAME SEGMENT_COLUMN_ID COLUMN_NAME INMEMORY_COMPRESSION -------------------- ----------------- ------------------------------- -------------------------- IM_COL_TAB 1 ID NO INMEMORY IM_COL_TAB 2 COL1 NO INMEMORY IM_COL_TAB 3 COL2 NO INMEMORY IM_COL_TAB 4 COL3 FOR CAPACITY HIGH IM_COL_TAB 5 COL4 NO INMEMORY 5 rows selected. SQL>
Managing Materialized Views
The CREATE MATERIALIZED VIEW
and ALTER MATERIALIZED VIEW
commands work in a similar was to the CREATE TABLE
and ALTER TABLE
commands.
CONN test/test@pdb1 CREATE TABLE t1 AS SELECT * FROM all_objects; CREATE MATERIALIZED VIEW t1_mv INMEMORY AS SELECT * FROM t1; SELECT table_name, inmemory, inmemory_priority, inmemory_distribute, inmemory_compression, inmemory_duplicate FROM user_tables WHERE table_name = 'T1_MV'; TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL -------------------- -------- -------- --------------- ----------------- ------------- T1_MV ENABLED NONE AUTO FOR QUERY LOW NO DUPLICATE 1 row selected. SQL> ALTER MATERIALIZED VIEW t1_mv INMEMORY MEMCOMPRESS FOR CAPACITY HIGH PRIORITY HIGH; SELECT table_name, inmemory, inmemory_priority, inmemory_distribute, inmemory_compression, inmemory_duplicate FROM user_tables WHERE table_name = 'T1_MV'; TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL -------------------- -------- -------- --------------- ----------------- ------------- T1_MV ENABLED HIGH AUTO FOR CAPACITY HIGH NO DUPLICATE 1 row selected. SQL> ALTER MATERIALIZED VIEW t1_mv NO INMEMORY; SELECT table_name, inmemory, inmemory_priority, inmemory_distribute, inmemory_compression, inmemory_duplicate FROM user_tables WHERE table_name = 'T1_MV'; TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL -------------------- -------- -------- --------------- ----------------- ------------- T1_MV DISABLED 1 row selected. SQL>
Managing Tablespaces
Setting the default IM column store parameters for a tablespace means all tables and materialized views in that tablespace will use those setting unless explicitly overridden.
The following example shows how to set the IM column store parameters during a tablespace creation. The keyword DEFAULT
is part of the IM clause. The default IM settings are displayed using the DBA_TABLESPACES
view.
CONN sys@pdb1 AS SYSDBA CREATE TABLESPACE new_ts DATAFILE '/u01/app/oracle/oradata/CDB1/datafile/pdb1/pdb1_new_ts.dbf' SIZE 10M DEFAULT INMEMORY; SELECT tablespace_name, def_inmemory, def_inmemory_priority, def_inmemory_distribute, def_inmemory_compression, def_inmemory_duplicate FROM dba_tablespaces ORDER BY tablespace_name; TABLESPACE_NAME DEF_INME DEF_INME DEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_ ------------------------------ -------- -------- --------------- ----------------- ------------- NEW_TS ENABLED NONE AUTO FOR QUERY LOW NO DUPLICATE SYSAUX DISABLED SYSTEM DISABLED TEMP DISABLED USERS DISABLED 5 rows selected. SQL>
The ALTER TABLESPACE
command is used to change the IM column store parameters.
ALTER TABLESPACE new_ts DEFAULT INMEMORY MEMCOMPRESS FOR CAPACITY HIGH; SELECT tablespace_name, def_inmemory, def_inmemory_priority, def_inmemory_distribute, def_inmemory_compression, def_inmemory_duplicate FROM dba_tablespaces ORDER BY tablespace_name; TABLESPACE_NAME DEF_INME DEF_INME DEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_ ------------------------------ -------- -------- --------------- ----------------- ------------- NEW_TS ENABLED NONE AUTO FOR CAPACITY HIGH NO DUPLICATE SYSAUX DISABLED SYSTEM DISABLED TEMP DISABLED USERS DISABLED SQL> ALTER TABLESPACE new_ts DEFAULT NO INMEMORY; SELECT tablespace_name, def_inmemory, def_inmemory_priority, def_inmemory_distribute, def_inmemory_compression, def_inmemory_duplicate FROM dba_tablespaces ORDER BY tablespace_name; TABLESPACE_NAME DEF_INME DEF_INME DEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_ ------------------------------ -------- -------- --------------- ----------------- ------------- NEW_TS DISABLED SYSAUX DISABLED SYSTEM DISABLED TEMP DISABLED USERS DISABLED 5 rows selected. SQL>
The default settings can be overridden by specific object settings shown above.
Views
We've already seen some of the existing views have been modified to include IM column store information. The following V$
views have been added in to support the IM column store.
For more information see:
Hope this helps. Regards Tim...