- 浏览: 90314 次
- 性别:
- 来自: 深圳
文章分类
- 全部博客 (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》Chapter3--3.2.4 Index range scan
Index range scan 索引范围扫描
(page 101)
An index range scan is chosen when a predicate contains a condition that will return a range of data.
The index can be unique or non-unique as it is the condition that determines whether or not multiple
rows will be returned or not. The conditions specified can use operators such as <, >, LIKE, BETWEEN and even =. In order for a range scan to be selected, the range will need to be fairly selective. The larger the range, the more likely a full scan operation will be chosen instead.
Listing 3-11 shows an example of a query that will produce an index range scan plan.
当谓词包含条件将返回一范围内的数据时,则(可能)选择“索引范围扫描”。
索引可以是唯一或者不唯一的,因为条件确定了是或不是多行返回或者没有(返回空)。指定的条件可用操作符如<,>,LIKE,BETWEEN和甚至=。为了选中范围扫描,范围必须具有相当的选择性的。范围越大,越可能是选中了全扫描操作。
列表3-11展示了一个查询例子将生成一个索引范围扫描计划。
Listing 3-11. Index Range Scan
SQL> set autotrace traceonly explain
SQL>
SQL> select * from hr.employees where department_id = 60 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2056577954
--------------------------------------------------------------------------------------------------------------------
|
Id | Operation |
Name | Rows | Bytes | Cost(%CPU)|
--------------------------------------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT | | 5 | 340
| 2(0) |
| 1 | TABLE ACCESSBY INDEX ROWID| EMPLOYEES | 5 | 340 | 2(0) |
|*
2 | INDEX RANGE SCAN | EMP_DEPARTMENT_I X | 5 | | 1(0) |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-------------------------------------------------------------------
2 - access("DEPARTMENT_ID"=60)
A range scan wil traverse the index structrue from the root block to
the first leaf block containing an entry matching the specified
condition. From that starting point, a rowid will be retrieved from the
index entry and the table data block will be retrieved (TABLE ACCESS BY
INDEX ROWID). After the first row is retrieved, the index leaf block
will be accessed again and the next entry will be read to retrieve the
next rowid. This back-and-forth between the index leaf blocks and the
data blocks will continue until all the matching index entries have been
read. Therefore, the number of block accesses required will include the
number of branch blocks in the index (this can be found using the
blevel statistic for the index) plus the number of index entries that
match the condition multiplied by two. You have to multiply by two
because each retrieval of a single row in the table will require that
the index leaf block be accessed to retrieve the rowid and then the
table data block will be accessed using that rowid.
Therefor, if the
example returned 5 rows and the blevel was 3, the total block access
required would (5 rows x 2) + 3 = 13.
范围扫描遍历索引结构,从根块到第一个包含匹配指定条件记录的叶块。从那个起点开始,rowid将从索引记录中检索出再用于检索表数据块(TABLE
ACCESS BY INDEX
ROWID)。在第一行检索之后,将再访问索引叶块,读取下一条记录,检索下一个rowid。在索引叶块和数据块之间的不断的来回,直到所有匹配的索引记
录都读取完。因此,需要访问的块数包括索引中的分支块数(这可以通过索引的blevel统计找出)加上匹配条件的索引记录的数量乘以2。你必须乘以2,
因为每次对表中的单行检索都需要访问索引叶块获取rowid,再通过它去表数据块中检索。
因此,如果例子中返回5行且blevel是3,则需要访问的总块
数是(5 rows x 2) + 3 = 13 。
If the range of entries matching the
condition is large enough, it is likely that more than one leaf block
will have to be accessed. When that is the case, the next leaf block
needed can be read using a pointer stored in the current leaf block that
leads to the next leaf blcok (there's also a pointer to the previous
leaf block). Since these pointers exist, there is no need to go back up
to ther branch block to determine where to go next.
如果匹配条件的记录的范围足够的大,很可能需要访问多于一个叶块。这种情况下,可用存于当前叶块的指针指向到下一叶块,来访问所需的下一叶块(也有指向前一叶块的指针)。由于这些指针的存在,就不必回退到分支块,确定下一步如何走了。
When an index range scan is chosen, the predicate information in the
plan will show the condition used to access the index.
In the example,
step 2 in the plan has an asterisk beside it. This is an indicator that
predicate information for that step is listed below the plan. In that
section ,you see an entry showing that the index entry access was
determined using the condition DEPRTMENT_ID = 60.
当选中了索引范围扫描,计划中的谓词信息将展示用于访问索引的条件。
在例子中,计划中的步骤2有一个星号在旁边。这是一个标识,那一步的谓词信息列于计划下面。在那一段,你将看见一记录展示:索引记录访问确定使用条件DEPRTMENT_ID=60。
There are cases when predicates that you might think should use index
range scans do not. For example, if you use a LIKE operator with a
condition that starts with a wild card such as '%abc', the optimizer
will not choose a range scan on an index for that column because the
condition is too broad.Another similar case is when you have a predicate
the uses a column that isn't the leading column in a compound index.
In
that case, as I'll discuss shortly, it is more likely for an index skip
scan to be chosen instead.
有些情况当谓词并没有如你所料的使用索引范围扫描。例如,如果你使用LIKE操作符带有条件,由通配符开始如“%abc”,优化器将不选择那列上的索引范围扫描,因为条件太广了。另一个相似的情况是,当你有一谓词用的列不是复合索引中的前导列。
如果是那样,我将等会讨论,很可能是选中了索引跳跃扫描替代
了。
One final nuance
of an index range scan that I'd like to note
is the ability of an ascending ordered index (the default) to return
rows in descending sorted order. The optimizer may choose to use an
index to access rows via an index even if a full scan might be
warranted. This may occur when the query includes an ORDER BY clause on a
column that is indexed. Since the index is stored in sorted order,
reading rows using the index will mean the rows are retrieved in sorted
order and the need to do a separate sort step can be avoided.
But, what
if the ORDER BY clause is requested in descending order? Since the
index is stored in ascending order, the index couldn't be used for a
descending order request, could it ? Listing 3-12 shows an example of
this behavior and the special range scan operation used to handle it.
索引范围扫描最后的一个细微差别
,我想要提出注意的能力是:(默认情况下)升序排列的索引可返回
呈降序排列
的行。
优化器可能使用索引访问行即使全扫描可能更合理的(保证的,担保的)。当查询包含在索引列上的ORDER
BY子句,这种情况可能就会发生。由于索引是按序存储的,用索引读取行就意味着行是按序读取的,且单独的排序步骤就可以省略。
但是如果ORDER
BY子句请求的是降序了?由于索引是按升序排列的,索引就不能用于降序的请求了,可以么?列表3-12展示了这个行为的一个例子而是用了特殊的范围扫描操作处理它。
Listing 3-12. An Index Range Scan Used to Avoid a Sort
SQL> set autotrace traceonly explain
SQL>
SQL> select * from hr.employees
2 where department_id in (90, 100)
3 order by department_id desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 3707994525
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 612 | 2 (0) |
|
1 | INLIST ITERATOR |
| | | |
| 2 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 9 | 612 | 2 (0) |
|*
3 | INDEX RANGE SCAN DESCENDING
| EMP_DEPARTMENT_IX | 9 | | 1 (0) |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"=90 OR "DEPARTMENT_ID"=100)
filter("DEPARTMENT_ID"=90 OR "DEPARTMENT_ID"=100)
In this case, the index entries are actually read in reverse order to avoid the need for a separate sort.
在这种情况下,索引记录实际上是反序读入的而避免需要单独的排序。
发表评论
-
《Pro Oracle SQL》Chapter3 -- 3.3.5 Outer Joins
2012-04-02 11:41 1279Outer Joins 外连接 (p ... -
《Pro Oracle SQL》Chapter 3 -- 3.3.4 Cartesian Joins
2012-04-01 02:14 1033Cartesian Joins 笛卡尔连接 ... -
《Pro Oracle SQL》Chapter 3 -- 3.3.3 Hash Joins
2012-03-30 02:15 1038Hash Joins 哈希连接 (pa ... -
《Pro Oracle SQL》Chapter 3 -- 3.3.2 Sort-Merge Joins
2012-03-27 22:07 1021Sort-Merge Joins 排序-合并连接 ... -
《Pro Oracle SQL》Chapter 3 -- 3.3.1 Nested Loops Joins
2012-03-25 18:12 1055Nested Loops Joins 嵌套循环连接 ... -
《Pro Oracle SQL》Chapter 3 -- 3.3 Join Methods
2012-03-24 15:51 1032Join Methods 连接方法 ... -
《Pro Oracle SQL》Chapter3--3.2.7 Index Fast Full Scan
2012-03-24 01:09 1543Index Fast Full Scan 索引快速全扫描 ... -
《Pro Oracle SQL》Chapter3--3.2.6 Index Skip Scan
2012-03-23 00:57 1152Index Skip Scan 索引跳跃扫描 ... -
《Pro Oracle SQL》Chapter 3--3.2.5 Index Full Scan
2012-03-22 05:08 955Index Full Scan 索引全扫描 (pa ... -
《Pro Oracle SQL》Chapter3--3.2.3 Index Unique Scan
2012-03-19 05:26 989Index Unique Scan 索引 ... -
《Pro Oracle SQL》Chapter3--3.2.2 Index Scan Types
2012-03-17 23:34 1037Index Scan Types 索引扫描 ... -
《Pro Oracle SQL》Chapter3--3.2 Index Scan Access Methods--3.2.1 Index Structure
2012-03-15 20:04 965Index Scan Access Methods ... -
《Pro Oracle SQL》Chapter3--3.1.4 Full Scans and the Highwater Mark
2012-03-14 20:49 965Full Scans and the Highwater Ma ... -
《Pro Oracle SQL》Chapter3--3.1 Full Scan Access Methods 之二
2012-03-11 23:14 940Full Scans and Throwaway 全扫描和“ ... -
《Pro Oracle SQL》Chapter3--3.1Full Scan Access Methods之一
2012-03-10 18:01 939Chapter3 Access and Join Metho ...
相关推荐
spring 3.2.4 Realease 的所有jar包: spring-context-3.2.4.RELEASE.jar spring-core-3.2.4.RELEASE.jar spring-beans-3.2.4.RELEASE.jar spring-test-3.2.4.RELEASE.jar spring-web-3.2.4.RELEASE.jar spring-aop-...
java spring3.2.4 jar包 spring-aop-3.2.4.RELEASE spring-aspects-3.2.4.RELEASE spring-beans-3.2.4.RELEASE spring-build-src-3.2.4.RELEASE spring-context-3.2.4.RELEASE spring-context-support-3.2.4.RELEASE...
在深入理解`spring-cglib-repack-3.2.4.jar`和`spring-objenesis-repack-2.5.1.jar`这两个jar包之前,我们先来了解一下Spring框架的核心概念。 Spring框架的核心包括依赖注入(Dependency Injection,DI)和面向切...
wstx-asl-3.2.4.jar wstx-asl-3.2.4.jar
spring-orm-3.2.4.RELEASE.jar,ssh整合的时候用的上。
mockito-core-3.2.4
8. **平台兼容性**:MongoDB支持多种操作系统,包括Windows,这里我们讨论的是适用于64位Windows 2008及更高版本的“mongodb-win32-x86_64-2008plus-ssl-3.2.4-signed.msi”安装包。 安装MongoDB的步骤通常包括: ...
支持springmvc框架,版本为3.2.4,Java技术开发使用
MyBatisCodeHelper-Pro-3.2.4-SNAPSHOT-obfuscated.zip
《Spring Framework 3.2.4-RELEASE Maven Source Code 深度解析》 Spring Framework作为Java开发领域的重要支柱,其3.2.4版本在当时具有广泛的影响力。本源码包“spring-framework-3.2.4-RELEASE”不仅包含了Spring...
这款"mongodb-win32-i386-3.2.4-signed.zip"压缩包是专为32位Windows操作系统设计的MongoDB安装资源,包含了MongoDB 3.2.4版本的安装程序。 MongoDB 3.2版是一个重要的里程碑,它引入了多项关键改进和新特性。以下...
- **API稳定**:3.x系列是CXF的稳定版本,3.2.4在3.2.x分支上,意味着API已经相当成熟,开发者可以放心使用。 - **bug修复**:每次新版本发布通常都会修复一些已知的问题,提高整体的软件质量。 - **新特性**:...
赠送原API文档:cglib-nodep-3.2.4-javadoc.jar; 赠送源代码:cglib-nodep-3.2.4-sources.jar; 赠送Maven依赖信息文件:cglib-nodep-3.2.4.pom; 包含翻译后的API文档:cglib-nodep-3.2.4-javadoc-API文档-中文...
开发工具 cglib-3.2.4开发工具 cglib-3.2.4开发工具 cglib-3.2.4开发工具 cglib-3.2.4开发工具 cglib-3.2.4开发工具 cglib-3.2.4开发工具 cglib-3.2.4开发工具 cglib-3.2.4开发工具 cglib-3.2.4开发工具 cglib-3.2.4...
QLExpress-3.2.4.jarQLExpress-3.2.4.jarQLExpress-3.2.4.jarQLExpress-3.2.4.jarQLExpress-3.2.4.jarQLExpress-3.2.4.jarQLExpress-3.2.4.jarQLExpress-3.2.4.jarQLExpress-3.2.4.jarQLExpress-3.2.4.jarQLExpress-...
报错 Imageio: 'ffmpeg-win32-v3.2.4.exe' was not found on your computer; downloading it now. 因为代码会检测在C:\Users\Administrator\AppData\Local\imageio\ffmpeg 目录下有没有'fwmpeag-win32-v3.2.4.exe...
《ActiveMQ Web 3.2.4:Java消息服务与Web集成详解》 Apache ActiveMQ是业界广泛使用的开源消息代理,它遵循Java消息服务(JMS)标准,为分布式环境中的应用程序提供高效、可靠的异步通信。在本篇中,我们将深入...
3. **CXF的功能**: - **代码生成**:CXF的Wsdl2Java工具可以将WSDL文件转换为Java类和服务接口,这样开发者只需要关注业务逻辑,而无需手动编写繁琐的Web服务代码。 - **绑定层**:CXF支持多种绑定,包括JAX-WS...
报错 Imageio: 'ffmpeg-win32-v3.2.4.exe' was not found on your computer; downloading it now. 因为代码会检测在C:\Users\Administrator\AppData\Local\imageio\ffmpeg 目录下有没有'fwmpeag-win32-v3.2.4....
Spring Framework 3.2.4.RELEASE 是一个重要的版本,它是Spring生态系统的里程碑,为开发者提供了全面的Java应用程序开发支持,特别是针对企业级应用。这个版本包含了完整的文档、jar包、以及源代码,使得开发者可以...