Chapter 5 optimizing queries using in memory expressions (IM 5.1)

Upper connection IM 4.8. This chapter is IM The first part of the fifth chapter of the series .

stay IM In the context of column storage , expression Is one or more values , Operator and the... That resolves to a value SQL or PL / SQL function （ only DETERMINISTIC ） The combination of .

Expression statistics store （ESS） Automatic tracking often evaluates （“ heat ”） Result of expression . You can use DBMS_INMEMORY_ADMIN Package to capture hot expressions , And fill them with hidden virtual columns , Or delete some or all of them .

This section contains the following topics ：

• About IM expression By default ,DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS Process identification and filling “ heat ” expression , be called In-Memory expression （IM expression ）.
• To configure IM Expression usage （ Optional ） Use INMEMORY_EXPRESSIONS_USAGE What type of IM Expressions are eligible for padding , Or disable all IM Filling of expressions .
• Capture and fill IM expression IME_CAPTURE_EXPRESSIONS The procedure captures and populates the database within a specified time range 20 Most frequently visited （“ Hottest ”） The expression of .IME_POPULATE_EXPRESSIONS The procedure forces the last call to DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS Expression captured in .
• Delete IM expression The DBMS_INMEMORY_ADMIN.IME_DROP_ALL_EXPRESSIONS procedure drops all SYS_IME expression virtual columns in the database. The DBMS_INMEMORY.IME_DROP_EXPRESSIONS procedure drops a specified set of SYS_IME virtual columns from a table.

About IM expression

By default ,DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS Process identification and filling “ heat ” expression , be called In-Memory expression （IM expression ）.

IM The expression is implemented as a hidden virtual column , But access... In the same way as non virtual columns . To store materialized expressions ,IM Column storage uses a special compression format , Such as fixed-width vectors And with fixed-width Dictionary code of code .

Oracle Automatic database identification IM An expression in the column store as a candidate for padding . stay DBA_IM_EXPRESSIONS.COLUMN_NAME in ,IM The expression column has a prefix SYS_IME. You cannot directly create SYS_IME Column . for example , Consider the following query , It specifies two expressions , An alias for weekly_sal and ann_comp：

SELECT employee_id, last_name, salary, commission_pct,

       ROUND(salary*12/52,2) as "weekly_sal",

       12*(salary*NVL(commission_pct,0)+salary) as "ann_comp"

FROM   employees

ORDER BY ann_comp;

Arithmetic expressions ROUND(salary*12/52,2) and 12*(salary*NVL(commission_pct,0)+salary) It is computationally intensive and recurrent , This makes them hidden IM Candidates for expression columns .

DBMS_INMEMORY_ADMIN Package management IM The main interface of expression ：

· To make the database identify IM Expressions and add them to their respective tables during the next refill , Please use IME_CAPTURE_EXPRESSIONS.

· To make the database identify IM Expressions and add them to their respective tables during the next refill , Please use IME_POPULATE_EXPRESSIONS.

· To delete SYS_IME Column , Please use DBMS_INMEMORY_ADMIN.IME_DROP_ALL_EXPRESSIONS or DBMS_INMEMORY.IME_DROP_EXPRESSIONS.

This section contains the following topics ：

• IM The purpose of the expression IM Expressions accelerate the query speed of large data sets by pre calculating dense expressions . IM Expressions are especially useful for frequently executed table joins , Mapping and predicate evaluation .
• 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 .
• IM The user interface of the expression DBMS_INMEMORY_ADMIN package ,DBMS_INMEMORY Bao He INMEMORY_EXPRESSIONS_USAGE Initialization parameter control IM The behavior of an expression .
• IM The basic task of expressions INMEMORY_EXPRESSIONS_USAGE The default setting of allows the database to use dynamic and static data IM expression . You have to use DBMS_INMEMORY_ADMIN fill IM Expression in column store .

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）

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 .