4 Enabling Objects for In-Memory Population
This chapter explains how to enable and disable objects for population in the IM column store, including setting compression and priority options.
This chapter contains the following topics:
4.1 About In-Memory Population
In-Memory population (population) 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.
Only objects with the INMEMORY
attribute are eligible for population.
This section contains the following topics:
4.1.1 Purpose of In-Memory Population
The IM column store does not automatically load all objects in the database into the IM column store.
If you do not use DDL to specify any objects as INMEMORY
, then the IM column store remains empty. Population is necessary to transform rows from user-specified INMEMORY
objects into columnar format, so that they are available for analytic queries.
Population, which transforms existing data on disk into columnar format, is different from repopulation, which loads new data into the IM column store. 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.
4.1.2 How In-Memory Population Works
You can specify that the database populates objects in the IM column store either at database instance startup or when INMEMORY
objects are accessed.
The population algorithm also varies depending on whether you use single-instance or Oracle RAC.
This section contains the following topics:
4.1.2.1 Prioritization of In-Memory Population
DDL statements include an INMEMORY PRIORITY
subclause that provides more control over the population queue.
Note:
The INMEMORY PRIORITY
subclause controls the priority of population, but not the speed of population.
The priority level setting applies to an entire table, partition, or subpartition, not to different column subsets. Setting the INMEMORY
attribute on an object means that this object is a candidate for population in the IM column store. It does not mean that the database immediately populates the object.
Note:
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.
Oracle Database manages prioritization as follows:
-
On-demand population
By default, the
INMEMORY PRIORITY
parameter is set toNONE
. In this case, the database only populates the object when it is accessed through a full table scan. If the object is never accessed, or if it is accessed only through an index scan or fetch by rowid, then population never occurs. -
Priority-based population
When
PRIORITY
is set to a value other thanNONE
, Oracle database automatically populates the objects using an internally managed priority queue. In this case, a full scan is not a necessary condition for population. The database does the following:-
Populates columnar data in the IM column store automatically after the database instance restarts
-
Queues population of
INMEMORY
objects based on the specified priority levelFor example, a table altered with
INMEMORY PRIORITY CRITICAL
takes precedence over a table altered withINMEMORY PRIORITY HIGH
, which in turn takes precedence over a table altered withINMEMORY PRIORITY LOW
. If the IM column store has insufficient space, then Oracle Database does not populate additional objects until space is available. -
Waits to return from
ALTER TABLE
orALTER MATERIALIZED VIEW
statements until the changes to the object are recorded in the IM column store
-
After a segment is populated in the IM column store, the database only evicts it when the segment is dropped or moved, or the segment is updated with the NO INMEMORY
attribute. You can evict a segment manually or by means of an ADO policy.
Example 4-1 Population of an Object in the IM Column Store
Before completing this example, the IM column store must be enabled for the database.
-
Log in to the database as an administrator, and then query the
customers
table as follows:SELECT cust_id, cust_last_name, cust_first_name FROM sh.customers WHERE cust_city = 'Hyderabad' AND cust_income_level LIKE 'C%' AND cust_year_of_birth > 1960;
-
Display the execution plan for the query:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'+ALLSTATS')); SQL_ID frgk9dbaftmm9, child number 0 ------------------------------------- SELECT cust_id, cust_last_name, cust_first_name FROM sh.customers WHERE cust_city = 'Hyderabad' AND cust_income_level LIKE 'C%' AND cust_year_of_birth > 1960 Plan hash value: 2008213504 ------------------------------------------------------------------------------- | Id| Operation | Name |Starts|E-Rows|A-Rows| A-Time |Buffers| ------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 1| | 6 |00:00:00.01 | 1523| |* 1| TABLE ACCESS FULL| CUSTOMERS | 1| 6 | 6 |00:00:00.01 | 1523| ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("CUST_CITY"='Hyderabad' AND "CUST_YEAR_OF_BIRTH">1960 AND "CUST_INCOME_LEVEL" LIKE 'C%'))
-
Enable the
sh.customers
table for population in the IM column store:ALTER TABLE sh.customers INMEMORY;
The preceding statement uses the default priority of
NONE
. A full scan is required to populate objects with no priority. -
To determine whether data from the
sh.customers
table has been populated in the IM column store, execute the following query (sample output included):SELECT SEGMENT_NAME, POPULATE_STATUS FROM V$IM_SEGMENTS WHERE SEGMENT_NAME = 'CUSTOMERS'; no rows selected
In this case, no segments are populated in the IM column store because the
sh.customers
table has not yet been scanned. -
Query
sh.customers
using the same statement as in Step 1:SELECT cust_id, cust_last_name, cust_first_name FROM sh.customers WHERE cust_city = 'Hyderabad' AND cust_income_level LIKE 'C%' AND cust_year_of_birth > 1960;
-
Querying the cursor shows that the database performed a full scan and accessed the IM column store:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'+ALLSTATS')); SQL_ID frgk9dbaftmm9, child number 0 ------------------------------------- SELECT cust_id, cust_last_name, cust_first_name FROM sh.customers WHERE cust_city = 'Hyderabad' AND cust_income_level LIKE 'C%' AND cust_year_of_birth > 1960 Plan hash value: 2008213504 --------------------------------------------------------------------------------- | Id| Operation | Name |Starts|E-Rows|A-Rows|A-Time|Buffers| --------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 1| | 6 |00:00:00.02| 1523 | |* 1| TABLE ACCESS INMEMORY FULL| CUSTOMERS | 1| 6| 6 |00:00:00.02| 1523 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - inmemory(("CUST_CITY"='Hyderabad' AND "CUST_YEAR_OF_BIRTH">1960 AND "CUST_INCOME_LEVEL" LIKE 'C%')) filter(("CUST_CITY"='Hyderabad' AND "CUST_YEAR_OF_BIRTH">1960 AND "CUST_INCOME_LEVEL" LIKE 'C%'))
-
Query
V$IM_SEGMENTS
again (sample output included):COL SEGMENT_NAME FORMAT a20 SELECT SEGMENT_NAME, POPULATE_STATUS FROM V$IM_SEGMENTS WHERE SEGMENT_NAME = 'CUSTOMERS'; SEGMENT_NAME POPULATE_STATUS -------------------- --------------- CUSTOMERS COMPLETED
The value
COMPLETED
inPOPULATE_STATUS
means that the table is populated in the IM column store. -
The
DBA_FEATURE_USAGE_STATISTICS
view confirms that the database used the IM column store to retrieve the results:COL NAME FORMAT a25 SELECT ul.NAME, ul.DETECTED_USAGES FROM DBA_FEATURE_USAGE_STATISTICS ul WHERE ul.VERSION= (SELECT MAX(u2.VERSION) FROM DBA_FEATURE_USAGE_STATISTICS u2 WHERE u2.NAME = ul.NAME AND ul.NAME LIKE '%Column Store%'); NAME DETECTED_USAGES ------------------------- --------------- In-Memory Column Store 1
See Also:
"Priority Options for In-Memory Population"
Oracle Database SQL Language Reference to learn about the INMEMORY PRIORITY
clause
4.1.2.2 How Background Processes Populate IMCUs
During population, the database reads data from disk in its row format, pivots the rows to create columns, and then compresses the data into In-Memory Compression Units (IMCUs).
Worker processes (Wnnn) populate the data in the IM column store. Each worker process operates on a subset of database blocks from the object. Population is a streaming mechanism, simultaneously compressing the data and converting it into columnar format.
The INMEMORY_MAX_POPULATE_SERVERS
initialization parameter specifies the maximum number of worker processes to use for IM column store population. By default, the setting is one half of CPU_COUNT
. Set this parameter to an appropriate value for your environment. More worker processes result in faster population, but they use more CPU resources. Fewer worker processes result in slower population, which reduces CPU overhead.
Note:
If INMEMORY_MAX_POPULATE_SERVERS
is set to 0
, then population is disabled.
See Also:
Oracle Database Reference for more information about the INMEMORY_MAX_POPULATE_SERVERS
initialization parameter
4.1.3 Controls for In-Memory Population
Use the INMEMORY
clause in DDL statements to specify which objects are eligible for population into the IM column store. You can enable tablespaces, tables (internal and external), partitions, and materialized views.
This section contains the following topics:
4.1.3.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
This section contains the following topics:
4.1.3.1.1 In-Memory Tables
To make heap-organized tables eligible for population, specify INMEMORY
on the CREATE TABLE
or ALTER TABLE
statements.
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.
For a partitioned table, you can populate all or some partitions in the IM column store. By default, all partitions in a partitioned table inherit the INMEMORY
attribute.
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.
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
Example 4-2 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
4.1.3.1.2 In-Memory External Tables
To make external tables eligible for population, specify INMEMORY
on the CREATE TABLE ... EXTERNAL
or ALTER TABLE ... EXTERNAL
statements.
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
Restrictions for In-Memory External Tables
The IM column store manages the data for external tables in the same way as for heap-organized tables, with the following differences:
-
Automatic population and repopulation are not supported: you must use
DBMS_INMEMORY.REPOPULATE
. -
Some
INMEMORY
subclauses for external tables are not valid, including the column clause, distribute clause, and priority clause. -
Only the
ORACLE_LOADER
andORACLE_DATAPUMP
access drivers are supported. -
In-Memory Optimized Arithmetic does not support external tables.
-
In-Memory external tables cannot be accessed by parallel execution.
-
Partitioning is not supported for In-Memory external tables.
-
Join groups are not supported for In-Memory external tables.
-
IM expressions are not supported for In-Memory external tables.
Note:
Sessions that query In-Memory external tables must have the initialization parameter QUERY_REWRITE_INTEGRITY
set to stale_tolerated
. If an external table is modified, then the results from the IM column store are undefined.
See Also:
-
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 editions and services support In-Memory external tables
4.1.3.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
4.1.3.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
4.1.3.2 Priority Options for In-Memory Population
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 4-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
4.1.3.3 IM Column Store Compression Methods
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 a new set of algorithms that also improve query performance. If the columnar data is compressed using the FOR DML
or FOR QUERY
options, then SQL queries execute directly on the compressed data. Thus, scanning and filtering operations execute on a much smaller amount of data. The database only decompresses data when it is 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.
The following table summarizes the data compression methods supported in the IM column store.
Table 4-2 IM Column Store Compression Methods
CREATE/ALTER Syntax | Description |
---|---|
|
The data is not compressed. |
|
This method results in the best DML performance. This method compresses IM column store data the least, with the exception of Note: This compression method is not supported for |
|
This method results in the best query performance. This method compresses IM column store data more than This method is the default when the |
|
This method results in good query performance, and saves space. This method compresses IM column store data more than |
|
This method balances space saving and query performance, with a bias toward space saving. This method compresses IM column store data more than This method is the default when |
|
This method results in the best space saving. This method compresses IM column store data the most. |
In a SQL statement, the MEMCOMPRESS
keyword must be preceded by the INMEMORY
keyword.
See Also:
-
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
4.1.3.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
4.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.
This section contains the following topics:
4.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 Database".
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
4.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 Database".
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
4.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.
Example 4-3 Creating a Table and Enabling It for the IM Column Store
The following example creates the test_inmem
table and enables it for the IM column store:
CREATE TABLE test_inmem (
id NUMBER(5) PRIMARY KEY,
test_col VARCHAR2(15))
INMEMORY;
This example uses the defaults for the INMEMORY
clause: MEMCOMPRESS FOR QUERY
and PRIORITY NONE
.
Example 4-4 Enabling a Table for the IM Column Store
The following DDL statement enables the sh.sales
table for the IM column store:
ALTER TABLE sh.sales INMEMORY;
The preceding statement uses the defaults for the INMEMORY
clause: MEMCOMPRESS FOR QUERY
and PRIORITY NONE
.
The following query causes a full scan of sales
, which populates the table into the IM column store:
SELECT /*+ FULL(sales) NO_PARALLEL(sales) */ COUNT(*) FROM sh.sales;
The following query shows the population status of sales
(sample output included):
COL OWNER FORMAT a3
COL NAME FORMAT a10
COL STATUS FORMAT a20
SELECT OWNER, SEGMENT_NAME NAME,
POPULATE_STATUS STATUS
FROM V$IM_SEGMENTS;
OWN NAME STATUS
--- ---------- --------------------
SH SALES COMPLETED
The following query calculates the compression ratio. The query assumes that the tables are not further compressed on disk.
COL OWNER FORMAT a5
COL SEGMENT_NAME FORMAT a5
SET PAGESIZE 50000
SELECT v.OWNER, v.SEGMENT_NAME, v.BYTES ORIG_SIZE,
v.INMEMORY_SIZE IN_MEM_SIZE,
ROUND(v.BYTES / v.INMEMORY_SIZE, 2) COMP_RATIO
FROM V$IM_SEGMENTS v
ORDER BY 4;
OWNER SEGME ORIG_SIZE IN_MEM_SIZE COMP_RATIO
----- ----- ---------- ----------- ----------
SH SALES 851968 1310720 .65
SH SALES 835584 1310720 .64
SH SALES 925696 1310720 .71
SH SALES 958464 1310720 .73
SH SALES 950272 1310720 .73
SH SALES 786432 1310720 .6
SH SALES 876544 1310720 .67
SH SALES 753664 1310720 .58
SH SALES 1081344 1310720 .83
SH SALES 901120 1310720 .69
SH SALES 925696 1310720 .71
SH SALES 933888 1310720 .71
SH SALES 843776 1310720 .64
SH SALES 999424 1310720 .76
SH SALES 581632 1507328 .39
SH SALES 696320 1507328 .46
16 rows selected.
Example 4-5 Enabling a Table for the IM Column Store with FOR CAPACITY LOW Compression
The following DDL statement enables the oe.product_information
table for the IM column store and specifies the compression method FOR CAPACITY LOW
:
ALTER TABLE oe.product_information
INMEMORY
MEMCOMPRESS FOR CAPACITY LOW;
The preceding DDL statement uses the default for the PRIORITY
clause, which is NONE
. The following query scans the oe.product_information
table to populate it (sample output included):
SELECT /*+ FULL(p) NO_PARALLEL(p) */ COUNT(*)
FROM oe.product_information p;
COUNT(*)
----------
288
The following query calculates the compression ratio (sample output included):
COL OWNER FORMAT a5
COL SEGMENT_NAME FORMAT a19
SET PAGESIZE 50000
SELECT v.OWNER, v.SEGMENT_NAME, v.BYTES ORIG_SIZE,
v.INMEMORY_SIZE IN_MEM_SIZE,
ROUND(v.BYTES / v.INMEMORY_SIZE, 2) COMP_RATIO
FROM V$IM_SEGMENTS v
WHERE SEGMENT_NAME LIKE 'P%'
ORDER BY 4;
OWNER SEGMENT_NAME ORIG_SIZE IN_MEM_SIZE COMP_RATIO
----- ------------------- ---------- ----------- ----------
OE PRODUCT_INFORMATION 98304 1310720 .08
Example 4-6 Enabling a Table for the IM Column Store with HIGH Data Population Priority
The following DDL statement 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:
ALTER TABLE
oe.product_information
INMEMORY
PRIORITY HIGH;
Example 4-7 Enabling a Table for the IM Column Store with FOR CAPACITY HIGH Compression and LOW Data Population Priority
The following query shows the 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;
Example 4-8 Enabling a Partitioned Table for the IM Column Store
This following DDL statement creates a partitioned table named range_sales
and specifies a different compression level for the first three partitions in the IM column store. The last two partitions are not populated in the IM column store.
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));
Example 4-9 Disabling a Table for the IM Column Store
To disable a table for the IM column store, specify the NO INMEMORY
clause. The following example disables the oe.product_information
table for the IM column store:
ALTER TABLE oe.product_information NO INMEMORY;
You can query the V$IM_SEGMENTS
view to list the database objects that are populated in the IM column store.
Example 4-10 Disabling Columnar Format on Flash Cache
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.
The following example disables the columnar format for oe.product_information
on flash cache storage:
ALTER TABLE oe.product_information NO CELLMEMORY;
4.2.4 Creating and Populating External Tables: Examples
This example creates an external table with the INMEMORY
option, populates it in the IM column store, and then refreshes it.
The following code examples assume that you have created the directories /tmp/data/
, /tmp/log/
, and /tmp/bad/
on a Linux or UNIX host.
Example 4-11 Creating an External Table with the INMEMORY Attribute
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 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 admin_ext_sales
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
.
Example 4-12 Populating an External Table into the IM Column Store
The admin_ext_sales
table is enabled for the IM column store, but is not yet populated. The following query, executed as user sh
, populates the table. Note that queries of In-Memory external tables must have the QUERY_REWRITE_INTEGRITY
initialization parameter set to stale_tolerated
.
ALTER SESSION SET QUERY_REWRITE_INTEGRITY=stale_tolerated;
EXEC DBMS_INMEMORY.POPULATE('SH', 'ADMIN_EXT_SALES');
The following query indicates that the admin_ext_sales
table has been populated:
COL OWNER FORMAT a3
COL NAME FORMAT a15
COL STATUS FORMAT a9
SELECT OWNER, SEGMENT_NAME NAME,
POPULATE_STATUS STATUS
FROM V$IM_SEGMENTS;
OWN NAME STATUS
--- --------------- ---------
SH ADMIN_EXT_SALES COMPLETED
Example 4-13 Refreshing an External Table in the IM Column Store
Assume that a record is added to /tmp/data/sh_sales.csv
as follows:
echo "148,8787,23-NOV-01,2,999,1,23.43" >> /tmp/data/sh_sales.csv
Unlike internal tables, external tables do not use the automatic repopulation mechanism. To refresh external tables, you must use the DBMS_INMEMORY.REPOPULATE
procedure. A full scan will not populate the table.
The following example forces the IM column store to populate admin_ext_sales
:
EXEC DBMS_INMEMORY.REPOPULATE('SH', 'ADMIN_EXT_SALES');
No queries of admin_ext_sales
will succeed until the table is fully populated.
See Also:
-
Oracle Database PL/SQL Packages and Types Reference to learn more about
DBMS_INMEMORY.REPOPULATE
-
Oracle Database Reference to learn about
QUERY_REWRITE_INTEGRITY
,ALL_EXTERNAL_TABLES
, and the other external table views
4.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.
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 can be populated.
This section contains the following topics:
4.3.1 About IM Virtual Columns
An IM virtual column is like any other column, except that its value is derived by evaluating an expression.
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.
Note:
A virtual column or IM expression counts toward the limit of 1000 columns per populated object.
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.
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.
See Also:
-
Oracle Database SQL Language Reference for the syntax and semantics of the
INMEMORY
clause
4.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 enabled for the IM column store.
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 4-14 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=SPFILE;
System altered.
Example 4-15 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);
4.3.3 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);
Specifically, the preceding statement specifies 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. -
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
4.3.4 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
4.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 Database".
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 4-16 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 4-17 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;
4.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 Database".
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 4-18 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 4-19 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
4.6 Forcing Initial Population of an In-Memory Object: Tutorial
Enabling an object for In-Memory population does not immediately populate the object.
PRIORITY
set to NONE
, and if you want to populate it immediately, then you have the following options:
-
Force a full table scan
-
Use the
DBMS_INMEMORY.POPULATE
procedure
Assumptions
This tutorial assumes the following:
-
The IM column store is enabled.
-
You want to enable the
sh.customers
table for In-Memory population, using the defaultPRIORITY
ofNONE
. -
You want to force the immediate population of
sh.customers
into the IM column store.
To force population of an INMEMORY table:
-
In SQL*Plus or SQL Developer, log in to the database as a user with administrative privileges.
-
Apply the
INMEMORY
attribute to the table.For example, enable
sh.customers
for IM population as follows:ALTER TABLE sh.customers INMEMORY;
-
Optionally, to check the population status, query
V$IM_SEGMENTS
.For example, use the following statement (sample output included):
COL OWNER FORMAT a10; COL NAME FORMAT a25; COL STATUS FORMAT a10; SELECT OWNER, SEGMENT_NAME NAME, POPULATE_STATUS STATUS FROM V$IM_SEGMENTS WHERE SEGMENT_NAME = 'CUSTOMERS'; no rows selected
The preceding output shows that the object is not yet populated in the IM column store.
-
Execute the
DBMS_INMEMORY.POPULATE
procedure on the table.For example, enable this procedure against
sh.customers
as follows:EXEC DBMS_INMEMORY.POPULATE('SH', 'CUSTOMERS');
-
Optionally, to check the population status, query
V$IM_SEGMENTS
.For example, use the following statement (sample output included):
SELECT OWNER, SEGMENT_NAME NAME, POPULATE_STATUS STATUS FROM V$IM_SEGMENTS WHERE SEGMENT_NAME = 'CUSTOMERS'; OWN NAME STATUS --- ---------- -------------------- SH CUSTOMERS COMPLETED
The object is now populated in the IM column store.
See Also:
-
Oracle Database Reference to learn about
V$IM_SEGMENTS
-
Oracle Database PL/SQL Packages and Types Reference to learn about
DBMS_INMEMORY.POPULATE