按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
型的查询有利，可能对另一种类型的查询不利，鱼与熊掌不可得兼。
总结：类似的索引，性能却不同，这可能是物理数据的散布引起的。
“ ”
条件的““可索引性””
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
……Firstin…line view
（select
deptnum；
accounting_period；
ledger；
…………………………………………………………Page 53……………………………………………………………
count（account） cnt
from
glreport
groupby
deptnum；
ledger；
accounting_period） total；
……Second in…line view
（select
deptnum；
accounting_period；
ledger；
count（account） err_cnt
from
glreport
where
amount_diff0
groupby
deptnum；
ledger；
accounting_period） error；
……Third in…line view
（select
deptnum；
accounting_period；
ledger；
count（distinct account） bad_acct_count
from
glreport
where
amount_diff0
groupby
deptnum；
ledger；
accounting_period
）cpt_error
where
total。deptnum =error。deptnum（＋） and
total。accounting_period =error。accounting_period（＋） and
total。ledger =error。ledger（＋）and
…………………………………………………………Page 54……………………………………………………………
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 特有的语法，代表外连接（outerjoin）。换言之：
select whatever
from ta；
tb
where ta。id=tb。id （＋）
相当于：
select whatever
from ta
outerjoin tb
on tb。id=ta。id
下列SQL*Plus输出显示了该查询的执行计划：
10：16：57SQL》 set autotracetraceonly
10：17：02SQL》 /
37rows selected。
Elapsed： 00：30：00。06
Execution Plan
………………………………………………………………………………………………………………………………………………………
0 SELECTSTATEMENTOptimizer=CHOOSE
（Cost=1779554 Card=154Bytes=16170）
1 0 MERGEJOIN（OUTER）（Cost=1779554 Card=154Bytes=16170）
2 1 MERGEJOIN（OUTER）（Cost=1185645 Card=154 Bytes=10780）
3 2 VIEW（Cost=591736 Card=154Bytes=5390）
4 3 SORT（GROUPBY）（Cost=591736 Card=154Bytes=3388）
5 4 TABLEACCESS（FULL） OF'GLREPORT'
（Cost=582346 Card=4370894Bytes=96159668）
6 2 SORT（JOIN）（Cost=593910 Card=154Bytes=5390）
7 6 VIEW （Cost=593908Card=154Bytes=5390）
8 7 SORT（GROUP BY）（Cost=593908 Card=154Bytes=4004）
9 8 TABLEACCESS（FULL） OF'GLREPORT'
（Cost=584519 Card=4370885Bytes=113643010）
10 1 SORT（JOIN）（Cost=593910 Card=154Bytes=5390）
11 10 VIEW（Cost=593908 Card=154Bytes=5390）
…………………………………………………………Page 55……………………………………………………………
12 11 SORT（GROUPBY）（Cost=593908Card=154 Bytes=5698）
13 12 TABLEACCESS（FULL） OF'GLREPORT'
（Cost=584519 Card=4370885Bytes=161722745）
Statistics
………………………………………………………………………………………………………………………………………………………
193 recursive calls
0 db block gets
3803355consistent gets
3794172 physical reads
1620 redo size
2219 bytes sentvia SQL*Net toclient
677bytes 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=vthen welsexend，即可轻松实现这项计算。
第三个内嵌视图所过滤的记录与第一个视图相同，但要计算不同账目数。把这个计数合并到第
一个子查询中并不难：用chr（1）代表amount_diff 为 0 时的“账户编号（account number）”，就很
容易统计有多少个不同的账户编号了，当然，记住减1去掉chr（1）这个虚拟的账户编号。其中，
账户编号字段的类型为varchar2（注1），而chr（1）在 Oracle 中代表ASCII码值为 1 的字符——
在使用 Oracle 这类用 C 语言编写的系统时，我总是不敢安心使用chr（0），因为 C语言 以
chr（0）作为字符串终止符。
Sothis is thesuggestion thatI returnedtothe developer：
select deptnum；
accounting_period；
ledger；
…………………………………………………………Page 56……………………………………………………………
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
groupby
deptnum；
ledger；
accounting_period
这个新的查询，