使用Oracle9i全索引扫描快速访问数据

最新推荐文章于 2024-11-08 23:38:17 发布

bianfuxia

最新推荐文章于 2024-11-08 23:38:17 发布

阅读量814

点赞数

分类专栏：技术拾贝文章标签： oracle sql 优化 parallel date 数据库

技术拾贝专栏收录该内容

16 篇文章 0 订阅

订阅专栏

为了实现Oracle关于增加SQL优化查询智能的承诺，Oracle9i增强了全索引SQL执行计划以支持基于功能的索引（function-based index）。在Oracle8中，SQL优化器添加了判断查询是否可以专门用一个现存的索引来解决的智能。一旦存在索引，Oracle就会绕过对表的访问，索引组织表（index-organized table，IOT）结构就是一个例子。在IOT结构中，所有的数据都载入索引的b-树结构，这样表（table）就成为一个多余的东西了。

一旦Oracle SQL优化器检测到查询无需访问表时，Oracle就调用全索引扫描并快速读取每一个索引块而无需接触表本身。有一点很重要：全索引扫描并没有读取索引节点，而是一块一块的执行扫描并快速捕获索引节点。最好，Oracle调用多块读取功能，调用多个过程来读取表。

Oracle和多块读取

为了加快表和索引的访问速度，Oracle使用了db_file_multiblock_read_count参数（默认参数为8）来辅助把全表扫描和全索引扫描所获得的数据块尽快送到数据缓冲区中。然而，这个参数只有当SQL查询执行全表扫描时才可用，并且，在绝大多数情况下，查询要使用索引来访问表。

Oracle对全索引扫描有如下限制：

SQL请求的全部列（column）必须驻留在索引树中；也就是说，SELECT和WHERE字句中的所有数据列必须存在于索引中。
查询访问大量的行（row）。根据你查询的范围，比例变化范围为10％到25％之间，这个比例参数db_file_multiblock_read_count的设置和查询的并行程度极大的影响到这个比例。
由于索引节点并没有按索引顺序排列，所以列并没有顺序。这样，ORDER BY字句将要求附加的排序操作。

Oracle提供了一个SQL提示（hint）来强制全索引扫描。你也可以通过指定index_ffs提示来强制快速索引扫描，这常常与parallel_index提示组合来提高性能。例如，下面的查询强迫使用并行快速全索引扫描：

select distinct /*+ index_ffs(c,pk_auto) parallel_index_
   (automobile, pk_auto) color, count(*)
from
automobiles
group by color;

由于涉及了所有的变量，所以全索引是否会加快查询速度并不能简单的加以回答。所以，大多数有经验的SQL调试者（tuner）会对符合快速全索引扫描标准的查询进行手工计时，看看使用全索引扫描的反映时间是否会降低。

函数索引基础

函数索引基础

在Oracle9i之前，全索引扫描只有当创建的索引没有空值时才可以使用，也就是说，Oracle建立索引时必须用一个NOT NULL子句才可以使用该索引。由于Oracle9i支持用函数（function-based）索引实现的唯索索引扫描，这种情况大大改观。

简单回归一下，函数索引是Oracle8的一个重要改进，因为它提供了一种有效的消除无必要长表全扫描的机制，由于函数索引可以在任何查询语句中的WHERE子句中精确复制，Oracle总会用一个索引来匹配上SQL查询的WHERE子句。

现在，我要会用一个student表来举一个简单的例子来解释全索引扫描如何与函数索引一起工作的过程。

创建student表

    (student_name varchar2(40), date_of_birth date);

使用这个表，创建与表中所有列相关联的函数索引。在本例中，该函数为initcap（即大写每个单词的首字母）和to_char（即把一个数字变成字符）：

create index whole_student
on student
(initcap(student_name), to_char(date_of_birth,’MM-DD-YY’));

定义完函数索引后，Oracle9i中任何可以引用这些列（column）的SQL语句都可以使用全索引扫描。下面是SQL匹配函数索引的查询例子：

select * from student
where initcap(student_name) = ‘Jones’;
select * from student
where to_char(date_of_birth,’MM-DD=YY’) = ’04-07-85’;

用函数索引来调用全索引扫描

Oracle9i只要可能就会使用函数索引并在函数索引的基础上调用全索引扫描。如果SQL优化器统计结果表明使用全索引扫描的速度将会超过通过索引的b-树访问的速度，那么Oracle9i就会这么做。

下面是用函数索引调用唯索扫描的准则。所有的SQL谓词匹配索引中的列，查询必须从表中返回足够多的行，这样做的目的是为了让代价优化器（cost -based optimizer）来判断全索引扫描是否要比传统的索引访问方法要快。是否调用全索引扫描的决定取决于下面几个参数设置：

代价优化器的适当统计数字——这个计划（schema）最近已经分析过了，而且optimizer_mode参数不能设置为RULE。
索引的并行程度——注意索引的并行程度是独立设置的；索引并没有继承表的并行程度。
optimizer_index_cost_adj的设置——它控制代价优化器是否倾向于全索引扫描。
db_file_multiblock_read_count的设置——这个参数影响到全索引扫描的代价。这个值越高，全索引扫描的代价也就会越“便宜”。
索引的直方图表示——对偏移（skewed）索引，它帮助代价优化器评估查询返回的行数。

Oracle的一个重要提高

在函数索引基础上的快速全索引扫描是 Oracle9i的另一个提高性能的途径。当数据库迁移到Oracle9i时，许多数据库自动开始使用这个新的执行计划。不过，当SQL代价优化器决定是否选择全索引扫描时，还要考虑几个因素。Oracle专业人员需要适当设置参数，以确保代价优化器不会用不合适的方式使用快速全索引扫描——这一点需要特别注意。