andinv_type =0; 上例中,可能出现重复更新相同字段为相同内容的情况,这的确增加了一小点儿开销。但在多 数情况下,一个update会比多个update快得多。注意上例中的“逻辑(logic)”,我们通过case 语 句实现了隐式的条件逻辑(implicit conditional logic),来处理那些符合更新条件的数据记录,并 且更新条件可以有多条。 总结:有可能的话,用一个语句处理多个更新;尽量减少对同一个表的重复访问。 慎用自定义函数 Careful Use of User…Written Functions 将自定义函数(User…Written Function)嵌到SQL语句后,它可能被调用相当多次。如果在select 语句的选出项列表中使用自定义函数,则每返回一行数据就会调用一次该函数。如果自定义函 数出现在 where 子句中,则每一行数据要成功通过过滤条件都会调用一次该函数;如果此时 其他过滤条件的筛选能力不够强,自定义函数被调用的次数就非常可观了。 如果自定义函数内部还要执行一个查询,会发生什么情况呢?每次函数调用都将执行此内部查 询。实际上,这和关联子查询(correlated subquery)效果相同,只不过自定义函数的方式阻 碍了基于开销的优化器(cost…based optimizer,CBO)对整个查询的优化效果,因为“子查询” 隐藏在函数中,数据库优化器鞭长莫及。 下面举例说明将SQL语句隐藏在自定义函数中的危险性。表flights描述商务航班,有航班号、起 飞时间、到达时间及机场 IATA 代码(注5)等字段。IATA代码均为三个字母,有9 000多个, 它们的解释保存在参照表中,包含城市名称(若一个城市有多个机场则应为机场名称)、国家名 称等。显然,显示航班信息时,应该包含目的城市的机场名称,而不是简单的 IATA 代码。 在此就遇到了现代软件工程中的矛盾之一。被认为是“优良传统”的模块化编程一般情况下非常 适用,但对数据库编程而言,代码是开发者和数据库引擎的共享活动(shared activity),模块 化要求并不明确。例如,我们可以遵循模块化原则编写一个小函数来查找 IATA 代码,并返回 完整的机场名称: create or replace function airport_city(iata_code in char) return varchar2 is …………………………………………………………Page 21…………………………………………………………… city_name varchar2(50); begin select city into city_name from iata_airport_codes where code = iata_code; return(city_name); end; / 对于不熟悉 Oracle 语法的读者,在此做个说明,以下查询中trunc(sysdate)的返回值为“今天的 00:00 a。m。”,日期计算以天为单位;所以起飞时间的条件是指今天 8:30 a。m。 至 4:00 p。m。 之 间。调用airport_city函数的查询可以非常简单,例如: select flight_number; to_char(departure_time; "HH24:MI") DEPARTURE; airport_city(arrival) 〃TO〃 from flights where departure_time between trunc(sysdate) + 17/48 and trunc(sysdate) + 16/24 order by departure_time / 这个查询的执行速度令人满意;在我机器上的随机样本中,返回77行数据只用了0。18 秒(多次 执行的平均值),用户对这样的速度肯定满意(统计数据表明,此处理访问了 303个数据块,53个是从磁盘读出的——而且每行数据有个递归调用)。 我们还可以用join来重写这段代码,作为查找函数的替代方案,当然它看起来会稍微复杂些: select f。flight_number; to_char(f。departure_time; "HH24:MI") DEPARTURE; a。city 〃TO〃 from flights f; iata_airport_codes a where a。code = f。arrival and departure_time between trunc(sysdate) + 17/48 and trunc(sysdate) + 16/24 order by departure_time / …………………………………………………………Page 22…………………………………………………………… 这个查询只用了 0。05 秒(统计数据同前,但没有递归调用)。对于执行时间不到 0。2 秒的查 询来说,速度快了3倍似乎无关紧要,但在大型系统中,这些查询每天经常执行数十万次——假 设以上查询每天只执行五万次,于是查询的总耗时为 2。5 小时。若不使用上述查找函数(lookup function)则只需要不到 42 分钟,速度提高超过300%,这对大数据量的系统意义重大,最终 带来经济上的节约。通常,使用查找函数会使批处理程序的性能极差。而且查询时间的增加, 会使同一台机器支持的并发用户数减少,我们将在第9章对此展开讨论。 总结:优化器对自定义函数的代码无能为力。 SQL 简洁的SSQQLL Succinct SQL 熟练的开发者使用尽可能少的 SQL语句完成尽可能多的事情。相反,拙劣的开发者则倾向于严 格遵循已制订好的各功能步骤,下面是个真实的例子: …Get the start of the accounting period select closure_date into dtPerSta from tperrslt where fiscal_year=to_char(Param_dtAcc;"YYYY") and rslt_period="1" || to_char(Param_dtAcc;"MM"); …Get the end of the period out of closure select closure_date into dtPerClosure from tperrslt where fiscal_year=to_char(Param_dtAcc;"YYYY") and rslt_period="9" || to_char(Param_dtAcc;"MM"); 就算速度可以接受,这也是段极糟的代码。很不幸,性能专家经常遇到这种糟糕的代码。既然 两个值来自于同一表,为什么要分别用两个不同的语句呢?下面用Oracle的bulk collect子句, 一次性将两个值放到数组中,这很容易实现,关键在于对rslt_period进行order by操作,如下所 示: select closure_date bulk collect into dtPerStaArray from tperrslt where fiscal_year=to_char(Param_dtAcc;"YYYY") and rslt_period in ("1" || to_char(Param_dtAcc;"MM"); …………………………………………………………Page 23…………………………………………………………… "9" || to_char(Param_dtAcc;"MM")) order by rslt_period; 于是,这两个日期被分别保存在数组的第一个和第二个位置。其中,bulkcollect 是 PL/SQL 语 言特有的,但任何支持显式或隐式数组提取的语言都可如法炮制。 其实甚至数组都是不必要的,用以下的小技巧(注6),这两个值就可以被提取到两个变量中: select max(decode(substr(rslt_period; 1; 1); …Check the first character "1"; closure_date; …If it"s "1" return the date we want to_date("14/10/1066"; "DD/MM/YYYY"))); …Otherwise something old max(decode(substr(rslt_period; 1; 1); "9"; closure_date; …The date wewant to_date("14/10/1066"; "DD/MM/YYYY"))); into dtPerSta; dtPerClosure from tperrslt where fiscal_year=to_char(Param_dtAcc;"YYYY") and rslt_period in ("1" || to_char(Param_dtAcc;"MM"); "9" || to_char(Param_dtAcc;"MM")); 在这个例子中,预期返回值为两行数据,所以问题是:如何把原本属于一个字段的两行数据, 以一行数据两个字段的方式检索出来(正如数组提取的例子一样)。为此,我们 检查rslt_period字段,两行数据的rslt_period字段有不同值;如果找到需要的记录,就返回要找 的日期;否则,就返回一个在任何情况下都远比我们所需日期要早的日期(此处选了哈斯?