- 浏览: 90684 次
- 性别:
- 来自: 深圳
文章分类
- 全部博客 (81)
- 读书笔记 (14)
- NetBeans学习 (1)
- JavaBeans and Bean Events (3)
- 《Pro Oracle SQL》Chapter 2 SQL Execution (13)
- 《Pro Oracle SQL》Chapter 3 Access and Join Methods (16)
- Pro Oracle SQL Chapter 5 (0)
- Pro Oracle SQL Chapter 6 (0)
- Pro Oracle SQL Chapter 7 (9)
- Pro Oracle SQL Chapter 8 (9)
- 《Pro Oracle SQL》Chapter 9 The Model Clause (11)
- 《Pro Oracle SQL》Chapter 10 Subquery Factoring (7)
最新评论
-
mojunbin:
这个不能不顶。
《Pro Oracle SQL》 Chapter2--2.1 Oracle Architecture Basics -
Branding:
谢谢,获益匪浅
《Pro Oracle SQL》--chapter 5--5.6 Building Logical Expressions -
Branding:
《Pro Oracle SQL》--Chapter 5--5.4 Questions about the Question -
Branding:
谢谢
《Pro Oracle SQL》 翻译序 -- 读书心得 -
jiaoshiguoke:
继续 加油
《Pro Oracle SQL》--Chapter 6--6.1 Explain Plans--之三
《Pro Oracle SQL》CHAPTER2--2.8 Subquery Unnesting
Subquery Unnesting 子查询反嵌套
(page 66)
Subquery unnesting is similar to view merging in that just like a view a subquery is represented by a
separate query block. The main difference between mergeable views and subqueries that can be
unnested is location: Subqueries located within the WHERE clause are reviewed for unnesting by the
transformer.
The most typical transformation is to convert the subquery into a join. If a subquery isn’t
unnested, a separate subplan will be generated for it and executed in an order within the overall plan
that allows for optimal execution speed.
子查询反嵌套相似于视图合并,就像一个视图一样一个子查询代表一个单独的查询块。可合并的视图与可反嵌套的子查询之间主要的不同是位置:位于WHERE子句中的子查询由变换器检查(是否能)反嵌套。
最典型的变换是转换子查询成连接。如果一子查询不能反嵌套,将生成一单独的子计划且在整个计划中按一定顺序,以最佳的执行速度,执行。
When the subquery is not correlated, the transformed query is very straightforward, as shown in Listing 2-6.
当子查询不是相关的,变换的查询是非常直接的,如列表2-6所示。
Listing 2-6. Unnesting Transformation of an Uncorrelated Subquery 一不相关子查询的反嵌套查询
SQL> set autotrace traceonly explain
SQL>
SQL> select * from employees where department_id in (select department_id from departments);
Execution Plan
----------------------------------------------------------
Plan hash value: 169719308
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 7632 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 106 | 7632 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | 4 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
The subquery in this case is simply merged into the main query block and converted to a table join.
The query plan is derived as if the statement were written as follows:
在本例的子查询直接合并入主查询块且转换成表连接。派生出的查询计划就像如下语句写的:
select e.*
from employees e, departments d
where e.department_id = d.department_id
Using the
NO_UNNEST
hint, I could have forced the query to be optimized as written, which would mean that a separate subplan would be created for the subquery
(as shown in Listing 2-7).
使用NO_UNNEST
提示,我就能强制优化查询如所写的,意味着将为子查询创建单独的子计划
(如列表2-7所示)
Listing 2-7. Using the NO_UNNEST Hint
SQL> select employee_id, last_name, salary, department_id
2 from employees
3 where department_id in
4 (select /*+ NO_UNNEST */department_id
5 from departments where location_id > 1700);
Execution Plan
----------------------------------------------------------
Plan hash value: 4233807898
--------------------------------------------------------------------------------------------
|
Id | Operation |
Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT | | 10 | 190 | 14 (0) |
00:00:01 |
|* 1 |
FILTER | | | |
| |
| 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | 2033 | 3 (0) | 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 7 | 1 (0) | 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "HR"."DEPARTMENTS"
"DEPARTMENTS" WHERE "DEPARTMENT_ID"=:B1 AND "LOCATION_ID">1700))
3 - filter("LOCATION_ID">1700)
4 - access("DEPARTMENT_ID"=:B1)
The main difference between the plans is that without query transformation, a FILTER operation is
chosen instead of a NESTED LOOPS join. I’ll discuss both of these operations in detail in Chapters 3 and 6, but for now just note that the FILTER operation typically represents a less efficient way of accomplishing a match, or join, between two tables.
You can see that the subquery remains intact
if you look at the Predicate Information for step 1. What happens with this “as is” version is that for each row in the employees table, the subquery must execute using the employees table department_id column as a bind variable for comparison with the list of department_ids returned from the execution of the subquery. Since there are 107 rows in the employees table, the subquery will execute once for each row. That’s not precisely what happens due to a nice optimization feature Oracle uses called subquery caching, but hopefully you can see that executing the query for each row isn’t as efficient as joining the two tables. I’ll discuss the details of these operations and review why the choice of a NESTED LOOPS join is more efficient than the FILTER operation in the chapters ahead.
两个计划的主要不同在于:没有查询变换,选择是过滤器(FILTER)操作而非嵌套循环(NESTED
LOOPS)连接。我将在第3,6章深入讨论这些操作,但是现在仅需要注意:在两表之间进行完成一次匹配或连接,FILTER操作通常代表一种低效率方
式。
如果你看到第一步的谓词信息就发现子查询(在这里)原封不动
。在“等价”版的(执行计划)发生的是:对于employees表的每行,子查询必须执行使用
employees表的department_id列作为绑定变量与子查询执行返回的department_ids列表的比较。由于在employees
表中有107行,子查询将在每行都执行一次。由于Oracle使用了一好的优化特性“子查询缓存”,所发生的(执行107次)是不准确的。我将详细讨论这些操作和复习在本章开头(所述的)为什么选择嵌套循环连接比过滤器操作会更加有效率。
The subquery unnesting transformation is a bit more complicated when a correlated subquery is involved. In this case, the correlated subquery is typically transformed into a view, unnested, and then
joined to the table in the main query block. Listing 2-8 shows an example of subquery unnesting of a
correlated subquery.
当一个相关子查询涉及时子查询反嵌套变换就会有些复杂。在这种情况下,相关子查询通常变换成视图,反嵌套,然后同主查询块的表连接。列表2-8展示了一个子查询反嵌套一个相关子查询的例子。
Listing 2-8. Unnesting Transformation of a Correlated Subquery
SQL> select outer.employee_id, outer.last_name, outer.salary, outer.department_id
2 from employees outer
3 where outer.salary >
4 (select avg(inner.salary)
5 from employees inner
6 where inner.department_id = outer.department_id)
7 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2167610409
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 765 | 8 (25) | 00:00:01 |
|* 1 | HASH JOIN | | 17 | 765 | 8 (25) | 00:00:01 |
| 2 | VIEW | VW_SQ_1 | 11 | 286 | 4 (25) | 00:00:01 |
| 3 | HASH GROUP BY | | 11 | 77 | 4 (25) | 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0) | 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 2033 | 3 (0) | 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPARTMENT_ID"="OUTER"."DEPARTMENT_ID")
filter("OUTER"."SALARY">"VW_COL_1")
Notice in this example how the subquery is transformed into an in-line view, then merged with the
outer query and joined. The correlated column becomes the join condition and the rest of the subquery
is used to formulate an inline view. The rewritten version of the query would look something like this:
注意在本例中子查询是如何变换成内联视图的,然后同外部的查询块合并和连接的。相关列变成了连接条件然后子查询的其余部分用于形成一内联视图。查询的重写版本将看的像如下语句:
select outer.employee_id, outer.last_name, outer.salary, outer.department_id
from employees outer,
(select department_id, avg(salary) avg_sal
from employees
group by department_id) inner
where outer.department_id = inner.department_id
and outer.salary > inner.avg_sal; (注:这里原文中少了这句条件,具体查看附件List_2-8.rar)
Subquery unnesting behavior is controlled by the hidden parameter _unnest_subquery that defaults
to TRUE in version 9 and above. This parameter is specifically described as controlling unnesting
behavior for correlated subqueries. Just like with view merging, starting in version 10, transformed
queries are reviewed by the optimizer, and the costs are evaluated to determine whether or not an
unnested version would be the least costly.
子查询反嵌套行为被隐式参数_unnest_subquery所控制,在版本9之前默认值是TRUE。这个参数特别地描述控制对相关子查询的反嵌套行为。正如视图合并,从版本10开始,优化器检查变换过的查询,然后评估成本确定是否非反嵌套版本的成本更低。
- List_2-8.rar (283 Bytes)
- 下载次数: 4
发表评论
-
《Pro Oracle SQL》Chapter2--2.13 SQL Execution – Putting It All Together
2012-02-10 05:26 1164SQL Execution – Putting It All ... -
《Pro Oracle SQL》CHAPTER2--2.12Executing the Plan and Fetching Rows
2012-02-09 09:41 1184Executing the Plan and Fetching ... -
《Pro Oracle SQL》CHAPTER2--2.11 Determining the Execution Plan
2012-02-04 20:09 1067Determining the Execution Plan ... -
《Pro Oracle SQL》CHAPTER2--2.10 Query Rewrite with Materialized Views
2012-01-27 13:27 1008Query Rewrite with Materialized ... -
《Pro Oracle SQL》CHAPTER2--2.9 Predicate Pushing
2012-01-25 20:51 1339Predicate Pushing 谓词推进 ... -
《Pro Oracle SQL》CHAPTER2--2.7 View Merging
2012-01-22 06:36 1252View Merging 视图合并 (Page 6 ... -
《Pro Oracle SQL》CHAPTER2--2.6Query Transformation
2012-01-18 15:17 759Query Transformation 查询变换 ... -
《Pro Oracle SQL》Charpter2 --2.5 SGA – The Buffer Cache
2012-01-15 16:10 1067SGA – The Buffer Cache SGA-缓 ... -
《Pro Oracle SQL》Chapter 2--2.4 Identical Statements
2012-01-14 14:42 9632.4 Identical Statements 同 ... -
《Pro Oracle SQL》CHAPTER2--2.3 The Library Cache
2012-01-10 22:00 802The Library Cache 库缓存 ... -
《Pro Oracle SQL》Chapter2-2.2 SGA – The Shared Pool
2012-01-08 16:16 9262.2 SGA – The Shared Pool ... -
《Pro Oracle SQL》 Chapter2--2.1 Oracle Architecture Basics
2012-01-03 11:13 1297Chapter 2 SQL Execution ...
相关推荐
《Pro Oracle SQL》是Oracle数据库查询的一本权威指南,其中Chapter 9深入探讨了SQL查询的各种高级技巧。在9.11章节中,主要讲解了Subquery Factoring,也被称为Common Table Expression(CTE),这是一个非常实用且...
《Pro Oracle SQL》第10章"Subquery Factoring"深入探讨了这一主题,尤其是10.1节“Standard Usage”部分,讲解了子查询在实际应用中的常见用法。在本章节中,作者可能详细阐述了如何利用子查询来优化SQL语句,提高...
这一概念在《Pro Oracle SQL》的第10章第2.5节中得到了深入的探讨,该章节专门讲解如何将Subquery Factoring应用到PL/SQL中。在本篇文章中,我们将深入理解这一强大的特性,并结合提供的SQL脚本文件进行解析。 首先...
Pro Oracle SQL unlocks the power of SQL in the Oracle Database—one of the most potent SQL implementations on the market today. To master it requires a three-pronged approach: learn the language ...
Objectives 3-2 Review of Group Functions 3-3 Review of the GROUP BY Clause 3-4 Review of the HAVING Clause 3-5 GROUP BY with ROLLUP and CUBE Operators 3-6 ROLLUP Operator 3-7 ROLLUP Operator Example 3...
本资料“oracle-function--api.zip”包含了Oracle数据库中的一些核心API和SQL函数,特别是针对字符串、日期和数字类型的处理,对于Oracle开发人员来说极具参考价值。 一、Oracle SQL API Oracle SQL API是Oracle...
Oracle SQL 是一种强大的数据库查询语言,用于从关系型数据库中检索、更新和管理数据。本实例主要探讨了如何使用复杂的查询语句,包括子查询和外连接,来处理多表查询。 1. 子查询(Subquery) 子查询是嵌套在其他...
### Oracle SQL Recipes: A Problem-Solution Approach #### 核心知识点概述 《Oracle SQL Recipes: A Problem-Solution Approach》是一本面向Oracle SQL开发者和技术人员的专业书籍。本书通过一系列实际问题和...
子查询是在一个SQL查询中嵌套另一个完整的SQL查询,它可以作为选择、比较或者联接的数据源。在Laravel的Eloquent中,可以使用`DB::raw()`方法创建子查询。Eloquent Subquery Magic则提供了更高级的API,使得子查询...
**1.1.2 PL/SQL连接数据库配置,Oracle客户端的配置文件** - **用途:** 配置客户端环境,以便能够连接到Oracle数据库。 - **配置文件:** `tnsnames.ora` 和 `sqlnet.ora` **1.1.3 配置Oracle数据库监听Assistant...
Java笔记--Oracle中SQL不能用Limit的处理 在Java开发中,我们经常需要对数据库进行分页查询,但是Oracle数据库不支持Limit关键字,那么如何在Oracle中实现分页查询呢?下面,我们将详细介绍Oracle中SQL不能用Limit...
10. **SQL标准与方言**: ANSI SQL是通用标准,但各数据库系统如MySQL、Oracle、SQL Server等有其特定的语法和功能。 这份“SQL-grammer-collection.doc”文档将这些知识点逐一阐述,是学习和提升SQL技能的宝贵资源...
### SQL语句教程知识点详解 #### 一、SELECT语句 **定义与作用:** - `SELECT` 是SQL中最常用的语句之一,主要用于从数据库表中检索数据。 - **基本语法**: ```sql SELECT column_name FROM table_name; ``` - ...
SQL(Structured Query Language)是用于管理和处理关系数据库的标准语言,其功能强大且广泛应用于各种数据库系统,如MySQL、Oracle、SQL Server等。本资料包主要包含四个方面的内容,分别是SQL基本查询操作、多表...
《专业Oracle SQL》一书由Karen Morton、Kerry Osborne、Robyn Sands、Riyaj Shamsudeen和Jared Still联合编写,旨在深入探讨Oracle数据库中的SQL语言及其支持功能,帮助读者全面掌握并充分利用SQL的强大能力。...
Oracle SQL 是一种强大的数据库查询和管理语言,广泛用于企业级数据管理和分析。在这个文档中,NiCoBee 提供了一组 Oracle SQL 的练习题,旨在帮助学习者掌握各种查询技术。我们将深入探讨其中的一些关键概念。 1. ...
### Oracle Interview Questions详解 ...以上内容涵盖了Oracle面试中常见的问题及其解答,这些问题涵盖了从基本的SQL操作到更高级的数据库管理技巧,对于准备Oracle面试的人来说是非常有用的资源。
本篇将深入探讨“基于Oracle的SQL优化典型案例分析”,旨在帮助数据库管理员和开发人员提升SQL查询效率,降低系统负载,从而提高整体系统性能。 1. **SQL执行计划分析** - Oracle通过执行计划来确定如何执行SQL...