INDEX RANGE SCAN DESCENDING的逻辑读问题

最新推荐文章于 2020-07-06 20:29:10 发布
最新推荐文章于 2020-07-06 20:29:10 发布
阅读量439 收藏
点赞数

itpub:http://www.itpub.net/thread-1335784-1-1.html

INDEX RANGE SCAN DESCENDING的逻辑读126明显大于INDEX RANGE SCAN 5，但优化器依然选择了INDEX RANGE SCAN DESCENDING

这里先不讨论优化器的选择，仅仅看看是否INDEX RANGE SCAN DESCENDING的逻辑读会高的这么厉害

下面是测试代码：

[@more@]

SQL> create table emp (id,name) as select object_id,object_name from dba_objects
;

表已创建。

SQL> create index t_idx_emp on emp(id);

索引已创建。

SQL> set arraysize 5000
SQL> select id,name from emp where id between 100 and 1000;

已选择845行。


执行计划
----------------------------------------------------------
Plan hash value: 162245774

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 845 | 66755 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 845 | 66755 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX_EMP | 845 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">=100 AND "ID"<=1000)

Note
-----
- dynamic sampling used for this statement (level=4)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
22102 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
845 rows processed

SQL> select id,name from emp where id between 100 and 1000 order by id;

已选择845行。


执行计划
----------------------------------------------------------
Plan hash value: 162245774

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 845 | 66755 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 845 | 66755 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX_EMP | 845 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">=100 AND "ID"<=1000)

Note
-----
- dynamic sampling used for this statement (level=4)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
22102 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
845 rows processed

SQL> select id,name from emp where id between 100 and 1000 order by id desc;

已选择845行。


执行计划
----------------------------------------------------------
Plan hash value: 1596401432

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 845 | 66755 | 7 (0)|00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 845 | 66755 | 7 (0)|00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| T_IDX_EMP | 845 | | 3 (0)|00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">=100 AND "ID"<=1000)

Note
-----
- dynamic sampling used for this statement (level=4)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
22102 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
845 rows processed

SQL> select /*+ index(emp) */ id,name from emp where id between 100 and 1000 ord
er by id desc;

已选择845行。


执行计划
----------------------------------------------------------
Plan hash value: 1321738087

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 845 | 66755 | 8 (13)|00:00:01 |
| 1 | SORT ORDER BY | | 845 | 66755 | 8 (13)|00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 845 | 66755 | 7 (0)|00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX_EMP | 845 | | 3 (0)|00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("ID">=100 AND "ID"<=1000)

Note
-----
- dynamic sampling used for this statement (level=4)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
22078 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
845 rows processed

SQL> set arraysize 15
SQL> select id,name from emp where id between 100 and 1000;

已选择845行。


执行计划
----------------------------------------------------------
Plan hash value: 162245774

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 845 | 66755 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 845 | 66755 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX_EMP | 845 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">=100 AND "ID"<=1000)

Note
-----
- dynamic sampling used for this statement (level=4)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
121 consistent gets
0 physical reads
0 redo size
29382 bytes sent via SQL*Net to client
1031 bytes received via SQL*Net from client
58 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
845 rows processed

SQL> select id,name from emp where id between 100 and 1000 order by id desc;

已选择845行。


执行计划
----------------------------------------------------------
Plan hash value: 1596401432

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 845 | 66755 | 7 (0)|00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 845 | 66755 | 7 (0)|00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| T_IDX_EMP | 845 | | 3 (0)|00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">=100 AND "ID"<=1000)

Note
-----
- dynamic sampling used for this statement (level=4)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
121 consistent gets
0 physical reads
0 redo size
29382 bytes sent via SQL*Net to client
1031 bytes received via SQL*Net from client
58 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
845 rows processed

SQL> select id,name from emp where id between 100 and 1000 order by id;

已选择845行。

执行计划
----------------------------------------------------------
Plan hash value: 162245774

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 845 | 66755 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 845 | 66755 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX_EMP | 845 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">=100 AND "ID"<=1000)

Note
-----
- dynamic sampling used for this statement (level=4)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
121 consistent gets
0 physical reads
0 redo size
29382 bytes sent via SQL*Net to client
1031 bytes received via SQL*Net from client
58 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
845 rows processed

