sqldeveloper的查看执⾏计划快捷键F10
简介：
本⽂全⾯详细介绍执⾏计划的相关的概念，访问数据的存取⽅法，表之间的连接等内容。
并有总结和概述，便于理解与记忆!
+++
⽬录
---
⼀．相关的概念
Rowid的概念
Recursive 概念
Predicate(谓词)
DRiving Table(驱动表)
Probed Table(被探查表)
组合索引(concatenated )
可选择性(selectivity)
⼆．oracle访问数据的存取⽅法
1）全表扫描（Full Table Scans， FTS）
2）通过ROWID的表存取（Table Access by ROWID或rowid lookup）
3）索引扫描（Index Scan或index lookup）有4种类型的索引扫描：
　（1）索引唯⼀扫描（index unique scan）
　（2）索引范围扫描（index range scan）
在⾮唯⼀索引上都使⽤索引范围扫描。使⽤index rang scan的3种情况：
  （a）在唯⼀索引列上使⽤了range操作符（> < <> >= <= between）
  （b）在组合索引上，只使⽤部分列进⾏查询，导致查询出多⾏
  （c）对⾮唯⼀索引列上进⾏的任何查询。 
　（3）索引全扫描（index full scan）
　（4）索引快速扫描（index fast full scan）
三、表之间的连接
1，排序 - - 合并连接（Sort Merge Join， SMJ）
2，嵌套循环（Nested Loops， NL）
3，哈希连接（Hash Join， HJ）
另外，笛卡⼉乘积（Cartesian Product）
总结Oracle连接⽅法
Oracle执⾏计划总结概述
+++
  ⼀．相关的概念
  Rowid的概念：rowid是⼀个伪列，既然是伪列，那么这个列就不是⽤户定义，⽽是系统⾃⼰给加上的。对每个表都有⼀个rowid的伪列，但是表中并不物理存储ROWID列的值。不过你可以像使⽤其它列那样使⽤它，但是不能删除改列，也不能对该列的值进⾏修改、插⼊。⼀旦⼀⾏数据插⼊，则rowid在该⾏的⽣命周期内是唯⼀的，即即使该⾏产⽣⾏迁移，⾏的rowid也不会改变。
  Recursive SQL概念：有时为了执⾏⽤户发出的⼀个sql语句，Oracle必须执⾏⼀些额外的语句，我们将这些额外的语句称之为''recursive calls''或''recursive SQL statements''.如当⼀个DDL语句发出后，ORACLE总是隐含的发出⼀些recursive SQL语句，来修改数据字典信息，以便⽤户可以成功的执⾏该DDL语句。当需要的数据字典信息没有在共享内存中时，经常会发⽣Recursive calls，这些Recursive calls会将数据字典信息从硬盘读⼊内存中。⽤户不⽐关⼼这些recursive SQL语句的执⾏情况，在需要的时候，ORACLE会⾃动的在内部执⾏这些语句。当然DML语句与SELECT都可能引起recursive SQL.简单的说，我们可以将触发器视为recursive SQL.
  Row Source（⾏源）：⽤在查询中，由上⼀操作返回的符合条件的⾏的集合，即可以是表的全部⾏数据的集合；也可以是表的部分⾏数据的集合；也可以为对上2个row source进⾏连接操作（如join连接）后得到的⾏数据集合。
  Predicate（谓词）：⼀个查询中的WHERE限制条件
  Driving Table（驱动表）：该表⼜称为外层表（OUTER TABLE）。这个概念⽤于嵌套与HASH连接中。如果该row source返回较多的⾏数据，则对所有的后续操作有负⾯影响。注意此处虽然翻译为驱动表，但实际上翻译为驱动⾏源（driving row source）更为确切。⼀般说来，是应⽤查询的限制条件后，返回较少⾏源的表作为驱动表，所以如果⼀个⼤表在WHERE条件有有限制条件（如等值限制），则该⼤表作为驱动表也是合适的，所以并不是只有较⼩的表可以作为驱动表，正确说法应该为应⽤查询的限制条件后，返回较少⾏源的表作为驱动表。在执⾏计划中，应该为靠上的那个row source，后⾯会给出具体说明。在我们后⾯的描述中，⼀般将该表称为连接操作的row source 1.
  Probed Table（被探查表）：该表⼜称为内层表（INNER TABLE）。在我们从驱动表中得到具体⼀⾏的数据后，在该表中寻符合连接条件的⾏。所以该表应当为⼤表（实际上应该为返回较⼤row source的表）且相应的列上应该有索引。在我们后⾯的描述中，⼀般将该表称为连接操作的row source 2.
  组合索引（concatenated index）：由多个列构成的索引，如create index idx_emp on emp（col1， col2， col3， ……），则我们称idx_emp索引为组合索引。在组合索引中有⼀个重要的概念：引导列（leading column），在上⾯的例⼦中，col1列为引导列。当我们进⾏查询时可以使⽤“where col1 = ？ ”，也可以使⽤“where col1 = ？ and col2 = ？”，这样的限制条件都会使⽤索引，但是“where col2 = ？ ”查询就不会使⽤该索引。所以限制条件中包含先导列时，该限制条件才会使⽤该组合索引。
  可选择性（selectivity）：⽐较⼀下列中唯⼀键的数量和表中的⾏数，就可以判断该列的可选择性。如果该列的“唯⼀键的数量/表中的⾏数”的⽐值越接近1，则该列的可选择性越⾼，该列就越适合创建索引，同样索引的可选择性也越⾼。在可选择性⾼的列上进⾏查询时，返回的数据就较少，⽐较适合使⽤索引查询。
  ⼆．oracle访问数据的存取⽅法
  1）全表扫描（Full Table Scans， FTS）
  为实现全表扫描，Oracle读取表中所有的⾏，并检查每⼀⾏是否满⾜语句的WHERE限制条件⼀个多块读操作可以使⼀次I/O能读取多块数据块（db_block_multiblock_read_count参数设定），⽽不是只读取⼀个数据块，这极⼤的减少了I/O总次数，提⾼了系统的吞吐量，所以利⽤多块读的⽅法可以⼗分⾼效地实现全表扫描，⽽且只有在全表扫描的情况下才能使⽤多块读操作。在这种访问模式下，每个
