按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
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
这个新的查询，执行速度是原先的四倍。这丝毫不令人意外，因为三次的完整扫描变成了一次。
注意，查询中不再有where子句：amount_diff上的条件已被“迁移”到了select列表中decode（）函数
执行的逻辑，以及由groupby子句执行的聚合（aggregation）中。
使用聚合代替过滤条件有点特殊，这正是我们要说明的“九种典型情况”中的另一种—— 以聚合
函数为基础获得结果集。
总结：内嵌查询可以简化查询，但若使用不慎，可能造成重复处理。
小结果集，间接条件
Small Result Set； Indirect Criteria
与上一节类似，这一节也是要获取小结果集，只是查询条件不再针对源表，而是针对其他表。
我们想要的数据来自一个表，但查询条件是针对其他表的，且不需要从这些表返回任何数据。
典型的例子是在第4章讨论过的“哪些客户订购了特定商品”问题。如第4章所述，这类查询可用
两种方法表达：
使用连接，加上 distinct 去除结果中的重复记录，因为有的客户会多次订购相同商品
使用关联或非关联子查询
如果可以使用作用于源表的条件，请参考前一节“小结果集，直接条件”中的方法。但如果找不
到这样的条件，就必须多加小心了。
取用第4章中例子的简化版本，找出订购蝙蝠车的客户，典型实现如下：
select distinct orders。custid
from orders
join orderdetail
on （orderdetail。ordid =orders。ordid）
join articles
on （articles。artid=orderdetail。artid）
where articles。artname ='BATMOBILE'
依我看，明确使用子查询来检查客户订单是否包含某项商品，才是较好的方式，而且也比较容
易理解。但应该采用“关联子查询”还是“非关联子查询”呢？由于我们没有其他条件，所以答案
…………………………………………………………Page 57……………………………………………………………
应该很清楚：非关联子查询。否则，就必须扫描orders表，并针对每条记录执行子查询——当orders
表规模小时通常不会查觉其中问题，但随着orders表越来越大，它的性能就逐渐让我们如坐针毡
了。
非关联子查询可以用如下的经典风格编写：
select distinct orders。custid
from orders
where ordid in（select orderdetails。ordid
from orderdetail
join articles
on （articles。artid=orderdetail。artid）
where articles。artname ='BATMOBILE'）
或采用from子句中的子查询：
select distinct orders。custid
from orders；
（select orderdetails。ordid
from orderdetail
join articles
on （articles。artid=orderdetail。artid）
where articles。artname ='BATMOBILE'） assub_q
where sub_q。ordid =orders。ordid
我认为第一个查询较为易读，当然这取决于个人喜好。别忘了，在子查询结果上的 in（） 条件暗
含了distinct处理，会引起排序，而排序把我们带到了关系模型的边缘。
总结：如果要使用子查询，在选择关联子查询、还是非关联子查询的问题上，应仔细考虑。
多个宽泛条件的交集
Small Intersection of BroadCriteria
本节讨论对多个宽泛条件取交集获得较小结果集的情况。在分别使用各个条件时，会产生大型
数据集，但最终各个大型数据集的交集却是小结果集。
继续上一节的例子。如果“判断订购的商品是否存在”可选择性较差，就必须考虑其他条件（否
则结果集就不是小结果集）。在这种情况下，使用正规连接、关联子查询，还是非关联子查询，
要根据不同条件的过滤能力和已存在哪些索引而定。
例如，由于不太畅销，我们不再检索订购蝙蝠车的人，而是查找上周六购买某种肥皂的客户。
此时，我们的查询语句为：
select distinct orders。custid
from orders
join orderdetail
…………………………………………………………Page 58……………………………………………………………
on （orderdetail。ordid =orders。ordid）
join articles
on （articles。artid=orderdetail。artid）
where articles。artname ='SOAP'
and
这个处理流程很合逻辑，该逻辑和商品具有高可选择性时相反：先取得商品，再取得包含商品
的明细订单，最后处理订单。对目前讨论的肥皂订单的情况而言，我们应该先取得在较短期间
内下的少量订单，再检查哪些订单涉及肥皂。从实践角度来看，我们将使用完全不同的索引：
第一个例子需要orderdetail表的商品名称、商品ID这两个字段上的索引，以及orders表的主键
orderid上的索引；而此肥皂订单的例子需要orders表日期字段的索引、orderdetail表的订单ID字
段的索引，以及articles表的主键orderid上的索引。当然，我们首先假设索引对上述两例都是最
佳方式。
要知道哪些客户在上星期六买了肥皂，最明显而自然的选择是使用关联子查询：
select distinct orders。custid