5 Enabling Objects for In-Memory Population Manually
If INMEMORY_AUTOMATIC_LEVEL
is not set to HIGH
, then you must manually enable and disable objects for population and set compression and priority options.
Note:
IfINMEMORY_AUTOMATIC_LEVEL
is set to HIGH
, and if INMEMORY_FORCE
is not set to BASE_LEVEL
, then the database automatically enables all objects for In-Memory population, and also populates and evicts them as needed. Manually specifying the INMEMORY
clause in DDL statements is not necessary.
5.1 About Manually Enabling Objects for In-Memory Population
Only objects with the INMEMORY
clause are eligible for population into the IM column store. To apply this clause manually, you must use DDL statements such as CREATE TABLE
or ALTER TABLE
.
5.1.1 Purpose of Enabling Objects for In-Memory Population
Unless objects have the INMEMORY
attribute, they are not eligible for population.
When an object has the INMEMORY
attribute, it can potentially reside in the IM column store. In-Memory population is a separate step that occurs when the database reads existing row-format data from disk, transforms it into columnar format, and then stores it in the IM column store.
Note:
Population, which transforms existing data on disk into columnar format, is different from repopulation, which transforms new data into columnar format. Because IMCUs are read-only structures, Oracle Database does not populate them when rows change. Rather, the database records the row changes in a transaction journal, and then creates new IMCUs as part of repopulation.
Before Oracle Database 21c, all objects were implicitly NO INMEMORY
by default. Starting in Oracle Database 21c, when the
INMEMORY_AUTOMATIC_LEVEL
initialization parameter is set to
HIGH
, all objects are INMEMORY
by default, and
therefore automatically eligible for population. No manual DDL statements to specify
individual objects as INMEMORY
are necessary. When
INMEMORY_AUTOMATIC_LEVEL
is not HIGH
, then you
must specify the INMEMORY
clause manually.
5.1.2 Controls for In-Memory Objects
You can enable tablespaces, tables (internal and external), partitions, and materialized views for In-Memory access. You can also specify options such as compression and population priority.
5.1.2.1 The INMEMORY Subclause
INMEMORY
is a segment-level attribute, not a column-level attribute. However, you can apply the INMEMORY
attribute to a subset of columns within a specific object.
To enable or disable an object for the IM column store, specify the INMEMORY
clause in DDL statements for tablespaces, tables, and materialized views. The INMEMORY
column in the DBA_TABLES
view indicates which tables have the INMEMORY
attribute set (ENABLED
) or not set (DISABLED
).
The following objects are not eligible for population in the IM column store:
-
Indexes
-
Index-organized tables
-
Hash clusters
-
Objects owned by the
SYS
user and stored in theSYSTEM
orSYSAUX
tablespace
5.1.2.1.1 In-Memory Tables
To make heap-organized tables eligible for population, specify the INMEMORY
clause on the CREATE TABLE
or ALTER TABLE
statements.
Columns Eligible for Population
By default, the IM column store populates all nonvirtual columns in the table. You can specify all or some columns of an internal table. For example, you might exclude the weight_class
and catalog_url
columns in oe.product_information
from eligibility.
If you enable a table for the IM column store and it contains any of the following types of columns, then they will not be populated in the IM column store:
-
Out-of-line columns (varrays, nested table columns, and out-of-line LOBs)
Note:
For inline LOB columns, the IM column store allocates up to 4 KB of contiguous buffer storage, and up to 32 KB when the inline LOBs contain OSON (binary JSON) data. For out-of-line LOBs, the IM column store allocates up to 40 bytes for the locator, but does not store the LOB itself.
-
Columns that use the
LONG
orLONG RAW
data types -
Extended data type columns
Starting in Oracle Database 21c, the INMEMORY TEXT
clause enables
you specify In-Memory full text columns. These are CHAR
,
VARCHAR2
, CLOB
, BLOB
, or
JSON
columns that support fast In-Memory queries using the
CONTAINS()
or JSON_TEXTCONTAINS()
operators.
The IM column store stores the column data, such as a text, JSON, or XML document,
in its domain-specific IM format.
In-Memory Partitioned Tables
For a partitioned table, you can specify the INMEMORY
clause at the table level. Partitioned tables can have only external partitions, only internal partitions, or a hybrid mixture of both internal and external partitions. By default, all partitions in a partitioned table inherit the table-level INMEMORY
clause. You can also specify this clause on individual partitions.
Column Storage in the Flash Cache on Oracle Exadata Storage Server
On Oracle Exadata Storage Server, the CELLMEMORY
keyword (default) enables the flash cache to store data in the In-Memory format. You can use ALTER TABLE
to choose FOR QUERY
or FOR CAPACITY
compression. Specifying NO CELLMEMORY
disables columnar storage in the flash cache.
Example 5-1 Specifying a Table as INMEMORY
Assume that you are connected to the database as user sh
. You enable the customers
table for population in the IM column store, using the default compression level of FOR QUERY LOW
:
SQL> SELECT TABLE_NAME, INMEMORY FROM USER_TABLES WHERE TABLE_NAME = 'CUSTOMERS';
TABLE_NAME INMEMORY
---------- --------
CUSTOMERS DISABLED
SQL> ALTER TABLE customers INMEMORY;
Table altered.
SQL> SELECT TABLE_NAME, INMEMORY, INMEMORY_COMPRESSION FROM USER_TABLES WHERE TABLE_NAME='CUSTOMERS';
TABLE_NAME INMEMORY INMEMORY_COMPRESS
---------- -------- -----------------
CUSTOMERS ENABLED FOR QUERY LOW
See Also:
-
Oracle Exadata System Software User's Guide to learn more about
ALTER TABLE ... CELLMEMORY
-
Oracle Database SQL Language Reference for information about
INMEMORY
clause of theCREATE TABLE
statement
5.1.2.1.2 In-Memory External Tables
To make external tables eligible for population, specify the EXTERNAL ... INMEMORY
clause in CREATE TABLE
or ALTER TABLE
.
Purpose of In-Memory External Tables
In-Memory external tables are useful in the following cases:
-
Short-term data that must be scanned repeatedly in a short time span and does not require retention in Oracle Database
-
External data that must be joined to relational data for fast analytic processing
-
Data that is accessed by analytic queries in both Oracle Database and external tools, and which does not need to be materialized in database storage
How In-Memory External Tables Work
The IM column store manages the data for external tables in the same way as for heap-organized tables. For example, a full table scan populates both internal tables and external tables into the IM column store. The same drivers supported for external tables are supported for In-Memory external tables.
User Interface for In-Memory External Tables
You can specify the INMEMORY
clause at the top-level of a partitioned external or hybrid partitioned table. This clause is inherited by every partition. You can also specify INMEMORY
for an individual partition, which enables different partitions within an external table to have different In-Memory specifications.
Note:
If you specify INMEMORY
clauses on a hybrid table that are not supported by IM external tables, then those attributes are only be inherited by the internal partitions.
Note the following restrictions for In-Memory external tables:
-
Some
INMEMORY
subclauses for external tables are not valid, including the column clause, distribute clause, and priority clause. -
Join groups are not supported for In-Memory external tables.
-
In-Memory Optimized Arithmetic does not support external tables.
-
IM expressions are not supported for In-Memory external tables.
-
In-Memory external tables do not support the
DISTRIBUTE ... FOR SERVICE
clause for Oracle Active Data Guard instances.
The following dictionary views for external tables have an INMEMORY
and INMEMORY_COMPRESSION
column:
-
DBA_XTERNAL_PART_TABLES
-
DBA_XTERNAL_TAB_PARTITIONS
-
DBA_XTERNAL_TAB_SUBPARTITIONS
Note:
Sessions that query In-Memory external tables must have the initialization parameter
QUERY_REWRITE_INTEGRITY
set to stale_tolerated
.
It is important to keep in mind that if an external table is modified, then
the results from the IM column store are undefined. Results are also undefined if a
partition is altered (by dropping or adding values). This may lead to differences in
results between IM and non-IM based scans. You can run
DBMS_INMEMORY.REPOPULATE
to refresh the IM store so that it is
resynchronized with the table data.
See Also:
-
"Populating an In-Memory External Table Using DBMS_INMEMORY.POPULATE: Example"
-
Oracle Database SQL Language Reference for information about
INMEMORY
clause of theCREATE TABLE ... EXTERNAL
statement -
Oracle Database Licensing Information User Manual for details on which features are supported for different editions and services
5.1.2.1.3 In-Memory Materialized Views
You can make materialized views eligible for population by specifying INMEMORY
on the CREATE MATERIALIZED VIEW
or ALTER MATERIALIZED VIEW
statements.
For a partitioned materialized view, you can populate all or a subset of the partitions in the IM column store.
See Also:
Oracle Database SQL Language Reference for ALTER MATERIALIZED VIEW
syntax and semantics
5.1.2.1.4 In-Memory Tablespaces
You can make permanent tablespaces eligible for population by specifying INMEMORY
on the CREATE TABLESPACE
or ALTER TABLESPACE
statements.
By default, all tables and materialized views in the tablespace are enabled for the IM column store. Individual tables and materialized views in the tablespace may have different INMEMORY
attributes. The attributes for individual database objects override the attributes for the tablespace.
Note:
Temporary tablespaces are not eligible for In-Memory population.
See Also:
Oracle Database SQL Language Reference for ALTER TABLESPACE
syntax and semantics
5.1.2.2 Priority Options for the Population of In-Memory Objects
When you enable an object for the IM column store, you can either let Oracle Database control when the object is populated (default), or you can specify a level that determines the priority of the object in the population queue.
Oracle SQL includes an INMEMORY PRIORITY
clause that provides more control over the queue for population. For example, it might be more important or less important to populate a database object's data before populating the data for other database objects.
Video:
The following table describes the supported priority levels.
Table 5-1 Priority Levels for Populating a Database Object in the IM Column Store
CREATE/ALTER Syntax | Description |
---|---|
|
The database populates the object on demand only. A full scan of the database object triggers the population of the object into the IM column store. This is the default level when |
|
The database assigns the object a low priority and populates it after startup based on its position in the queue. Population does not depend on whether the object is accessed. The object is populated in the IM column store before database objects with the following priority level: |
|
The database assigns the object a medium priority and populates it after startup based on its position in the queue. Population does not depend on whether the object is accessed. The database object is populated in the IM column store before database objects with the following priority levels: |
|
The database assigns the object a high priority and populates it after startup based on its position in the queue. Population does not depend on whether the object is accessed. The database object's data is populated in the IM column store before database objects with the following priority levels: |
|
The database assigns the object a low priority and populates it after startup based on its position in the queue. Population does not depend on whether the object is accessed. The database object's data is populated in the IM column store before database objects with the following priority levels: |
When more than one database object has a priority level other than NONE
, Oracle Database queues the data for objects to be populated based on priority level. Database objects with the CRITICAL
priority level are populated first; database objects with the HIGH
priority level are populated next, and so on. If no space remains in the IM column store, then no additional objects are populated in it until space becomes available.
Note:
If you specify all objects as CRITICAL
, then the database does not consider any object as more critical than any other.
When a database is restarted, all of the data for database objects with a priority level other than NONE
are populated in the IM column store during startup. For a database object with a priority level other than NONE
, an ALTER
TABLE
or ALTER MATERIALIZED VIEW
DDL statement involving the database object does not return until the DDL changes are recorded in the IM column store.
Note:
-
The priority level setting must apply to an entire table or to a table partition. Specifying different IM column store priority levels for different subsets of columns in a table is not permitted.
-
If a segment on disk is 64 KB or less, then it is not populated in the IM column store. Therefore, some small database objects that were enabled for the IM column store might not be populated.
See Also:
-
Oracle Database SQL Language Reference for
CREATE TABLE ... INMEMORY PRIORITY
syntax and semantics
5.1.2.3 Compression Levels for In-Memory Objects
Depending on your requirement, you can compress In-Memory objects at different levels.
Typically, compression is a space-saving mechanism. However, the IM column store can compress data using algorithms that also improve query performance. If the columnar data uses the MEMCOMPRESS FOR DML
or MEMCOMPRESS FOR QUERY
options, then SQL queries execute directly on the compressed data. Thus, scanning and filtering operations execute on a smaller amount of data. The database only decompresses data when required for the result set.
Video:
The V$IM_SEGMENTS
and V$IM_COLUMN_LEVEL
views indicate the current compression level. You can change compression levels by using the appropriate ALTER
command. If a table is currently populated in the IM column store, and if you change any INMEMORY
attribute of the table other than PRIORITY
, then the database evicts the table from the IM column store. The repopulation behavior depends on the PRIORITY
setting.
If the INMEMORY_FORCE
initialization parameter is set to BASE_LEVEL
, then INMEMORY
objects and columns automatically use QUERY LOW
compression. The data dictionary views may continue to show pre-existing compression settings, but the Base Level always transparently compresses objects and columns at the QUERY LOW
level.
The following table summarizes the valid INMEMORY MEMCOMPRESS
clauses.
Table 5-2 IM Column Store Compression Levels
CREATE/ALTER … INMEMORY Syntax | Description |
---|---|
|
The data is not compressed. |
|
This level results in the best DML performance. This level compresses IM column store data the least, with the exception of Note: This compression level is not supported for |
|
This level results in the best query performance. This level compresses IM column store data more than This level is the default in the following scenarios:
|
|
This level results in good query performance, and saves space. This level compresses IM column store data more than |
|
This level balances space saving and query performance, with a bias toward space saving. This level compresses IM column store data more than This level is the default when |
|
This level results in the best space saving. This level compresses IM column store data the most. |
|
This level only applies when |
See Also:
-
"Enabling the IM Column Store for a CDB or PDB" to learn about the Database In-Memory Base Level
-
Oracle Exadata System Software User's Guide to learn more about
ALTER TABLE ... CELLMEMORY
-
Oracle Database SQL Language Reference for
CREATE TABLE ... INMEMORY PRIORITY
syntax and semantics
5.1.2.4 Oracle Compression Advisor
Oracle Compression Advisor estimates the compression ratio that you can realize using the MEMCOMPRESS
clause. The advisor uses the DBMS_COMPRESSION
interface.
When you run DBMS_COMPRESSION.GET_COMPRESSION_RATIO
for a table, Oracle Database analyzes a sample of the rows. For this reason, Oracle Compression Advisor provides a good estimate of the compression results that a table achieves after it is populated into the IM column store.
See Also:
Oracle Database PL/SQL Packages and Types Reference to learn about DBMS_COMPRESSION.GET_COMPRESSION_RATIO
5.2 Enabling and Disabling Tables for the IM Column Store
Enable a table for the IM column store by including an INMEMORY
clause in a CREATE TABLE
or ALTER TABLE
statement. Disable a table for the IM column store by including a NO INMEMORY
clause in a CREATE TABLE
or ALTER TABLE
statement.
5.2.1 Enabling New Tables for the In-Memory Column Store
You enable a new table for the IM column store by including an INMEMORY
clause in a CREATE TABLE
statement.
You can enable either internal or external tables for the IM column store. Some INMEMORY
subclauses, including the columns and priority subclauses, are not valid for external tables.
Prerequisites
Ensure that the IM column store is enabled for the database. See "Enabling the IM Column Store for a CDB or PDB".
To enable a new table for the IM column store:
-
Log in to the database as a user with the necessary privileges to create the table.
-
Run a
CREATE TABLE
statement with anINMEMORY
clause.
See Also:
-
"Enabling and Disabling Tables for the IM Column Store: Examples"
-
"Enabling a Subset of Columns for the IM Column Store: Example"
-
Oracle Database SQL Language Reference for information about
INMEMORY
clause of theCREATE TABLE
statement
5.2.2 Enabling and Disabling Existing Tables for the IM Column Store
Enable or disable an existing table for the IM column store by including an INMEMORY
or NO INMEMORY
clause in an ALTER TABLE
statement.
Prerequisites
Ensure that the IM column store is enabled for the database. See "Enabling the IM Column Store for a CDB or PDB".
To enable or disable an existing table for the IM column store:
-
Log in to the database as a user with
ALTER TABLE
privileges. -
Run an
ALTER TABLE
statement with anINMEMORY
clause or aNO INMEMORY
clause. -
Optionally, to view metadata (size, priority, compression level) about the In-Memory segment, query
V$IM_SEGMENTS
.
See Also:
-
"Enabling and Disabling Tables for the IM Column Store: Examples"
-
"Enabling a Subset of Columns for the IM Column Store: Example"
-
Oracle Database SQL Language Reference for information about the
ALTER TABLE
statement -
Oracle Database Reference for information about the
V$IM_SEGMENTS
view
5.2.3 Enabling and Disabling Tables for the IM Column Store: Examples
The following examples illustrate how to enable or disable tables for the IM column store.
5.2.3.1 Creating an In-Memory Table: Example
This example creates the test_inmem
table and enables it for the IM column store.
In SQL*Plus, log in to the database as the user who will own the table, and then execute the following SQL statement:
CREATE TABLE test_inmem (
id NUMBER(5) PRIMARY KEY,
test_col VARCHAR2(15))
INMEMORY;
The preceding statement uses the defaults for the INMEMORY
clause: MEMCOMPRESS FOR QUERY
and PRIORITY NONE
. Because PRIORITY
is NONE
, the database will not automatically populate the table.
5.2.3.2 Creating a Table with In-Memory Partitions: Example
This example creates a partitioned table named range_sales
, specifying a subset of the partitions as INMEMORY
.
Log in to SQL*Plus as the user that will own the new table, and then execute the following DDL statement:
CREATE TABLE range_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
(PARTITION SALES_Q4_1999
VALUES LESS THAN (TO_DATE('01-JAN-2015','DD-MON-YYYY'))
INMEMORY MEMCOMPRESS FOR DML,
PARTITION SALES_Q1_2000
VALUES LESS THAN (TO_DATE('01-APR-2015','DD-MON-YYYY'))
INMEMORY MEMCOMPRESS FOR QUERY,
PARTITION SALES_Q2_2000
VALUES LESS THAN (TO_DATE('01-JUL-2015','DD-MON-YYYY'))
INMEMORY MEMCOMPRESS FOR CAPACITY,
PARTITION SALES_Q3_2000
VALUES LESS THAN (TO_DATE('01-OCT-2015','DD-MON-YYYY'))
NO INMEMORY,
PARTITION SALES_Q4_2000
VALUES LESS THAN (MAXVALUE));
The preceding SQL specifies a different compression level for the first three partitions in the IM column store. The last two partitions are not eligible for population in the IM column store.
5.2.3.3 Creating an In-Memory External Table: Example
This example creates an external table with the INMEMORY
option.
This example assumes that the host has the directories /tmp/data/
, /tmp/log/
, and /tmp/bad/
.
The following SQL script creates the comma-delimited flat file /tmp/data/sh_sales.csv
from the sh.sales
table. Execute the script as user sh
.
SET HEAD OFF
SET TRIMSPOOL ON
SET PAGES 0
SET FEEDBACK OFF
SET TERMOUT OFF
SPOOL /tmp/data/sh_sales.csv
SELECT prod_id || ',' || cust_id || ',' || time_id || ',' ||
channel_id || ',' || promo_id || ',' ||
quantity_sold || ',' || amount_sold
FROM sales;
SPOOL OFF
Using the sh_sales.csv
file, the following SQL script creates the external table sh.admin_ext_sales
with the INMEMORY
option:
CONNECT / AS SYSDBA;
-- Set up directories and grant access to sh
CREATE OR REPLACE DIRECTORY admin_dat_dir
AS '/tmp/data';
CREATE OR REPLACE DIRECTORY admin_log_dir
AS '/tmp/log';
CREATE OR REPLACE DIRECTORY admin_bad_dir
AS '/tmp/bad';
GRANT READ ON DIRECTORY admin_dat_dir TO sh;
GRANT WRITE ON DIRECTORY admin_log_dir TO sh;
GRANT WRITE ON DIRECTORY admin_bad_dir TO sh;
-- sh connects. Provide the user password (sh) when prompted.
CONNECT sh
-- create the external table
DROP TABLE admin_ext_sales;
CREATE TABLE admin_ext_sales
( prod_id NUMBER,
cust_id NUMBER,
time_id DATE,
channel_id NUMBER,
promo_id NUMBER,
quantity_sold NUMBER(10,2),
amount_sold NUMBER(10,2)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY admin_dat_dir
ACCESS PARAMETERS
( records delimited by newline
badfile admin_bad_dir:'empxt%a_%p.bad'
logfile admin_log_dir:'empxt%a_%p.log'
fields terminated by ','
missing field values are null
( prod_id, cust_id,
time_id char date_format date mask "dd-mon-yy",
channel_id, promo_id, quantity_sold, amount_sold
)
)
LOCATION ('sh_sales.csv')
)
REJECT LIMIT UNLIMITED
INMEMORY;
The following query of ALL_EXTERNAL_TABLES
shows that the admin_ext_sales
table is enabled for INMEMORY
:
COL OWNER FORMAT A10
COL TABLE_NAME FORMAT A15
SELECT OWNER, TABLE_NAME,
INMEMORY, INMEMORY_COMPRESSION
FROM ALL_EXTERNAL_TABLES
WHERE TABLE_NAME = 'ADMIN_EXT_SALES';
OWNER TABLE_NAME INMEMORY INMEMORY_COMPRESS
---------- --------------- -------- -----------------
SH ADMIN_EXT_SALES ENABLED FOR QUERY LOW
Related views include ALL_XTERNAL_PART_TABLES
, ALL_XTERNAL_TAB_PARTITIONS
, and ALL_XTERNAL_TAB_SUBPARTITIONS
.
See Also:
-
"Populating an In-Memory External Table Using DBMS_INMEMORY.POPULATE: Example"
-
Oracle Database Reference to learn about
ALL_EXTERNAL_TABLES
and the related external table views
5.2.3.4 Creating an In-Memory Partitioned External Table: Example
This example creates an external partitioned table with the INMEMORY
option.
This example assumes that the host has the directories /tmp/data/
, /tmp/log/
, and /tmp/bad/
.
-
Log in to the database as user
sh
. -
Run following SQL script creates the comma-delimited flat files
/tmp/data/sh_sales_98.csv
and/tmp/data/sh_sales_99.csv
from thesh.sales
table.SET ECHO OFF SET HEAD OFF SET TAB OFF SET PAGES 0 SET TRIMSPOOL ON SET FEEDBACK OFF SET TERMOUT OFF SPOOL /tmp/data/sh_sales_98.csv SELECT prod_id || ',' || cust_id || ',' || time_id || ',' || channel_id || ',' || promo_id || ',' || quantity_sold || ',' || amount_sold FROM sales WHERE time_id < TO_DATE('1999-01-01','SYYYY-MM-DD','NLS_CALENDAR=GREGORIAN'); SPOOL OFF SPOOL /tmp/data/sh_sales_99.csv SELECT prod_id || ',' || cust_id || ',' || time_id || ',' || channel_id || ',' || promo_id || ',' || quantity_sold || ',' || amount_sold FROM sales WHERE time_id > TO_DATE('1998-12-31','SYYYY-MM-DD','NLS_CALENDAR=GREGORIAN') AND time_id < TO_DATE('2000-01-01','SYYYY-MM-DD','NLS_CALENDAR=GREGORIAN'); SPOOL OFF
-
Run the following SQL script to create the external table
sh.admin_ext_pt_sales
with theINMEMORY
option:CONNECT / AS SYSDBA; -- Set up directories and grant access to sh CREATE OR REPLACE DIRECTORY admin_dat_dir AS '/tmp/data'; CREATE OR REPLACE DIRECTORY admin_log_dir AS '/tmp/log'; CREATE OR REPLACE DIRECTORY admin_bad_dir AS '/tmp/bad'; GRANT READ ON DIRECTORY admin_dat_dir TO sh; GRANT WRITE ON DIRECTORY admin_log_dir TO sh; GRANT WRITE ON DIRECTORY admin_bad_dir TO sh; -- sh connects. Provide the user password (sh) when prompted. CONNECT sh -- create the external partitioned table DROP TABLE admin_ext_pt sales; CREATE TABLE admin_ext_pt_sales ( prod_id NUMBER, cust_id NUMBER, time_id DATE, channel_id NUMBER, promo_id NUMBER, quantity_sold NUMBER(10,2), amount_sold NUMBER(10,2) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY admin_dat_dir ACCESS PARAMETERS ( records delimited by newline badfile admin_bad_dir:'empxt%a_%p.bad' logfile admin_log_dir:'empxt%a_%p.log' fields terminated by ',' missing field values are null ( prod_id, cust_id, time_id char date_format date mask "dd-mon-yy", channel_id, promo_id, quantity_sold, amount_sold ) ) ) REJECT LIMIT UNLIMITED INMEMORY PARTITION BY RANGE (time_id) ( PARTITION sales_1998 VALUES LESS THAN (TO_DATE('1996-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) LOCATION('sh_sales_98.csv'), PARTITION sales_1999 VALUES LESS THAN (TO_DATE('1997-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) LOCATION('sh_sales_99.csv') );
Note:
To apply the
INMEMORY
attribute to an individual partition rather than at the table level, place it directly after thePARTITION ... LOCATION('part_name')
clause. -
Query
ALL_EXTERNAL_TABLES
to confirm that theadmin_ext_pt_sales
table is enabled forINMEMORY
:COL OWNER FORMAT A10 COL TABLE_NAME FORMAT A18 SELECT OWNER, TABLE_NAME, INMEMORY, INMEMORY_COMPRESSION FROM ALL_EXTERNAL_TABLES WHERE TABLE_NAME = 'ADMIN_EXT_PT_SALES'; OWNER TABLE_NAME INMEMORY INMEMORY_COMPRESS ---------- ------------------ -------- ----------------- SH ADMIN_EXT_PT_SALES ENABLED FOR QUERY LOW
Related views include
ALL_XTERNAL_PART_TABLES
,ALL_XTERNAL_TAB_PARTITIONS
, andALL_XTERNAL_TAB_SUBPARTITIONS
. -
Populate
admin_ext_pt_sales
into the IM column store:EXEC DBMS_INMEMORY.POPULATE('SH', 'ADMIN_EXT_PT_SALES');
-
Query the population status of the
admin_ext_pt_sales
partitions:COL OWNER FORMAT a3 COL NAME FORMAT a18 COL PARTITION FORMAT a13 COL STATUS FORMAT a9 COL BNP FORMAT 99999 SELECT OWNER, SEGMENT_NAME NAME, PARTITION_NAME PARTITION, POPULATE_STATUS STATUS, BYTES_NOT_POPULATED AS "BNP" FROM V$IM_SEGMENTS; OWN NAME PARTITION STATUS BNP --- ------------------ ------------- --------- ------ SH ADMIN_EXT_PT_SALES SALES_1998 COMPLETED 0 SH ADMIN_EXT_PT_SALES SALES_1999 COMPLETED 0
The query shows that only the two external partitions were populated.
See Also:
-
"Populating an In-Memory External Table Using DBMS_INMEMORY.POPULATE: Example"
-
Oracle Database Reference to learn about
ALL_EXTERNAL_TABLES
and the related external table views
5.2.3.5 Creating and Populating a Hybrid External Table: Example
This example creates a hybrid external table with the INMEMORY
option, and then populates both the internal and external partitions.
This example assumes the existence of the sh.sales
table. The goal is to create a hybrid partitioned table sales_hpt
with two internal partitions, one of which uses the data from sh.sales
, and then add one external partition. When you apply the INMEMORY
attribute to sales_hpt
, this attribute applies to all partitions.
-
In Linux, create a temporary directory, and then create a text file with one row of sales data.
rm -rf /tmp/sales_data mkdir /tmp/sales_data echo "1002,110,19-MAR-2016,12,18,150,4800" > /tmp/sales_data/sales2016_data.txt
-
In SQL*Plus, log is with administrator privileges, and then create a directory object for the sales data:
CONNECT / AS SYSDBA CREATE DIRECTORY sales_data AS '/tmp/sales_data'; GRANT READ,WRITE ON DIRECTORY sales_data TO sh;
-
Log in as user
sh
, and then create thesales_hpt
table:CONNECT sh DROP TABLE sales_hpt; CREATE TABLE sales_hpt ( prod_id NUMBER NOT NULL, cust_id NUMBER NOT NULL, time_id DATE NOT NULL, channel_id NUMBER NOT NULL, promo_id NUMBER NOT NULL, quantity_sold NUMBER(10,2) NOT NULL, amount_sold NUMBER(10,2) NOT NULL ) EXTERNAL PARTITION ATTRIBUTES ( TYPE ORACLE_LOADER DEFAULT DIRECTORY sales_data ACCESS PARAMETERS( FIELDS TERMINATED BY ',' (prod_id,cust_id,time_id DATE 'dd-mm-yyyy',channel_id,promo_id,quantity_sold,amount_sold) ) REJECT LIMIT UNLIMITED ) PARTITION BY RANGE (time_id) ( PARTITION sales_2014 VALUES LESS THAN (TO_DATE('01-01-2015','dd-mm-yyyy')), PARTITION sales_2015 VALUES LESS THAN (TO_DATE('01-01-2016','dd-mm-yyyy')), PARTITION sales_2016 VALUES LESS THAN (TO_DATE('01-01-2017','dd-mm-yyyy')) EXTERNAL LOCATION ('sales2016_data.txt') );
The preceding statement shows that the table has three partitions:
sales_2014
,sales_2015
, andsales_2016
. Onlysales_2016
is designated as external. -
Query the data dictionary to confirm that the table is a hybrid (sample output included):
COL TABLE_NAME FORMAT a25 SELECT TABLE_NAME, HYBRID FROM USER_TABLES WHERE HYBRID = 'YES'; TABLE_NAME HYB ------------------------- --- SALES_HPT YES
-
Insert rows into the internal partitions
sales_2014
andsales_2015
:INSERT INTO sh.sales_hpt (SELECT * FROM sales); INSERT INTO sh.sales_hpt VALUES (30, 21086, TO_DATE('2015-12-30','SYYYY-MM-DD'), 2, 999, 1, 10.19); COMMIT;
The first of the preceding statements inserts all rows from the
sales
table. All dates insales
are before 2002, so all rows fromsales
are inserted into thesales_2014
partition. The second statement inserts a single row into thesales_2015
partition. -
Query the partitions to confirm that the correct data exists:
SQL> SELECT COUNT(*) FROM sales_hpt PARTITION(sales_2014); COUNT(*) ---------- 918843 SQL> SELECT COUNT(*) FROM sales_hpt PARTITION(sales_2015); COUNT(*) ---------- 1 SQL> SELECT COUNT(*) FROM sales_hpt PARTITION(sales_2016); COUNT(*) ---------- 1
-
Apply the
INMEMORY
attribute at the table level, and then force the database to populate the table into the IM column store:ALTER TABLE sales_hpt INMEMORY; EXEC DBMS_INMEMORY.POPULATE('SH', 'SALES_HPT');
-
Query the population status of the
sales_hpt
partitions:COL OWNER FORMAT a3 COL SEGMENT FORMAT a18 COL PARTITION FORMAT a13 COL STATUS FORMAT a9 COL BNP FORMAT 99999 SELECT OWNER, SEGMENT_NAME SEGMENT, PARTITION_NAME PARTITION, IS_EXTERNAL AS EXT, POPULATE_STATUS STATUS, BYTES_NOT_POPULATED AS "BNP" FROM V$IM_SEGMENTS WHERE SEGMENT_NAME = 'SALES_HPT' ORDER BY PARTITION; OWN SEGMENT PARTITION EXT STATUS BNP --- ------------------ ------------- ----- --------- ------ SH SALES_HPT SALES_2014 FALSE COMPLETED 0 SH SALES_HPT SALES_2015 FALSE COMPLETED 0 SH SALES_HPT SALES_2016 TRUE COMPLETED 0
The query shows that all partitions, both internal and external, were populated.
5.2.3.6 Enabling an Existing Table for the IM Column Store: Example
This example enables the existing sh.sales
table for the IM column store.
In SQL*Plus, log in to the database as the sh
user, and then execute the following DDL statement:
ALTER TABLE sales INMEMORY;
The preceding statement uses the defaults for the INMEMORY
clause: MEMCOMPRESS FOR QUERY
and PRIORITY NONE
.
5.2.3.7 Setting In-Memory Compression to FOR CAPACITY LOW: Example
This example enables the existing oe.product_information
table for the IM column store and specifies the compression method FOR CAPACITY LOW
.
In SQL*Plus, log in to the database as the oe
user, and then execute the following DDL statement:
ALTER TABLE product_information
INMEMORY
MEMCOMPRESS FOR CAPACITY LOW;
The preceding statement uses the default for the PRIORITY
clause of NONE
. Populate the table by forcing a full table scan as follows (sample output included):
SELECT /*+ FULL(p) NO_PARALLEL(p) */ COUNT(*)
FROM product_information p;
COUNT(*)
----------
288
In a separate session, log in as a user with administrative privileges, and then calculate the compression ratio by executing the following query (sample output included):
COL OWNER FORMAT a5
COL SEGMENT_NAME FORMAT a19
SET PAGESIZE 50000
SELECT OWNER, SEGMENT_NAME, BYTES ORIG_SIZE,
INMEMORY_SIZE IN_MEM_SIZE,
ROUND (BYTES / INMEMORY_SIZE, 2) COMP_RATIO
FROM V$IM_SEGMENTS
WHERE SEGMENT_NAME LIKE 'P%'
ORDER BY 4;
OWNER SEGMENT_NAME ORIG_SIZE IN_MEM_SIZE COMP_RATIO
----- ------------------- ---------- ----------- ----------
OE PRODUCT_INFORMATION 98304 1310720 .08
5.2.3.8 Setting In-Memory Priority to HIGH: Example
This example enables the oe.product_information
table for the IM column store and specifies PRIORITY HIGH
for populating the table data in the IM column store.
In SQL*Plus, log in to the database as the oe
user, and then execute the following DDL statement:
ALTER TABLE
product_information
INMEMORY
PRIORITY HIGH;
5.2.3.9 Changing the Compression and Priority Settings for an In-Memory Table: Example
This example alters the oe.product_information
table to use FOR CAPACITY HIGH
table compression and a LOW
priority setting.
In SQL*Plus, log in to the database as an administrative user, and then execute the following query to show the current priority and compression setting for the oe.product_information
table:
COL OWNER FORMAT a5
COL SEGMENT_NAME FORMAT a19
SET PAGESIZE 50000
SELECT v.OWNER, v.SEGMENT_NAME, v.INMEMORY_PRIORITY,
v.INMEMORY_COMPRESSION
FROM V$IM_SEGMENTS v
WHERE SEGMENT_NAME LIKE 'P%';
OWNER SEGMENT_NAME INMEMORY INMEMORY_COMPRESS
----- ------------------- -------- -----------------
OE PRODUCT_INFORMATION HIGH FOR CAPACITY LOW
The following DDL statement alters oe.product_information
to use FOR CAPACITY HIGH
table compression and PRIORITY LOW
:
ALTER TABLE oe.product_information
INMEMORY
MEMCOMPRESS FOR CAPACITY HIGH
PRIORITY LOW;
5.2.3.10 Disabling a Table for the IM Column Store: Example
To disable a table for the IM column store, specify the NO INMEMORY
clause.
Log in to the database as the user oe
, and then execute the following statement to disable the product_information
table for the IM column store:
ALTER TABLE oe.product_information NO INMEMORY;
The V$IM_SEGMENTS
view lists the database objects that are populated in the IM column store.
5.2.3.11 Disabling Columnar Format on Exadata Smart Flash Cache: Example
This example disables the columnar format for oe.product_information
on Exadata Smart Flash Cache storage.
By default, Exadata Smart Flash Cache compresses data using the level MEMCOMPRESS FOR CAPACITY LOW
. To change the compression level or disable the columnar format altogether, use the ALTER TABLE ... NO CELLMEMORY
statement.
Log in to the database as user oe
, and execute the following DDL statement:
ALTER TABLE product_information NO CELLMEMORY;
5.3 Enabling and Disabling Columns for In-Memory Tables
You can specify the INMEMORY
clause for individual columns in an internal table. External tables do not support specifying INMEMORY
at the column level.
5.3.1 About In-Memory Columns
For internal tables, both In-Memory virtual columns (IM virtual columns) and nonvirtual columns are eligible for IM population. For external tables, only nonvirtual columns are eligible.
5.3.1.1 Selective Columns
By default, all columns in an INMEMORY
table are enabled for the IM column store and therefore eligible for population. To save memory, you may decide to make a subset of columns ineligible for the IM column store.
Note:
If the INMEMORY_FORCE
initialization parameter is set to BASE_LEVEL
, then INMEMORY
objects and columns automatically use QUERY LOW
compression. The data dictionary views may continue to show pre-existing compression settings, but the Base Level always transparently compresses objects and columns at the QUERY LOW
level.
5.3.1.1.1 The NO INMEMORY Attribute
If some columns in an INMEMORY
table are specified NO INMEMORY
, then only the INMEMORY
columns are eligible for population.
To apply the NO INMEMORY
attribute to a subset of columns, specify ALTER TABLE table_name INMEMORY ... NO INMEMORY excluded_columns
, where excluded_columns lists the NO INMEMORY
columns. Only the columns that do not have the NO INMEMORY
attribute, that is, columns that are not in the excluded columns list, inherit the segment-level INMEMORY
attribute.
The following DDL statement enables all columns in employees
for In-Memory, except for the salary
column:
ALTER TABLE hr.employees INMEMORY NO INMEMORY (salary);
The following query of V$IM_COLUMN_LEVEL
shows that salary is NO INMEMORY
:
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 = 'EMPLOYEES'
ORDER BY COLUMN_NAME;
TABLE_NAME COLUMN_NAME INMEMORY_COMPRESSION
-------------------- -------------------- --------------------------
EMPLOYEES COMMISSION_PCT DEFAULT
EMPLOYEES DEPARTMENT_ID DEFAULT
EMPLOYEES EMAIL DEFAULT
EMPLOYEES EMPLOYEE_ID DEFAULT
EMPLOYEES FIRST_NAME DEFAULT
EMPLOYEES HIRE_DATE DEFAULT
EMPLOYEES JOB_ID DEFAULT
EMPLOYEES LAST_NAME DEFAULT
EMPLOYEES MANAGER_ID DEFAULT
EMPLOYEES PHONE_NUMBER DEFAULT
EMPLOYEES SALARY NO INMEMORY
See Also:
-
Oracle Database SQL Language Reference for the syntax and semantics of the
INMEMORY
clause -
https://blogs.oracle.com/in-memory/what-happens-if-a-column-is-not-populated for a blog entry on accessing columns that are not populated
5.3.1.1.2 In-Memory Hybrid Scans
An In-Memory hybrid scan accesses a table in the IM column store when not all columns are populated.
Before Oracle Database 21c, if a query referenced any column with the
NO INMEMORY
setting, then the query accessed all data from
the row store. Therefore, the table scan could not take advantage of columnar formats,
predicate pushdown, and other In-Memory features. Starting in Oracle Database 21c,
queries that reference both INMEMORY
and NO INMEMORY
columns can access columnar data.
In some cases, an In-Memory hybrid scan can improve performance by orders of magnitude. The greatest performance benefits occur when a query has selective filters. In this case, the IM column store can quickly filter out most rows so that the row store projects only a small number of rows.
To achieve optimal performance, the optimizer compares different access methods. If the optimizer chooses a table scan, then the storage engine automatically determines whether an In-Memory hybrid scan performs better than a regular row store scan. The optimizer considers hybrid scans when the following conditions are met:
-
The predicate contains only
INMEMORY
columns. -
The
SELECT
list contains an arbitrary combination ofINMEMORY
andNO INMEMORY
columns.
For example, assume that the salary
and commission_pct
columns in the employees
table are specified as INMEMORY
. The first_name
column is NO INMEMORY
because it is rarely referenced. The following queries are eligible for hybrid IM scans because they reference both NO INMEMORY
and INMEMORY
columns:
SELECT first_name FROM employees WHERE salary=6000 ORDER BY first_name;
SELECT first_name, salary AS base_sal, ((salary*commission_pct)+salary) AS total_sal
FROM employees
WHERE commission_pct=.1
ORDER BY total_sal DESC;
An In-Memory hybrid scan logically divides the work into two: one part processes the query on the IM column store, and the other part processes the query on the row store. In the execution plan, the operation named TABLE ACCESS INMEMORY FULL (HYBRID)
indicates a hybrid scan. Note that if runtime statistics indicate that performance will be faster by accessing the row store only, then the database can disable the In-Memory hybrid scan at runtime.
Example 5-2 In-Memory Hybrid Scan
In this example, you enable all sales
columns for In-Memory access except for amount_sold
:
SH@21c:21c> ALTER TABLE sales INMEMORY NO INMEMORY (amount_sold);
Table altered.
You populate the table into the IM column store:
SH@21c:21c> SELECT /*+ FULL(sales) NO_PARALLEL(sales) */ COUNT(*) FROM sales;
COUNT(*)
----------
918843
You apply the SUM
function to amount_sold
, which is a NO INMEMORY
column, and then reference only INMEMORY
columns in the predicate:
SELECT SUM(amount_sold) AS revenue
FROM sales
WHERE time_id >= TO_DATE('1994-01-01', 'YYYY-MM-DD')
AND prod_id BETWEEN 30 and 40
AND quantity_sold < 2;
REVENUE
----------
7695555.89
Step 3 of the following execution plan shows that the optimizer chose an In-Memory hybrid scan:
SH@21c> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
SQL_ID 6nz5k0y07akx8, child number 0
-------------------------------------
SELECT SUM(amount_sold) as revenue FROM sales WHERE time_id >=
TO_DATE('1994-01-01', 'YYYY-MM-DD') AND prod_id BETWEEN 30 and 40
AND quantity_sold < 2
Plan hash value: 3519235612
-------------------------------------------------------------------------------------------
|Id | Operation |Name|Rows|Bytes|Cost (%CPU)|Time|Pstart|Pstop|
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |463 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 20 | | | | |
| 2 | PARTITION RANGE ALL | |196K|3834K|463 (2)|00:00:01| 1 | 28 |
|*3 | TABLE ACCESS INMEMORY FULL (HYBRID)| SALES|196K|3834K|463 (2)|00:00:01| 1 | 28 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("PROD_ID"<=40 AND "PROD_ID">=30 AND
"TIME_ID">=TO_DATE(' 1994-01-01 00:00:00','syyyy-mm-dd hh24:mi:ss')
AND "QUANTITY_SOLD"<2))
5.3.1.2 IM Virtual Columns
An IM virtual column is like any other column, except that its value is derived by evaluating an expression.
Purpose of IM Virtual Columns
Storing the precalculated IM virtual column values in the IM column store can improve query performance. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions (DETERMINISTIC
only). You cannot explicitly write to an IM virtual column.
Virtual columns have uses in a variety of contexts. For example, you can add virtual columns to an INMEMORY
spatial table, and then use operators such as SDO_FILTER
to query that table without using a spatial index.
User Interface for IM Virtual Columns
To populate IM virtual columns in the IM column store, set the INMEMORY_VIRTUAL_COLUMNS
initialization parameter to one of the following values:
-
MANUAL
(default): If a table is enabled for the IM column store, then no IM virtual columns defined on this table are eligible for population, unless they are explicitly set asINMEMORY
. -
ENABLE
: If a table is enabled for the IM column store, then all IM virtual columns defined on this table are eligible for population, unless they are explicitly set asNO INMEMORY
.By default, the compression level of the column in the IM column store is the same as the table or partition in which it is stored. However, when a different compression level is specified for the IM virtual column, it is populated at the specified compression level.
Note:
A virtual column or IM expression counts toward the limit of 1000 columns per populated object.
To specify that no IM virtual columns are populated in the IM column store, set this initialization parameter to DISABLE
.
The underlying storage structures for IM virtual columns and IM expressions are the same. However, different mechanisms control IM expressions and IM virtual columns.
Note:
-
The IM column store only populates virtual columns for tables marked
INMEMORY
. -
To populate IM virtual columns in the IM column store, the value for the initialization parameter
COMPATIBLE
must be set to 12.1.0 or higher.
5.3.1.3 IM Full Text Columns
You specify an In-Memory full text column with the INMEMORY TEXT
clause.
Purpose of IM Full Text Columns
In previous releases, the IM column store did not support predicates for non-scalar
document objects such as text, XML, and JSON. These types have their own
domain-specific predicate and projection query constructs, such as
CONTAINS()
for CLOB
columns. Fast predicate
evaluations required domain indexes such as Oracle full text index, XML Search
Index, or JSON Search Index. Starting in Oracle Database 21c, optimized In-Memory
searching occurs when a CONTAINS()
or
JSON_TEXTCONTAINS()
operator appears in a predicate.
An IM table scan can evaluate both scalar and non-scalar data. When the IM column store contains both scalar and non-scalar columns, OLAP applications that access both types of data can avoid accessing the row store, thereby improving performance.
How Full Text Columns Work
Every domain-specific data object is stored in the IM column store in its domain-specific format. The IM full text feature supports the following data types:
-
CHAR
-
VARCHAR2
-
CLOB
-
BLOB
-
JSON
In previous releases, queries using CONTAINS()
and JSON_TEXTCONTAINS()
were only evaluated with a text index and JSON search index. Starting in Oracle Database 20, when the underlying columns that store the documents are specified as INMEMORY TEXT
, queries evaluate these operators in SQL predicates. Domain-specific indexes are optional.
Both JSON and non-JSON columns support a custom indexing policy created with the CTX_DDL.CREATE_POLICY
procedure, which requires the CTXAPP
role or execute privileges on the CTXSYS.CTX_DDL
package. If the column data type is JSON
, then the IM full text version of this column enables path-aware search using JSON_TEXTCONTAINS()
when the column uses either of the following:
-
A default policy
-
A custom policy with a
PATH_SECTION_GROUP
havingJSON_ENABLED
attribute set toTRUE
User Interface for Full Text Columns
Both CREATE TABLE
and ALTER TABLE
support the INMEMORY TEXT
clause. The PRIORITY
clause has the same effect on population of IM full text columns as standard In-Memory columns. The default priority is NONE
. The MEMCOMPRESS
clause is not valid with INMEMORY TEXT
.
Table 5-3 INMEMORY TEXT Clause
Syntax | Description |
---|---|
|
Specifies the list of columns to be enabled as IM full text. The columns must be of type |
|
Specifies the list of columns to be enabled as IM full text along with custom indexing policies. The columns must be of type |
Table 5-4 Initialization Parameters Relating to IM Full Text
Syntax | Description |
---|---|
|
Controls the maximum size of |
|
Controls which user-defined virtual columns are stored as IM virtual columns. IM full text columns require that you set to |
See Also:
-
Oracle Text Application Developer's Guide and Oracle Database JSON Developer’s Guide to learn more about using full text search with Oracle Text and JSON
-
Oracle Text Reference to learn about
CTX_DDL.CREATE_POLICY
-
Oracle Database SQL Language Reference for the syntax and semantics of the
INMEMORY
clause
5.3.2 Enabling IM Virtual Columns
IM virtual columns improve query performance by avoiding repeated calculations. Also, the database can scan and filter IM virtual columns using techniques such as SIMD vector processing.
Prerequisites
To enable IM virtual columns, the following conditions must be true:
-
The IM column store is enabled for the database.
-
The table that contains the virtual columns is internal and has the
INMEMORY
attribute.See "Enabling and Disabling Tables for the IM Column Store".
-
The
INMEMORY_VIRTUAL_COLUMNS
initialization parameter is not set toDISABLE
. -
The value for the initialization parameter
COMPATIBLE
is set to12.1.0
or higher.
To enable IM virtual columns:
-
In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.
-
Either set the
INMEMORY_VIRTUAL_COLUMNS
initialization parameter toENABLE
, or enable specific virtual columns for the IM column store.
Example 5-3 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=BOTH;
System altered.
Note that you could also use SCOPE=SPFILE
, but in that case the change
will not take effect until the next database restart. When SCOPE=BOTH
is
used, the alteration takes place immediately. A restart is not required.
Example 5-4 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);
Example 5-5 Adding Virtual Columns to an In-Memory Spatial Table
In this example, create a table that contains location data, but initially does not include
a spatial geometry column. Then, add a spatial geometry column. Update the table to populate
geometry objects based on the existing latitude and longitude coordinates. Also update the
spatial metadata. Finally, convert the table to INMEMORY, specifying the spatial column as
INMEMORY SPATIAL
to create the inmemory spatial index.
-
Create the sample table
city_points
and insert a set of coordinates (latitude
andlongitude
) for a location in or near each city.CREATE TABLE city_points ( city_id NUMBER PRIMARY KEY, city_name VARCHAR2(25), latitude NUMBER, longitude NUMBER); INSERT INTO city_points (city_id, city_name, latitude, longitude) VALUES (1, 'Boston', 42.207905, -71.015625); INSERT INTO city_points (city_id, city_name, latitude, longitude) VALUES (2, 'Raleigh', 35.634679, -78.618164); INSERT INTO city_points (city_id, city_name, latitude, longitude) VALUES (3, 'San Francisco', 37.661791, -122.453613); INSERT INTO city_points (city_id, city_name, latitude, longitude) VALUES (4, 'Memphis', 35.097140, -90.065918);
- Add a spatial geometry column to the
city_points
table.ALTER TABLE city_points ADD (shape SDO_GEOMETRY);
- Populate the new column with geometry objects based on the location coordinates
that you
inserted.
UPDATE city_points SET shape = SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(LONGITUDE, LATITUDE, NULL), NULL, NULL );
- Update the spatial metadata in the
user_sdo_geom_metadata
view.INSERT INTO user_sdo_geom_metadata VALUES ( 'city_points', 'SHAPE', SDO_DIM_ARRAY( SDO_DIM_ELEMENT('Longitude',-180,180,0.5), SDO_DIM_ELEMENT('Latitude',-90,90,0.5) ), 8307 ); commit;
- Alter the
city_points
table to make it a candidate for population in the IM column store. Specify the table as INMEMORY. Also include theINMEMORY SPATIAL
keywords since this table includes theshape
spatial geometry column.ALTER TABLE city_points INMEMORY PRIORITY high INMEMORY SPATIAL (shape);
You can then use
DBMS_INMEMORY.POPULATE
to populate thecity_points
table in the IM column store.EXEC DBMS_INMEMORY.POPULATE('chicago','city_points');
Note:
Virtual columns are created as part of the spatial geometry column. This is not related to the In-Memory feature. With the use of theINMEMORY SPATIAL
keywords, one or more IME columns are created.
See Also:
The Spatial and Graph Developer's Guide, where this same example is used in a broader discussion of spatial concepts.Example 5-6 Using Spatial Digest (MIN/MAX Value IME in IM Column Store)
SELECT city_name
FROM city_points c
where
sdo_filter(c.shape,
sdo_geometry(2001,8307,sdo_point_type(-122.453613,37.661791,null),null,null)
) = 'TRUE';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
Example 5-7 Using DBIM With no Spatial Enhancements
SELECT * FROM city_points c where c.shape.sdo_point.x =
-122.453613; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
Example 5-8 Verifying Virtual Column Creation
col table_name format a20
col column_name format a30
col data_type format a20
col data_default format a30 word_wrapped;
select
table_name, column_name, data_type, DATA_LENGTH, DATA_DEFAULT
from user_tab_cols
where column_name like 'SYS%';
SELECT * FROM city_points c;
city_points
sample
table.delete from user_sdo_geom_metadata where table_name = 'CITY_POINTS'; drop table city_points purge;
5.3.3 Enabling IM Full Text Columns
To enable IM full text columns, specify the INMEMORY TEXT
clause on the CREATE TABLE
and ALTER TABLE
statement.
Prerequisites
To enable IM full text columns, the following conditions must be true:
-
The IM column store must be enabled for the database.
-
IM virtual columns must be enabled.
See "Enabling IM Virtual Columns".
-
The initialization parameter
MAX_STRING_SIZE
must be set toEXTENDED
. -
If you specify a custom indexing policy, then the policy must exist.
You can create a policy with
CTX_DDL.CREATE_POLICY
. This procedure requires theCTXAPP
role or beEXECUTE
privileges on theCTXSYS.CTX_DDL
package.
To enable IM full text columns:
-
In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.
-
Specify either the
CREATE TABLE
orALTER TABLE
statement with theINMEMORY TEXT
clause, using either of the following forms:-
INMEMORY TEXT (col1, col2, …)
-
INMEMORY TEXT (col1 USING policy1, col2 USING policy2, …)
-
Example 5-9 Enabling IM Full Text Columns
In this example, you log in with the biblio
user account, which has the CTXAPP
role. You create a table with two IM full text search columns: text_doc
and json_doc
:
CREATE TABLE books (id NUMBER, createTime DATE, text_doc CLOB, json_doc JSON)
INMEMORY TEXT(text_doc, json_doc);
You create a custom policy for text search, and then apply it to the text_doc
column as follows:
EXEC CTX_DDL.CREATE_POLICY('book_search_policy');
ALTER TABLE books INMEMORY TEXT (text_doc USING 'book_search_policy');
Note that the books.json_doc
column, which uses the JSON
data type, uses a default policy.
Example 5-10 Replacing a Custom Policy on an INMEMORY TEXT Column
In this example, the table books
is enabled with two IM full text search columns: text_doc
and json_doc
. The text_doc
column uses the custom policy book_search_policy
. Your goal is replace the existing policy with a policy named book_search_policy2
. You must apply the NO INMEMORY
attribute and then apply INMEMORY
as follows:
ALTER TABLE books NO INMEMORY TEXT(text_doc);
ALTER TABLE books INMEMORY TEXT (text_doc USING 'book_search_policy2');
See Also:
-
Oracle Text Reference to learn about
CTX_DDL.CREATE_POLICY
-
Oracle Database SQL Language Reference for the syntax and semantics of the
INMEMORY
clause
5.3.4 Enabling a Subset of Columns for the IM Column Store: Example
This example enables all columns in the oe.product_information
table for the IM column store except weight_class
and catalog_url
.
The following statement also specifies different IM column store compression methods for the columns enabled for the IM column store:
ALTER TABLE oe.product_information
INMEMORY MEMCOMPRESS FOR QUERY (
product_id, product_name, category_id, supplier_id, min_price)
INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (
product_description, warranty_period, product_status, list_price)
NO INMEMORY (
weight_class, catalog_url);
Note the following:
-
The columns
product_id
,product_name
,category_id
,supplier_id
, andmin_price
are enabled for the IM column store with theMEMCOMPRESS FOR QUERY
compression method. -
The columns
product_description
,warranty_period
,product_status
, andlist_price
are enabled for the IM column store with theMEMCOMPRESS FOR CAPACITY HIGH
compression method. -
The
weight_class
andcatalog_url
columns are not enabled for the IM column store. Consequently, any query that references these two columns, either in theSELECT
list or in the predicate, must use the row store rather than the IM column store. -
The table uses the default for the
PRIORITY
clause, which isPRIORITY NONE
.
Note:
The priority level setting must apply to an entire table or partition. Specifying different IM column store priority levels for different subsets of columns in a table is not allowed.
To determine the selective column compression levels defined for a database object, query the V$IM_COLUMN_LEVEL
view, as shown in the following example:
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 = 'PRODUCT_INFORMATION'
ORDER BY COLUMN_NAME;
TABLE_NAME COLUMN_NAME INMEMORY_COMPRESSION
-------------------- -------------------- --------------------------
PRODUCT_INFORMATION CATALOG_URL NO INMEMORY
PRODUCT_INFORMATION CATEGORY_ID FOR QUERY LOW
PRODUCT_INFORMATION LIST_PRICE FOR CAPACITY HIGH
PRODUCT_INFORMATION MIN_PRICE FOR QUERY LOW
PRODUCT_INFORMATION PRODUCT_DESCRIPTION FOR CAPACITY HIGH
PRODUCT_INFORMATION PRODUCT_ID FOR QUERY LOW
PRODUCT_INFORMATION PRODUCT_NAME FOR QUERY LOW
PRODUCT_INFORMATION PRODUCT_STATUS FOR CAPACITY HIGH
PRODUCT_INFORMATION SUPPLIER_ID FOR QUERY LOW
PRODUCT_INFORMATION WARRANTY_PERIOD FOR CAPACITY HIGH
PRODUCT_INFORMATION WEIGHT_CLASS NO INMEMORY
See Also:
-
Oracle Database Reference for more information about the
V$IM_COLUMN_LEVEL
view
5.3.5 Specifying INMEMORY Column Attributes on a NO INMEMORY Table: Example
Starting in Oracle Database 12c Release 2 (12.2), you can specify the INMEMORY
clause at the column level on an object that is not yet specified as INMEMORY
.
In previous releases, the column-level INMEMORY
clause was only valid when specified on an INMEMORY
table or partition. This restriction meant that a column could not be associated with an INMEMORY
clause before the table or partition was associated with an INMEMORY
clause.
Starting in Oracle Database 12c Release 2 (12.2), if you specify the INMEMORY
clause at the column level, then the database records the attributes of the specified column. If the table is NO INMEMORY
(default), then the column-level attributes do not affect how the table is queried until the table or partition is specified as INMEMORY
. If you mark the table itself as NO INMEMORY
, then the database drops any existing column-level attributes.
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:
-
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
isNO INMEMORY
. The table is partitioned by list on columnc1
, and has three partitions:p1
,p2
, andp3
. -
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. -
To ensure that column
c3
is never populated, apply theNO INMEMORY
attribute to columnc3
:ALTER TABLE t NO INMEMORY (c3);
-
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 forc3
. The other columns use the default compression. -
Specify partition
p3
asINMEMORY
:ALTER TABLE t MODIFY PARTITION p3 INMEMORY PRIORITY CRITICAL;
Because column
c3
was previously specified asNO INMEMORY
, initial population of partitionp3
will not include columnc3
. -
Specify the entire table as
INMEMORY
:ALTER TABLE t INMEMORY;
-
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 columnc3
. The other columns use the default compression. - Apply different compression levels to columns
c1
andc2
:ALTER TABLE t INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (c1) INMEMORY MEMCOMPRESS FOR CAPACITY LOW (c2);
-
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.
-
Specify the entire table as
NO INMEMORY
:ALTER TABLE t NO INMEMORY;
-
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-levelINMEMORY
attributes.
See Also:
Oracle Database SQL Language Reference for ALTER TABLE
syntax and semantics
5.4 Enabling and Disabling Tablespaces for the IM Column Store
You can enable or disable tablespaces for the IM column store.
Enable a tablespace for the IM column store during tablespace creation with a CREATE TABLESPACE
statement that includes the INMEMORY
clause. You can also alter a tablespace to enable it for the IM column store with an ALTER TABLESPACE
statement that includes the INMEMORY
clause.
Disable a tablespace for the IM column store by including a NO INMEMORY
clause in a CREATE TABLESPACE
or ALTER TABLESPACE
statement.
When a tablespace is enabled for the IM column store, all tables and materialized views in the tablespace are enabled for the IM column store by default. The INMEMORY
clause is the same for tables, materialized views, and tablespaces. The DEFAULT
storage clause is required before the INMEMORY
clause when enabling a tablespace for the IM column store and before the NO INMEMORY
clause when disabling a tablespace for the IM column store.
When a tablespace is enabled for the IM column store, individual tables and materialized views in the tablespace can have different in-memory settings, and the settings for individual database objects override the settings for the tablespace. For example, if the tablespace is set to PRIORITY LOW
for populating data in memory, and if a table in the tablespace is set to PRIORITY HIGH
, then the table uses PRIORITY HIGH
.
Prerequisites
Ensure that the IM column store is enabled for the database. See "Enabling the IM Column Store for a CDB or PDB".
To enable or disable tablespaces for the IM column store:
-
In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.
-
Run a
CREATE TABLESPACE
orALTER TABLESPACE
statement with anINMEMORY
clause or aNO INMEMORY
clause.
Example 5-11 Creating a Tablespace and Enabling It for the IM Column Store
The following example creates the users01
tablespace and enables it for the IM column store:
CREATE TABLESPACE users01
DATAFILE 'users01.dbf' SIZE 40M
ONLINE
DEFAULT INMEMORY;
This example uses the defaults for the INMEMORY
clause. Therefore, MEMCOMPRESS FOR QUERY
is used, and PRIORITY NONE
is used.
Example 5-12 Altering a Tablespace to Enable It for the IM Column Store
The following example alters the users01
tablespace to enable it for the IM column store and specifies FOR CAPACITY HIGH
compression for the database objects in the tablespace and PRIORITY LOW
for populating data in memory:
ALTER TABLESPACE users01 DEFAULT INMEMORY
MEMCOMPRESS FOR CAPACITY HIGH
PRIORITY LOW;
5.5 Enabling and Disabling Materialized Views for the IM Column Store
You can enable and disable materialized views for the IM column store.
Enable a materialized view for the IM column store by including an INMEMORY
clause in a CREATE MATERIALIZED VIEW
or ALTER MATERIALIZED VIEW
statement. Disable a materialized view for the IM column store by including a NO INMEMORY
clause in a CREATE MATERIALIZED VIEW
or ALTER MATERIALIZED VIEW
statement.
Prerequisites
Ensure that the IM column store is enabled for the database. See "Enabling the IM Column Store for a CDB or PDB".
To enable or disable a materialized view for the IM column store:
-
In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.
-
Run a
CREATE MATERIALIZED VIEW
orALTER MATERIALIZED VIEW
statement with either anINMEMORY
clause or aNO INMEMORY
clause.
Example 5-13 Creating a Materialized View and Enabling It for the IM Column Store
The following statement creates the oe.prod_info_mv
materialized view and enables it for the IM column store:
CREATE MATERIALIZED VIEW oe.prod_info_mv INMEMORY
AS SELECT * FROM oe.product_information;
This example uses the defaults for the INMEMORY
clause: MEMCOMPRESS FOR QUERY LOW
and PRIORITY
NONE
.
Example 5-14 Enabling a Materialized View for the IM Column Store with HIGH Data Population Priority
The following statement enables the oe.prod_info_mv
materialized view for the IM column store:
ALTER MATERIALIZED VIEW oe.prod_info_mv INMEMORY PRIORITY HIGH;
This example uses the default compression: MEMCOMPRESS FOR QUERY LOW
.
See Also:
Oracle Database SQL Language Reference to learn more about the CREATE
or ALTER MATERIALIZED VIEW
statements