友情提示：如果本网页打开太慢或显示不完整，请尝试鼠标右键“刷新”本网页！
SQL语言艺术(PDF格式)-第8部分
快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部! 如果本书没有阅读完，想下次继续接着阅读，可使用上方 "收藏到我的浏览器" 功能 和 "加入书签" 功能！
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
from orders
where
andexists （select 1
from orderdetail
join articles
on （articles。artid=orderdetail。artid）
where articles。artname ='SOAP'
andorderdetails。ordid =orders。ordid）
在这个方法中，为了使关联子查询速度较快，需要orderdetail表的 ordid字段上有索引（就可以
通过主键artid取得商品，无需其他索引）。
第3章已提到，事务处理型数据库（transactional database）的索引是种奢侈，因为它处在经常更
改的环境中，维护的成本很高。于是选择“次佳”解决方案：当表orderdetail 上的索引并不重要，
而且也有充足理由不再另建索引时，我们考虑以下方式：
select distinct orders。custid
from orders；
（select orderdetails。ordid
from orderdetail；
articles
where articles。artid=orderdetail。artid
andarticles。artname ='SOAP'） assub_q
where sub_q。ordid =orders。ordid
and
…………………………………………………………Page 59……………………………………………………………
这第二个方法对索引的要求有所不同：如果商品数量不超过数百万项，即使artname字段上没有
索引，基于商品名称条件的查询性能也不错。表orderdetail的artid字段可能也不需索引：如果商
品很畅销，出现在许多订单中，则表orderdetail和articles之间的连接通过哈希或合并连接（merge
join）更高效，而artid字段上的索引会引起嵌套的循环。与第一种方法相比，第二种方法属于索
引较少的解决方案。一方面，我们无法承受为表的每个字段建立索引；另一方面，应用中都有
一些“次要的”查询，它们不太重要，对响应时间要求也不苛刻，索引较少的解决方案完全满足
它们的要求。
总结：为现存的查询增加搜索条件，可能彻底改变先前的构想：修改过的查询成了新查询。
多个间接宽泛条件的交集
Small Intersection； Indirect BroadCriteria
为了构造查询条件，需要连接（join）源表之外的表，并在条件中使用该表的字段，就叫间接条
件（indirect criterion）。正如上一节“多个宽泛条件的交集”的情况，通过两个或多个宽泛条件的
交集处理获取小结果集，是项艰难的工作；若是涉及多次join操作，或者对中心表（centraltable）
进行join操作，则会更加困难——这是典型的“星形schema（starschema）”（第10章详细讨论），
实际的数据库系统中经常遇到。对于多个可选择性差的条件，一些罕见的组合要求我们预测哪
些地方会执行完整扫描。当牵涉到多个表时，这种情况颇值得研究。
DBMS引擎的执行始于一个表、一个索引或一个分区，就算DBMS引擎能并行处理数据也是如
此。虽然由多个大型数据集合的交集所定义的结果集非常小，但前期的全表扫描、两次扫描等
问题依然存在，还可能在结果上执行嵌套循环（nested loop）、哈希连接
（hash join）或合并连接（merge join）。此时，困难在于确定结果集的哪种表组合产生的记录数
最少。这就好比，找到防线最弱的环节，然后利用它获得最终结果。
下面通过一个实际的 Oracle 案例说明这种情况。原始查询相当复杂，有两个表在from 子句中
都出现了两次，虽然表本身不太庞大（大的包含700000 行数据），但传递给查询的九个参数可
选择性都太差：
select （datafrom ttex_a；
ttex_b；
ttraoma；
topeoma；
ttypobj；
ttrcap_a；
ttrcap_b；
trgppdt；
tstg_a）
from ttrcappttrcap_a；
ttrcapp ttrcap_b；
tstgtstg_a；
…………………………………………………………Page 60……………………………………………………………
topeoma；
ttraoma；
ttexttex_a；
ttexttex_b；
tbooks；
tpdt；
trgppdt；
ttypobj
where （ttraoma。txnum=topeoma。txnum ）
and（ttraoma。bkcod =tbooks。trscod ）
and（ttex_b。trscod =tbooks。permor ）
and（ttraoma。trscod =ttrcap_a。valnumcod ）
and（ttex_a。nttcod =ttrcap_b。valnumcod ）
and（ttypobj。objtyp =ttraoma。objtyp）
and（ttraoma。trscod =ttex_a。trscod ）
and（ttrcap_a。colcod =：0）……not selective
and（ttrcap_b。colcod =：1）……not selective
and（ttraoma。pdtcod =tpdt。pdtcod ）
and（tpdt。risktyp=trgppdt。risktyp ）
and（tpdt。riskflg=trgppdt。riskflg）
and（tpdt。pdtcod =trgppdt。pdtcod ）
and（trgppdt。risktyp =：2）……not selective
and（trgppdt。riskflg =：3）……not selective
and（ttraoma。txnum=tstg_a。txnum）
and（ttrcap_a。refcod =：5）……not selective
and（ttrcap_b。refcod =：6）……not selective
and（tstg_a。risktyp =：4）……not selective
and（tstg_a。chncod =：7）……not selective
and（tstg_a。stgnum =：8）……not selective
我们提供适当的参数（这里以 ：0 到 ：8 代表）执行此查询：耗时超过 25 秒，返回记录不到20
条，做了3000 次物理 I/O，访问数据块3 000000 次。上述统计数据反映了实际执行的情况，
这是必须首先明确的。下面，通过查询数据字典，得到表记录数情况：
TABLE_NAME NUM_ROWS
………………………………………………………………………………………………
ttypobj 186
trgppdt 366
tpdt 5370
topeoma 12118
ttraoma 12118
tbooks 12268
…………………………………………………………Page 61……………………………………………………………
ttex 102554
ttrcapp 187759
tstg 702403
认真研究表及表的关联情况，得到图6…2所示的分析图：小箭头代表较弱的选择条件，方块为表，
方块的大小代表记录数多少。注意：在中心位置的 tTRaoma表，几乎和其他所有表有关联关系，
但很不幸，选择条件都不在tTRaoma表。另一个有趣的事实是：上述的查询语句中，我们必须
提供TRgppdt表的 risktyp字段 和 riskflg字段的值作为条件——为了连接（join）TRgppdt表和tpdt
表要使用这两个字段和pdtcod 字段。在这种情况下，应该思考倒转此流程——例如把 tpdt表的
字段与所提供的常数做比较，然后只从 trgppdt表取得数据。
…………………………………………………………Page 62……………………………………………………………
图6…2：数据的位置关系
多数 DBMS提供“检查优化器选择的执行计划”这一功能，比如通过explain命令直接检查内存中
执行的项目。上述查询花了 25 秒（虽然不是特别糟），通常是先完整扫描tTRaoma表，接着进
行一连串的嵌套循环，使用了各种高效的索引（详述这些索引
很乏味，我们假设所有字段都建立了合适的索引）。速度慢的原因是完整扫描吗？当然不是。为
了证明完整扫描所花时间占的比例甚微，只需做如下简单的测试：读取tTRaoma表的所有记录；
为了避免受到字符显示时间的干扰，这些记录无需显示。
优化器发现：tstg表有“大量敌军”，而查询中针对此表的选择条件比较弱，所以难以对它形成“正
…………………………………………………………Page 63……………………………………………………………
面攻击”；而ttrcapp表在查询的from子句中出现两次，但基于该表的判断条件也较弱，所以也不
会带来查询效率的提升；但是，ttraoma表的位置显然很关键，且该表比较小，适合作为“第一攻
击点”——优化器会毫不犹豫地这么做。
那么，既然对tTRaoma表的完整扫描无可厚非，优化器到底错在哪里呢？请看图6…3所示的查询
执行情况。
…………………………………………………………Page 64……………………………………………………………
图6…3：优化器选择的执行路径
…………………………………………………………Page 65……………………………………………………………
注意观察图中所示的操作执行顺序，查询速度慢的原因显露无遗：我们的查询条件很糟糕，优
化器选择完全忽略它们。优化器决定先对ttraoma表进行完整扫描；接着，访问和表ttraoma关联
的所有小型表；最后，对其他表运用我们的过滤条件。这样执行是错误的：虽然优化器决定首
先访问表ttraoma有道理（该表的索引可能非常高效，每个键平均对应的记录数较少，或者索引
与记录的顺序有较好的对应关系），但将我们提供的查询条件推迟执行，不利于减少要处理的数
据量。
既然已访问了ttraoma这个关键表，应该紧接着执行语句中的查询条件，这样可以借助这些表与
ttraoma表之间的连接（join）先去除ttraoma表中无用的记录——甚至在结果集更大时，如此执
行的效率仍比较高。但是上述信息我们知道，“优化器”却无从知道。
怎样才能迫使DBMS 依我们所要求的方式执行查询呢？要依靠SQL 方言（SQLdialect）。正如
你将在第11章看到的，多数 SQL 方言都支持针对优化器的指示或提示（hint），虽然各种方言
所用语法不同；例如，告诉优化器按表名在from 子句中出现的顺序依次访问各表。不过，“提
示”的实际影响远比它的名字暗示的要大得多，采用“提示”的问题在于，每个提示都是在“赌未
来”——我们已强制规定了执行路径，所以环境、数据量、数据库算法、硬件等因素的发展变化
即使不能绝对适合我们的执行路径，也应该基本适合。例如，既然索引的嵌套循环是最高效选
择，并且嵌套循环不会因并行化而受益，那么命令优化器按照表的排列顺序访问它们几乎没什
么风险。明确指定表的访问顺序，就是这个案例中实际采用的方法，最终查询不到1秒即可完成，
不过物理 I/O 次数减少并不明显（原来3000次，现在2340次，因为我们仍以ttraoma表的完整
扫描开始），但逻辑 I/O 次数的大幅降低（从3000000次降到16500次）使总体响应时间显著缩
短，因为我们“建议”了更高效的执行路径。
总结：记住，你应该详细说明所有强迫 DBMS 做的事。
显式地通过优化器指令，指定表的访问顺序，是个笨拙的方法。更优雅的方法是在from子句中
采用嵌套查询，在数值表达式中建议连接关系，这样不必大幅修改SQL子�
快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部!
温馨提示： 温看小说的同时发表评论，说出自己的看法和其它小伙伴们分享也不错哦！发表书评还可以获得积分和经验奖励，认真写原创书评 被采纳为精评可以获得大量金币、积分和经验奖励哦！