SQL> select /*+ index(emp) */ id,name from emp where id between 100 and 1000 ord
er by id desc;

已选择845行。


执行计划
----------------------------------------------------------
Plan hash value: 1321738087

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 845 | 66755 | 8 (13)|00:00:01 |
| 1 | SORT ORDER BY | | 845 | 66755 | 8 (13)|00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 845 | 66755 | 7 (0)|00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX_EMP | 845 | | 3 (0)|00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("ID">=100 AND "ID"<=1000)

Note
-----
- dynamic sampling used for this statement (level=4)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
26058 bytes sent via SQL*Net to client
1031 bytes received via SQL*Net from client
58 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
845 rows processed

对于select /*+ index(emp) */ id,name from emp where id between 100 and 1000 ord
er by id desc;
貌似不受arraysize的影响，不是经过了特殊的优化，就是个BUG。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19423/viewspace-1036681/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19423/viewspace-1036681/

ct827519
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
LinkedList_Descending
02-17
标题“LinkedList_Descending”暗示了我们正在探讨一个与LinkedList数据结构相关的主题，特别是关于如何以降序（Descending）顺序操作或遍历LinkedList。LinkedList是一种线性数据结构，它在计算机科学中广泛使用，...
索引扫描
EdwinKoo的专栏
07-21 2967
索引行的存储顺序与表中行的存储顺序之间的相似程度被称为聚簇因子。聚簇因子会随着这种相似程度的不同而不同。         当相类似行的密集程度比较高时，这些数据行就会被密集地存储在相对较少的数据块中，这是聚簇因子比较好的情况。相反，数据行被分散地存储在多个数据块中就是聚簇因子比较
27 分页语句优化--优化主题系列
leo0805的博客
01-08 325
分页语句 一般都有orderby column desc/asc 或者说 分页语句里直接就是where rownum xx 一般只返回20行或者50行 一般不会一页显示超过100行   分页语句的优化技巧： 1.分页SQL要想快最好走索引根据orderby asc/desc 用hintindex_asc/index_desc   强制它走索引INDEXFULL SCAN  DES
在OLTP中，索引访问的几种常规操作
数据库
11-13 860
在OLTP中，索引访问的几种常规操作 1、index(unique scan):唯一索引扫描，即对唯一索引进行单一匹配访问。在唯一索引中，每一个非空健值只会存在一条。主键本身是一个没有null值的唯一索引。 ------------------------------------------------------------------------------------------ |
索引范围扫描(INDEX RANGE SCAN)
weixin_33762130的博客
04-28 786
      索引范围扫描（INDEX RANGE SCAN）适用于所有类型的B树索引，当扫描的对象是唯一性索引时，此时目标SQL的where条件一定是范围查询（谓词条件为 BETWEEN、&lt;、&gt;等）；当扫描的对象是非唯一性索引时，对目标SQL的where条件没有限制（可以是等值查询，也可以是范围查询）。 索引范围扫描的结果可能会返回多条记录，其实这就是索引范围扫描中"范围"二字的本质含...
