The fourth chapter is the enable and disable columns of IM enabled fill objects (Part III of im-4.3)

In-Memory The manual : Preface （IM- Preface ）

Chapter one Oracle Database In-Memory Relevant concepts (IM-1.1)

Chapter one Oracle Database In-Memory Relevant concepts （ To continue ）(IM-1.2)

Chapter two Oracle Database In-Memory Architecture ( On ) (IM-2.1)

Chapter two In-Memory Architecture (IM-2.2)

Chapter two IM Architecture ：CPU framework ：SIMD Vector processing （IM-2.3）

The third chapter Enable and adjust IM The size of the column store （IM-3.1）

Chapter four by In-Memory Enable fill objects （IM-4.1 The first part ）

Chapter four by In-Memory Enable fill objects （IM-4.2 The second part ） This article is IM Chapter 4 of the series ： by IM Enable and disable columns for fill objects （IM-4.3 The third part ）

In-Memory Table enable and disable Columns

You can specify... For individual columns INMEMORY Clause . Non virtual columns and In-Memory Virtual column （IM Virtual column ） Are eligible to enter IM Column store .

This section contains the following topics ：

• About IM Virtual column IM Virtual columns and enable INMEMORY Any other column in the table is similar to , Only its value is derived from the evaluation expression . stay IM The column store stores precomputed data IM Virtual column values can improve query performance .
• Enable IM Virtual column IM Virtual columns improve query performance by avoiding double counting . Besides , The database can use such as SIMD Vector processing technology to scan and filter IM Virtual column .
• by IM Enable save set columns ： Example Enable this example in addition to weight_class and catalog_url In addition to the IM Column storage oe.product_information All the columns in the table . It is also for IM The columns enabled by the column store specify different columns IM Column storage compression method .
• stay NO IN MEMORY The table specifies INMEMORY Column properties ： Example from Oracle Database 12c The first 2 edition （12.2） Start , Can be specified as INMEMORY Specifies the column level of the object INMEMORY Clause .

About IM Virtual column

IM Virtual columns and enable INMEMORY Any other column in the table is similar to , Only its value is derived from the evaluation expression . stay IM The column store stores precomputed data IM Virtual column values can improve query performance .

about IM Virtual column , Expressions can include expressions from the same table 、 Constant 、SQL Functions and user-defined PL /SQL function （ only DETERMINISTIC） The column of . You cannot explicitly write IM Virtual column .

notes :

Virtual column or IM The expression count is limited to... Per fill object 1000 Column .A virtual column or IM expression counts toward the limit of 1000columns per populated object.

To be in IM Fill in the column store IM Virtual column , Please put INMEMORY_VIRTUAL_COLUMNS The initialization parameter is set to one of the following values ：

· MANUAL（ Default ）： If IM The column store has tables enabled , Unless they are explicitly set to INMEMORY, Otherwise... Defined on this table IM Virtual columns are not suitable for populating .

· ENABLE： If IM The column store has tables enabled , All defined on this table IM Virtual columns are eligible for filling , Unless they are explicitly set to NO INMEMORY.

By default ,IM A column in a column store has the same compression level as the table or partition in which it is stored . however , When it comes to IM When virtual columns specify different compression levels , The compression level is populated with the specified compression level .

To specify in IM The column store is not populated IM Virtual column , Please set this initialization parameter to DISABLE.

IM Virtual columns and IM The basic storage structure of expressions is the same . However , control IM Expression and IM The mechanism of virtual columns is different .

notes :

· IM The column store is only populated with information marked INMEMORY Virtual column of the table .

· To fill IM Column store IM Virtual column , Initialize parameters COMPATIBLE The value of must be set to 12.1.0 Or higher .

Enable IM Virtual column

IM Virtual columns improve query performance by avoiding double counting . Besides , The database can use such as SIMD Vector processing technology to scan and filter IM Virtual column .

precondition

To enable the IM Virtual column , The following conditions must be met ：

1. Database enabled IM Column store .

Please see the “ Enable... For the database IM Column store ”.

2. by IM The column store table enables the inclusion of virtual columns .

Please see the “ Enable and disable... For tables IM Column store ”.

3. INMEMORY_VIRTUAL_COLUMNS Initialization parameter is not set to DISABLE.

4. Initialize parameters COMPATIBLE Is set to 12.1.0 Or higher .

To enable the IM Virtual column ：

1. Connect to the database instance as a user with appropriate privileges , To change the table and set initialization parameters .

2. take INMEMORY_VIRTUAL_COLUMNS The initialization parameter is set to ENABLE, Or enable IM Columns store specific virtual columns .

Example 4-10 Enable IM Virtual columns stored in columns

In this example , You take SYSTEM Log in to the database as . Enabled IM Column store , But the number 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 hr.employees In the table , Then specify the table as 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 Not suitable for filling , Even though hr.employees Non virtual columns in are eligible for padding . The following statement allows you to fill in weekly_sal and hr.employees Any other virtual column in ：

SQL> ALTER SYSTEM SET INMEMORY_VIRTUAL_COLUMNS=ENABLE SCOPE=SPFILE;

System altered.

Example 4-11 by IM Column storage enables specific storage IM Virtual column

This example assumes INMEMORY_VIRTUAL_COLUMNS The initialization parameter is set to MANUAL, It means IM Virtual columns must be explicitly added to IM Column store . This example first creates hr.admin_emp surface ：

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 ,hrly_rate Virtual columns are not suitable for populating . The following statement explicitly specifies the virtual column as INMEMORY：

ALTER TABLE hr.admin_emp INMEMORY(hrly_rate);

by IM Enable save set columns ： Example

Enable this example in addition to weight_class and catalog_url In addition to the IM Column storage oe.product_information All the columns in the table . It's also for IM The columns enabled by the column store specify different columns IM Column storage compression method .

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);

say concretely , This example specifies the following ：

· Use MEMCOMPRESS FOR QUERY The compression method is IM Column storage is enabled to product_id Start with min_price List of ending Columns .

· With product_description Start with list_price The list of ended columns has MEMCOMPRESS FORCAPACITY HIGH Compression method IM Column storage enabled .

· Not for IM Column storage enabled weight_class and catalog_url Column .

· This table uses PRIORITY The default value of the clause , namely PRIORITY NONE.

notes :

The priority setting must apply to the entire table or partition . Specifying different columns for different column subsets in a table is not allowed IM Column storage priority .

To determine which column compression level to select for the database object definition , Please check V$IM_COLUMN_LEVEL View , This is 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

Shandong Oracle User group (Shandong Oracle User Group), abbreviation ：SDOUG, Is a full of vitality 、 Young non-profit organizations , It aims to provide an exchange platform for technology lovers in Jinan and surrounding areas .SDOUG Organize offline technology sharing activities from time to time , Promote local and surrounding IT Technological development 、 Help technology enthusiasts improve themselves . Share technology 、 Share happiness ,SDOUG On the road .