当前位置:网站首页>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
边栏推荐
- Practical data Lake iceberg lesson 30 MySQL - > iceberg, time zone problems of different clients
- 科创人·派拉软件CEO谭翔:零信任本质是数字安全,To B也要深研用户心智
- 分享两个实用的shell脚本
- 让中小学生在快乐中学习的创客教育
- Tensorflow common functions
- Interpretation of 2022 robot education industry analysis report
- QT信号量 无法解析的错误的总结
- C# F23.StringSimilarity库 字符串重复度、文本相似度、防抄袭
- Compress the curl library into a sending string of utf8 and send it with curl library
- Laravel增加自定义助手函数
猜你喜欢
Design and practice of the smallest short website system in the whole network
赛微微电科创板上市破发:跌幅达26% 公司市值44亿
5个免费音频素材网站,建议收藏
GPU, CUDA,cuDNN三者的关系总结
Interpretation of 2022 robot education industry analysis report
Sofa weekly | excellent Committee of the year, contributor of this week, QA of this week
让中小学生在快乐中学习的创客教育
分享两个实用的shell脚本
Simple construction of rebbitmq
解析社交性机器人对基础科学的作用
随机推荐
WIN10 启动后花屏
1.Electron开发环境搭建
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
How to count fixed assets and how to generate an asset count report with one click
Overall plan management mode in maker Education
The listing of saiweidian Technology Innovation Board broke: a decrease of 26% and the market value of the company was 4.4 billion
AcWing 1874. 哞加密(枚举,哈希)
Nacos Foundation (9): Nacos configuration management from single architecture to microservices
MQ的了解
创客教育中的统筹方案管理模式
nacos基础(9):nacos配置管理之从单体架构到微服务
docker MySQL主从备份
Advanced file IO of system programming (13) -- IO multiplexing - Select
赛微微电科创板上市破发:跌幅达26% 公司市值44亿
oh-my-lotto
让中小学生在快乐中学习的创客教育
PSCP 基本使用
IM 体系结构:CPU架构:SIMD向量处理(IM-2.3)
论坛系统数据库设计
第四章 为IM 启用填充对象之强制填充In-Memory对象:教程(IM 4.7)