SQL语言艺术(PDF格式)-第9部分

快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部! 如果本书没有阅读完，想下次继续接着阅读，可使用上方 "收藏到我的浏览器" 功能 和 "加入书签" 功能！



采用嵌套查询，在数值表达式中建议连接关系，这样不必大幅修改SQL子句：　



select　（select　list）　

from　（select　ttraoma。txnum；　

ttraoma。bkcod；　

ttraoma。trscod；　

ttraoma。pdtcod；　

ttraoma。objtyp；　

。。。　

from　ttraoma；　

tstgtstg_a；　

ttrcapp　ttrcap_a　

where　tstg_a。chncod　=：7　


…………………………………………………………Page　66……………………………………………………………

andtstg_a。stgnum　=：8　

andtstg_a。risktyp　=：4　

andttraoma。txnum　=tstg_a。txnum　

andttrcap_a。colcod　=：0　

andttrcap_a。refcod　=：5　

andttraoma。trscod　=ttrcap_a。valnumcod）　a；　

ttexttex_a；　

ttrcapp　ttrcap_b；　

tbooks；　

topeoma；　

ttexttex_b；　

ttypobj；　

tpdt；　

trgppdt　

where　（a。txnum=topeoma。txnum　）　

and（a。bkcod　=tbooks。trscod　）　

and（ttex_b。trscod　=tbooks。permor　）　

and（ttex_a。nttcod　=ttrcap_b。valnumcod　）　

and（ttypobj。objtyp　=a。objtyp）　

and（a。trscod=ttex_a。trscod　）　

and（ttrcap_b。colcod　=：1）　

and（a。pdtcod　=tpdt。pdtcod　）　

and（tpdt。risktyp=trgppdt。risktyp　）　

and（tpdt。riskflg=trgppdt。riskflg）　

and（tpdt。pdtcod　=trgppdt。pdtcod　）　

and（tpdt。risktyp=：2）　

and（tpdt。riskflg=：3）　

and（ttrcap_b。refcod　=：6）　

通常，没有必要采用非常具体的方式和难以理解的提示，其实，提供正确的最初指导就可使优　

化器找到正确的执行路径。嵌套查询是个不错的选择，它使表的关联变得明确，而SQL语句的　

阅读也相当容易。　



总结：混乱的查询会让优化器困惑。结构清晰的查询及合理的连接建议，通常足以帮助优化器　

提升性能。　



大结果集　



LargeResult　Set　



无论结果集是如何获得的，只要结果集“很大”，就符合我们下面要讨论的“大结果集”的情况。　


…………………………………………………………Page　67……………………………………………………………

批处理环境下，产生大结果集是明智的。当需要返回大量记录时，只要查询条件的可选择性不　

高，那么即使结果集只占表中数据量的一小部分，也会引起DBMS引擎执行全表扫描；只有某　

些数据仓库例外，我们将在第10章中讨论之。　



如果查询返回几万条记录，那么使用索引是没有意义的，无论索引用于产生最终结果，还是用　

于复杂查询的中间步骤。相比而言，借助哈希或合并连接进行全表扫描是合适的。当然，强力　

手段背后也必须有智慧：我们必须尽量扫描数据返回比例最高的表、索引，或者这两者的分区；　

扫描时的过滤条件必须是粗粒度的，从而返回的数据量比较大，使扫描更有价值；扫描显然违　

背了“尽快去除不必要数据”这一原则，但一旦扫描结束应立即重新贯彻该原则。　

相反，采取扫描方式不合适的情况下，应尽量减少要访问数据的块数。为此，最常用的手段就　

是使用索引（而不是表），尽管所有索引的总数据量经常比表还大，但单个索引则远比表要小。　

如果索引包含了所有需要的信息，则扫描索引而不扫描表是相当合理的，可以利用诸如聚集索　

引等避免访问表的技术。　

无论是要返回大量记录，还是要对大量记录进行检查，每条记录的处理都需小心。例如，一个　

