本章讨论存取经过适当规范化的数据时，经常遇到的情况。虽然本章主要讨论查询，但也适用于更新和删除操作，只要它们也有where 子句，毕竟要先读取数据才能修改数据。无论是单纯为了查询、还是更新或删除记录，过滤数据会遇到的最典型情况有九种：

l 小结果集，源表较少，查询条件直接针对源表

l 小结果集，查询条件涉及源表之外的表

l 小结果集，多个宽泛条件，结果取交集

l 小结果集，一个源表，查询条件宽泛且涉及多个源表之外的表

l 大结果集

l 结果集来自基于一个表的自连接

l 结果集以聚合函数为基础获得

l 结果集通过简单搜索或基于日期的范围搜索获得

l 结果集和别的数据存在与否有关

本章将依次讨论上述各种情况。至于例子，有的简单明了，有的较为复杂（来自实际案例）。虽然案例大小存在差异，但解决问题的模式是相通的。

通常，在执行查询时，应过滤掉所有不属于结果集的数据，这意味着应尽量采用最高效的搜索条件。决定先执行哪个条件，通常是优化器的工作。但是，正如第4章所述，优化器必须考虑大量不同情况——例如表的物理结构、查询编写方式等，所以优化器未必总能“理解正确”。因此，提高性能还有很多事情可做，下面对九种模式的讨论中，每种模式均是如此。

小结果集，直接条件

Small Result Set, Direct Specific Criteria

对于典型的在线交易处理，多为返回小结果集的查询，源表数量较少，查询条件也是“直接”针对源表的。当我们要通过一组条件查询出少许记录时，首先要注意的就是索引。

一般而言，通过一个表或通过两个表的连接查询较少记录，只要确保查询有适当的索引支持即可。然而，当很多表连接在一起，并且查询条件要参照不同的表时（例如 TA 和 TB），会面临连接顺序的问题。连接顺序的选择，取决于如何更快地过滤不想要的记录。如果统计数据足够精确地反映了表的内容，优化器有可能对连接顺序做出适当选择。

当查询仅返回少量记录，且过滤条件直接针对源表时，我们必须保证这些过滤条件高效；对于非常重要的条件，必须事先为相应字段加上索引，以便查询时使用。

索引可用性

Index Usability

如第3章所述，对某字段使用函数时，则该字段上的索引并不能起作用。当然，你可以建立函数索引（functional index），这意味着要对函数的结果加索引，而不是为字段加索引。

注意，“函数调用”不光是指“显式函数调用”。如果你将某类型的字段与一个不同类型的字段或常量进行比较，则DBMS会执行“隐式类型转换”（隐式调用一个转换函数），如你所料，这会对性能造成影响。

一旦确定重要的搜索条件上有索引，而查询编写方式也的确能因索引而提高性能，我们还须进一步区别如下两种情况：

l 使用唯一性索引（unique index）检索单条记录

l 非唯一性索引（non-unique index）或基于唯一性索引的范围扫描（range scan）

查询的效率与索引的使用

Query Efficiency and Index Usage

需要连接（join）表时，唯一性索引非常有用。然而，当程序获得的原始输入（primitive input）不是查询语句需要的主键值时，必须通过编程来解决转换问题。

这里的“原始输入”指程序接受的数据，可能由使用者输入，也可能从文件中读入。如果查询语句需要的主键值本身，就是根据原始输入利用另一个查询所获得的结果，则说明设计不合理。因为这意味着一个查询的输出被用作另一个查询的输入，应该考虑合并这两个查询。

总结：优秀的查询未必来自优秀的程序。

数据散布

Data Dispersion

当条件是“非唯一性”的，或者条件以唯一性索引上的范围来表达时，DBMS 就必须执行范围扫描。例如：

where customer_id between ... and ...

或：

where supplier_name like 'SOMENAME%'

键对应的记录很可能散布在整个表中，而基于成本的优化器知道这一点。所以，索引范围扫描会使 DBMS 核心逐一读取表的存储页，此时，优化器会决定 DBMS 核心忽略索引对表进行扫描。

如第5章所述，许多数据库系统提供了诸如分区（partition）和聚集索引（clustered index）等功能，直接将可能一并读取的数据存储在一起。其实，数据插入处理也常造成数据丛聚（clumping）保存的现象：如果每条记录插入表时都要加时间戳（timestamp），则相继插入的记录会彼此紧邻（除非我们采取特殊手段避免资源竞争，见第9章的讨论）。这其实没有必要，而且关系理论中也没有“顺序”的概念，但在实际中却很可能发生。

