How Im expressions work (5.3)

Upper connection IM 5.2. This chapter is IM Chapter 5 of the series Use In-Memory Expression optimization query part 3 IM How expressions work .

IM How expressions work

To identify the expression as IM Candidates for expressions , The database will query ESS. Optimizer usage ESS To maintain statistics about the expression evaluation of a specific table .

This section contains the following topics ：

• IM Expression infrastructure IM The expression infrastructure is responsible for IM Calculate and populate the column store IM expression ,IM The results of virtual columns and any other useful internal calculations . These optimizations are mainly conducive to analyzing queries .
• Capture IM expression Whenever called IME_CAPTURE_EXPRESSIONS The process , The database will query ESS, And identify... Within the specified time range 20 Most frequently visited （“ Hottest ”） expression .
• ESS How to work ESS Is a repository maintained by the optimizer for storing statistics about expression evaluation .
• How to fill the database IM expression In the memory coordinator process （IMCO） Under the guidance of , Space management work process （Wnnn） Will automatically IM The expression is loaded into IMEU in .
• IMEU How to communicate with IMCU Related to For any line , Physical column at IMCU in , Virtual columns reside in associated columns IMEU in . IMEU It's read-only and columnar , It's like IMCU equally .

IM Expression infrastructure

IM The expression infrastructure is responsible for IM Calculate and populate the column store IM expression ,IM The results of virtual columns and any other useful internal calculations . These optimizations are mainly conducive to analyzing queries .

The filling results can be included in the project , Function evaluation of columns used in scan or join expressions . During query evaluation ,IM Column storage can be based on SQL Expressions evaluated by the runtime engine automatically cache internal calculations .

Virtual column

Except filling IM expression ,IM The column store can be populated In-Memory Virtual column . The underlying mechanism is the same ：IM Expressions are virtual columns . however ,IM Virtual columns are created and exposed by users , and IM Expressions are created and hidden from the database .

Static expression ： Binary system JSON Column

IM The expression infrastructure supports dynamic expressions （IM Expressions and virtual columns ） And static expressions . from OracleDatabase 12c Release 2（12.2） Start ,IM Column storage supports binary JSON Format , Its performance is better than row based JSON The text is stored . Database usage IM The expression infrastructure will JSON An efficient binary representation of a text column is loaded as a virtual column . Query access to the actual JSON data , But use optimized virtual columns to speed up access .

Oracle The database supports multiple JSON function ：JSON_TABLE,JSON_VALUE and JSON_EXISTS.INMEMORY_EXPRESSIONS_USAGE Initialization parameters control the behavior of dynamic and static expressions .

Capture IM expression

Whenever called IME_CAPTURE_EXPRESSIONS The process , The database will query ESS, And identify... Within the specified time range 20 Most frequently visited （“ Hottest ”） expression .

The time frame is the past 24 Hours , Or after the database is created . The database is only considered in IM An expression on a table that is at least partially populated in the column store .

Database will 20 The hottest expressions are added to their respective tables as hidden SYS_IME Virtual column , And apply the default INMEMORY Column compression clause . If any... Was added during the last call SYS_IME The column is no longer in the latest expression list , The database changes its properties to NO INMEMORY.

chart 5-1 Capture SYS_IME Column

Tabular SYS_IME The maximum number of columns is 50, Regardless of whether the attribute is INMEMORY. Express to 50 After expression restriction , The database will not add new SYS_IME Column . To allow new expressions , You have to use DBMS_INMEMORY.IME_DROP_EXPRESSIONS or DBMS_INMEMORY_ADMIN.IME_DROP_ALL_EXPRESSIONS Process delete SYS_IME Column .

SYS_IME Both virtual columns and user-defined virtual columns are included in the table 1000 Column restrictions . for example , If the table contains 980 A non virtual （ disk ） Column , Then you can only add 20 A virtual column .

ESS How to work

ESS Is a repository maintained by the optimizer for storing statistics about expression evaluation .

