沈阳专业网站制作公司,网络营销策划方案怎么做,网站怎么做全站搜索,seo服务器配置#x1f4ab;《博主介绍》#xff1a;✨又是一天没白过#xff0c;我是奈斯#xff0c;从事IT领域✨ #x1f4ab;《擅长领域》#xff1a;✌️擅长阿里云AnalyticDB for MySQL(分布式数据仓库)、Oracle、MySQL、Linux、prometheus监控#xff1b;并对SQLserver、NoSQL(… 《博主介绍》✨又是一天没白过我是奈斯从事IT领域✨ 《擅长领域》✌️擅长阿里云AnalyticDB for MySQL(分布式数据仓库)、Oracle、MySQL、Linux、prometheus监控并对SQLserver、NoSQL(MongoDB)有了解✌️ 大佬们都喜欢静静的看文章并且也会默默的点赞收藏加关注 中秋佳节月圆人团圆 今天是中秋节祝大家月饼节快乐平时做SQL优化的大佬都知道优化 SQL 语句的核心步骤之一就是分析和优化其执行计划。而在执行计划中访问路径的选择更是至关重要。访问路径决定了数据库在执行查询时采用的数据访问方式直接影响查询的执行效率。平常常见的访问路径包括全表扫描Full Table Scan、索引范围扫描Index Range Scan、索引快速全扫描Index Fast Full Scan、索引跳跃扫描Index Skip Scan等然而访问路径的种类远不止这些。 博主为了更深入理解这些访问路径花了一周的时间深入学习了官方文档中关于访问路径的知识点并且也结合了自己在工作中的经验从而整理了这篇文章旨在与大家分享 Oracle 中的所有访问路径及其应用。因为只有非常了解访问路径才能更好的优化SQL语句的执行计划。 特别说明本篇文章部分知识点均来源于 Oracle 公开可查的官方文档手册并结合了我个人的理解和案例演示。如有冲突请联系会立即处理。转载请标明出处 官方文档对访问路径的介绍Oracle 12cOptimizer Access Paths 目录
一、表级别的访问路径Oracle默认表类型是堆表
堆表介绍
1、执行计划访问路径之全表扫描Full Table Scans
1.1 全表扫描Full Table Scans的工作原理
1.2 全表扫描Full Table Scans案例讲解
2、执行计划访问路径之按Rowid访问表Table Access by Rowid
2.1 按Rowid访问表Table Access by Rowid的工作原理
2.2 按Rowid访问表Table Access by Rowid案例讲解
3、执行计划访问路径之表取样扫描Sample Table Scans
3.1 表取样扫描Sample Table Scans案例讲解
二、B树索引的访问路径
1、执行计划访问路径之索引唯一扫描Index Unique Scans
1.1 索引唯一扫描Index Unique Scans的工作原理
1.2 索引唯一扫描Index Unique Scans案例讲解
2、执行计划访问路径之索引范围扫描Index Range Scans
2.1 索引范围扫描Index Range Scans的工作原理
2.2 索引范围扫描Index Range Scans案例讲解
2.3 索引范围扫描Index Range Scans降序案例讲解
3、执行计划访问路径之索引全扫描Index Full Scans或者Index Full Scans (MIN/MAX)
3.1 索引全扫描Index Full Scans的工作原理
3.2 INDEX FULL SCAN和INDEX FULL SCAN (MIN/MAX)的区别
3.3 索引全扫描Index Full Scans或者Index Full Scans (MIN/MAX)案例讲解
4、执行计划访问路径之索引快速全扫描Index Fast Full Scans
4.1 索引快速全扫描Index Fast Full Scans的工作原理
4.2 索引快速全扫描Index Fast Full Scans案例讲解
5、执行计划访问路径之索引跳跃扫描Index Skip Scans
5.1 索引跳跃扫描Index Skip Scans的工作原理
5.2 索引跳跃扫描Index Skip Scans案例讲解
6、执行计划访问路径之索引连接扫描Index Join Scans
6.1 索引连接扫描Index Join Scans的工作原理
6.2 索引连接扫描Index Join Scans案例讲解
三、位图索引的访问路径
位图索引的适用场合
四、簇表的访问路径
簇表介绍
簇表之簇索引簇索引是一种特殊的索引说直白点就是为簇表创建簇索引使用簇索引来定位簇表的数据加快数据访问
簇表之hash簇表hash簇类似于索引簇只是索引键被散列函数所取代。不存在单独的聚集索引。在hash簇中数据就是索引。
1、执行计划访问路径之簇扫描Cluster Scans
1.1 簇扫描Cluster Scans的工作原理
1.2 簇扫描Cluster Scans案例讲解
2、执行计划访问路径之哈希扫描Hash Scans
2.1 哈希扫描Hash Scans的工作原理
2.2 哈希扫描Hash Scans案例讲解 什么是访问路径 在数据库查询中 访问路径Access Path指的是数据库在执行查询时选择的数据访问方法 。简单来说就是数据库从存储的数据中找到并检索所需数据的方式。每个查询都有不同的访问路径而数据库通过执行计划来决定使用哪一种访问路径进而影响查询的效率和性能。 Oracle 在处理 SQL 查询时会根据表结构、索引、查询条件等因素选择不同的访问路径来执行查询操作。 访问路径介绍 Oracle对不同的关系数据结构使用不同的访问路径下表总结了主要数据结构的常见访问路径。 访问路径 Access Path 堆组织表 Heap-Organized Tables 索引组织表IOT B-Tree Indexes and IOTs 位图索引 Bitmap Indexes 簇表 Table Clusters Full Table Scans √ Table Access by Rowid √ Sample Table Scans √ Index Unique Scans √ Index Range Scans √ Index Full Scans √ Index Fast Full Scans √ Index Skip Scans √ Index Join Scans √ Bitmap Conversion to Rowid √ Bitmap Index Single Value √ Bitmap Index Range Scans √ Bitmap Merge √ Cluster Scans √ Hash Scans √ 计划生成器计划生成器是啥可以参考这篇文章【Oracle篇】一条 SQL 语句的执行流程(含优化器详解)_row source generation-CSDN博客通过尝试不同的访问路径、连接方法和连接顺序来探索查询块的各种计划。许多计划都是可能的因为数据库可以使用各种组合来产生相同的结果。优化器选择成本最低的计划。 一般来说索引访问路径对于访问表的小部分行数据时更有效而在访问表的大部分行数据时全表扫描更有效。 一、表级别的访问路径Oracle默认表类型是堆表 表是Oracle数据库中数据组织的基本单位。 关系表是最常见的表类型不同的关系表具有以下组织特征
堆组织的表Heap-Organized Tables不按任何特定的顺序存储行数据Oracle的默认表类型。按索引组织的表B-Tree Indexes and IOTs根据主键值对行数据进行排序MySQL的默认表类型。外部表external table是只读表其元数据存储在数据库中但其数据存储在数据库之外。簇表Cluster Table是一种特殊的存储数据方式它将一组经常一起使用的表中相同的列存储在相同的数据块中。 虽然上面列了几种表类型但 下面主要还是介绍下堆组织的表Heap-Organized Tables的优化器访问路径因为Oracle默认的表类型是堆表所以需要重点了解下。 堆表介绍 默认情况下表被组织为一个堆 这意味着数据库将行放在它们最适合的位置而不是按照用户指定的顺序 。当用户添加行时数据库将这些行放在数据段的第一个可用空间中。不能保证按照插入行的顺序检索行。 数据块和数据段中的行存储 数据库将行存储在数据块中。在表中数据库可以在块底部的任何地方写入一行。Oracle数据库使用包含行目录和表目录的块开销来管理块本身。 一个扩展区由逻辑上连续的数据块组成。这些块在磁盘上可能不是物理上连续的。段是一组区包含表空间中逻辑存储结构的所有数据。例如Oracle数据库分配一个或多个区来形成表的数据段。数据库还分配一个或多个区来形成表的索引段。 默认情况下数据库对永久的本地管理的表空间使用自动段空间管理(ASSM)。当会话首次向表中插入数据时数据库会格式化位图块。位图跟踪段中的块。数据库使用位图来查找空闲块然后在写入前格式化每个块。ASSM将插入分散在各个块中以避免并发问题。 高水位线(HWM)是数据段中的一个点超过该点数据块将被取消格式化并且从未被使用过。在HWM之下块可以被格式化和写入格式化和空或者未格式化。低高水位线(低HWM)标记这样一个点在该点之下所有数据块都被认为已格式化因为它们包含数据或以前包含的数据。 在全表扫描过程中数据库读取低HWM高水位之前的所有块 这些块已知已被格式化然后读取段位图以确定HWM和低HWM之间的哪些块已被格式化并且可以安全读取。数据库知道不要读取HWM因为这些块是未格式化的。 ROWID对于行访问的重要性 堆组织的表中的每一行都有一个rowid这个rowid对于这个表是唯一的它对应于一个行段的物理地址。rowid是一行的10字节物理地址。 rowid指向特定的文件、块和行号。例如rowid为AAAPecAAFAAAABSAAA最后一个AAA表示行号。行号是行目录条目的索引。行目录条目包含指向块中行位置的指针。博主手绘了一张rowid的逻辑解析图。 rowid以四段格式显示OOOOOOFFFBBBBBBRRR该格式分为以下几个部分 OOOOOO数据对象编号标识该段在示例中为AAAPec。数据对象编号被分配给每个数据库段。同一段中的模式对象(如表簇)具有相同的数据对象编号。FFF与表空间相关的数据文件号标识包含该行的数据文件在示例中为AAF。BBBBBB数据块编号标识包含该行的块在示例中为AAAABS。块号与它们的数据文件相关而不是与它们的表空间相关。因此具有相同块号的两行可能位于同一表空间的不同数据文件中。RRR行号标识块中的行在示例中为AAA。 在某些特殊情况下rowid 可能会发生变化尤其是在启用了行移动功能时。例如rowid 可能会因为以下原因而改变分区键更新、闪回表操作或收缩表操作等。如果禁用了行移动功能那么在使用 Oracle 数据库的导出和导入工具时rowid 也会发生变化。 Oracle数据库在内部使用rowids来构建索引 。例如B树索引中的每个键都与指向相关行地址的rowid相关联。 物理rowids提供了对表行最快的访问使数据库只需一次I/O就可以检索一行。 Direct Path Reads直接路径读取 在直接路径读取中数据库将缓冲区从磁盘直接读取到PGA中完全绕过SGA。下图显示了分散读取和顺序读取(在SGA中存储缓冲区)与直接路径读取之间的差异。 此图描述了从缓冲区读取到SGA缓冲区缓存和进程PGA的数据。读取到SGA缓冲区缓存中的数据是通过db顺序读取或db分散读取获取的。读取到过程PGA中的数据是通过直接路径读取获得的。 Oracle数据库可能执行直接路径读取的情况包括 执行CREATE TABLE AS SELECT语句执行ALTER REBUILD或ALTER MOVE语句从临时表空间读取并行查询从LOB段读取 1、执行计划访问路径之全表扫描Full Table Scans 全表扫描从表中读取所有行然后筛选出不符合选择条件的行 。一般来说当优化器无法使用不同的访问路径或者另一个可用的访问路径成本较高时它会选择全表扫描并不是所有的执行计划适合走索引扫描小表走全表大表走索引。下表显示了选择全表扫描的典型原因。 原因描述不存在索引如果不存在索引则优化器使用全表扫描。列上有索引但是在索引列上使用了函数除非索引是基于函数的索引否则数据库会索引列的值而不是应用函数的列的值。一个典型的应用程序级错误是对字符列如char_col进行索引然后使用WHERE char_col1等语法查询该列。数据库隐式地将TO_NUMBER函数应用于常数1这会阻止使用索引。发出SELECT COUNT*查询并且存在索引但索引列包含空值优化器无法使用索引来计算表行数因为索引不能包含空条目。索引类型为组合索引但在where条件中没有使用索引的前置列例如员工上可能存在索引first_name、last_name。如果用户使用谓词WHERE last_nameKING发出查询则优化器可能不会选择索引因为前置列列first_name不在where条件中。然而在这种情况下优化器可能会选择使用索引跳过扫描。查询是非选择性的如果优化器确定查询需要表中的大部分块那么即使索引可用它也会使用全表扫描。全表扫描可以使用更大的I/O调用。减少大型I/O调用比进行许多小型调用更便宜。表的统计信息已过时例如一个表很小但现在已经变大了。如果表统计信息已经过时不能反映表的当前大小那么优化器就不知道索引现在比全表扫描更有效。表的行数据非常少如果一个表在高水位线下包含的块少于n个其中n等于DB_FILE_MOLIBLOCK_READ_COUNT初始化参数的设置那么全表扫描可能比索引范围扫描便宜。无论访问的表或存在的索引的比例如何扫描都可能更便宜。该表具有高度的并行性表的高度并行性使优化器倾向于在范围扫描中进行全表扫描。查询ALL_TABLES中的值。DEGREE列用于确定平行度。查询时使用了Hint干预强制进行全表扫描 使用Hint干预优化器使用全表扫描。全表扫描的Hint干预语法为/* FULL(table alias) */ 1.1 全表扫描Full Table Scans的工作原理 全表扫描是如何读取数据块的 在全表扫描中数据库按顺序读取高水位线high water mark下的每个格式化块 。数据库只读取每个块一次。下图显示了表段的扫描显示了扫描如何跳过高水位线以下的未格式化块。 此图显示了一系列水平块前6块是灰色的最后一个灰色块的右边缘标记为“低HWMLow HWM”。接下来的2个是空的接着是1个灰色的块然后是5个空的块。 图例中空块表示为“从未使用未格式化Never Used, Unformatted”灰色块表示为“已使用Used”。箭头从左向右落在每个用过的方块上然后停在高水位线HWM处。 由于这些块是相邻的数据库可以通过使I/O调用大于单个块来加速扫描这被称为“多块读取multiblock read”。读取调用的大小从一个块到DB_FILE_MULTIBLOCK_READ_COUNT初始化参数指定的块数不等。例如将此参数设置为4指示数据库在一次调用中最多读取4个块。 在全表扫描期间缓存块的算法很复杂。例如数据库缓存块的方式因表的大小而异。 1.2 全表扫描Full Table Scans案例讲解 查询employees表通过salary字段筛选出月薪超过4000美元的人并且salary字段并没有创建索引。 SELECT salary
FROM hr.employees
WHERE salary 4000;通过dbms_xplan.DISPLAY_CURSOR方式查看SQL语句的执行计划由于salary字段上不存在索引优化器无法使用索引范围扫描因此使用了全表扫描。 SQL_ID 54c20f3udfnws, child number 0
-------------------------------------
select salary from hr.employees where salary 4000Plan hash value: 3476115102---------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes |Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | | 3 (100)| |
|* 1| TABLE ACCESS FULL| EMPLOYEES | 98 | 6762 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter(SALARY4000) 2、执行计划访问路径之按Rowid访问表Table Access by Rowid rowid是数据存储位置的内部表示。行的rowid指定了包含该行的数据文件和数据块以及该行在该块中的位置。通过指定行ID来定位行是检索单行的最快方法因为它指定了行在数据库中的确切位置。 小提示Rowid可以在不同版本之间更改。不建议根据位置访问数据因为行可以移动。 当优化器选择按Rowid访问表时在大多数情况下数据库在扫描一个或多个索引后通过rowid访问表。但是通过rowid访问表不需要遵循每次索引扫描。如果索引包含所有需要的列则可能无法通过rowid进行访问。 2.1 按Rowid访问表Table Access by Rowid的工作原理 要通过rowid访问表数据库将执行多个步骤。数据库执行以下操作 从语句WHERE子句或通过一个或多个索引的索引扫描获取选定行的Rowid对于索引中不存在的语句中的列可能需要表访问。根据Rowid定位表中的每个选定行。 2.2 按Rowid访问表Table Access by Rowid案例讲解 查询employees表通过employee_id字段筛选出来id大于190的行数据employee_id字段作为主键那么数据库为其创建了emp_emp_id_pk主键索引。 SELECT *
FROM employees
WHERE employee_id 190; 以下计划的步骤2显示了对employees表上的emp_emp_id_pk索引的范围扫描。数据库使用索引从中获得Rowid从employees表中查找相应的行然后检索它们。步骤1中显示的批量访问意味着数据库从索引中检索一些Rowid然后尝试访问块中的行以改善集群并减少数据库必须访问块的次数。 --------------------------------------------------------------------------------
|Id| Operation | Name |Rows|Bytes|Cost(%CPU)|Time|
--------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | |2(100)| |
| 1| TABLE ACCESS BY INDEX ROWID BATCHED|EMPLOYEES |16|1104|2 (0)|00:00:01|
|*2| INDEX RANGE SCAN |EMP_EMP_ID_PK|16| |1 (0)|00:00:01|
--------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access(EMPLOYEE_ID190) 3、执行计划访问路径之表取样扫描Sample Table Scans 表取样扫描Sample Table Scans从简单的表或复杂的SELECT语句(如涉及联接和视图的语句)中检索随机样本数据。当语句FROM子句包含sample关键字时数据库使用表取样扫描。FROM子句中的sample关键字具有以下形式 SAMPLE (sample_percent)数据库读取表中指定百分比的行来执行表取样扫描。SAMPLE BLOCK (sample_percent)数据库读取指定百分比的表块来执行表取样扫描。 sample_percent指定要包含在样本中的总行数或块数的百分比。该值必须在. 000001到100的范围内但不包括100。此百分比表示块抽样中的每一行或每一组行被选为样本的概率。这并不意味着数据库准确地检索sample_percent的行。 小提示只有在全表扫描full table scans或索引快速全扫描index fast full scans期间才可能进行块采样。如果存在更有效的执行路径则数据库不会对块进行采样。若要保证对特定表或索引进行块采样请使用FULL或INDEX_FFS进行Hint干预。 3.1 表取样扫描Sample Table Scans案例讲解 此示例使用表取样扫描Sample Table Scans来访问员工表的1%按块而不是行进行采样。 SELECT * FROM hr.employees SAMPLE BLOCK (1); 通过dbms_xplan.DISPLAY_CURSOR方式查看SQL语句的执行计划。 -------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 68 | 3 (34)|
| 1 | TABLE ACCESS SAMPLE | EMPLOYEES | 1 | 68 | 3 (34)|
------------------------------------------------------------------------- 二、B树索引的访问路径 索引是一种可选结构与表或表簇相关联可以加快数据访问。通过在表的一列或多列上创建索引在某些情况下可以从表中检索一小组随机分布的行。索引是减少磁盘I/O的许多方法之一。 B树是balanced trees平衡树的缩写是最常见的数据库索引类型。B树索引是按范围划分的有序值列表。通过将一个键与一行或一系列行相关联B树为各种查询提供了出色的检索性能包括精确匹配和范围搜索。 B树索引结构B-treebalance tree即平衡树左右两个分支相对平衡 下图是博主手绘了一张B树索引的逻辑结构。分支块存储在两个关键字之间进行分支决策所需的最小关键字前缀。叶块包含每个索引数据值和用于定位实际行的相应rowid。每个索引条目按(keyrowid)排序。叶块是双重链接的。 该图形分为两个带虚线边框的框一个在另一个的上面。顶部标记为“分支块Branch Blocks”下部标记为“叶块Leaf Blocks”。该图形包含一个方框树。顶部是一个包含以下值的框0-40、41-90等等。三个箭头指向第二排分支块。这一行中的一个块具有值1-10、11-19、20-25等等。相邻块的值为41-48、49-53等等。最后一个黑色的值为200-209、210-220等等。 最左边和最右边的块各有一对向下的箭头。最左边的箭头指向包含以下值的叶块Leaf Blocks0,rowid、0,rowid、10,rowid等等。右边的块包含以下值11,rowid、11,rowid等等。下一个块包含值:221,rowid、222,rowid等等。最右边的块包含以下值246,rowid、248,rowid等等。除了最左边和最右边的块之外底行中的每个叶块都通过双向箭头链接到两侧的块。 B树索引是如何影响扫描的 上图中显示了彼此相邻的叶块。例如1-10块位于11-19块的旁边和之前。此排序显示了连接索引条目的链表。然而索引块不需要在索引段内按顺序存储。例如246-250块可以出现在段中的任何位置包括直接在1-10块之前。因此有序索引扫描必须执行单块I/O。数据库必须读取索引块以确定下一步必须读取哪个索引块。 索引块体将索引条目存储在堆中就像表行一样。例如如果值10首先插入到表中则具有键10的索引条目可能会插入到索引块的底部。如果接下来将0插入表中则键0的索引条目可能会插入到10的条目的顶部。因此块体中的索引条目不是按关键字顺序存储的。但是在索引块中行标题按键顺序存储记录。例如标题中的第一条记录指向键为0的索引条目依此类推直到指向键为10的索引条目的记录。因此索引扫描可以读取行标题以确定范围扫描的开始和结束位置从而避免了读取块中每个条目的必要性。 B树索引之唯一和非唯一索引 在非唯一索引中数据库通过将rowid作为额外的列附加到键来存储rowid。该条目添加了一个长度字节以使密钥唯一。例如在上图所示的非唯一索引中第一个索引键是0,rowid而不仅仅是0。数据库先按索引键值然后按rowid升序对数据进行排序。例如条目排序如下 0,AAAPvCAAFAAAAFaAAa
0,AAAPvCAAFAAAAFaAAg
0,AAAPvCAAFAAAAFaAAl
2,AAAPvCAAFAAAAFaAAm在唯一索引中索引键不包括rowid。数据库只按索引键值对数据进行排序如0、1、2等。 B树索引对于空值的处理 B树索引从不存储完全为空的键这对于优化器如何选择访问路径非常重要。此规则的结果是单列B树索引从不存储空值。 在下列两个查询中employees表在employee_id上有主键索引在department_id上有唯一索引。department_id列可以包含null值但employee_id列不能存在null值。 SQL SELECT COUNT(*) FROM employees WHERE department_id IS NULL;COUNT(*)
----------1SQL SELECT COUNT(*) FROM employees WHERE employee_id IS NULL;COUNT(*)
----------0以下示例中显示了优化器为employees中所有部门ID的查询选择全表扫描。优化器无法在employees表中的department_id字段上使用索引因为不能保证索引包含表中每一行的条目。 SQL EXPLAIN PLAN FOR SELECT department_id FROM employees;Explained.SQL SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 3476115102---------------------------------------------------------------------------
|Id | Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 321 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 321 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------以下示例中显示了优化器可以使用department_id上的索引来查询特定的部门id因为所有非空行都被索引。 SQL EXPLAIN PLAN FOR SELECT department_id FROM employees WHERE department_id10;Explained.SQL SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 67425611---------------------------------------------------------------------------
|Id| Operation | Name |Rows|Bytes|Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:0 0:01|
|*1| INDEX RANGE SCAN| EMP_DEPARTMENT_IX | 1 | 3 | 1 (0)| 00:0 0:01|
---------------------------------------------------------------------------Predicate Information (identified by operation id):1 - access(DEPARTMENT_ID10)以下示例中显示了当条件中排除空值时优化器会选择索引扫描 SQL EXPLAIN PLAN FOR SELECT department_id FROM employees
WHERE department_id IS NOT NULL;Explained.SQL SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 1590637672---------------------------------------------------------------------------
| Id| Operation | Name |Rows|Bytes| Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0| SELECT STATEMENT | |106| 318 | 1 (0)| 00:0 0:01|
|*1| INDEX FULL SCAN | EMP_DEPARTMENT_IX |106| 318 | 1 (0)| 00:0 0:01|
---------------------------------------------------------------------------Predicate Information (identified by operation id): 1 - filter(DEPARTMENT_ID IS NOT NULL) 1、执行计划访问路径之索引唯一扫描Index Unique Scans 索引唯一扫描最多返回1个rowid。索引唯一扫描Index Unique Scans需要在where条件中使用等于运算符。具体来说只有当where查询使用相等运算符引用唯一索引键中的所有列时数据库才会执行唯一扫描例如当WHERE prod_id10时。 unique或primary key约束本身不足以生成索引唯一扫描Index Unique Scans因为该列上可能已经存在非唯一索引。下面示例中创建了一个t_table表然后在numcol字段上创建非唯一索引 SQL CREATE TABLE t_table(numcol INT);
SQL CREATE INDEX t_table_idx ON t_table(numcol);
SQL SELECT UNIQUENESS FROM USER_INDEXES WHERE INDEX_NAME T_TABLE_IDX;UNIQUENES
---------
NONUNIQUE 以下SQL中在具有非唯一索引的列上创建primary key约束从而导致索引范围扫描而不是索引唯一扫描 SQL ALTER TABLE t_table ADD CONSTRAINT t_table_pk PRIMARY KEY(numcol);
SQL SET AUTOTRACE TRACEONLY EXPLAIN
SQL SELECT * FROM t_table WHERE numcol 1;Execution Plan
----------------------------------------------------------
Plan hash value: 868081059---------------------------------------------------------------------------
| Id | Operation | Name |Rows |Bytes |Cost (%CPU)|Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)|00:00:01 |
|* 1 | INDEX RANGE SCAN| T_TABLE_IDX | 1 | 13 | 1 (0)|00:00:01 |
---------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - access(NUMCOL1) 1.1 索引唯一扫描Index Unique Scans的工作原理 扫描按指定键的顺序搜索索引。索引唯一扫描一找到第一条记录就停止处理因为不可能有第二条记录。数据库从索引条目中获取rowid然后检索由rowid指定的行。 下图说明了索引唯一扫描。该语句请求prod_id列中产品ID为19的记录该列有一个主键索引。 该图形分为两个带虚线边框的框一个在另一个的上面。顶部标记为“分支块Branch Blocks”下部标记为“叶块Leaf Blocks”该图形包含一个方框树。顶部是一个包含以下值的框:0-40、41-90等等。三个箭头指向第二排分支块。这一行中的一个块具有值1-10、11-19、20-25等等。相邻块的值为41-48、49-53等等。最后一个黑色的值为200-209、210-220等等。 最左边和最右边的块各有一对向下的箭头。最左边的箭头指向包含以下值的叶块0,rowid、1,rowid、10,rowid等等。右边的块包含以下值:11,rowid、12,rowid等等。下一个块包含值:221,rowid、222,rowid等等。最右边的块包含以下值:246,rowid、248,rowid等等。除了最左边和最右边的块之外底行中的每个叶块都通过双向箭头链接到两侧的块。 1.2 索引唯一扫描Index Unique Scans案例讲解 此示例使用唯一扫描从products表中检索一行查询products表中prod_id字段为19的记录 SELECT *
FROM sh.products
WHERE prod_id 19;因为products表中的prod_id列上存在主键索引并且WHERE子句使用相等运算符引用所有列所以优化器选择唯一扫描 SQL_ID 3ptq5tsd5vb3d, child number 0
-------------------------------------
select * from sh.products where prod_id 19Plan hash value: 4047888317---------------------------------------------------------------------------
| Id| Operation | Name |Rows|Bytes|Cost (%CPU)|Time |
---------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | |1 (100)| |
| 1| TABLE ACCESS BY INDEX ROWID| PRODUCTS |1 | 173 |1 (0)|00:00:01|
|* 2| INDEX UNIQUE SCAN | PRODUCTS_PK |1 | |0 (0)| |
---------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access(PROD_ID19) 2、执行计划访问路径之索引范围扫描Index Range Scans 索引范围扫描是对值的有序扫描。扫描的范围可以在两侧有界或者在一侧或两侧无界。优化器通常为具有高选择性的查询选择范围扫描。 默认情况下数据库以升序存储索引并以相同的顺序扫描它们。例如wehere统计中department_id 20的查询使用范围扫描返回按索引键20、30、40等排序的行。如果多个索引条目具有相同的键则数据库按rowid的升序返回它们因此0,AAAPvCAAFAAAAFaAAa后面跟0,AAAPvCAAFAAAAFaAAg依此类推。 降序索引范围扫描与降序索引范围扫描相同只是数据库按降序返回行。通常当按降序排列数据时或者当查找小于指定值的值时数据库使用降序扫描。 对于索引范围扫描索引键必须有多个值。具体来说优化器会在以下情况下考虑索引范围扫描 在条件中指定了索引的一个或多个前导列。条件指定一个或多个表达式和逻辑(布尔)运算符的组合并返回值TRUE、FALSE或UNKNOWN。条件的示例包括 department_id :id
department_id :id
department_id :id
以及索引中前导列的前述条件的组合例如department_id :low AND department_id :hi 一个索引键可能有0、1或多个值。 当索引可以满足ORDER BY DESCENDING子句时优化器会将索引范围扫描视为降序。如果优化器选择全表扫描或另一个索引那么可能需要一个提示来强制使用这个访问路径。索引(tbl_alias ix_name)和索引_DESC(tbl_alias ix_name)提示指示优化器使用特定的索引。 2.1 索引范围扫描Index Range Scans的工作原理 在索引范围扫描期间Oracle数据库从根到分支进行扫描。通常扫描算法如下 读取根块。读取分支块。交替执行以下步骤直到检索到所有数据 a. 读取叶块以获得rowid
b. 读取表块以检索行 在某些情况下索引扫描会读取一组索引块对rowids进行排序然后读取一组表块。因此为了扫描索引数据库在叶块中向前或向后移动。例如对介于20和40之间的id的扫描定位具有20或更大的最低键值的第一个叶块。扫描在叶节点的链表中水平进行直到找到一个大于40的值然后停止。 下图说明了使用升序进行索引范围扫描。语句请求department_id列中值为20的employees员工表记录该列具有非唯一索引。在这个例子中部门20有2个索引条目。 该图形分为两个带虚线边框的框一个在另一个的上面。顶部标记为“分支块Branch Blocks”下部标记为“叶块Leaf Blocks”该图形包含一个方框树。顶部是一个包含以下值的框0-40、41-90等等。三个箭头指向第二排分支块。这一行中的一个块具有值1-10、11-19、20-25等等。相邻块的值为41-48、49-53等等。最后一个黑色的值为200-209、210-220等等。 最左边和最右边的块各有一对向下的箭头。最左边的箭头指向包含以下值的叶块0,rowid、0,rowid、10,rowid等等。右边的块包含以下值11,rowid、11,rowid等等。下一个块包含值:221,rowid、222,rowid等等。最右边的块包含以下值:246,rowid、248,rowid等等。除了最左边和最右边的块之外底行中的每个叶块都通过双向箭头链接到两侧的块。 2.2 索引范围扫描Index Range Scans案例讲解 此示例使用索引范围扫描从employees表中检索一组值。以下语句查询部门20中薪金高于1000的雇员的记录 SELECT *
FROM employees
WHERE department_id 20
AND salary 1000;前面的查询基数较低(返回的行很少)因此该查询使用department_id列上的索引。数据库扫描索引从employees表中获取记录然后对这些获取的记录应用salary 1000筛选器以生成结果。 SQL_ID brt5abvbxw9tq, child number 0
-------------------------------------
SELECT * FROM employees WHERE department_id 20 AND salary 1000Plan hash value: 2799965532-------------------------------------------------------------------------------------------
|Id | Operation | Name |Rows|Bytes|Cost(%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|*1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 2 | 138 | 2 (0)|00:00:01|
|*2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX| 2 | | 1 (0)|00:00:01|
-------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter(SALARY1000)2 - access(DEPARTMENT_ID20) 2.3 索引范围扫描Index Range Scans降序案例讲解 此示例使用索引按排序顺序从employees表中检索行。以下语句按降序查询部门20中的雇员记录 SELECT *
FROM employees
WHERE department_id 20
ORDER BY department_id DESC;前面的查询基数较低因此该查询使用department_id列上的索引。 SQL_ID 8182ndfj1ttj6, child number 0
-------------------------------------
SELECT * FROM employees WHERE department_id20 ORDER BY department_id DESCPlan hash value: 1681890450
---------------------------------------------------------------------------
|Id| Operation | Name |Rows|Bytes|Cost(%CPU)|Time |
---------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | |2(100)| |
| 1| TABLE ACCESS BY INDEX ROWID |EMPLOYEES |2|138|2 (0)|00:00:01|
|*2| INDEX RANGE SCAN DESCENDING|EMP_DEPARTMENT_IX|2| |1 (0)|00:00:01|
---------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access(DEPARTMENT_ID20) 数据库定位包含最高键值(等于或小于20)的第一个索引叶块。然后扫描通过叶节点的链表向左水平进行。数据库从每个索引条目中获取rowid然后检索由rowid指定的行。 3、执行计划访问路径之索引全扫描Index Full Scans或者Index Full Scans (MIN/MAX) 与全表扫描Table Full Scan相比Index Full Scan只扫描了索引文件因此通常比全表扫描更快速。但是在索引文件较大的情况下仍可能会导致性能瓶颈和资源浪费。 索引全扫描按顺序读取整个索引。索引全扫描可以消除单独的排序操作因为索引中的数据是按索引键排序的。优化器在以下情况下都会考虑进行索引全扫描 where条件中引用索引中的列该列不必是前导列。未指定where条件但满足以下所有条件 a. 表和查询中的所有列都在索引中。
b. 至少有一个索引列不为空。 查询包括对索引的不可为空的列的ORDER BY。 3.1 索引全扫描Index Full Scans的工作原理 数据库读取根块然后向下导航到索引的左侧(如果进行降序全扫描则向下导航到右侧)直到到达叶块。 然后数据库到达一个叶块扫描在索引的底部进行一次一个块按排序顺序进行。数据库使用单块I/O而不是多块I/O。 下图说明了索引全扫描一条语句请求按department_id排序的部门表departments记录。 该图形分为两个带虚线边框的框一个在另一个的上面。顶部标记为“分支块Branch Blocks”下部标记为“叶块Leaf Blocks”该图形包含一个方框树。顶部是一个包含以下值的框0-40、41-80等等。三个箭头指向第二排分支块。这一行中的一个块具有值1-10、11-19、20-25等等。相邻块的值为41-48、49-53等等。最后一个黑色的值为200-209、210-220等等。 最左边和最右边的块各有一对向下的箭头。最左边的箭头指向包含以下值的叶块0,rowid、1,rowid、10,rowid等等。右边的块包含以下值11,rowid、12,rowid等等。下一个块包含值221,rowid、222,rowid等等。最右边的块包含以下值246,rowid、247,rowid等等。除了最左边和最右边的块之外底行中的每个叶块都通过双向箭头链接到两侧的块。 3.2 INDEX FULL SCAN和INDEX FULL SCAN (MIN/MAX)的区别 INDEX FULL SCAN (MIN/MAX)是一种特殊的索引扫描方式用于快速查找最小或最大值。它是基于B-Tree索引实现的通过遍历索引树的最左或最右分支来查找最小或最大的键值。与普通的INDEX FULL SCAN相比INDEX FULL SCAN (MIN/MAX)只需要扫描部分索引树而不是整个索引文件因此通常比普通的索引扫描更快。 需要注意的是使用INDEX FULL SCAN (MIN/MAX)必须保证索引列是有序的否则将无法正确地找到最小或最大值。此外如果查询涉及到的数据量较大则继续使用该方法可能会导致性能下降。 总之INDEX FULL SCAN (MIN/MAX)适用于需要查找最小和最大值的情况并且索引列已经有序的情况下可以提高查询的性能。但是在使用时需要权衡其适用范围和实际性能表现。 而INDEX FULL SCAN则是指数据库系统在执行查询时对某个索引进行全表扫描的操作即读取整个索引文件中的所有数据页来查找符合条件的数据。它通常发生在没有合适的索引可供使用或优化器选择错误索引的情况下会导致性能瓶颈和资源浪费。 3.3 索引全扫描Index Full Scans或者Index Full Scans (MIN/MAX)案例讲解 INDEX FULL SCAN (MIN/MAX) jobid列为索引键索引全扫描对有索引键的列的叶子节点和根节点的数据进行全扫描。 select max(jobid) from yg;
select min(jobid) from yg; INDEX FULL SCAN 此示例使用索引全扫描来满足带有ORDER BY子句的查询。以下语句按部门ID的顺序查询部门的ID和名称 SELECT department_id, department_name
FROM departments
ORDER BY department_id;以下执行计划显示优化器选择了索引全扫描 SQL_ID 94t4a20h8what, child number 0
-------------------------------------
select department_id, department_name from departments order by department_idPlan hash value: 4179022242------------------------------------------------------------------------
|Id | Operation | Name |Rows|Bytes|Cost(%CPU)|Time |
------------------------------------------------------------------------
|0| SELECT STATEMENT | | | |2 (100)| |
|1| TABLE ACCESS BY INDEX ROWID|DEPARTMENTS |27 |432|2 (0)|00:00:01 |
|2| INDEX FULL SCAN |DEPT_ID_PK |27 | |1 (0)|00:00:01 |
------------------------------------------------------------------------ 数据库定位第一个索引叶块然后水平向右遍历叶节点的链表。对于每个索引条目数据库从条目中获取rowid然后检索由rowid指定的表行。因为索引是按department_id排序的所以数据库避免了对检索到的行进行排序的单独操作。 4、执行计划访问路径之索引快速全扫描Index Fast Full Scans 索引快速全扫描以未排序的顺序读取索引块因为它们存在于磁盘上。这种扫描不使用索引来探测表而是读取索引而不是表本质上是将索引本身用作表。 当查询只访问索引中的属性时优化器会考虑索引快速全扫描。 小提示与索引全扫描Index Full Scans不同索引快速全扫描Index Fast Full Scans无法消除排序操作因为它不会按顺序读取索引。 4.1 索引快速全扫描Index Fast Full Scans的工作原理 数据库使用多块I/O来读取根块以及所有叶块和分支块。数据库忽略分支和根块并读取叶块上的索引条目。 索引快速全扫描(Index Fast Full Scans是一种高效的索引扫描方式它扫描整个索引来查找满足查询条件的行。Oracle在以下情况可能会使用索引快速全扫描 1)查询中的列都被包含在索引中如果select语句中的列都被包含在组合索引中而且where条件中没有出现组合索引的引导列并且需要检索出大部分数据那么可能会执行Index Fast Full Scan。
2)查询涉及到索引列的函数操作如果查询条件涉及到索引列的函数操作而该函数操作对于索引列中的每个值都是可确定的那么Oracle优化器可能会选择使用Index Fast Full Scan来提高查询性能。 注意Index Fast Full Scan并不总是比其他扫描方式更快。在某些情况下全表扫描可能比使用Index Fast Full Scan更快。因此Oracle优化器会根据查询条件、表统计信息、索引统计信息等多种因素来决定是否使用Index Fast Full Scan。在实际应用中应该根据具体情况进行性能测试和调优以确定最佳的查询策略。 4.2 索引快速全扫描Index Fast Full Scans案例讲解 下面例子中使用了Hint干预让执行计划走索引快速全扫描。索引快速全扫描的Hint干预语法为/* INDEX_FFS(table_name index_name) */ SELECT /* INDEX_FFS(departments dept_id_pk) */ COUNT(*)
FROM departments;以下执行计划中显示优化器选择了索引快速全扫描 SQL_ID fu0k5nvx7sftm, child number 0
-------------------------------------
select /* index_ffs(departments dept_id_pk) */ count(*) from departmentsPlan hash value: 3940160378
--------------------------------------------------------------------------
| Id | Operation | Name | Rows |Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| DEPT_ID_PK | 27 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------- 5、执行计划访问路径之索引跳跃扫描Index Skip Scans 当组合索引的前置列被“跳过skipped”或未在查询中指定时会发生索引跳跃扫描。通常来说跳过扫描索引块skip scanning index blocks比扫描表块scanning table blocks要快很多也比执行完全索引扫描full index scans快。 当满足以下条件时优化器会考虑进行索引跳跃扫描 where条件中未指定组合索引的前置列例如表中创建了一个组合索引复合索引键是cust_gendercust_emailwhere条件中不引用cust_gender列在组合索引的前置列中存在很少的非重复值但是在索引的非前置列中存在许多非重复值例如如果组合索引键是(cust_gendercust_email)那么cust_gender列只有两个不同的值但是cust_email有数千个值 5.1 索引跳跃扫描Index Skip Scans的工作原理 索引跳过扫描在逻辑上将复合索引拆分成更小的子索引。 索引前导列中不同值的数量决定了逻辑子索引的数量。该数字越小优化器必须创建的逻辑子索引就越少扫描效率就越高。扫描分别读取每个逻辑索引并“跳过skips”非前导列上不满足筛选条件的索引块。 5.2 索引跳跃扫描Index Skip Scans案例讲解 下面示例中使用索引跳跃扫描来满足customers表的查询customers表中的cust_gender字段只包含有M或者F的数据并且对列(cust_gendercust_email)创建了一个复合索引 ###cust_gender列和cust_email列上的部分数据
F,Wolfcompany.example.com,rowid
F,Wolseycompany.example.com,rowid
F,Woodcompany.example.com,rowid
F,Woodmancompany.example.com,rowid
F,Yangcompany.example.com,rowid
F,Zimmermancompany.example.com,rowid
M,Abbassicompany.example.com,rowid
M,Abbeycompany.example.com,rowid###创建索引
CREATE INDEX cust_gender_email_ixON sh.customers (cust_gender, cust_email);将cust_email字段当做where条件进行查询 SELECT *
FROM sh.customers
WHERE cust_email Abbeycompany.example.com;即使where子句中没有指定cust_ender数据库也可以使用CUST_GENDER_EMAIL_IX 索引的跳跃扫描。在示例索引中前置列cust_ender有两个可能的值F和M。数据库在逻辑上将索引一分为二。一个子索引的键为F条目格式如下 F,Wolfcompany.example.com,rowid
F,Wolseycompany.example.com,rowid
F,Woodcompany.example.com,rowid
F,Woodmancompany.example.com,rowid
F,Yangcompany.example.com,rowid
F,Zimmermancompany.example.com,rowid第二个子索引的键为M条目的格式如下 M,Abbassicompany.example.com,rowid
M,Abbeycompany.example.com,rowid通过cust_email 字段搜索数据为Abbeycompany.example.com时数据库首先搜索以F开头的子索引然后搜索以M开头的子指数。查询转换器对查询做如下处理查询转换器觉得这种写法成本更低查询转换器可以参考之前的文章哦直通车【Oracle篇】一条 SQL 语句的执行流程(含优化器详解)_row source generation-CSDN博客 ( SELECT * FROM sh.customers WHERE cust_gender F AND cust_email Abbeycompany.example.com )
UNION ALL
( SELECT * FROM sh.customers WHERE cust_gender MAND cust_email Abbeycompany.example.com ) 执行计划如下 SQL_ID d7a6xurcnx2dj, child number 0
-------------------------------------
SELECT * FROM sh.customers WHERE cust_email Abbeycompany.example.comPlan hash value: 797907791-----------------------------------------------------------------------------------------
|Id| Operation | Name |Rows|Bytes|Cost(%CPU)|Time|
-----------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | | |10(100)| |
| 1| TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS |33|6237| 10(0)|00:00:01|
|*2| INDEX SKIP SCAN | CUST_GENDER_EMAIL_IX |33| | 4(0)|00:00:01|
-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access(CUST_EMAILAbbeycompany.example.com)filter(CUST_EMAILAbbeycompany.example.com) 6、执行计划访问路径之索引连接扫描Index Join Scans 索引连接扫描是多个索引的散列连接这些索引一起返回查询请求的所有列。数据库不需要访问表因为所有数据都是从索引中检索的。 在某些情况下避免表访问是最具成本效益的选择。在下列情况下优化器会考虑进行索引连接扫描 多个索引的散列连接检索查询请求的所有数据而不需要访问表。 从表中检索行的成本高于读取索引而不从表中检索行的成本。索引连接通常开销很大。例如当扫描两个索引并连接它们时选择最具选择性的索引然后探测表通常成本更低。 6.1 索引连接扫描Index Join Scans的工作原理 索引连接包括扫描多个索引然后对从这些扫描中获得的rowids使用散列连接来返回行。在索引连接扫描中总是避免表访问。例如联接单个表上的两个索引的过程如下 扫描第一个索引以检索行Rowid。扫描第二个索引以检索行Rowid。通过Rowid执行哈希连接以获取行。 6.2 索引连接扫描Index Join Scans案例讲解 下面例子中使用了Hint干预让执行计划走索引连接扫描查询last_name字段中以A开头的雇员的姓氏last_name和电子邮件email。索引连接扫描的Hint干预语法为/* INDEX_JOIN(table_name) */ SELECT /* INDEX_JOIN(employees) */ last_name, email
FROM employees
WHERE last_name like A%;last_namefirst_name和email列上存在单独的索引。emp_name_ix索引的一部分可能如下 Banda,Amit,AAAVgdAALAAAABSABD
Bates,Elizabeth,AAAVgdAALAAAABSABI
Bell,Sarah,AAAVgdAALAAAABSABc
Bernstein,David,AAAVgdAALAAAABSAAz
Bissot,Laura,AAAVgdAALAAAABSAAd
Bloom,Harrison,AAAVgdAALAAAABSABF
Bull,Alexis,AAAVgdAALAAAABSABVemp_email_uk索引的第一部分可能如下 ABANDA,AAAVgdAALAAAABSABD
ABULL,AAAVgdAALAAAABSABV
ACABRIO,AAAVgdAALAAAABSABX
AERRAZUR,AAAVgdAALAAAABSAAv
AFRIPP,AAAVgdAALAAAABSAAV
AHUNOLD,AAAVgdAALAAAABSAAD
AHUTTON,AAAVgdAALAAAABSABL执行计划中检索emp_email_uk索引中的所有Rowid然后检索emp_name_ix中以a开头的姓氏的rowid并且使用哈希联接HASH JOIN来搜索这两组rowid以查找匹配项。例如rowid AAAVgdAALAAAABSABD出现在两个rowid集合中因此数据库在employees表中查找与该Rowid对应的记录。 SQL_ID d2djchyc9hmrz, child number 0
-------------------------------------
SELECT /* INDEX_JOIN(employees) */ last_name, email FROM employees
WHERE last_name like A%Plan hash value: 3719800892
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | VIEW | index$_join$_001 | 3 | 48 | 3 (34)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | EMP_NAME_IX | 3 | 48 | 1 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| EMP_EMAIL_UK | 3 | 48 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter(LAST_NAME LIKE A%)2 - access(ROWIDROWID)3 - access(LAST_NAME LIKE A%) ♂️各位大佬到这里文章已经突破2万字大关憋尿的小伙伴可以去放个水或者转转脖子休息一下啦................... 三、位图索引的访问路径 在介绍位图索引的访问路径之前首先让我们了解什么是位图索引位图索引和B树索引的区别是什么。 位图索引的概念 位图索引用位图来表示索引Oracle对于选择度低的列的每个键值建立一个位图。位图中的每一位可能对应多个列位图中位等于1表示特定的行含有此位图表示的键值。 位图索引的优缺点 优点 减少即席查询的响应时间位图索引能够高效处理大量数据的即席查询提高查询速度。节约索引数据空间与其他类型索引相比位图索引占用的空间较少。高效的并行DML和LOAD操作位图索引支持高效的并行数据处理操作。创建索引时更高效位图索引创建时不需要排序且按位存储所需空间少创建速度快。支持空值查询位图索引允许键值为空且对空值查询效率较高。直接计数可以通过位图索引直接获取统计数据如行数等。 缺点 不适合选择度高的列位图索引在选择度高的列上可能表现不佳。频繁更新可能导致性能下降因为更新索引用的是行锁可能锁定多行而不是排它锁所以如果有比较频繁的insert、update等操作可能导致性能下降。可能会溢出当索引数据块难以放下整个索引值时会发生溢出导致查询效率降低。受Oracle版本和特性限制位图索引在Trusted Oracle中不支持不能被规则优化器RBO使用不能用于分区表的全局索引不支持build或rebuild的ONLINE选项等。 位图索引的适用场合 适用于有大量重复值的列查询位图索引在处理大量重复值的列时表现优异。适用于静态数据位图索引不适合频繁更新的列更适合静态数据。适用于批量插入的数据对于批量插入的数据位图索引能够减少索引更新的次数提高性能。适用于OLAP应用由于并发DML操作锁定的是整个位图段的大量数据行位图索引主要用于OLAP应用也可以用于OLTP中主要为读操作的表。 总结 位图索引适合于数据仓库中不适合OLTP中在Oracle这种关系数据库中使用的非常少博主目前还没有见过在Oracle项目上有用位图索引的。参考官方文档学习可以看到位图索引的访问路径有四个。 四、簇表的访问路径 可能有些小伙伴第一次听到簇表簇表是oracle中表类型在oracle中默认表的类型为堆表。既然要学习簇表的访问路径那么先来学习一下簇表的结构然后再开始访问路径的学习。 簇表介绍 簇表是一组共享公共列并在相同的块中存储相关数据的表。当表被聚集时单个数据块可以包含多个表中的行。例如一个块可以存储employees表和departments表中的行而不是只存储一个表中的行。 聚集键cluster key是聚集表共有的一列或多列。例如employees和departments表共享department_id列。在创建簇表和创建添加到簇表中的每个表时需要指定簇键也称聚集键cluster key。 簇键值是一组特定行的簇键列的值。包含相同分类键值(如department_id20)的所有数据在物理上存储在一起。每个簇键值在簇和簇索引中只存储一次不管不同表中有多少行包含该值。 打个比方假设一位人力资源经理有两个书架一个装有员工文件夹另一个装有部门文件夹。用户经常要求为特定部门的所有员工提供文件夹。为了便于检索经理将所有箱子重新排列在一个书架上。她按部门ID划分这些框因此部门20的所有员工文件夹和部门20本身的文件夹都在一个框中部门100中的员工文件夹和部门100的文件夹在另一个框中依此类推。 当表主要被查询但未被修改并且表中的记录经常被一起查询或连接时可以考虑对表进行聚类也就是使用簇表这个表类型。由于表簇将不同表的相关行存储在同一数据块中因此与非聚集表相比正确使用簇表具有以下优点 簇表的联接减少了磁盘I/O。簇表连接的访问时间缩短。存储相关表和索引数据所需的存储空间更少因为不会为每一行重复存储簇键值。 如果是一下情况那么就不适合使用簇表 表经常需要更新update操作。表经常需要全表扫描例如表经常需要count(*)全表数据或者需要全表查询数据。表偶尔需要truncate清空全部数据的操作。 簇表之簇索引簇索引是一种特殊的索引说直白点就是为簇表创建簇索引使用簇索引来定位簇表的数据加快数据访问 簇索引是使用索引来定位簇表的数据。簇索引是簇键上的B树索引。必须先创建簇索引然后才能将任何行插入到簇表中。 创建簇表和关联的簇索引 假设使用聚集键cluster keydepartment_id创建了簇表employees_departments_cluster如下例所示 CREATE CLUSTER employees_departments_cluster(department_id NUMBER(4))
SIZE 512;CREATE INDEX idx_emp_dept_cluster ON CLUSTER employees_departments_cluster; 因为没有指定HASHKEYS子句所以employees_departments_cluster是一个索引簇集。前面的示例在聚集键cluster keydepartment_id上创建了一个名为idx_emp_dept_cluster的索引。 索引簇中创建表 创建employees和departments簇表将department_id列指定为聚集键cluster key如下所示(省略号表示列说明的位置) CREATE TABLE employees ( ... )CLUSTER employees_departments_cluster (department_id);CREATE TABLE departments ( ... )CLUSTER employees_departments_cluster (department_id);假设向employees和departments表中添加了行。数据库将employees和departments表中每个部门的所有行物理存储在同一个数据块中。数据库将行存储在一个堆中并使用索引来定位它们。 下图显示了employees_departments_cluster表簇其中包含雇员和部门。数据库将部门20和部门110的雇员的行存储在一起依此类推。如果表没有聚集则数据库不能确保相关的行存储在一起。 图的左边是一个标有“簇表Clustered Tables”的数据库圆柱图标。圆柱体的顶部是一个正方形。正方形指向一个看起来像一张纸的图标。该图标的标签为“聚集关键部门标识Clustered Key department_id”图标显示了部门20的一组行和部门110的另一组行。 在图的右边是一个标有“非集群表Unclustered Tables”的数据库圆柱图标。圆柱体的顶部是两个正方形。左边的方块指向一个看起来像一张纸的图标。该图标标记为“雇员employees”包含雇员行。右边的方块标记为“部门departments”显示了部门20的一行和部门110的另一行。 B树簇索引将簇键值与包含数据的块的数据库块地址(DBA)相关联。例如键20的索引条目显示了包含部门20中员工数据的块的地址 20,AADAAAA9d簇索引是单独管理的就像非聚集表上的索引一样并且可以存在于与表聚集不同的表空间中。 簇表之hash簇表hash簇类似于索引簇只是索引键被散列函数所取代。不存在单独的聚集索引。在hash簇中数据就是索引。 hash簇类似于索引簇只是索引键被散列函数所取代。不存在单独的聚集索引。在hash簇中数据就是索引。 对于索引表或索引簇Oracle数据库使用存储在单独索引中的键值来定位表行。要在索引表或表簇中查找或存储一行数据库必须至少执行两次I/o 在索引中查找或存储键值的一个或多个I/o另一个I/O读取或写入表或表簇中的行 为了在hash簇中查找或存储一行Oracle数据库将散列函数应用于该行的簇键值。产生的哈希值对应于集群中的一个数据块数据库代表发出的语句读取或写入该数据块。 哈希是存储表数据的一种可选方式可以提高数据检索的性能。当满足以下条件时hash簇可能是有益的 表被查询的次数比被修改的次数多得多哈希键列经常使用相等条件进行查询例如其中department_id20。对于这种查询会对群集键值进行哈希处理。散列键值直接指向存储行的磁盘区域可以合理地猜测散列键的数量以及每个键值存储的数据大小 创建hash簇表 要创建hash簇表可以使用与索引簇相同的CREATE CLUSTER语句并添加一个散列键。群集的哈希值的数量取决于哈希键。 与索引簇的键一样簇键是由簇中的表共享的单列或组合键。散列键值是插入到簇键列中的实际或可能的值。例如如果集群键是department_id那么散列键值可以是10、20、30等等。 Oracle数据库使用散列函数该函数接受无限数量的散列键值作为输入并将它们分类到有限数量的桶中。每个存储桶都有一个唯一的数字ID称为哈希值。每个哈希值映射到存储与哈希值对应的行的块的数据库块地址(部门10、20、30等)。 在下面的示例中可能存在的部门数量是100因此HASHKEYS被设置为100 CREATE CLUSTER employees_departments_cluster(department_id NUMBER(4))
SIZE 8192 HASHKEYS 100;创建employees_departments_cluster后可以在集群中创建employees和departments表。然后您可以像在索引簇中一样将数据加载到散列簇中。 1、执行计划访问路径之簇扫描Cluster Scans 簇索引是使用索引来定位簇表的数据。簇索引是簇键上的B树索引簇扫描Cluster Scans从存储在索引集群中的表中检索具有相同集群键值的所有行。 当查询访问簇索引中的表时数据库会考虑簇扫描。 1.1 簇扫描Cluster Scans的工作原理 在索引簇中数据库将具有相同簇键值的所有行存储在同一数据块中。 例如如果employees2和departments2表在emp_dept_cluster簇索引中并且聚集键是department_id则数据库将部门10中的所有雇员存储在同一块中部门20中的所有雇员存储在同一块中依此类推。 B树簇索引将簇键值与包含数据的块的数据库块地址(DBA)相关联。例如键30的索引条目显示了包含部门30中雇员行的块的地址 30,AADAAAA9d 当用户请求集群中的行时数据库扫描索引以获取包含这些行的块的DBA。然后Oracle数据库根据这些DBA定位行。 1.2 簇扫描Cluster Scans案例讲解 此示例在department_id列上对员工表employees和部门表departments进行聚类然后在聚类中查询单个部门。 创建一个簇表、簇索引和簇中的表如下所示 CREATE CLUSTER employees_departments_cluster(department_id NUMBER(4)) SIZE 512;CREATE INDEX idx_emp_dept_clusterON CLUSTER employees_departments_cluster;CREATE TABLE employees2CLUSTER employees_departments_cluster (department_id)AS SELECT * FROM employees;CREATE TABLE departments2CLUSTER employees_departments_cluster (department_id)AS SELECT * FROM departments;查询部门30中的员工 SELECT *
FROM employees2
WHERE department_id 30; 为了执行扫描Oracle数据库首先通过扫描簇索引获得描述部门30的行的rowid(步骤2)。然后Oracle数据库使用这个rowid定位employees2中的行(步骤1)。 SQL_ID b7xk1jzuwdc6t, child number 0
-------------------------------------
SELECT * FROM employees2 WHERE department_id 30Plan hash value: 49826199---------------------------------------------------------------------------
|Id| Operation | Name |Rows|Bytes|Cost(%CPU)|Time|
---------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | | 2 (100)| |
| 1| TABLE ACCESS CLUSTER| EMPLOYEES2 | 6 |798 | 2 (0)|00:00:01|
|*2| INDEX UNIQUE SCAN |IDX_EMP_DEPT_CLUSTER| 1 | | 1 (0)|00:00:01|
---------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access(DEPARTMENT_ID30) 2、执行计划访问路径之哈希扫描Hash Scans 散列簇类似于索引簇只是索引键被散列函数所取代。不存在单独的聚集索引。在散列簇中数据就是索引。数据库使用哈希扫描来根据哈希值定位哈希簇中的行。 当查询访问哈希簇中的表时数据库会考虑哈希扫描。 2.1 哈希扫描Hash Scans的工作原理 在散列簇中具有相同散列值的所有行都存储在同一个数据块中。 为了对集群执行散列扫描Oracle数据库首先通过将散列函数应用于由语句指定的集群键值来获得散列值。然后Oracle数据库扫描包含具有该哈希值的行的数据块。 2.2 哈希扫描Hash Scans案例讲解 此示例对department_id列上的employees和departments表进行哈希运算然后在群集中查询单个部门。 创建一个散列簇和簇中的表如下所示 CREATE CLUSTER employees_departments_cluster(department_id NUMBER(4)) SIZE 8192 HASHKEYS 100;CREATE TABLE employees2CLUSTER employees_departments_cluster (department_id) AS SELECT * FROM employees;CREATE TABLE departments2 CLUSTER employees_departments_cluster (department_id) AS SELECT * FROM departments;查询部门30中的员工 SELECT *
FROM employees2
WHERE department_id 30为了执行散列扫描Oracle数据库首先通过对键值30应用散列函数来获得散列值然后使用该散列值来扫描数据块并检索行(步骤1)。 SQL_ID 919x7hyyxr6p4, child number 0
-------------------------------------
SELECT * FROM employees2 WHERE department_id 30Plan hash value: 2399378016----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 |
|* 1 | TABLE ACCESS HASH| EMPLOYEES2 | 10 | 1330 | |
----------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - access(DEPARTMENT_ID30) 我的发这篇文章到这里就算结束了前前后后整理了7天撰写超过10小时因为有些访问路径博主之前也不了解是边看官方文档边学习然后再整理到博客的所以看在博主这么用心这么“拼”的份上不给个“三连”支持一下良心真的不会痛吗并且文章3.6万字了需要各位小伙伴慢慢学习细细品保证你越读越有味比追剧还上瘾