因此，当我们在时间戳字段的索引上执行范围扫描、查询时间上接近的索引项时，这些记录可能彼此紧邻——如果特意为此设置了存储选项参数，就更是如此了。

现在做一个假定：键值与特定插入环境无关、与存储设置无关，与键值（或键值范围）对应的记录可能存储在磁盘的任何位置。索引仅以特定顺序来存储键值，而对应的记录随机散落在表中。此时，若既不分区、也不采用聚集索引，则需访问的存储区会更多。于是，可能出现下列情况：同一个表上有两个可选择性完全相同的索引，但一个索引性能好、一个索引性能差。这种情况在第3章已提到过，下面来分析一下。

为了说明上述情况，先创建一个具有 1 000 000条记录的表，这个表有 c1、c2和 c3 三个字段，c1 保存序号（1 到 1 000 000），c2 保存从 1 到 2 000 000 不等的随机数，c3 保存可重复、且经常重复的随机值。表面看来，c1 和 c2 都具唯一性，因此具有完全相同的可选择性。索引建在c1上，则表中字段的顺序，与索引中的顺序相符——当然，实际上，对表的删除操作会留下“空洞”，随后又有新的插入记录填入，所以记录顺序会被打乱。相比之下，索引建在c2上，则表中记录顺序与索引中的顺序无关。

下面读取c3 ，使用如下范围条件：

where column_name between some_value and some_value + 10

如图6-1所示，使用c1索引（有序索引，索引中键的顺序与表中记录顺序相同）和c2索引（随机索引）的性能差异很大。别忘了造成这种差异的原因：为了读取c3的值，除了访问索引，还要访问表。如果我们有两个复合索引，分别在 (c1, c3) 和 (c2, c3) 上，就不会有上述差异了，因为这时不必访问表，从索引中即可获得要返回的内容。

图6-1说明的这种性能差异，也解释了下述情况的原因：有时性能会随时间而降低，尤其是在新系统刚投入生产环境并导入旧系统的大量数据时。最初加载的数据的物理排序，可能是有利于特定查询的；但随后几个月的各种活动破坏了这种顺序，于是性能“神秘”降低 30%～40%。

图6-1：“索引项顺序与表中记录顺序是否一致”对性能的影响

现在很清楚了，“DBA可以随时重新组织数据库”其实是错误的。数据库的重新组织曾一度流行；但不断增加的数据量及99 999 9% 正常运行等要求，使得重新组织数据库变得不再适合。如果物理存储方式很重要，则应考虑第5章讨论过的“自组织结构（self-organizing structure）”之一，例如聚集索引（clustered indexe）或索引组织表（index-organized table）。但要记住，对某种类型的查询有利，可能对另一种类型的查询不利，鱼与熊掌不可得兼。

总结：类似的索引，性能却不同，这可能是物理数据的散布引起的。

条件的“可索引性”

Criterion Indexability

对“小结果集，直接条件”的情况而言，适当的索引非常重要。但是，其中也有不适合加索引的例外情况：以下案例，用来判断会计账目是否存在“金额不平”的情况，虽然可选择性很高，但不适合加索引。

此例中，有个表glreport，该表包含一个应为0的字段amount_diff。此查询的目的是要追踪会计错误，并找出amount_diff不是0的记录。既然使用了现代的DBMS，直接把账目对应成表，并应用从前“纸笔记账”的逻辑，实在有点问题；但很不幸，我们经常遇到这种有问题的数据库。无论设计的质量如何，像amount_diff这样的字段

通常不应加索引，因为在理想情况下每条记录的amount_diff字段都是 0。此外，amount_diff字段明显是“非规范化”设计的结果，大量计算要操作该字段。维护一个计算字段上的索引，代价要高于静态字段上的索引，因为被修改的键会在索引内“移动”，于是索引要承受的开销比简单节点增／删要高。

总结：并非所有明确的条件都适合加索引。特别是，频繁更新的字段会增加索引维护的成本。

回到例子。开发者有天来找我，说他已最佳化了以下 Oracle 查询，并询问过专家建议：

select

total.deptnum,

total.accounting_period,

total.ledger,

total.cnt,

error.err_cnt,

cpt_error.bad_acct_count

from

-- First in-line view

(select

deptnum,

accounting_period,

ledger,

count(account) cnt

from

glreport

group by

deptnum,

ledger,

accounting_period) total,

-- Second in-line view

(select

deptnum,

accounting_period,

ledger,

count(account) err_cnt

from

glreport

where

amount_diff <> 0

group by

deptnum,

ledger,

accounting_period) error,

-- Third in-line view

(select

deptnum,

accounting_period,

ledger,

count(distinct account) bad_acct_count

from

glreport

where

amount_diff <> 0

group by

deptnum,

ledger,

accounting_period

) cpt_error

