oracle 基于sql语句优化的一些tips　（－）

最新推荐文章于 2025-08-08 11:12:28 发布
转载最新推荐文章于 2025-08-08 11:12:28 发布 · 58 阅读
0 ·
CC 4.0 BY-SA版权
原文链接：http://www.cnblogs.com/ylastnight/p/3271711.html
文章标签：
#数据库
本文分享了SQL优化的实用技巧，包括批量更新、避免重复访问表、慎用自定义函数、简化SQL语句等。同时，介绍了SQL编程中的‘进攻式编程’理念，以及SQL方言的误区和过滤条件的合理运用。文章提供了SQL查询优化和编程的最佳实践，旨在提升数据库查询性能。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
  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 closure
100 SELECT closure_date
101 INTO dtPerClosure
102 FROM tperrslt
103 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 collect
112 INTO dtPerStaArray
113 FROM tperrslt
114 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   dtPerClosure
128 FROM tperrslt
129 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_id
144 
145 SELECT card_num,
146   expiry_date,
147   credit_limit
148 FROM ACCOUNTS
149 WHERE customer_id = provided_id
150 
151 ---这是个count(*)被误用
152 --进攻式编程”的本质特征是：以合理的可能性（reasonable probabilities）为基础。例如，检查客户是否存在是毫无意义的——因为既然该客户不存在，那么他的记录根本就不在数据库中！
153 --所以，应该先假设没有事情会出错；但如果出错了，就在出错的地方（而且只在那个地方）采取相应措施。这种方法很像一些数据库系统中采用的“乐观并发控制（optimisticconcurrency control）”，
154 --后者会假设update冲突不会发生，只在冲突真的发生时才进行控制处理。乐观方法比悲观方法的吞吐量高得多。
155 
156 ----下面另一种写法
157 UPDATE accounts
158 SET balance       = balance - purchased_amount
159 WHERE balance    >= purchased_amount
160 AND credit_limit >= purchased_amount
161 AND expiry_date   > today()
162 AND CUSTOMER_ID   = PROVIDED_ID
163 AND card_num      = provided_cardnum
164 
165 --检查被更新的行数。如果结果为0，只需执行下面的一个操作即可判断出错原因：
166 SELECT c.customer_id,
167   a.card_num,
168   a.expiry_date,
169   a.credit_limit,
170   a.balance
171 FROM customers c
172 LEFT OUTER JOIN accounts a
173 ON a.customer_id    = c.customer_id
174 AND A.CARD_NUM      = PROVIDED_CARDNUM
175 WHERE c.customer_id = provided_id
176 
177 
178 ---------------------------------------------------------------------------------------------------------------------------------
179 ---------------------------------------------------------------------------------------------------------------------------------
180 --tips
181 
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   salary
201 FROM employees
202 WHERE status != 'EXECUTIVE'
203 AND ROWNUM   <= 5
204 ORDER BY salary DESC
205 
206 查询的关系操作部分仅从employees表中，以完全不可知的顺序，取出最先发现的五位非经理人员（只包含empname和salary字段）。关系（以及描述关系的表）是无序的，关系中的元组（即记录）可以被存储或检索。上面的查询执
207 行后，收入最高的非经理人员或许在查询结果中，或许不在，无从知道查询结果是否满足查询条件。
208 
209 
210 SELECT *
211 FROM
212   (SELECT empname,
213     salary
214   FROM employees
215   WHERE status != 'EXECUTIVE'
216   ORDER BY SALARY DESC
217   )
218 WHERE rownum <= 5
219 
220 
221 ！注意第一个还有第二个sql.rownum 这种非关系型判断条件，慎用。
222 
223 
224 
225 ---------------------------------------------------------------------------------------------------------------------------------
226 ---------------------------------------------------------------------------------------------------------------------------------
227 --过滤条件的好坏
228 
229 蝙蝠车买主 假设有四个表： customers、orders、orderdetail、articles
230 现在假设SQL 要处理的问题是：找出最近六个月内居住在GOTHAM市、订购了蝙蝠车的所有客户
231 
232 
233 一种写法 
234 
235 SELECT DISTINCT c.custname
236 FROM customers c
237 JOIN orders o
238 ON o.custid = c.custid
239 JOIN ORDERDETAIL OD
240 ON OD.ORDID    = O.ORDID
241 join articles a
242 ON a.artid     = od.artid
243 WHERE c.city   = 'GOTHAM'
244 AND A.ARTNAME  = 'BATMOBILE'
245 AND o.ordered >= somefunc
246 
247 SOMEFUNC是个函数，返回距今六个月前的具体日期。注意上面用了DISTINCT，因为考虑到某个客户可以是大买家，最近订购了好几台蝙蝠车。
248 
249 首先，来自customers表的数据应只保留城市名为Gotham 的记录。接着，搜索orders表，这意味着custid字段最好有索
250 引，否则只有通过排序、合并或扫描orders表建立一个哈希表才能保证查询速度。对orders表，
251 还要针对订单日期进行过滤：如果优化器比较聪明，它会在连接（join）前先过滤掉一些数据，
252 从而减少后面要处理的数据量；不太聪明的优化器则可能会先做连接，再作过滤，这时在连接中指定过滤条件利于提高性能，
253 
254 例如（关联自查询）：
255 SELECT DISTINCT c.custname
256 FROM customers c,
257   orders o,
258   orderdetail od,
259   articles a
260 WHERE c.city   = 'GOTHAM'
261 AND c.custid   = o.custid
262 AND o.ordid    = od.ordid
263 AND od.artid   = a.artid
264 AND A.ARTNAME  = 'BATMOBILE'
265 AND o.ordered >= somefunc
266 
267 从逻辑的角度来看，第二个方法突显出数据处理顺序无足轻重这一事实；无论以什么顺序查询表，返回结果都是一样的。CUSTOMERS 表非常重要，因为最终所需数据都来自该表，在此例中，其他表只起辅助作用
268 
269 再试试非关联子查询
270 SELECT custname FROM customers WHERE city = 'GOTHAM' AND custid IN
271   (SELECT custid
272   FROM orders
273   WHERE ordered >= somefunc
274   AND ordid     IN
275     (SELECT od.ordid
276     FROM orderdetail od,
277       articles a
278     WHERE A.ARTNAME = 'BATMOBILE'
279     AND a.artid     = od.artid
280     )
281     
282     
283     或者
284     
285 SELECT custname
286 FROM customers
287 WHERE city  = 'GOTHAM'
288 AND custid IN
289   (SELECT o.custid
290   FROM orders o
291   WHERE o.ordered >= somefunc
292   AND EXISTS
293     (SELECT NULL
294     FROM orderdetail od,
295       articles a
296     WHERE a.artname = 'BATMOBILE'
297     AND A.ARTID     = OD.ARTID
298     AND od.ordid    = o.ordid
299     )
300   )
301   
302   
303   SELECT custname
304 FROM customers
305 WHERE city  = 'GOTHAM'
306 AND custid IN
307   (SELECT o.custid
308   FROM orders o,
309     (SELECT DISTINCT od.ordid
310     FROM orderdetail od,
311       articles a
312     WHERE a.artname = 'BATMOBILE'
313     AND a.artid     = od.artid
314     ) x
315   WHERE O.ORDERED >= SOMEFUNC
316   AND x.ordid      = o.ordid
317   )
318   
319   
320 ---------------------------------------------------------------------------------------------------------------------------------
321 ---------------------------------------------------------------------------------------------------------------------------------
322 利于多数SQL 方言，非关联子查询可以被改写成from 子句中的内嵌视图。然而，一定要记住的是，IN 会隐式地剔除重复项目，当子查询改写为FROM 子句中的内嵌视图时，必须要显式地
323 消除重复项目。
324 
325 
326 
327 --将上面这个例子简单化处理一下。假设没有函数过滤这个条件，关于关联还是非关联的选择。  
328   
329 使用关联子查询还是非关联子查询
330 
331 关联
332 SELECT DISTINCT orders.custid
333 FROM orders
334 JOIN orderdetail
335 ON (orderdetail.ordid = orders.ordid)
336 JOIN articles
337 ON (ARTICLES.ARTID     = ORDERDETAIL.ARTID)
338 WHERE articles.artname = 'BATMOBILE'
339 
340 --非关联
341 SELECT DISTINCT orders.custid
342 FROM orders
343 WHERE ordid IN
344   (SELECT orderdetails.ordid
345   FROM orderdetail
346   JOIN articles
347   ON (articles.artid     = orderdetail.artid)
348   WHERE articles.artname = 'BATMOBILE'
349   )
350 或采用from子句中的非关联子查询：
351 SELECT DISTINCT orders.custid
352 FROM orders,
353   (SELECT orderdetails.ordid
354   FROM orderdetail
355   JOIN articles
356   ON (articles.artid     = orderdetail.artid)
357   WHERE ARTICLES.ARTNAME = 'BATMOBILE'
358   ) AS SUB_Q
359 WHERE sub_q.ordid = orders.ordid
360 
361 如果这种没有其他判断条件那么就使用非关联子查询，因为关联字查询中orders 表肯定全表扫。order有可能会随着时间累计越来越大。