Oracle 访问路径

环境oracle 10g r2

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

毫无疑问access path是极其重要的。

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

 

定义：从数据库中提取数据的方式。

通常，提取少量数据的时候，使用索引，而提取表格一大部分数据的时候，全表扫描更好一些。

对于OLTP系统而言，通常会使用索引，因为所提取的数据相对比较有选择性。

对于OLAP,或者DSS系统而言，则用全表扫描。

 

讨论的内容：

• 全表扫描
• ROWID（行号）扫描
• 索引扫描
• 簇访问
• 哈希访问
• 表抽样扫描
• 查询优化器是如何选择访问路径的

 

一、全表扫描

     所谓全表扫描指的是从表格中读取所有记录，并过滤掉所有不符合条件的内容。

     在全表扫描的时候，在高水位线（HWM)下的所有块都会扫描。HWM标记使用过的空间，或者是被标记为准备接受数据的空间。

     每一行都需要用WHere后面的条件来过滤。

     当oracle执行全表扫描的时候，数据块是连续读取的。因为块是相邻的，比单个块大的I/O调用能够提高处理的速度。读调用的大小（块）会从1块到DB_FILE_MULTIBLOCK_READ_COUNT参数确定的值之间变化。使用多块读意味着一个全表扫描会变得非常高效。每个块只会被读取一次。

     为什么全表扫描在访问大量数据的时候更快？

     在访问一个表格的大部分的数据的时候，全表扫描比索引扫描快。这是因为全表扫描可以使用更大的I/O调用，更少的大I/O调用当然比许多的小调用来得划算。（还有些因素没有详细说出）。

     什么时候优化器利用全表扫描？

     以下的任意一种情况都会利用到全表扫描

     缺乏索引 如果无法利用任何现存的索引，那么就使用全表扫描。要么是没有索引，要么是索引不可用。索引不可用的情况很多，一个简单的例子，例如在一个索引列上使用函数，那么对应索引不会被用到 。

     大部分的数据  如果优化器认为查询会访问表格中大部分的数据，那么会使用全表扫描，即使索引是可用的。

     小表 如果表HWM下块的数量比DB_FILE_MULTIBLOCK_READ_COUNT参数值还小（是否包含呢），那么就能够在一次I/O中读取。那么毫无疑问全表扫描可能比索引范围扫描来得快。

     高并行度(high degree of parallelism)  并行度高的情况下，优化器倾向于使用全表扫描。检查ALL_TABLES中DEGREE列确定并行度。 

     全表扫描提示  这是毫无疑问的/*+ FULL(A) */.这个时候可以使用CACHE或者NOCACHE的提示来指示数据块是否存放在数据缓冲区。CACHE指令提示优化器把提取的数据块放在LRU的最近端（这样能更容易访问，且更不容易过时）

   小表在符合下表标准的情况下，是自动缓存的：

尺寸	标准	缓存
小	块数<20,或者2%的缓存块	如果statistics_level=TYPICAL或者更高，那么oracle是根据表的扫描历史来缓存表格的。仅当未来的一个表格扫描可能找到缓存快的情况下 ，oracle才会缓存表的数据块。如果STATISTICS_LEVEL=BASIC，那么表格的数据块是不缓存的。
中等	比一个小表大，或者<10%的内容被缓存了	根据表扫描和负载历史来确定是否缓存。仅当发现将来的扫描中可能可以在缓存中找到数据块的时候才会缓存（有点复杂的机制）
大	>10%的数据块要缓存	不缓存

    小表的自动缓存是禁止的，如果在创建或者alter命令中使用cache属性（不太明白).

    并行查询执行 当被要求全表扫描的时候，相应时间可能可以通过使用多个并行的执行服务器来提高。并行查询通常用在并发度较低的数据仓库环境中使用，因为潜在的资源利用（较底）。  

二、ROWID（行号）扫描

     行号确定数据文件和数据块。通过定位行号是提取单行数据的最快方式，因为行的确切位置在数据库中是确定的。

     为了用行号来访问表，oracle首先获得选定行的行号数据，要么从语句的where语句，要么是通过索引扫描获得。oracle然后通过行号来定位每条选定的行。

     什么时候优化器使用行号扫描

     使用行号通常是从索引中获得行号的下一步。对表格的扫描可能会要求访问索引中没有涉及的任意列。

     不是每个索引扫描后都是行号的访问。如果索引包含了语句中需要的所有行，那么行号扫描就不会发生。

     注意：行号是oracle内部表明数据存储的方式。版本的变化可能导致行号变化。索引通过行号来访问数据是不推荐的，因为行可能会因为行的迁移或者串连而移动，也可能在通过export,import后发生变化。外键应该基于主键上（为什么？）