性能不佳的用户自定义函数的调用，如果发生在“返回小结果集的　select　列表”中或在“可选择性　

很高的　where　子句”中，则影响不大；但返回大数据集的查询可能会调用这个函数几十万次，　

DBMS服务器就不堪重负了，这时必须优化代码。　



还要重点关注子查询的使用。处理大量记录时，关联子查询（Correlatedsubquery）是性能杀手。　

当一个查询包含多个子查询时，必须让它们操作各不相同、自给自足的数据子集，以避免子查　

询相互依赖；到查询执行的最后阶段，多个子查询分别得到的不同数据集经过哈希连接或集合　

操作得到结果集。　

查询执行的并行化（parallelism）也是个好主意，不过只应在“并发活动会话数（concurrentlyactive　

sessions）”很少（典型情况为批处理操作）时才这么做。并行化是由　DBMS　实现的，如果有可　

能，DBMS把一个查询分割为多个并行运行的子任务，并由另一个专门的任务来协调。并发用　

户数很大时，并行化反而会影响处理能力。一般而言，并发用户数又多、要处理的信息量又大　

的情况下，最好做好战斗准备，因为这经常靠投入更多硬件来解决。　

除了处理过程中由资源争用引起的等待之外，查询必须访问的数据量是影响“响应时间”的主要　

因素。但正如第4章讲过的，最终用户并不关心客观的数据量分析，他们只关心查询获得的数据。　

基于一个表的自连接　

Self…Joins　on　OneTable　

利用卓越的、广为流行的范式（注2），有助于我们设计正确的关系数据库（至少满足3NF）。所　

有非键字段均与键相关、并完整依赖于键，非键字段之间没有任何依赖。每条记录具有逻辑一　

致性，同一个表中没有重复记录。于是，才能够建立同一个表之间的连接关系：使用同一查询　

从同一表中选择不同记录的集合（可以相交），然后连接它们，就好像它们来自不同表一样。本　

节将讨论简单的自连接。本节不讨论较复杂的嵌套层次结构，这一主题在第7章中讨论。　

自连接，指表与自身的连接，这种情况比分层查询更常见。自连接用于“从不同角度看　



待相同数据”的情况，例如，查询航班会两次用到airports　表，一次找到“出发机场”的名称，另　

一次找出“到达机场”的名称：　

select　f。flight_number；　

a。airport_name　departure_airport；　


…………………………………………………………Page　68……………………………………………………………

b。airport_name　arrival_airport　

from　flights　f；　

airports　a；　

airports　b　

where　f。dep_iata_code　=a。iata_code　

andf。arr_iata_code　=b。iata_code　

此时，一般规则仍然适用：重点保证索引访问的高效。但是，如果此时索引访问不太高效怎么　

办呢？首当其冲地，应避免“第一轮处理丢弃了第二轮处理所需的记录”。应该通过一次处理收　

集所有感兴趣的记录，再使用诸如case　语句等结构分别显示记录，第11章将详细说明这种方法。　

非常微妙的是，有些情况看似与“机场的例子”很像，但其实不然。例如，如何利用一个保存“定　

期累计值”（注3）的表，显示每个时间段内累计值的增量？此时，该表内的两个不同记录间虽　

然有关联，但这种关联很弱：两个记录之所以相关，是因为它们的时间戳之间有前后关系。而　

连接两个flights表是通过airports表进行的，这种关联很强。　

例如，时间段为5分钟，时间戳以“距参照日期多少秒（seconds　elapsed　since　a　reference　date）”　

表示，则查询如下：　

select　a。timestamp；　

a。statistic_id；　

（b。counter　…a。counter）/5hits_per_minute　

from　hit_counter　a；　

hit_counter　b　

where　b。timestamp　=a。timestamp＋300　

andb。statistic_id　=a。statistic_id　

order　bya。timestamp；　a。statistic_id　



上述脚本有重大缺陷：如果第二个累计值不是正好在第一个累计值之后5分钟取得的，那么就无　

法连接这两条记录。于是，我们改以“范围条件”定义连接。查询如下：　