数据块只被读⼀次。
  使⽤FTS的前提条件：在较⼤的表上不建议使⽤全表扫描，除⾮取出数据的⽐较多，超过总量的5% —— 10%，或你想使⽤并⾏查询功能时。
  使⽤全表扫描的例⼦：
  SQL> explain plan for select * from dual;
  Query Plan
  -----------------------------------------
  SELECT STATEMENT[CHOOSE] Cost=
  TABLE ACCESS FULL DUAL
  2）通过ROWID的表存取（Table Access by ROWID或rowid lookup）
  ⾏的ROWID指出了该⾏所在的数据⽂件、数据块以及⾏在该块中的位置，所以通过ROWID来存取数据可以快速定位到⽬标数据上，是Oracle存取单⾏数据的最快⽅法。
  这种存取⽅法不会⽤到多块读操作，⼀次I/O只能读取⼀个数据块。我们会经常在执⾏计划中看到该存取⽅法，如通过索引查询数据。
  使⽤ROWID存取的⽅法：
  SQL> explain plan for select * from dept where rowid = ''AAAAyGAADAAAAATAAF''；
  Query Plan
  ------------------------------------
  SELECT STATEMENT [CHOOSE] Cost=1
  TABLE ACCESS BY ROWID DEPT [ANALYZED]
  3）索引扫描（Index Scan或index lookup）
  我们先通过index查到数据对应的rowid值（对于⾮唯⼀索引可能返回多个rowid值），然后根据rowid直接从表中得到具体的数据，这种查⽅式称为索引扫描或索引查（index lookup）。⼀个rowid唯⼀的表⽰⼀⾏数据，该⾏对应的数据块是通过⼀次i/o得到的，在此情况下该次i/o只会读取⼀个数据库块。
  在索引中，除了存储每个索引的值外，索引还存储具有此值的⾏对应的ROWID值。
  索引扫描可以由2步组成：
  （1）扫描索引得到对应的rowid值。
  （2）通过到的rowid从表中读出具体的数据。
  每步都是单独的⼀次I/O，但是对于索引，由于经常使⽤，绝⼤多数都已经CACHE到内存中，所以第1步的 I/O经常是逻辑I/O，即数据可以从内存中得到。但是对于第2步来说，如果表⽐较⼤，则其数据不可能全在内存中，所以其I/O很有可能是物理I/O，这是⼀个机械操作，相对逻辑I/O来说，是极其费时间的。所以如果多⼤表进⾏索引扫描，取出的数据如果⼤于总量的5% —— 10%，使⽤索引扫描会效率下降很多。如下列所⽰：
  SQL> explain plan for select empno， ename from emp where empno=10；
  Query Plan
  ------------------------------------
  SELECT STATEMENT [CHOOSE] Cost=1
  TABLE ACCESS BY ROWID EMP [ANALYZED]
  INDEX UNIQUE SCAN EMP_I1
  但是如果查询的数据能全在索引中到，就可以避免进⾏第2步操作，避免了不必要的I/O，此时即使通过索引扫描取出的数据⽐较多，效率还是很⾼的
  SQL> explain plan for select empno from emp where empno=10;-- 只查询empno列值
  Query Plan
  ------------------------------------
  SELECT STATEMENT [CHOOSE] Cost=1
  INDEX UNIQUE SCAN EMP_I1
  进⼀步讲，如果sql语句中对索引列进⾏排序，因为索引已经预先排序好了，所以在执⾏计划中不需要再对索引列进⾏排序
  SQL> explain plan for select empno, ename from emp
  where empno > 7876 order by empno;
  Query Plan
  --------------------------------------------------------------------------------
  SELECT STATEMENT[CHOOSE] Cost=1
  TABLE ACCESS BY ROWID EMP [ANALYZED]
  INDEX RANGE SCAN EMP_I1 [ANALYZED]
  从这个例⼦中可以看到：因为索引是已经排序了的，所以将按照索引的顺序查询出符合条件的⾏，因此避免了进⼀步排序操作。
  根据索引的类型与where限制条件的不同，有4种类型的索引扫描：
  索引唯⼀扫描（index unique scan）
  索引范围扫描（index range scan）
  索引全扫描（index full scan）
  索引快速扫描（index fast full scan）
  （1）索引唯⼀扫描（index unique scan）
  通过唯⼀索引查⼀个数值经常返回单个ROWID.如果存在UNIQUE 或PRIMARY KEY 约束（它保证了语句只存取单⾏）的话，Oracle经常实现唯⼀性扫描。
  使⽤唯⼀性约束的例⼦：
  SQL> explain plan for
  select empno，ename from emp where empno=10；
  Query Plan
  ------------------------------------
  SELECT STATEMENT [CHOOSE] Cost=1
  TABLE ACCESS BY ROWID EMP [ANALYZED]
  INDEX UNIQUE SCAN EMP_I1
  （2）索引范围扫描（index range scan）
  使⽤⼀个索引存取多⾏数据，在唯⼀索引上使⽤索引范围扫描的典型情况下是在谓词（where限制条件）中使⽤了范围操作符（如>、<、<>、>=、<=、between）  使⽤索引范围扫描的例⼦：
  SQL> explain plan for select empno，ename from emp
  where empno > 7876 order by empno；
  Query Plan
  --------------------------------------------------------------------------------
  SELECT STATEMENT[CHOOSE] Cost=1
  TABLE ACCESS BY ROWID EMP [ANALYZED]
  INDEX RANGE SCAN EMP_I1 [ANALYZED]
  在⾮唯⼀索引上，谓词col = 5可能返回多⾏数据，所以在⾮唯⼀索引上都使⽤索引范围扫描。
  使⽤index rang scan的3种情况：创建唯一约束sql语句
  （a）在唯⼀索引列上使⽤了range操作符（> < <> >= <= between）
  （b）在组合索引上，只使⽤部分列进⾏查询，导致查询出多⾏
  （c）对⾮唯⼀索引列上进⾏的任何查询。
  （3）索引全扫描（index full scan）
  与全表扫描对应，也有相应的全索引扫描。⽽且此时查询出的数据都必须从索引中可以直接得到。
  全索引扫描的例⼦：
  An Index full scan will not perform. single block i/o''s and so it may prove to be inefficient.
  e.g.
  Index BE_IX is a concatenated index on big_emp （empno， ename）
  SQL> explain plan for select empno， ename from big_emp order by empno，ename；
  Query Plan
  --------------------------------------------------------------------------------
  SELECT STATEMENT[CHOOSE] Cost=26
  INDEX FULL SCAN BE_IX [ANALYZED]
  （4）索引快速扫描（index fast full scan）
  扫描索引中的所有的数据块，与 index full scan很类似，但是⼀个显著的区别就是它不对查询出的数据进⾏排序，即数据不是以排序顺序被返回。在这种存取⽅法中，可以使⽤多块读功能，也可以使⽤并⾏读⼊，以便获得最⼤吞吐量与缩短执⾏时间。
  索引快速扫描的例⼦：
  BE_IX索引是⼀个多列索引： big_emp （empno，ename）
  SQL> explain plan for select empno，ename from big_emp；
  Query Plan
  ------------------------------------------
  SELECT STATEMENT[CHOOSE] Cost=1
  INDEX FAST FULL SCAN BE_IX [ANALYZED]
  只选择多列索引的第2列：
  SQL> explain plan for select ename from big_emp；
  Query Plan
  ------------------------------------------
  SELECT STATEMENT[CHOOSE] Cost=1
  INDEX FAST FULL SCAN BE_IX [ANALYZED]
  三、表之间的连接
  Join是⼀种试图将两个表结合在⼀起的谓词，⼀次只能连接2个表，表连接也可以被称为表关联。在后⾯的叙述中，我们将会使⽤“row source”来代替“表”，因为使⽤row source更严谨⼀些，并且将参与连接的2个row source分别称为row source1和row source 2.Join过程的各个步骤经常是串⾏操作，即
