1 ---------------------------------------------------------------------------------------------------------------------------------  2 ---------------------------------------------------------------------------------------------------------------------------------  3 基于sql得一些优化的tips，有些错误不一定会犯，但是思路很好。有则改之，无则加勉。  4   5 --一次完成多个更新,update语句尽量在一次对表的访问中完成所有操作。  6   7 ---------前两个查询分两句query  8   9 UPDATE tbo_invoice_extractor 10 SET pga_status    = 0 11 WHERE pga_status IN (1,3) 12 and INV_TYPE      = 0; 13  14 UPDATE tbo_invoice_extractor 15 SET rd_status    = 0 16 WHERE rd_status IN (1,3) 17 and INV_TYPE     = 0; 18  19  20 --尽量减少对同一个表的重复访问。 21 UPDATE tbo_invoice_extractor 22 SET pga_status = ( 23   CASE pga_status 24     WHEN 1 25     THEN 0 26     WHEN 3 27     THEN 0 28     ELSE PGA_STATUS 29   END), 30   rd_status = ( 31   CASE rd_status 32     WHEN 1 33     THEN 0 34     WHEN 3 35     THEN 0 36     ELSE rd_status 37   END) 38 WHERE (pga_status IN (1,3) 39 OR rd_status      IN (1, 3)) 40 AND inv_type       = 0; 41  42  43  44  45 --------------------------------------------------------------------------------------------------------------------------------- 46 --------------------------------------------------------------------------------------------------------------------------------- 47 ----慎用自定义函数.假设就是根据一个code 找到与这个code对应的字符串。优化器对自定义函数的代码无能为力。 48  49 CREATE OR REPLACE 50   FUNCTION airport_city( 51       iata_code IN CHAR) 52     RETURN VARCHAR2 53   IS 54     city_name VARCHAR2(50); 55   BEGIN 56     SELECT city INTO city_name FROM iata_airport_codes WHERE code = iata_code; 57     RETURN(city_name); 58   END; 59   / 60  61 --TRUNC(sysdate) + 17/48 AND TRUNC(sysdate) + 16/24  随便加了一个参量，表示时间区间，不用多计较 62 SELECT flight_number, 63   TO_CHAR(departure_time, 'HH24:MI') DEPARTURE, 64   airport_city(arrival) "TO" 65 FROM flights 66 WHERE departure_time BETWEEN TRUNC(sysdate) + 17/48 AND TRUNC(sysdate) + 16/24 67 ORDER BY DEPARTURE_TIME        68  69 --中trunc(sysdate)的返回值为“今天的00:00 a.m.” 70  71 --上述函数的使用其实是没有必要的，当然一般人也不会为了一个单纯的结果集去创建一个函数，上面效率低下的一个主要原因是select 语句每做一行数据查询，就要对函数执行一遍，这个函数又在另一个 72 --query 中进行扫描,这个函数的作用完全可以放在条件中过滤结果集。没必要做这种吃力不讨好的事情。 73 SELECT f.flight_number, 74   TO_CHAR(f.departure_time, 'HH24:MI') DEPARTURE, 75   a.city "TO" 76 FROM flights f, 77   iata_airport_codes a 78 WHERE a.code = f.arrival 79 AND departure_time BETWEEN TRUNC(sysdate) + 17/48 AND TRUNC(sysdate) + 16/24 80 ORDER BY departure_time 81  82  83  84 --------------------------------------------------------------------------------------------------------------------------------- 85 --------------------------------------------------------------------------------------------------------------------------------- 86  87 --Succinct SQL 简化sql 88  89 --还是一样，一句可以完成的sql。有时候没必要再两个query 里面，如果结果集会因为一句sql而稍显复杂。下面也有处理办法 90  91 -- Get the start of the accounting period 92  93 SELECT closure_date 94 INTO dtPerSta 95 FROM tperrslt 96 WHERE fiscal_year=TO_CHAR(Param_dtAcc,'YYYY') 97 AND rslt_period  ='1' 98   || TO_CHAR(Param_dtAcc,'MM'); 99 -- Get the end of the period out of closure100 SELECT closure_date101 INTO dtPerClosure102 FROM tperrslt103 WHERE FISCAL_YEAR=TO_CHAR(PARAM_DTACC,'YYYY')104 AND rslt_period  ='9'105   || TO_CHAR(Param_dtAcc,'MM');106   107 --下面是改良版  108   109 --bulk collect 是PL/SQL 语言特有的，但任何支持显式或隐式数组提取的语言都可以用用别的方法实现。110 111 SELECT closure_date bulk collect112 INTO dtPerStaArray113 FROM tperrslt114 WHERE FISCAL_YEAR=TO_CHAR(PARAM_DTACC,'YYYY')115 AND RSLT_PERIOD IN ('1' || TO_CHAR(PARAM_DTACC,'MM'), '9' || TO_CHAR(PARAM_DTACC,'MM'))116 ORDER BY rslt_period;117 118 119 120 --这个是更加简洁的，判断条件不变，只是返回的时候顺手处理了结果集，如果返回结果为１或者９ 就显示相应的时间，如果不是，就随便显示一个时间，Max 函数是原文的逻辑，不管有没有121 --找到相应的结果，无论如何返回一个最大的时间，这都可以改。122 --tips在这里，可以根据判断条件来处理返回结果，然后顺手就完成一些业务逻辑。很简单。123 124 SELECT MAX(DECODE(SUBSTR(rslt_period, 1, 1), '1', closure_date, to_date('14/10/1066', 'DD/MM/YYYY'))),125   MAX(DECODE(SUBSTR(rslt_period, 1, 1), '9', closure_date, to_date('14/10/1066', 'DD/MM/YYYY'))),126 INTO dtPerSta,127   dtPerClosure128 FROM tperrslt129 WHERE FISCAL_YEAR=TO_CHAR(PARAM_DTACC,'YYYY')130 AND RSLT_PERIOD IN ('1' || TO_CHAR(PARAM_DTACC,'MM'), '9' || TO_CHAR(Param_dtAcc,'MM'));131 132 133 134 ---------------------------------------------------------------------------------------------------------------------------------135 ---------------------------------------------------------------------------------------------------------------------------------136 137 ---Offensive Coding with SQL  进攻式编程138 139 --例如，检查所提交的客户身份和卡号是否有效，以及两者是否匹配；检查信用卡是否过期；最后，检查当前的支付额是否超过了信用额度。如果通过了所有检查，支付操作才继续进行。140 141 --一般写法 142 143 SELECT COUNT(*) FROM CUSTOMERS WHERE customer_id = provided_id144 145 SELECT card_num,146   expiry_date,147   credit_limit148 FROM ACCOUNTS149 WHERE customer_id = provided_id150 151 ---这是个count(*)被误用152 --进攻式编程”的本质特征是：以合理的可能性（reasonable probabilities）为基础。例如，检查客户是否存在是毫无意义的——因为既然该客户不存在，那么他的记录根本就不在数据库中！153 --所以，应该先假设没有事情会出错；但如果出错了，就在出错的地方（而且只在那个地方）采取相应措施。这种方法很像一些数据库系统中采用的“乐观并发控制（optimisticconcurrency control）”，154 --后者会假设update冲突不会发生，只在冲突真的发生时才进行控制处理。乐观方法比悲观方法的吞吐量高得多。155 156 ----下面另一种写法157 UPDATE accounts158 SET balance       = balance - purchased_amount159 WHERE balance    >= purchased_amount160 AND credit_limit >= purchased_amount161 AND expiry_date   > today()162 AND CUSTOMER_ID   = PROVIDED_ID163 AND card_num      = provided_cardnum164 165 --检查被更新的行数。如果结果为0，只需执行下面的一个操作即可判断出错原因：166 SELECT c.customer_id,167   a.card_num,168   a.expiry_date,169   a.credit_limit,170   a.balance171 FROM customers c172 LEFT OUTER JOIN accounts a173 ON a.customer_id    = c.customer_id174 AND A.CARD_NUM      = PROVIDED_CARDNUM175 WHERE c.customer_id = provided_id176 177 178 ---------------------------------------------------------------------------------------------------------------------------------179 ---------------------------------------------------------------------------------------------------------------------------------180 --tips181 182 183 以主键为条件进行查询时，如果没有结果返回则开销极少，因为只需检查索引即可判断。然而，如果查询无法使用索引，就必须搜索整个表——当此表数据量很大，所在机器又正在接近满负荷工作时，可能造成灾难。184 有些异常的处理代价高昂，即使是在最佳情况下也不例外，例如重复键（DUPLICATE KEY）的探测。“唯一性（UNIQUENESS）”如何保证呢？我们几乎总是建立一个唯一性索引，每次向该索引增加一个键时，都要检185 查是否违反了该唯一性索引的约束。然而，建立索引项需要记录物理地址，于是就要求先将记录插入表，后将索引项插入索引。如果违反此约束，数据库会取消不完全的插入，并返回违反约束的错误信息。上述这些186 操作开销巨大。但最大的问题是，整个处理必须围绕个别异常展开，于是我们必须“从个别记录的角度进行思考”，而不是“从数据集出发进行思考”，这与关系数据库理论完全背道而驰。多次违反此约束会导致性能严重下降。187 188 189 190 191 192 ---------------------------------------------------------------------------------------------------------------------------------193 ---------------------------------------------------------------------------------------------------------------------------------194 -- 一些SQL方言的误区195 举个简单的例子：不是经理的员工当中，哪五个人收入最高？196 但它包含了明显的非关系描述。“找出不是经理的员工”是其中的关系操作部分，由此获得一个有限的员工集合，然后排序。有些SQL方言通过在select语句中增加特殊子句来限制返回的记录数，排序和限制记录数都是非关系操作。197 其他SQL方言（这里主要是指Oracle）则采用另外的机制，即用一个名为ROWNUM的虚拟字段（DUMMY COLUMN）为查询结果编号——这意味着编号工作发生在关系操作阶段198 199 SELECT empname,200   salary201 FROM employees202 WHERE status != 'EXECUTIVE'203 AND ROWNUM   <= 5204 ORDER BY salary DESC205 206 查询的关系操作部分仅从employees表中，以完全不可知的顺序，取出最先发现的五位非经理人员（只包含empname和salary字段）。关系（以及描述关系的表）是无序的，关系中的元组（即记录）可以被存储或检索。上面的查询执207 行后，收入最高的非经理人员或许在查询结果中，或许不在，无从知道查询结果是否满足查询条件。208 209 210 SELECT *211 FROM212   (SELECT empname,213     salary214   FROM employees215   WHERE status != 'EXECUTIVE'216   ORDER BY SALARY DESC217   )218 WHERE rownum <= 5219 220 221 ！注意第一个还有第二个sql.rownum 这种非关系型判断条件，慎用。222 223 224 225 ---------------------------------------------------------------------------------------------------------------------------------226 ---------------------------------------------------------------------------------------------------------------------------------227 --过滤条件的好坏228 229 蝙蝠车买主 假设有四个表： customers、orders、orderdetail、articles230 现在假设SQL 要处理的问题是：找出最近六个月内居住在GOTHAM市、订购了蝙蝠车的所有客户231 232 233 一种写法 234 235 SELECT DISTINCT c.custname236 FROM customers c237 JOIN orders o238 ON o.custid = c.custid239 JOIN ORDERDETAIL OD240 ON OD.ORDID    = O.ORDID241 join articles a242 ON a.artid     = od.artid243 WHERE c.city   = 'GOTHAM'244 AND A.ARTNAME  = 'BATMOBILE'245 AND o.ordered >= somefunc246 247 SOMEFUNC是个函数，返回距今六个月前的具体日期。注意上面用了DISTINCT，因为考虑到某个客户可以是大买家，最近订购了好几台蝙蝠车。248 249 首先，来自customers表的数据应只保留城市名为Gotham 的记录。接着，搜索orders表，这意味着custid字段最好有索250 引，否则只有通过排序、合并或扫描orders表建立一个哈希表才能保证查询速度。对orders表，251 还要针对订单日期进行过滤：如果优化器比较聪明，它会在连接（join）前先过滤掉一些数据，252 从而减少后面要处理的数据量；不太聪明的优化器则可能会先做连接，再作过滤，这时在连接中指定过滤条件利于提高性能，253 254 例如（关联自查询）：255 SELECT DISTINCT c.custname256 FROM customers c,257   orders o,258   orderdetail od,259   articles a260 WHERE c.city   = 'GOTHAM'261 AND c.custid   = o.custid262 AND o.ordid    = od.ordid263 AND od.artid   = a.artid264 AND A.ARTNAME  = 'BATMOBILE'265 AND o.ordered >= somefunc266 267 从逻辑的角度来看，第二个方法突显出数据处理顺序无足轻重这一事实；无论以什么顺序查询表，返回结果都是一样的。CUSTOMERS 表非常重要，因为最终所需数据都来自该表，在此例中，其他表只起辅助作用268 269 再试试非关联子查询270 SELECT custname FROM customers WHERE city = 'GOTHAM' AND custid IN271   (SELECT custid272   FROM orders273   WHERE ordered >= somefunc274   AND ordid     IN275     (SELECT od.ordid276     FROM orderdetail od,277       articles a278     WHERE A.ARTNAME = 'BATMOBILE'279     AND a.artid     = od.artid280     )281     282     283     或者284     285 SELECT custname286 FROM customers287 WHERE city  = 'GOTHAM'288 AND custid IN289   (SELECT o.custid290   FROM orders o291   WHERE o.ordered >= somefunc292   AND EXISTS293     (SELECT NULL294     FROM orderdetail od,295       articles a296     WHERE a.artname = 'BATMOBILE'297     AND A.ARTID     = OD.ARTID298     AND od.ordid    = o.ordid299     )300   )301   302   303   SELECT custname304 FROM customers305 WHERE city  = 'GOTHAM'306 AND custid IN307   (SELECT o.custid308   FROM orders o,309     (SELECT DISTINCT od.ordid310     FROM orderdetail od,311       articles a312     WHERE a.artname = 'BATMOBILE'313     AND a.artid     = od.artid314     ) x315   WHERE O.ORDERED >= SOMEFUNC316   AND x.ordid      = o.ordid317   )318   319   320 ---------------------------------------------------------------------------------------------------------------------------------321 ---------------------------------------------------------------------------------------------------------------------------------322 利于多数SQL 方言，非关联子查询可以被改写成from 子句中的内嵌视图。然而，一定要记住的是，IN 会隐式地剔除重复项目，当子查询改写为FROM 子句中的内嵌视图时，必须要显式地323 消除重复项目。324 325 326 327 --将上面这个例子简单化处理一下。假设没有函数过滤这个条件，关于关联还是非关联的选择。  328   329 使用关联子查询还是非关联子查询330 331 关联332 SELECT DISTINCT orders.custid333 FROM orders334 JOIN orderdetail335 ON (orderdetail.ordid = orders.ordid)336 JOIN articles337 ON (ARTICLES.ARTID     = ORDERDETAIL.ARTID)338 WHERE articles.artname = 'BATMOBILE'339 340 --非关联341 SELECT DISTINCT orders.custid342 FROM orders343 WHERE ordid IN344   (SELECT orderdetails.ordid345   FROM orderdetail346   JOIN articles347   ON (articles.artid     = orderdetail.artid)348   WHERE articles.artname = 'BATMOBILE'349   )350 或采用from子句中的非关联子查询：351 SELECT DISTINCT orders.custid352 FROM orders,353   (SELECT orderdetails.ordid354   FROM orderdetail355   JOIN articles356   ON (articles.artid     = orderdetail.artid)357   WHERE ARTICLES.ARTNAME = 'BATMOBILE'358   ) AS SUB_Q359 WHERE sub_q.ordid = orders.ordid360 361 如果这种没有其他判断条件那么就使用非关联子查询，因为关联字查询中orders 表肯定全表扫。order有可能会随着时间累计越来越大。