select　a。timestamp；　

a。statistic_id；　

（b。counter　…a。counter）*60/　

（b。timestamp　…a。timestamp）　hits_per_minute　

from　hit_counter　a；　

hit_counter　b　

where　b。timestamp　between　a。timestamp＋200　

anda。timestamp　＋400　

andb。statistic_id　=a。statistic_id　

order　bya。timestamp；　a。statistic_id　

这个方法还是有缺陷：前后两次计算累计值的时间间隔，如果不介于　200　到　400　秒之间（例　

如取样频率改变了），如此之大的时间跨度就会引起风险。　

我们还有更安全的方法，就是使用基于“记录窗口（windows　of　rows）”的OLAP函数（OLAP　

function）。难以想象，这种本质上不太符合关系理论的技术可以显著提升性能，但应作为查询　

优化的最后手段使用。借助partition　子句，OLAP函数支持“分别处理结果集的不同子集”，比如　


…………………………………………………………Page　69……………………………………………………………

分别对它们进行排序、总计等处理。借助OLAP　函数row_number（），可以根据　statistic_id　建立　

子集，然后按时间戳增大的顺序为不同统计赋予连续整数编号，接下来，就可以连接statistic_id　

和两个序号了，如下例子所示：　

select　a。timestamp；　

a。statistic_id；　

（b。counter　…a。counter）*60/　

（b。timestamp　…a。timestamp）　

from　（select　timestamp；　

statistic_id；　

counter；　

row_number（　）over（partition　bystatistic_id　

order　bytimestamp）　rn　

from　hit_counter）　a；　

（select　timestamp；　

statistic_id；　

counter；　

row_number（　）over（partition　bystatistic_id　

order　bytimestamp）　rn　

from　hit_counter）　b　

where　b。rn=a。rn＋1　

anda。statistic_id　=b。statistic_id　

order　bya。timestamp；　a。statistic_id　



Oracle等DBMS支持OLAP　函数　lag（column_name；　n）。该函数借助分区（）和排序（），返回　

column_name之前的第n个值。如果使用lag（）函数，我们的查询甚至执行得更快——比先前的查　

询大约快25％。　

select　timestamp；　

statistic_id；　

（counter　…prev_counter）　*60/　

（timestamp　…prev_timestamp）　

from　（select　timestamp；　

statistic_id；　

counter；　

lag（counter；1）over（partition　bystatistic_id　

order　bytimestamp）　prev_counter；　

lag（timestamp；　1）over（partition　bystatistic_id　

order　bytimestamp）　prev_timestamp　

from　hit_counter）　a　

order　bya。timestamp；　a。statistic_id　

很多时候，我们的数据并不像航班案例中那样具有对称性。通常，当需要查找和最小、最大、　

最早、或最近的值相关联的数据时，首先必须找到这些值本身（此为第一遍扫描，需比较记录），　


…………………………………………………………Page　70……………………………………………………………

接下来的用这些值作为第二遍扫描的搜索条件。而以滑动窗口（sliding　window）为基础的OLAP　

函数，可以将两遍扫描合而为一（至少表面上如此）。基于时间戳或日期的数据查询，非常特殊　

也非常重要，本章在稍后的“基于日期的简单搜索或范围搜索”中专门讨论。　

总结：当多个选取条件用于同一个表的不同记录时，可以使用基于滑动窗口工作的函数。　



基于一个表的自连接　



Self…Joins　on　OneTable　



利用卓越的、广为流行的范式（注2），有助于我们设计正确的关系数据库（至少满足3NF）。所　

有非键字段均与键相关、并完整依赖于键，非键字段之间没有任何依赖。每条记录具有逻辑一　

致性，同一个表中没有重复记录。于是，才能够建立同一个表之间的连接关系：使用同一查询　

从同一表中选择不同记录的集合（可以相交），然后连接它们，就好像它们来自不同表一样。本　

节将讨论简单的自连接。本节不讨论较复杂的嵌套层次结构，这一主题在第7章中讨论。　