where

total.deptnum = error.deptnum(+) and

total.accounting_period = error.accounting_period(+) and

total.ledger = error.ledger(+) and

total.deptnum = cpt_error.deptnum(+) and

total.accounting_period = cpt_error.accounting_period(+) and

total.ledger = cpt_error.ledger(+)

order by

total.deptnum,

total.accounting_period,

total.ledger

外层查询where子句中的“(+)”是Oracle 特有的语法，代表外连接（outer join）。换言之：

select whatever

from ta,

where ta.id = tb.id (+)

相当于：

select whatever

from ta

outer join tb

on tb.id = ta.id

下列SQL*Plus输出显示了该查询的执行计划：

10:16:57 SQL> set autotrace traceonly

10:17:02 SQL> /

37 rows selected.

Elapsed: 00:30:00.06

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

(Cost=1779554 Card=154 Bytes=16170)

1 0 MERGE JOIN (OUTER) (Cost=1779554 Card=154 Bytes=16170)

2 1 MERGE JOIN (OUTER) (Cost=1185645 Card=154 Bytes=10780)

3 2 VIEW (Cost=591736 Card=154 Bytes=5390)

4 3 SORT (GROUP BY) (Cost=591736 Card=154 Bytes=3388)

5 4 TABLE ACCESS (FULL) OF 'GLREPORT'

(Cost=582346 Card=4370894 Bytes=96159668)

6 2 SORT (JOIN) (Cost=593910 Card=154 Bytes=5390)

7 6 VIEW (Cost=593908 Card=154 Bytes=5390)

8 7 SORT (GROUP BY) (Cost=593908 Card=154 Bytes=4004)

9 8 TABLE ACCESS (FULL) OF 'GLREPORT'

(Cost=584519 Card=4370885 Bytes=113643010)

10 1 SORT (JOIN) (Cost=593910 Card=154 Bytes=5390)

11 10 VIEW (Cost=593908 Card=154 Bytes=5390)

12 11 SORT (GROUP BY) (Cost=593908 Card=154 Bytes=5698)

13 12 TABLE ACCESS (FULL) OF 'GLREPORT'

(Cost=584519 Card=4370885 Bytes=161722745)

Statistics

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

193 recursive calls

0 db block gets

3803355 consistent gets

3794172 physical reads

1620 redo size

2219 bytes sent via SQL*Net to client

677 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

17 sorts (memory)

0 sorts (disk)

37 rows processed

在此说明，我没有浪费太多时间在执行计划上，因为查询本身的文字描述已显示了查询的最大特点：只有四~五百万条记录的glreport表，被访问了三次；每个子查询存取一次，而且每次都是完全扫描。

编写复杂查询时，嵌套查询通常很有用，尤其是你计划将查询划分为多个步骤，每个步骤对应一个子查询。但是，嵌套查询不是银弹，上述例子就属于“滥用嵌套查询”。

查询中的第一个内嵌视图，计算每个部门的账目数、会计期、分类账，这不可避免地要进行全表扫描。面对现实吧！我们必须完整扫描glreport表，因为检查有多少个账目涉及所有记录。但是，有必要扫描第二次甚至第三次吗？

总结：如果必须进行全表扫描，表上的索引就没用了。

不要单从“分析（analytic）”的观点看待处理，还要退一步，从整体角度考虑。除了在 amount_diff 值上的条件之外，第二个内嵌视图所做的计算，与第一个视图完全相同。我们没有必要使用count()计算总数，可以在amount_diif不是 0 时加 1，否则加0，通过 Oracle 特有的 decode(u, v w, x) 函数，或使用标准语法case when u = v then w else x end，即可轻松实现这项计算。

第三个内嵌视图所过滤的记录与第一个视图相同，但要计算不同账目数。把这个计数合并到第一个子查询中并不难：用chr(1)代表amount_diff 为 0 时的“账户编号（account number）”，就很容易统计有多少个不同的账户编号了，当然，记住减1去掉chr(1)这个虚拟的账户编号。其中，账户编号字段的类型为varchar2（注1），而chr(1)在 Oracle 中代表ASCII码值为 1 的字符——在使用 Oracle 这类用 C 语言编写的系统时，我总是不敢安心使用chr(0)，因为 C语言以 chr(0)作为字符串终止符。

So this is the suggestion that I returned to the developer:

select deptnum,

accounting_period,

ledger,

count(account) nb,

sum(decode(amount_diff, 0, 0, 1)) err_cnt,

count(distinct decode(amount_diff, 0, chr(1), account)) - 1

bad_acct_count

from

glreport

group by

deptnum,