oracle执行计划走索引的方式,执行计划中常见index访问方式 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429...

最新推荐文章于 2022-05-31 17:47:55 发布
最新推荐文章于 2022-05-31 17:47:55 发布
阅读量249 收藏
点赞数
文章标签： oracle执行计划走索引的方式

近期有朋友对于单个表上的index各种情况比较模糊,这里对于单个表上,单个index出现的大多数情况进行了总结性测试,给出了测试结果,至于为什么出现这样的试验结果未做过多解释,给读者留下思考的空间.本篇文章仅仅是为了测试hint对index的影响,而不是说明走各种index方式的好坏.参考: INDEX FULL SCAN vs INDEX FAST FULL SCAN

创建表模拟测试

SQL> create table t_xifenfei as select object_id,object_name from dba_objects;

Table created.

SQL> create index i_t_object_id on t_xifenfei(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(USER,'T_XIFENFEI',cascade=>true);

PL/SQL procedure successfully completed.

SQL> desc t_xifenfei

Name Null? Type

----------------------------------------- -------- ----------------------------

OBJECT_ID NUMBER

OBJECT_NAME VARCHAR2(128)

TABLE ACCESS FULL

SQL> SET AUTOT TRACE EXP STAT

SQL> SELECT OBJECT_ID FROM T_XIFENFEI;

49838 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 548923532

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 49838 | 243K| 57 (2)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T_XIFENFEI | 49838 | 243K| 57 (2)| 00:00:01 |

--------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3544 consistent gets

0 physical reads

0 redo size

721203 bytes sent via SQL*Net to client

36927 bytes received via SQL*Net from client

3324 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

49838 rows processed

SQL> SELECT /*+ INDEX(T i_t_object_id) */ OBJECT_ID FROM T_XIFENFEI;

49838 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 548923532

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 49838 | 243K| 57 (2)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T_XIFENFEI | 49838 | 243K| 57 (2)| 00:00:01 |

--------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3544 consistent gets

0 physical reads

0 redo size

721203 bytes sent via SQL*Net to client

36927 bytes received via SQL*Net from client

3324 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

49838 rows processed

从上面的执行计划中可知,此时走了全表扫描. 由于我们需要查询的列为object_id,因此理论上只需要读取索引就应该可以返回所有数据,而此时为什么是全表扫描呢？ 这是因为NULL值与索引的特性所决定的.即null值不会被存储到B树索引.因此应该为表 t_xifenfei 的列 object_id 添加 not null 约束.

INDEX FAST FULL SCAN

SQL> alter table t_xifenfei modify(object_id not null);

Table altered.

SQL> SELECT object_id from t_xifenfei;

49838 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2036340805

--------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 49838 | 243K| 27 (4)| 00:00:01 |

| 1 | INDEX FAST FULL SCAN| I_T_OBJECT_ID | 49838 | 243K| 27 (4)| 00:00:01 |

--------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3432 consistent gets

0 physical reads

0 redo size

721203 bytes sent via SQL*Net to client

36927 bytes received via SQL*Net from client

3324 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

49838 rows processed

INDEX FAST FULL SCAN:当在高速缓存中没有找到所需的索引块时,则根据db_file_multiblock_read_count的值进行多块读操作.对于索引的分支结构只是简单的获取,然后扫描所有的叶结点.其结果是导致索引结构没有访问,获取的数据没有根据索引键的顺序排序.INDEX FAST FULL SCAN使用multiblock_read,故产生db file scattered reads 事件.

INDEX RANGE SCAN

SQL> select object_id from t_xifenfei where object_id<10;

8 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2197008162

----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 10 | 2 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| I_T_OBJECT_ID | 2 | 10 | 2 (0)| 00:00:01 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("OBJECT_ID"<10)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

499 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

8 rows processed

SQL> select /*+ index_ffs(t i_t_object_id) */ object_id from t_xifenfei t where object_id<10;

8 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2036340805

--------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 10 | 27 (4)| 00:00:01 |

|* 1 | INDEX FAST FULL SCAN| I_T_OBJECT_ID | 2 | 10 | 27 (4)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID"<10)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

118 consistent gets

0 physical reads

0 redo size

499 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

8 rows processed

1

这里可以看出index_ffs已经生效,但是对于这样的情况hint index_ffs效率一般来说不会太高.

INDEX FULL SCAN

1

SQL> SELECT /*+ INDEX(T i_t_object_id) */ object_id from t_xifenfei t;

49838 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 431110666

----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 49838 | 243K| 113 (2)| 00:00:02 |

| 1 | INDEX FULL SCAN | I_T_OBJECT_ID | 49838 | 243K| 113 (2)| 00:00:02 |

----------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3426 consistent gets

0 physical reads

0 redo size

721203 bytes sent via SQL*Net to client

36927 bytes received via SQL*Net from client

3324 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

49838 rows processed

INDEX FULL SCAN:完全按照索引存储的顺序依次访问整个索引树.当访问到叶结点之后,按照双向链表方式读取相连节点的值.换言之,对于索引上所有的数据是按照有序的方式来读取的.如果索引块没有在高速缓存中被找到时,则需要从数据文件中单块进行读取.对于需要读取大量数据的全索引扫描而言,这将使其变得低效.INDEX FULL SCAN使用single read,故产生db file sequential reads事件.新版的Oracle支持db file parallel reads方式.

HINT INDEX不会使用INDEX FAST FULL SCAN功能.

INDEX列ORDER BY

SQL> SELECT OBJECT_ID FROM T_XIFENFEI order by object_id ;

49838 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 431110666

----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 49838 | 243K| 113 (2)| 00:00:02 |

| 1 | INDEX FULL SCAN | I_T_OBJECT_ID | 49838 | 243K| 113 (2)| 00:00:02 |

----------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3426 consistent gets

0 physical reads

0 redo size

721203 bytes sent via SQL*Net to client

36927 bytes received via SQL*Net from client

3324 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

49838 rows processed

SQL> SELECT OBJECT_ID FROM T_XIFENFEI order by object_id desc;

49838 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2808014233

--------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 49838 | 243K| 113 (2)| 00:00:02 |

| 1 | INDEX FULL SCAN DESCENDING| I_T_OBJECT_ID | 49838 | 243K| 113 (2)| 00:00:02 |

--------------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3427 consistent gets

0 physical reads

0 redo size

721203 bytes sent via SQL*Net to client

36927 bytes received via SQL*Net from client

3324 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

49838 rows processed

SQL> SELECT /*+ index_ffs(t i_t_object_id) */ object_id from t_xifenfei t order by object_id;

49838 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2527678987

-----------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

-----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 49838 | 243K| | 185 (4)| 00:00:03 |

| 1 | SORT ORDER BY | | 49838 | 243K| 1192K| 185 (4)| 00:00:03 |

| 2 | INDEX FAST FULL SCAN| I_T_OBJECT_ID | 49838 | 243K| | 27 (4)| 00:00:01 |

-----------------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

117 consistent gets

0 physical reads

0 redo size

721203 bytes sent via SQL*Net to client

36927 bytes received via SQL*Net from client

3324 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

49838 rows processed

对于index 列排序,默认情况下会使用INDEX FULL SCAN/INDEX FULL SCAN DESCENDING而不选择使用INDEX FAST FULL SCAN,因为INDEX FAST FULL SCAN获得数据后,还需要做一次SORT ORDER BY操作

INDEX FAST FULL SCAN+SORT AGGREGATE

SQL> SELECT count(object_id) FROM T_XIFENFEI;

Execution Plan

----------------------------------------------------------

Plan hash value: 3095383276

-------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 27 (4)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FAST FULL SCAN| I_T_OBJECT_ID | 49838 | 27 (4)| 00:00:01 |

-------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

117 consistent gets

0 physical reads

0 redo size

421 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> SELECT /*+ INDEX(T i_t_object_id) */ count(object_id) FROM T_XIFENFEI t;

Execution Plan

----------------------------------------------------------

Plan hash value: 3079973526

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 113 (2)| 00:00:02 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FULL SCAN| I_T_OBJECT_ID | 49838 | 113 (2)| 00:00:02 |

--------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

111 consistent gets

0 physical reads

0 redo size

421 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

sort aggregate通常发生在使用一些聚合函数的时候,sum(),avg(),min(),max(),count()等等,实际上sort aggregate不做真正的sort,并不会用到排序空间,而是通过一个全局变量＋全表或全索引扫描来实现.这样的操作在默认情况下使用INDEX FAST FULL SCAN

INDEX FULL SCAN (MIN/MAX)

SQL> SELECT max(object_id) FROM T_XIFENFEI;

Execution Plan

----------------------------------------------------------

Plan hash value: 2939893782

--------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| 2 | INDEX FULL SCAN (MIN/MAX)| I_T_OBJECT_ID | 49838 | 243K| 2 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

419 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> SELECT /*+ index_ffs(t i_t_object_id) */ max(object_id) FROM T_XIFENFEI t;

Execution Plan

----------------------------------------------------------

Plan hash value: 2939893782

--------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 27 (4)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| 2 | INDEX FULL SCAN (MIN/MAX)| I_T_OBJECT_ID | 49838 | 243K| 27 (4)| 00:00:01 |

--------------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

419 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

对于这样的查询INDEX FULL SCAN (MIN/MAX)明显是最优,但是此处奇怪的是使用了index_ffs提示无效,如果有知道的朋友,麻烦告知原因.

沈阳无距科技
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle SQL执行计划操作（2）——索引相关操作
数据库生态圈（RDB & NoSQL & Bigdata）——专注于关系库优化（Oracle&Mysql&PG&SQL Server ）
11-14 663
本文详细说明了Oracle SQL语句执行计划中索引相关操作，同时，结合实例对其进行了深入讲解。
oracle+mysql+sql server 数据库字典表生成，生成表字段，注释，索引等数据库表结构数据 支持在线化
最新发布
01-17
oracle+mysql+sql server 数据库字典表生成，生成表字段，注释，索引等数据库表结构数据。 支持在线化。
执行计划-数据访问方式（全表扫描与4种索引的方式）
张冲andy的博客
12-27 3288
执行计划 Oracle执行计划的相关概念：  Rowid：系统给oracle数据的每行附加的一个伪列，包含数据表名称，数据库id，存储数据库id以及一个流水号等信息，rowid在行的生命周期内唯一。  Recursive sql：为了执行用户语句，系统附加执行的额外操作语句，譬如对数据字典的维护等。  Row source（行源）：oracle执行步
执行计划中常见index访问方式(转)
weixin_34128237的博客
09-01 130
近期有朋友对于单个表上的index各种情况比较模糊,这里对于单个表上,单个index出现的大多数情况进行了总结性测试,给出了测试结果,至于为什么出现这样的试验结果未做过多解释,给读者留下思考的空间.本篇文章仅仅是为了测试hint对index的影响,而不是说明走各种index方式的好坏.参考: INDEX FULL SCAN vs INDEX FAST FULL SCAN创建表模拟测试 ...
Oracle访问索引的执行计划（一）
cuishan5183的博客
05-25 222
Oracle中常用的索引有B树索引和位图索引，先来看B树索引。 访问B树索引也有很多方式，最简单的是索引唯一扫描，出现在where条件的字段有唯一索引，且使用等值查询的时候，返回的记录至多1条。索引唯一扫描的执行计划为I...
oracle 执行计划索引,Oracle 索引和执行计划
weixin_32244597的博客
04-13 347
建了个测试的数据表，想测试建了索引和不建立索引的区别。建立表的数据量为108631962行。每次插入9999999行，每次大概半个小时。在id上建立索引，花时间为37秒，不建立索引花时间为：1分58秒。演示如下所示：SQL> insert into studyindex1 select rownum id,'db'dbms_random.value(2 1,100) name,dbms_...
数据库设计评审单 oracle/mysql/sqlserver
06-22
在Oracle、MySQL和SQL Server等主流数据库系统中，设计评审是一项至关重要的任务，旨在确保数据库的性能、一致性、可维护性和安全性。以下是对给定文件中提到的各个检查项目的详细说明： 1. **数据库设计的一般要求...
SqlServer,Oracle,MySql数据库结构相互对比同步.rar
12-14
标题中的“SqlServer,Oracle,MySql数据库结构相互对比同步”是指对这三种主流关系型数据库管理系统（RDBMS）在数据库结构方面的比较和同步方法。这些数据库系统在数据存储、查询语言、安全性、性能优化等方面都有...
Oracle|Sqlserver|MySql数据库表结构一键生成工具
09-25
"Oracle|Sqlserver|MySql数据库表结构一键生成工具" 提供了一种高效的方法来自动化这个过程，大大减轻了手动编写文档的工作量。这款工具能够支持三种主流的关系型数据库管理系统：Oracle、SQL Server和MySQL。 ...
整理mysql、oracle数据库相关 笔试面试题，主要为了应对面试过程中遇到的sql题目
10-17
5. **性能优化**：了解如何通过EXPLAIN PLAN分析查询执行计划，以及如何使用索引、物化视图、绑定变量等方式优化SQL性能。 在面试过程中，除了基础的SQL语法，面试官还可能考察问题解决能力、逻辑思维和实际操作...
oracle11g执行计划管理-(讲解如何固定sql的执行计划)
06-21
摘要：本文描述了11g的新特性之一：执行计划管理，介绍了引入该新特性的原因，以及该新特性的相关特点、工作原理等。最后通过引入一个测试案例来介绍如何使用该新特性。 分析了如何固定优化过的执行计划，避免被sql优化器CBO覆盖
Oracle 执行计划中常见index访问方式
congjian0578的博客
12-14 290
========================================= ========ZMOS_02=========================== ==============...
mysql 索引 index、执行计划explain、索引失效问题、聚簇索引
weixuan_的博客
05-31 374
mysql 索引 index、执行计划explain、索引失效问题、聚簇索引
执行计划中常见index访问方式
huangliang0703的专栏
07-12 516
SQL> create table test as select object_id,object_name from dba_objects; create table test as select object_id,object_name from dba_objects; Table created.SQL>  create index i_t_object_id on test(o
Oracle执行计划解释 - Index
Lin's Tech Log
12-04 136
根据索引的类型与where限制条件的不同，有5种类型的索引扫描：     索引唯一扫描（index unique scan）     索引范围扫描（index range scan）     索引全扫描（index full scan）     索引快速扫描（index fast full scan）     索引跳跃式扫描 (index skip scan) （1） 索引唯一扫描...
oracle 索引概念、索引相关、sql查询执行计划
热门推荐
不是一枚开发
03-21 6万+
文章目录一.索引1.1 索引概念1.2 索引分类1.3 位图索引(企业版oracle)：1.4 B树索引1.5 和分区相关的索引二.索引操作2.1 创建标准索引:2.2 查看表中的索引:2.3 查看索引信息:2.4 分析索引是否存在索引碎片:2.5 重建索引:2.6 删除索引：三.执行计划（explain plan）3.1 执行计划概念3.2 如何查看执行计划3.3 表访问方式 一.索引 1.1 索引概念 索引是与表相关的可选结构，用于提高sql语句执行性能，减少磁盘IO； 索引在逻辑上和物理上都独立于表的
【Cypher语言】id含义、index、属性唯一性约束、执行计划
XiaoCai
11-18 1737
1. id的含义 Cypher每个节点或Relation都有一个系统分配的id，从0开始递增，全局唯一！ 例如： Create(a:Person{id:123}),此ID不是系统分配的id，且该语句不会出现关键字错误。 2. 索引index ----创建索引 create index on:Person(id) ----删除索引 drop index on:Person(id) 不需要给索引起名称，只需要设置索引字段即可 通过该字段的查询都走索引，而关系型数据库中，索引字段套一层函数的话，基本上不走索
查看Oracle执行计划的几种方法
java3344520的专栏
04-20 5万+
查看Oracle执行计划的几种方法一、通过PL/SQL Dev工具    1、直接File->New->Explain Plan Window，在窗口中执行sql可以查看计划结果。其中，Cost表示cpu的消耗，单位为n%，Cardinality表示执行的行数，等价Rows。    2、先执行 EXPLAIN PLAN FOR   select * from tableA where
Oracle访问索引的执行计划（三）
cuishan5183的博客
05-28 91
Oracle访问索引的执行计划（一） Oracle访问索引的执行计划（二） 当需要获取某个字段的全部数据时，如果该字段非空且有索引，或者通过where条件将空值排除掉，则可能会使用索引全扫描（INDEX...
SQL Server 2008 vs. Oracle, MySQL, DB2：一场数据库大战
"本文对比了SQL Server 2008与其他主流数据库系统Oracle、MySQL和DB2，旨在帮助用户理解SQL Server 2008在市场中的定位和特性。SQL Server 2008是一个全面的关系型数据库管理系统，包含Database Engine、Integration...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值