自连接，指表与自身的连接，这种情况比分层查询更常见。自连接用于“从不同角度看　



待相同数据”的情况，例如，查询航班会两次用到airports　表，一次找到“出发机场”的名称，另　

一次找出“到达机场”的名称：　

select　f。flight_number；　

a。airport_name　departure_airport；　

b。airport_name　arrival_airport　

from　flights　f；　

airports　a；　

airports　b　

where　f。dep_iata_code　=a。iata_code　

andf。arr_iata_code　=b。iata_code　



此时，一般规则仍然适用：重点保证索引访问的高效。但是，如果此时索引访问不太高效怎么　

办呢？首当其冲地，应避免“第一轮处理丢弃了第二轮处理所需的记录”。应该通过一次处理收　

集所有感兴趣的记录，再使用诸如case　语句等结构分别显示记录，第11章将详细说明这种方法。　



非常微妙的是，有些情况看似与“机场的例子”很像，但其实不然。例如，如何利用一个保存“定　

期累计值”（注3）的表，显示每个时间段内累计值的增量？此时，该表内的两个不同记录间虽　

然有关联，但这种关联很弱：两个记录之所以相关，是因为它们的时间戳之间有前后关系。而　

连接两个flights表是通过airports表进行的，这种关联很强。　



例如，时间段为5分钟，时间戳以“距参照日期多少秒（seconds　elapsed　since　a　reference　date）”　

表示，则查询如下：　

select　a。timestamp；　

a。statistic_id；　

（b。counter　…a。counter）/5hits_per_minute　


…………………………………………………………Page　71……………………………………………………………

from　hit_counter　a；　

hit_counter　b　

where　b。timestamp　=a。timestamp＋300　

andb。statistic_id　=a。statistic_id　

order　bya。timestamp；　a。statistic_id　



上述脚本有重大缺陷：如果第二个累计值不是正好在第一个累计值之后5分钟取得的，那么就无　

法连接这两条记录。于是，我们改以“范围条件”定义连接。查询如下：　

select　a。timestamp；　

a。statistic_id；　

（b。counter　…a。counter）*60/　

（b。timestamp　…a。timestamp）　hits_per_minute　

from　hit_counter　a；　

hit_counter　b　

where　b。timestamp　between　a。timestamp＋200　

anda。timestamp　＋400　

andb。statistic_id　=a。statistic_id　

order　bya。timestamp；　a。statistic_id　

这个方法还是有缺陷：前后两次计算累计值的时间间隔，如果不介于　200　到　400　秒之间（例　

如取样频率改变了），如此之大的时间跨度就会引起风险。　



我们还有更安全的方法，就是使用基于“记录窗口（windows　of　rows）”的OLAP函数（OLAP　

function）。难以想象，这种本质上不太符合关系理论的技术可以显著提升性能，但应作为查询　

优化的最后手段使用。借助partition　子句，OLAP函数支持“分别处理结果集的不同子集”，比如　

分别对它们进行排序、总计等处理。借助OLAP　函数row_number（），可以根据　statistic_id　建立　

子集，然后按时间戳增大的顺序为不同统计赋予连续整数编号，接下来，就可以连接statistic_id　

和两个序号了，如下例子所示：　



select　a。timestamp；　

a。statistic_id；　

（b。counter　…a。counter）*60/　

（b。timestamp　…a。timestamp）　

from　（select　timestamp；　

statistic_id；　

counter；　

row_number（　）over（partition　bystatistic_id　

order　bytimestamp）　rn　

from　hit_counter）　a；　

（select　timestamp；　

statistic_id；　

counter；　

row_number（　）over（partition　bystatistic_id　


…………………………………………………………Page　72……………………………………………………………

order　bytimestamp）　rn　

from　hit_counter）　b　

where　b。rn=a。rn＋1　

anda。statistic_id　=b。statistic_id　

order　bya。timestamp；　a。statistic_id　



Oracle等DBMS支持OLAP　函数　lag（column_name；　n）。该函数借助分区（）和排序（），返回　

