当前位置:网站首页>Chapter 5 optimizing queries using in memory expressions (IM 5.1)
Chapter 5 optimizing queries using in memory expressions (IM 5.1)
2022-04-23 11:38:00 【Grainger】
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 toDBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS
Expression captured in . - Delete IM expression The
DBMS_INMEMORY_ADMIN.IME_DROP_ALL_EXPRESSIONS
procedure drops allSYS_IME
expression virtual columns in the database. TheDBMS_INMEMORY.IME_DROP_EXPRESSIONS
procedure drops a specified set ofSYS_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 HeINMEMORY_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 useDBMS_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 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 .
版权声明
本文为[Grainger]所创,转载请带上原文链接,感谢
https://yzsam.com/2022/04/202204231136323443.html
边栏推荐
- Design and practice of the smallest short website system in the whole network
- nacos基础(7):配置管理
- Usage record of map < qstring, bool >
- PCB的注意事项
- nacos基础(8):登录管理
- Precautions for PCB
- Interpreting the art created by robots
- Interpretation of 2022 robot education industry analysis report
- Castle.DynamicProxy实现事务单元控制
- Nacos Basics (5): getting started with Nacos configuration
猜你喜欢
Interprocess communication -- message queue
Understanding of MQ
On lambda powertools typescript
Resolution due to AMD not found_ ags_ x64. DLL, unable to continue code execution. Reinstallation of the program may solve this problem, Forza horizon 5
激活函数之relu函数
激活函数之阶跃函数
科创人·派拉软件CEO谭翔:零信任本质是数字安全,To B也要深研用户心智
Sofa weekly | excellent Committee of the year, contributor of this week, QA of this week
Analyzing the role of social robots in basic science
The way to change children's programming structure
随机推荐
MQ的了解
第四章 为IM 启用填充对象之为IM列存储启用ADO(IM 4.8)
配电房远程综合监控系统在10kV预制舱项目中的应用
IFLYTEK's revenue in 2021 was 18.3 billion yuan: a year-on-year increase of 41% and a net profit of 1.556 billion yuan
Redis optimization series (II) redis master-slave principle and master-slave common configuration
GPU, CUDA,cuDNN三者的关系总结
科创人·派拉软件CEO谭翔:零信任本质是数字安全,To B也要深研用户心智
1.Electron开发环境搭建
谁说抠图要会 PS?这个开源神器还能批量抠,效果拔群!
oh-my-lotto
Nacos Foundation (6): Nacos configuration management model
Share two practical shell scripts
第四章 为IM 启用填充对象之强制填充In-Memory对象:教程(IM 4.7)
Resolution due to AMD not found_ ags_ x64. DLL, unable to continue code execution. Reinstallation of the program may solve this problem, Forza horizon 5
激活函数之relu函数
ES6 learning notes II
Résumé de la relation entre GPU, cuda et cudnn
ES6学习笔记二
QT 怎么把QWigdet变成QDialog
Yunna | fixed assets inventory supports multiple inventory methods (asset inventory)