oracle 全扫描和索引扫描
天元喜羊羊的博客
03-28 1935
总共只有两种扫描方式：全表扫描和索引扫描。 索引扫描还分几种：索引范围扫描、索引唯一扫描、索引全扫描、索引跳跃扫描、索引快速全扫描。 先创建数据用于测试： CREATE TABLE TB_USER (     ID INTEGER NOT NULL,     USER_NAME VARCHAR2(20) NOT NULL,     USER_AGE INTEGER NOT NUL
index索引的8种使用模式
11-04
如果`object_type`上有索引，那么此查询将触发INDEX RANGE SCAN，因为Oracle只需要检索索引中类型为'TABLE'的记录。 ### 5. INDEX SKIP SCAN（索引跳跃扫描） INDEX SKIP SCAN用于多列复合索引，其中查询条件仅...
前端开源库-sanctuary-descending
08-30
在JavaScript中，实现降序排序并不直观，sanctuary-descending库就是为了解决这个问题而生。它提供了一种优雅的方式来处理降序排序，使代码更简洁、易读，并保持函数式的风格。 "Fantasy Land"是一个JavaScript中的...
Python列表list内建函数用法实例分析【insert、remove、index、pop等】
09-21
print("Sorted list (descending):", my_list) # 使用count统计元素数量 count_of_5 = my_list.count(5) print("Count of 5:", count_of_5) # 使用extend扩展列表 another_list = [7, 8, 9] my_list.extend...
WRS2_descending.rar
08-27
"WRS2_descending.rar"这个文件标题暗示我们它可能与Worldwide Reference System 2（WRS-2）有关，这是一个全球卫星影像的探测系统，常用于 Landsat 系列卫星的数据组织。"descending"可能指的是卫星的轨道方向，即...
Oracle 索引扫描的五种类型
热门推荐
David Dai -- Focus on Oracle
08-31 2万+
之前在讨论CBO和RBO的时候提到了索引扫描的几种类型。Oracle Optimizer CBO RBOhttp://blog.csdn.net/tianlesoftware/archive/2010/08/19/5824886.aspxOracle 索引详解http://blog.csdn.net/tianlesoftware/archive/2010/03/05/5347098.aspxOracle Explain Planhttp://blog.csdn.net/tianlesoftware/arch
深入理解Oracle索引(1)：INDEX SKIP SCAN 和 INDEX RANGE SCAN
数据库
01-28 832
㈠ Index SKIP SCAN当表有一个复合索引，而在查询中有除了索引中第一列的其他列作为条件，并且优化器模式为CBO，这时候查询计划就有可能使用到SS Skip scan会探测出索引前导列的唯一值个数，每个唯一值都会作为常规扫描的入口，在此基础上做一次查找，最后合并这些查询 例如：表employees (sex, employee_id, address) ，有一个组合索引(s...
警惕 Oracle 索引优化时陷阱之无效的索引范围扫描(INDEX RANGE SCAN)导致的全表扫描
Defonds 的专栏
07-06 2764
生产环境慢查询统计中，发现表 STATUS 的 MILESTONE 字段条件查询时进行了全表扫描。表 STATUS 的 MILESTONE 字段定义如下： 针对上述问题创建索引： create index STATUS_MILESTONE on STATUS("MILESTONE") tablespace DFS_INDEX2; 分析执行计划发现问题语句依然走的是全表扫描，新建的索引 STATUS_MILESTONE 没有用到。第一反应是索引是不是没有创建成功？ 查看索引库发现该索引确实存在并且生效：
index range scan(索引范围扫描)的计划分析
一切都是浮云
04-15 8204
 一、index range scan(索引范围扫描)： 1、对于unique index（唯一性索引，是带唯一性约束的，普通索引则没有唯一性约束。scott.emp表是主键，带有唯一性约束，同时有索引Rowid）来说，如果where 条件后面出现了&lt;,&gt; ,between ...and...的时候，那么就可能执行index range scan,如果where条件后面是=，那么...
使用倒序索引提升ORDER BY DESC性能
IT圈黎俊杰
06-30 1万+
使用倒序索引（INDEX DESC），可以大幅提升带有order by desc子句的SQL语句性能。
oracle使用倒序索引提升ORDER BY DESC性能
很多时候犯错都是在不知情的情况下发生的
04-13 1万+
使用倒序索引（INDEX DESC），可以大幅提升带有order by desc子句的SQL语句性能。 另一种方式就是创建一个倒序索引，如：create index t_idx on table_name（time  desc）;这种创建时，索引最左边的值就是最晚的时间插入的数据，好像数据就是往左增长一样，此时索引从左往右扫描的时候就是最大的时间了，也是比较实用的。一、场景1、表名：test_t，...
index range scan
loryliu的专栏
08-14 879
The index range scan starts at the root block.  Then, traverses the branch blocks to find the first leaf block that contains relevant data. When it gets to a leaf block, it is the leaf block with t
Write code to check if the elements are in descending order
最新发布
06-08
Sure, here's an example code snippet in Python to check if the elements of a list are in descending order: ``` def is_descending(lst): for i in range(1, len(lst)): if lst[i] > lst[i-1]: return False return True ``` This function takes a list `lst` as input and checks if its elements are in descending order. It does this by iterating over the list using a `for` loop and comparing each element to the previous one. If it finds an element that is greater than the previous one, it immediately returns `False`, indicating that the list is not in descending order. If it reaches the end of the list without finding any out-of-order elements, it returns `True`, indicating that the list is in descending order.
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值