使相关的row source可以被并⾏访问，即可以并⾏的读取做join 连接的两个row source的数据，但是在将表中符合限制条件的数据读⼊到内存形成row source后，join的其它步骤⼀般是串⾏的。有多种⽅法可以将2个表连接起来，当然每种⽅法都有⾃⼰的优缺点，每种连接类型只有在特定的条件下才会发挥出其最⼤优势。
  row source（表）之间的连接顺序对于查询的效率有⾮常⼤的影响。通过⾸先存取特定的表，即将该表作为驱动表，这样可以先应⽤某些限制条件，从⽽得到⼀个较⼩的row source，使连接的效率较⾼，这也就是我们常说的要先执⾏限制条件的原因。⼀般是在将表读⼊内存时，应⽤where⼦句中对该表的限制条件。
  根据2个row source的连接条件的中操作符的不同，可以将连接分为等值连接（如WHERE A.COL3 = B.COL4）、⾮等值连接（WHERE A.COL3 > B.COL4）、外连接（WHERE A.COL3 = B.COL4（+））。上⾯的各个连接的连接原理都基本⼀样，所以为了简单期间，下⾯以等值连接为例进⾏介绍。
  在后⾯的介绍中，都以以下Sql为例进⾏说明：
  SELECT A.COL1， B.COL2
  FROM A， B
  WHERE A.COL3 = B.COL4；
  假设A表为Row Soruce1，则其对应的连接操作关联列为COL 3；
  B表为Row Soruce2，则其对应的连接操作关联列为COL 4；
  连接类型：
  ⽬前为⽌，⽆论连接操作符如何，典型的连接类型共有3种：
  排序 - - 合并连接（Sort Merge Join （SMJ））
  嵌套循环（Nested Loops （NL））
  哈希连接（Hash Join）
  另外，还有⼀种Cartesian product（笛卡尔积），⼀般情况下，尽量避免使⽤。
  1，排序 - - 合并连接（Sort Merge Join， SMJ）
  内部连接过程：
  1）⾸先⽣成row source1需要的数据，然后对这些数据按照连接操作关联列（如A.col3）进⾏排序。
  2）随后⽣成row source2需要的数据，然后对这些数据按照与sort source1对应的连接操作关联列（如B.col4）进⾏排序。
  3）最后两边已排序的⾏被放在⼀起执⾏合并操作，即将2个row source按照连接条件连接起来
  下⾯是连接步骤的图形表⽰：
  MERGE
  /\
  SORTSORT
  ||
  Row Source 1Row Source 2
  如果row source已经在连接关联列上被排序，则该连接操作就不需要再进⾏sort操作，这样可以⼤⼤提⾼这种连接操作的连接速度，因为排序是个极其费资源的操作，特别是对于较⼤的表。预先排序的r
