Query Rewrite with Materialized Views 用物化视图重写查询
(page 72)
Query rewrite is a transformation that occurs when a query, or a portion
of a query , has been saved as a materialized view and the transformer
can rewrite the query to use the precomputed materialized view data
instead of executing the current query.
A materialized view is like a
normal view except that the query has been executed and its result set
has been stored in a table. What this does is to precompute the result
of the query and make it available whenever the specific query is
executed. That means that all the work to determine the plan, execute
it, and gather up all the data has already been done. So, when the same
query is executed again, there is no need to go through all that effort
again.
查询重写是一种变换发生在,当一查询,或一部分查询,(执行后)作为物化视图保存且变换器可使用预先计算好的物化视图数据重写查询,而不是执行当前的查询。
除了查询已经执行且它的结果已经存入表中以外,物化视图像普通视图一样。(物化视图所作的是)预先计算查询的结果且在特定查询执行时使其(结果)
有效。这意味着所有确定计划,执行计划,收集全部数据的工作已经完成。这样的话,当相同的查询再次执行时,就不再需要重头到尾费力一番。
The query transformer will match a query with available materialized
views and then rewrite the query to simply select from the materialized
result set.
Listing 2-10 walks through creating a materialized view and
how the transformer would rewrite the query to use the materialized view
result set .
查询变化器将把一个查询和有效的物化视图相匹配,然后重写查询,直接的从物化的结果集中选取(数据)。
列表2-10通过创建一物化视图,展示变换器如何用物化视图的结果集重写查询。
Listing 2-10. Query Rewrite with Materialized Views
SQL > set autotrace traceonly explain
SQL >
SQL > SELECT p.prod_id, p.prod_name, t.time_id, t.week_ending_day,
2 s.channel_id, s.promo_id, s.cust_id, s.amount_sold
3 FROM sales s, products p , times t
4 WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id;
Execution Plan
--------------------------------------------------------------------------
Plan hash value : 1109402314
-----------------------------------------------------------------------------------------------------------------------------------
|
Id | Operation | Name
| Rows | Bytes | Cost (%CPU ) | Pstart | Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
|
0 | SELECT STATEME N T | | 918
K| 65 M | 485 (17) | | |
|*
1 | HASH JOIN |
| 918 K| 65 M | 485 (17) | | |
|
2 | TABLE ACCESS FULL | TIMES | 1826
| 29216 | 15 (0) | | |
|*
3 | HASH JOIN |
| 918 K| 51 M | 453 (14) | |
|
| 4 | TABLE ACCESS FULL | PRODUCTS |
72 | 2160 | 3 (0) | | |
|
5 | PARTITION RANGE ALL | | 918
K| 25 M | 434 (11) | 1 | 28 |
|
6 | TABLE ACCESS FULL | SALES | 918
K| 25 M | 434 (11) | 1 | 28 |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-------------------------------------------------------------------------------
1 - access("S". "TIME_ID" = "T" . "TIME_ID")
3 - access("S". "PROD_ID" = "P"."PROD_ID")
SQL >
SQL > set autotrace off
SQL>
SQL> CREATE MATERIALIZED VIEW sales_time_product_mv
2 ENABLE QUERY REWRITE AS
3 SELECT p.prod_id, p.prod_name, t.time_id, t.week_ending_day,
4 s.channel_id, s.promo_id, s.cust_id, s.amount_sold
5 FROM sales s, products p, times t
6 WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id;
SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> SELECT p.prod_id, p.prod_name, t.time_id, t.week_ending_day,
2 s.channel_id, s.promo_id, s.cust_id, s.amount_sold
3 FROM sales s, products p, times t
4 WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1109402314
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K | 65M | 485 (17) | | |
|* 1 | HASH JOIN | | 918K | 65M | 485 (17) | | |
| 2 | TABLE ACCESS FULL | TIMES | 1826 | 29216 | 15 (0) | | |
|* 3 | HASH JOIN | | 918K | 51M | 453 (14) | | |
| 4 | TABLE ACCESS FULL | PRODUCTS | 72 | 2160 | 3 (0) | | |
| 5 | PARTITION RANGE ALL | | 918K | 25M | 434 (11) | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 918K | 25M | 434 (11) | 1 | 28 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("S"."TIME_ID"="T"."TIME_ID")
3 - access("S"."PROD_ID"="P"."PROD_ID")
Note
-----
- dynamic sampling used for this statement
SQL>
SQL> SELECT /*+ rewrite(sales_time_product_mv) */
2 p.prod_id, p.prod_name, t.time_id, t.week_ending_day,
3 s.channel_id, s.promo_id, s.cust_id, s.amount_sold
4 FROM sales s, products p, times t
5 WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 663088863
-------------------------------------------------------------------------------------------
|
Id | Operation |
Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 909K| 95M| 1935 (3)|
| 1 | MAT_VIEW REWRITE ACCESS FULL| SALES_TIME_PRODUCT_MV | 909K| 95M| 1935 (3)|
-------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
In order to keep the example simple, I used a REWRITE
hint to turn on the query rewrite
transformation. You can enable query rewrite to happen automatically as well. But as you notice in the
example, when the rewrite does occur, the plan simply shows a full access on the materialized view
instead of the entire set of operations required to produce the result set originally.
As you can imagine,
the time savings can be substantial for complicated queries with large results sets, particularly if the
query contains aggregations. For more information on query rewrite and materialized views, refer to
The Oracle Data Warehousing Guide where you’ll find an entire chapter on advanced query rewrite.
为了让例子简单,我使用REWRITE提示打开查询重写变换。你也能让查询重写自动有效。但是如你在例子中所见,当查询重写发生时,计划直接展示在物化视图上的全扫描而不是最初产生结果集所需的整个操作集合。
你能想象,对于复杂的大结果集的查询能实质上的节约时间,特别是如果查询包含聚合操作。更多的信息关
于查询重写和物化视图,参考《Oracle数据仓库指南》,其中你能找到整章的查询重写的高级(论述)。
分享到:
相关推荐
前端开源库-connect-url-rewriteconnect url rewrite,connect/express中间件,用于使用正则表达式(如mod_rewrite)重写url。
NohBoard-ReWrite-v1.3.0.zip
安装只需在您的shell环境中键入: eg plugin install express-gateway-plugin-rewrite快速开始确保该插件在列出。 如果您使用了上面的命令,这将自动为您完成。 将配置密钥添加到。 policies : - rewrite : - ...
leetcode下载Leetcode-SQL-rewrite-using-Python- 这是一个由黄磊创建的记录Leetcode SQL实践的仓库。 175. 合并两张表 sql select FirstName, LastName, City, State from Person P left join Address A on P . ...
Oracle 官方数据仓库指南,讲解透彻,知识权威,强烈推荐! Oracle® Database Data Warehousing Guide 10g Release 2 (10.2) Contents: Part I Concepts 1 Data Warehousing Concepts Part II Logical Design 2 ...
urlrewrite重写地址,Urlrewrite 2.5.2 现在有2.6版本的,自己去网上找吧
官方离线安装包,亲测可用
官方离线安装包,亲测可用
FFRouter 是 iOS 中一个强大且易用的 URL 路由库,支持 URL Rewrite,使 APP 在发布之后也可以动态修改相关路由逻辑。基于匹配查找 URL,效率高。集成和使用都非常简单!
前端开源库-koa-rewrite-75lbkoa-rewrite-75lb,用于koa的url重写中间件
iis建网站使用的微软URL重写程序,辅助HTTP自动跳转HTTPS,还有一些其他的作用未发掘,我只用它的自动跳转功能
composer require wyrihaximus/react-http-middleware-rewrite用法虽然非常适合提供静态文件。 它不(也不会)支持将/index.html用作/ 。 输入重写的中间件,在水下它会将路径从/更改为/index.html以便webroot预...
ts-transform-import-path-rewrite 这是TypeScript AST变形金刚,可让您相应地在输出JS& d.ts文件中重写导入路径。 这样做的主要用例是减轻不同的构建系统导入结构,例如相对import与绝对import以及别名化输出...
使用url-rewrite规则 64位系统
express-url-rewrite 使用模式重写快速request参数 用法 var express = require ( 'express' ) ; var rewrite = require ( 'express-url-rewrite' ) ; var app = express ( ) ; app . use ( rewrite ( 'http://*....
PHP - Rewrite - 伪静态 Rewrite.dll
Grunt Web 服务器重写安装 npm install grunt-web-server-rewrite用法示例: 第一步,创建一个名为rewrite.json的文件,其中是文件夹根目录。 重写.json { "pages": { "/": "index.html", "/products/(.*)": "index....
rewrite_x64_zh-CN
在IIS SQL Server中利用ISAPI ReWrite防SQL注入攻击.pdf