For each table ,ESS Maintain expression Statistics , For example, the number of executions and the cost of evaluation . When evaluating predicates ,Oracle The database tracks and provides runtime feedback on the dynamic cost of evaluating counts and expressions . be based on ESS Statistics , If a particular expression is IM expression , Then the database may decide that the query will perform better .

notes ：

In a specific table ESS The expression cached in involves only the columns of this table . When Oracle The database will be deterministic PL / SQL The function ID is IM When a candidate of an expression , This rule is particularly important .

chart 5-2 ESS and IM expression

In this diagram ,ESS Already in employees Two commonly used expressions are identified in the table ：ROUND(salary*12/52,2) and 12*(salary*NVL(commission_pct,0)+salary). When the database is filled IM Column store employees when , Two IMCU Store column data . Every IMCU With its only IMEU Related to , It contains IMCU Derived values of two common expressions in the middle row .

Not every expression is IM Candidates for expressions . The database only considers expressions that will be accessed frequently . because IM The expression is implemented as a hidden virtual column , They must also meet the limits of virtual columns .

although IM Column storage is ESS The client of , but ESS Independent of database memory function . Other clients can also use ESS Statistics , Including the optimizer itself .

How to fill the database IM expression

In the memory coordinator process （IMCO） Under the guidance of , Space management work process （Wnnn） Will automatically IM The expression is loaded into IMEU in .

The database will add each compression unit in memory （IMCU） Fill or repopulate tasks , It contains the user-defined or... To be populated IM Information about which virtual columns of the expression . The decision depends on INMEMORY_EXPRESSION_USAGE and INMEMORY_VIRTUAL_COLUMNS Initialization parameter settings .

notes ：

DBMS_INMEMORY.IME_CAPTURE_EXPRESSIONS The process will automatically add the detected expression as a hidden virtual column .

Wnnn Process creation IMCU. To create a IMEU, The process will perform the following additional steps ：

1. Create an expression value

2. Convert values to columnar format , And compress it into memory expression units （IMEU）

3. Each one IMEU Link to its associated IMCU

notes ：

With IMEU Increase in the number of expressions stored in , The work process may consume more CPU To evaluate the expression value . This overhead may increase the fill time .

IMEU How to communicate with IMCU Related to

For any line , Physical column at IMCU in , Virtual columns reside in associated columns IMEU in . IMEU It's read-only and columnar , It's like IMCU equally .

because IMEU For a particular INMEMORY Segment created IMCU Logical extension of , So by default , They inherit INMEMORY Clause and Oracle Real Applications Cluster（Oracle RAC） attribute （ Such as DISTRIBUTE and DUPLICATE）. IMEU With a IMCU Related to . However , One IMCU There may be more than one IMEU. Database will IMEU Managed as a separate structure , Make it easier to add and remove .

notes ：

IMEU It also contains user created IN Virtual column .

If the source data changes , The database changes during repopulation IM Derived data in expression . for example , If the transaction updates... In the table 100 A salary value , Then the work process of space management （Wnnn） Will automatically update from here 100 All... Derived from a changed value IM Expression value . Database will IMCU And its associated IMEU Refill , Instead of first refilling all IMCU, Then refill all IMEU. stay IMCU Refill period ,IMCU Can still be used to query .

To be continued .

Here are IM Series content ：

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 ）

Chapter four by IM Enable and disable columns for fill objects （IM-4.3 The third part ）

Chapter four by IM Enables the placement of fill objects NO INMEMORY The table specifies INMEMORY Column properties ： Example （IM-4.4 The fourth part ）

Chapter four by IM Enable and disable tablespaces for padding objects IM Column store （IM 4.5）

Chapter four Enable and disable... For materialized views IM Column store （IM 4.6）

Chapter four by IM Enables forced filling of filled objects In-Memory object ： course （IM 4.7）

Chapter four by IM Enable fill objects to IM Column storage enabled ADO（IM 4.8）

The fifth chapter Use In-Memory Expression optimization query （IM 5.1）

IM The purpose of the expression （IM 5.2）

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 .