ow source包括已经被索引的列（如a.col3或b.col4上有索引）或row source已经在前⾯的步骤中被排序了。尽管合并两个row source的过程是串⾏的，但是可以并⾏访问这两个row source（如并⾏读⼊数据，并⾏排序）。
  SMJ连接的例⼦：
  SQL> explain plan for
  select/*+ ordered */e.deptno， d.deptno
  from emp e， dept d
  where e.deptno = d.deptno
  order by e.deptno， d.deptno；
  Query Plan
  -------------------------------------
  SELECT STATEMENT [CHOOSE] Cost=17
  MERGE JOIN
  SORT JOIN
  TABLE ACCESS FULL EMP [ANALYZED]
  SORT JOIN
  TABLE ACCESS FULL DEPT [ANALYZED]
  排序是⼀个费时、费资源的操作，特别对于⼤表。基于这个原因，SMJ经常不是⼀个特别有效的连接⽅法，但是如果2个row source都已经预先排序，则这种连接⽅法的效率也是蛮⾼的。
  2，嵌套循环（Nested Loops， NL）
  这个连接⽅法有驱动表（外部表）的概念。其实，该连接过程就是⼀个2层嵌套循环，所以外层循环的次数越少越好，这也就是我们为什么将⼩表或返回较⼩ row source的表作为驱动表（⽤于外层循环）的理论依据。但是这个理论只是⼀般指导原则，因为遵循这个理论并不能总保证使语句产⽣的I/O次数最少。有时不遵守这个理论依据，反⽽会获得更好的效率。如果使⽤这种⽅法，决定使⽤哪个表作为驱动表很重要。有时如果驱动表选择不正确，将会导致语句的性能很差、很差。
  内部连接过程：
  Row source1的Row 1 —— Probe ->Row source 2
  Row source1的Row 2 —— Probe ->Row source 2
  Row source1的Row 3 —— Probe ->Row source 2
  ……。
  Row source1的Row n —— Probe ->Row source 2
  从内部连接过程来看，需要⽤row source1中的每⼀⾏，去匹配row source2中的所有⾏，所以此时保持row source1尽可能的⼩与⾼效的访问row source2（⼀般通过索引实现）是影响这个连接效率的关键问题。这只是理论指导原则，⽬的是使整个连接操作产⽣最少的物理I/O次数，⽽且如果遵守这个原则，⼀般也会使总的物理I/O数最少。但是如果不遵从这个指导原则，反⽽能⽤更少的物理I/O实现连接操作，那尽管违反指导原则吧！因为最少的物理 I/O次数才是我们应该遵从的真正的指导原则，在后⾯的具体案例分析中就给出这样的例⼦。
  在上⾯的连接过程中，我们称Row source1为驱动表或外部表。Row Source2被称为被探查表或内部表。
  在NESTED LOOPS连接中，Oracle读取row source1中的每⼀⾏，然后在row sourc2中检查是否有匹配的⾏，所有被匹配的⾏都被放到结果集中，然后处理row source1中的下⼀⾏。这个过程⼀直继续，直到row source1中的所有⾏都被处理。这是从连接操作中可以得到第⼀个匹配⾏的最快的⽅法之⼀，这种类型的连接可以⽤在需要快速响应的语句中，以响应速度为主要⽬标。
  如果driving row source（外部表）⽐较⼩，并且在inner row source（内部表）上有唯⼀索引，或有⾼选择性⾮唯⼀索引时，使⽤这种⽅法可以得到较好的效率。NESTED LOOPS有其它连接⽅法没有的的⼀个优点是：可以先返回已经连接的⾏，⽽不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。
  如果不使⽤并⾏操作，最好的驱动表是那些应⽤了where 限制条件后，可以返回较少⾏数据的的表，所以⼤表也可能称为驱动表，关键看限制条件。对于并⾏查询，我们经常选择⼤表作为驱动表，因为⼤表可以充分利⽤并⾏功能。当然，有时对查询使⽤并⾏操作并不⼀定会⽐查询不使⽤并⾏操作效率⾼，因为最后可能每个表只有很少的⾏符合限制条件，⽽且还要看你的硬件配置是否可以⽀持并⾏（如是否有多个CPU，多个硬盘控制器），所以要具体问题具体对待。
  NL连接的例⼦：
  SQL> explain plan for
  select a.dname，b.sql
  from dept a，emp b

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。