三、索引扫描

     所谓索引扫描就是通过SQL语句中的列相关索引来提取行数据。索引扫描基于索引上的列来提取数据。为了执行索引扫描，oracle在索引中查找指定的索引值。如果SQL语句仅仅访问索引中的列 ，那么oracle执行从索引中返回需要的值，而不是从表格中。

     索引不仅仅包含索引的值，还包含了含指定值的列的行号（rowid）。因此 ，如果sql语句访问的列不在索引中，那么oracle通过行号或者簇扫描来访问表，以返回数据。

     一个索引扫描可以下列类型之一：

• 访问I/O数据块，而不是行
• 索引唯一扫描
• 索引范围扫描
• 索引倒序范围扫描
• 索引跳跃扫描
• 全扫描
• 快速全索引扫描
• 索引连接
• 位图索引

     访问I/O数据块，而不是行

     oracle是按照块的方式来I/O的（或者说每次读写的数据都是块的N倍大小）。因此优化器决定全表扫描的条件是访问块的百分比而不是行的百分比。这就是所谓的索引簇因子。如果一个块只有一行，那么访问块和访问行是一回事。

     无论如何，绝大部分的表格一个块中包含了多行。因此，需要的行可能是集聚在少数的几个块中，或者他们可能是分布在许多的块中。

     虽然簇因子是索引的属性，簇因子实际和表数据块中相近索引列的分布有关。一个低簇因子意味着行分布在更少的数据块中。相反，高的簇因子意味着行分布在更多的数据块中。因此，高的簇因子意味着需要耗费更多资源通过行号来执行范围扫描，因为更多的数据块需要访问，以返回需要的数据。

     例子13-3 簇因子对成本的影响

     假设存在以下条件：

     只有9行数据

     列col1上有个非唯一索引

     列C1上包含的值有A,B,C

     表格只包含3个数据块

     情况1：如下图的低簇因子的数据分布图：

        Block 1 Block 2 Block 3 
        ------- ------- -------- 
        A A A    B B B   C C C

     这是因为具有同样索引列值的行都是在同一个物理块内。返回所有含A的行的范围扫描的成本低，因为仅仅只有一块数据需要读取。

     情况2：分散后的高簇因子图示：

        Block 1 Block 2 Block 3 
        ------- ------- -------- 
        A B C    A B C   A B C

     因为为了返回所有的包含A值的行，必须读取3块数据。

     

     3.1索引唯一扫描

     这种扫描至多返回个单独的行号。如果SQL语句（where列）包含 一个唯一或者主键约数，那么oracle会执行唯一扫描。

     什么时候优化器利用唯一索引扫描

     SQL语句的等值比较中存在唯一索引列

     索引唯一扫描提示

     通常，没有必要使用这个提示来执行索引唯一扫描。如果有DBLINK或者表过分小才有必要强制。

     INDEX(alias index_name) 设置了需要用的索引，但不是访问路径（范围扫描或者唯一扫描）

     
     3.2索引范围扫描

     INDEX RANGE SCANS    

     索引范围扫描是获取选定数据的常用操作。

     It can be bounded(bounded on both sides) or unbounded(on one or both sides)  ?

     数据是按照升序的方式返回或者是索引列（有的时候仅仅返回索引列）。具有不同的值的行的数据是按照行号升序的方式存储。

     如果数据必须按照指定顺序排序，那么使用order by 字句，而不是依赖于索引。如果索引能够满足order by 语句，那么优化器就会考虑使用索引的顺序，并避免排序--（这需要注意，并花费时间观察的）。

     下例中，就是使用范围扫描，并使用索引排序的。     

