《SQL语言艺术(PDF格式)》第18章

型的查询有利，可能对另一种类型的查询不利，鱼与熊掌不可得兼。　
总结：类似的索引，性能却不同，这可能是物理数据的散布引起的。　
“ ”　
条件的““可索引性””　
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　
这个新的查询，