column_name之前的第n个值。如果使用lag（）函数，我们的查询甚至执行得更快——比先前的查　

询大约快25％。　

select　timestamp；　

statistic_id；　

（counter　…prev_counter）　*60/　

（timestamp　…prev_timestamp）　

from　（select　timestamp；　

statistic_id；　

counter；　

lag（counter；1）over（partition　bystatistic_id　

order　bytimestamp）　prev_counter；　

lag（timestamp；　1）over（partition　bystatistic_id　

order　bytimestamp）　prev_timestamp　

from　hit_counter）　a　

order　bya。timestamp；　a。statistic_id　



很多时候，我们的数据并不像航班案例中那样具有对称性。通常，当需要查找和最小、最大、　

最早、或最近的值相关联的数据时，首先必须找到这些值本身（此为第一遍扫描，需比较记录），　

接下来的用这些值作为第二遍扫描的搜索条件。而以滑动窗口（sliding　window）为基础的OLAP　

函数，可以将两遍扫描合而为一（至少表面上如此）。基于时间戳或日期的数据查询，非常特殊　

也非常重要，本章在稍后的“基于日期的简单搜索或范围搜索”中专门讨论。　



总结：当多个选取条件用于同一个表的不同记录时，可以使用基于滑动窗口工作的函数。　



基于日期的简单搜索或范围搜索　



Simple　orRange　Searchingon　Dates　



搜索条件有多种，其中日期（和时间）占有特殊地位。日期极为常见，而且比其他数据类型更　

可能成为范围搜索的条件，范围搜索可以是有界的（如“在某两天之间”），也可以是部分有界　

（“在某天之前”）。通常，为了获得这种结果集，查询需要使用当前日期（如“前六个月”）。　



上一节“通过聚合获得结果集”所举的例子，用到了sales_history　表。当时，条件位于amount　上，　

其实对于sales_history这种表更常见的是日期条件，尤其是读取特定日期的数据、或读取两个日　

期之间的数据。在保存历史数据的表中查找特定日期（或其对应值）时，必须特别注意确定当　

前日期的方法，它可能成为聚合条件的基础。　


…………………………………………………………Page　73……………………………………………………………

第1章已指出，设计保存历史数据的表颇为困难，而且没有现成的简单解决方案。无论你对当前　

数据、还是历史数据感兴趣，设计历史数据的存储方案都要根据如何使用数据决定，同时还要　

看数据多快会过时。例如，零售系统中价格的变动速度比较慢（除非正在经受严重的通货膨胀），　

而网络流量或财务设备的价格改变速度比较快，甚至快很多。　

从宏观角度来看，关键是各项历史数据的数量：是“少量数据项、大量历史数据”，还是“大量数　

据项、少量历史数据”，或是介于两者之间？其重点是：数据项的可选择性取决于数据项的总数、　

取样频率（“每天一次”还是“每次改变时”）、时间长短（“永久”还是“一年”等）。因此，本节将首　

先讨论“大量数据项、少量历史数据”的情况，接着讨论“少量数据项、大量历史数据”的情况，　

最后讨论当前值问题。　



大量数据项、少量历史数据　



ManyItems；　Few　HistoricalValues　



既然没有为每个数据项保留大量历史数据，那么各项的ID可选择性很高。说明要查询哪些项，　

限定参与查询的少数历史记录，就可确定特定日期（当前日期或以前日期）对应的值。这种情　

况需要我们再次处理聚合值（aggregate　value）。　



除非建立了代理键（本情况不需要代理键），否则主键通常是复合键，由item_id和record_date组　

成。为了查询特定日期的值，可采用两种方法：子查询和　OLAP　函数。　

使用子查询　

查找某数据项在特定日期的值相对简单，但实际上，这种简单只是假象。通常你会遇到这样的　

代码：　

select　whatever　

from　hist_data　asouter　

where　outer。item_id　=somevalue　

andouter。record_date　=（select　max（inner。record_date）