SQL> DESC TEST_RANGESCAN;  Name    Type         Nullable Default Comments  ------- ------------ -------- ------- --------  YYBID   NUMBER(12)                              YYBMC   VARCHAR2(64)                            BK      NUMBER(10)   Y                          YYBTYPE NUMBER(2)    Y SQL> explain plan for SELECT a.*,rowid FROM TEST_RANGEsCAN a WHERE YYBTYPE=2    2  /  Explained  SQL> select * from table(dbms_xplan.display);  PLAN_TABLE_OUTPUT  --------------------------------------------------------------------------------  Plan hash value: 2423677275  --------------------------------------------------------------------------------  | Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP  --------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |                |    24 |  2040 |     2   (  |   1 |  TABLE ACCESS BY INDEX ROWID| TEST_RANGESCAN |    24 |  2040 |     2   (  |*  2 |   INDEX RANGE SCAN          | IND_TESTTYPE   |    24 |       |     1   (  --------------------------------------------------------------------------------  Predicate Information (identified by operation id):  ---------------------------------------------------     2 - access("YYBTYPE"=2)  Note  -----     - dynamic sampling used for this statement  18 rows selected 结果如下： SQL> select substr(yybmc,1,4) hh,rowid from test_rangeScan where yybtype=2;  HH       ROWID  -------- ------------------  湖北省   AAAXXHAARAAADflAAX  贵州省   AAAXXHAARAAADflAAY  河南省   AAAXXHAARAAADflAAZ  四川省   AAAXXHAARAAADflAAa  湖南省   AAAXXHAARAAADflAAb  福建省   AAAXXHAARAAADflAAc  山西省   AAAXXHAARAAADflAAd  新疆     AAAXXHAARAAADflAAe  江苏省   AAAXXHAARAAADflAAf  吉林省   AAAXXHAARAAADflAAg  重庆市   AAAXXHAARAAADflAAh  山东省   AAAXXHAARAAADflAAi  广西     AAAXXHAARAAADflAAj  天津市   AAAXXHAARAAADflAAk  陕西省   AAAXXHAARAAADflAAl  黑龙江省 AAAXXHAARAAADflAAm  辽宁省   AAAXXHAARAAADflAAn  云南省   AAAXXHAARAAADflAAo  浙江省   AAAXXHAARAAADflAAp  北京市   AAAXXHAARAAADflAAq  HH       ROWID  -------- ------------------  青海省   AAAXXHAARAAADflAAr  广东省   AAAXXHAARAAADflAAs  上海市   AAAXXHAARAAADflAAt  海南省   AAAXXHAARAAADflAAu  24 rows selected 这24行的rowid是完全有序的，的确是按照rowid升序输出的。

     什么时候优化器使用索引范围扫描

     当优化器发现索引中的一个或者多个重要（leading）列在条件中出现，例如：

     col1=:b1

     col1<:b1

     col1>:b1

     and 后接上3中情况的。

     col1 like  ‘ASD%’  ,通配符不能在收尾，否则会不会使用索引范围扫描.

     索引范围扫描可以使用唯一或者非唯一索引。当索引列被包含在ORDER BY/GROUP BY字句中的时候， 范围扫描会避免使用排序。

     INDEX RANGE SCAN提示

     /*+ index(table_alias index_name) */

     遗留问题还是不少，因为实际的例子不能确定需要的内容。    

     
     3.3索引倒序范围扫描

     索引倒序范围扫描和索引范围扫描基本一样，除了前者返回的结果是倒序的。

     索引，默认是按照顺序方式存储。

     通常，这种扫描方式用于返回按照倒序排序的最近的数据，或者是用于搜索小于某个特定值的值。

     什么时候优化器使用索引倒序范围扫描

     当索引可以满足一个order by desc的语句的时候，使用。

     应该如何理解呢？

     举例吧：

     表格还是test.test_rangescan

SQL> EXPLAIN PLAN FOR SELECT * FROM TEST.TEST_RANGESCAN WHERE YYBTYPE<3 ORDER BY YYBTYPE DESC    2  /  Explained  SQL> SELECT * FROM TABLE(DBMS_XPLAN.display);  PLAN_TABLE_OUTPUT  --------------------------------------------------------------------------------  Plan hash value: 1165466183  --------------------------------------------------------------------------------  | Id  | Operation                    | Name           | Rows  | Bytes | Cost (%C  --------------------------------------------------------------------------------  |   0 | SELECT STATEMENT             |                |   121 |  2783 |     4  |   1 |  TABLE ACCESS BY INDEX ROWID | TEST_RANGESCAN |   121 |  2783 |     4  |*  2 |   INDEX RANGE SCAN DESCENDING| IND_TESTTYPE   |   121 |       |     1  --------------------------------------------------------------------------------  Predicate Information (identified by operation id):  ---------------------------------------------------     2 - access("YYBTYPE"<3)  14 rows selected   这里用到了小于某个值的操作，而且还进行排序（按照索引字段倒序排序）

     
     3.4索引跳跃扫描

     索引跳跃扫描通过非前缀的列来提高索引扫描。通常，扫描索引块比扫描数据块来得快。

     跳跃扫描的作用就是把一个复合索引分割了逻辑上更小的子索引。

     在跳跃扫描过程中，复合索引的第一列并不出现在查询语句中（指select列），换句话说，它被跳过了。

     逻辑子索引的个数取决于第一列中唯一值的个数（有点难于了解，应该是指。。。。）。

     在前导列有很少唯一值（大部分值相同）或者是非前导列中相同的值较少的情况下，跳跃索引扫描具有优势。

     想举个例子，但是好像并不容易。

     在实际的过程中，一个执行计划是否一定使用索引跳跃扫描是有很多因素的。但如果你使用INDEX提示，那么索引通常还是可以用到的。

     此外有两句需要格外注意 ，就是红色和蓝色的两列：子索引个数，出现优势的情况。

     书本上的例子还是可以的：

Consider, for example, a table employees (sex, employee_id, address) with a  composite index on (sex, employee_id). Splitting this composite index would result  in two logical subindexes, one for M and one for F.  For this example, suppose you have the following index data:  ('F',98)  ('F',100)  ('F',102)  ('F',104)  ('M',101)  ('M',103)  ('M',105)  The index is split logically into the following two subindexes:  ■ The first subindex has the keys with the value F.  ■ The second subindex has the keys with the value M.

     
     3.5全扫描

     注意是全索引扫描，不是全表扫描。

     如果谓词关联索引中一列，就有可能产生全索引扫描 。谓词不必是索引驱动（？）。

     当没有谓词，且满足以下两个条件的时候，全索引扫描也会发生：

• 查询中的所有列都在索引中。
• 至少索引中的一个列是非空的。

     全索引扫描可以避免排序，因为索引中的数据是排好序的。且一个一个数据块读的（这能保证有序）。--

     第二个条件务必要满足，因为索引中空值是不体现出来的？

     3.6快速全索引扫描

     又快又全的索引扫描，不保证排序。

     当查询中所包含的列包含在索引中，且索引中至少一个列是非空的情况下，那么 快速全索引扫描就可以作为全表扫描替代操作。快速全索引扫描从索引中获得数据，而不是从表格中获得。

     快速全索引扫描不能用于避免排序操作，因为数据并不是按照索引键来排序的（？）。因为每次读取索引块的时候是读取多块的，不象全索引扫描那样，是可以并行的。

     通过初始化参数OPTIMIZER_FEATURES_ENABLE或者提示INDEX_FFS来设置快速全索引扫描。

     在位图索引上不能执行快速全索引扫描（因为它们不存储数据）。

     快速全索引扫描比全索引扫描快，因为一次读多块，且可以并行。

     快速全索引扫描提示

     提示INDEX_FFS 格式和INDEX提示一样。

     
     3.7索引连接

     所谓索引连接就是包含了查询中所有列的索引之间的哈希连接。如果使用了索引连接，那么就无需读取表格了，因为数据从索引来。索引连接不能用来派出排序操作。

     索引连接提示

     INDEX_JOIN

     3.8位图索引

     位图连接利用位图来存储键值，并利用映射函数来把每个比特的位置转换为行号。位图能有效地合并多个索引（where语句中多个条件关联到的），它是利用布尔操作来解决 and和or条件的。

     注意：位图索引和位图连接索引只有在oracle 企业版本中存在。

 

四、簇访问

     只有有簇索引的情况下才可以使用簇访问。访问簇索引之后，获得行号，再用行号访问表格。

 

五、哈希访问

     只有有哈希簇的情况下才可以使用哈希访问。原理和簇访问一样，不同是这里簇中存储的是哈希键值和行号，而簇索引中存储的是普通键值和行号。

 

六、表抽样扫描

     从表格或者视图中随机返回一定行。

     只有FROM 语句中包含了SAMPLE或者SAMPLE BLOCK语句的时候，才会采取这个扫描。

     SAMPLE的时候，ORACLE读取一定百分比的行。

     SAMPLE BLOCK的时候，ORACLE读取一定百分比的块。

     这二者还是有一定的区别的。

    

七、查询优化器是如何选择访问路径的

     2010/09/17

     oracle按照以下因素来选择存取路径：

     1)语句可用的存取路径。

     2)利用每个或者多个存取路径，估计执行语句所需要花费的成本。

     首先，通过检查语句中where字句的条件以及FROM字句的内容来确认哪些存取路径可以利用。然后根据可以利用的索引，列以及表格的统计信息来产生每个存取路径的执行计划（实际上不会每个路径都去执行），并估计每个计划的成本。最后，选择成本最低的那个执行计划。

      在选择存取路径的时候，查询优化器是受到下列因素影响的：

      1) 优化器提示

      如果where字句没有包含sample或者sample block，那么可以使用提示。

      2)老的统计信息

      例如，如果一个表格自创建依赖，从来没有分析过（analyzed),那优化器会认为它的低于高水位标志的块数比db_file_multiblock_read_count小，以至于采取全表扫描。查看ALL_TABLES中的LAST_ANALYZED和BLOCKS列以检查统计信息。