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 commory)
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,
ledger,
accounting_period
这个新的查询,执行速度是原先的四倍。这丝毫不令人意外,因为三次的完整扫描变成了一次。
注意,查询中不再有where子句:amount_diff上的条件已被“迁移”到了select列表中decode()函数
执行的逻辑,以及由group by子句执行的聚合(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.artncom = 'BATMOBILE'
依我看,明确使用子查询来检查客户订单是否包含某项商品,才是较好的方式,而且也比较容
易理解。但应该采用“关联子查询”还是“非关联子查询”呢?由于我们没有其他条件,所以答案
应该很清楚:非关联子查询。否则,就必须扫描orders表,并针对每条记录执行子查询——当orders
表规模小时通常不会查觉其中问题,但随着orders表越来越大,它的xìng能就逐渐让我们如坐针毡
了。
非关联子查询可以用如下的经典风格编写:
select distinct orders.custid
from orders
where ordid in (select orderdetails.ordid
from orderdetail
join articles
on (articles.artid = orderdetail.artid)
where articles.artncom = 'BATMOBILE')
或采用from子句中的子查询:
select distinct orders.custid
from orders,
(select orderdetails.ordid
from orderdetail
join articles
on (articles.artid = orderdetail.artid)
where articles.artncom = 'BATMOBILE') as sub_q
where sub_q.ordid = orders.ordid
我认为第一个查询较为易读,当然这取决于个人喜好。别忘了,在子查询结果上的in() 条件暗
含了distinct处理,会引起排序,而排序把我们带到了关系模型的边缘。
总结:如果要使用子查询,在选择关联子查询、还是非关联子查询的问题上,应仔细考虑。
多个宽泛条件的jiāo集
Small Intersection of Broad Criteria
本节讨论对多个宽泛条件取jiāo集获得较小结果集的情况。在分别使用各个条件时,会产生大型
数据集,但最终各个大型数据集的jiāo集却是小结果集。
继续上一节的例子。如果“判断订购的商品是否存在”可选择xìng较差,就必须考虑其他条件(否
则结果集就不是小结果集)。在这
松语文学免费小说阅读_www.16sy.com