sql 语句优化
最近在看Oracle Database 11g SQL-Master SQL and PL/SQL in the Oracle Database 其中的一个章节“SQL Tuning”,顺带记下书中提到的优化建议。
1. 使用表连接,而不是多条查询语句(use table joins rather than multiple queries)
注:如果有多个表join,应该大表先join,小表后join.
比如:Assume tab1 contains 1,000 rows, tab2 100 rows, and tab3 10 rows. You should join tab1 with tab2 first, followed by tab2 and tab3
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- BAD (two separate queries when one would work) SELECT name, product_type_id FROM products WHERE product_id = 1; NAME PRODUCT_TYPE_ID ------------------------------ --------------- Modern Science 1 SELECT name FROM product_types WHERE product_type_id = 1; NAME ---------- Book
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- GOOD (one query with a join) SELECT p.name, pt.name FROM products p, product_types pt WHERE p.product_type_id = pt.product_type_id AND p.product_id = 1; NAME NAME ------------------------------ ---------- Modern Science Book This query results in the same product name and product
2.在进行join查询时,select中提到的column都要有别名引用,不要把别名省了(Use Fully Qualified Column References When
Performing Joins)
比如下面,bad example中提到的,description, price 就没有被别名引用。这样就会增加查询时间。
数据库会查询products 和 product_types两个表,哪个表含有 description 和 price这两个列。
把select 语句改为“SELECT p.name, pt.name, p.description, p.price” 则会省去额外查找列的时间
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- BAD (description and price columns not fully qualified) SELECT p.name, pt.name, description, price FROM products p, product_types pt WHERE p.product_type_id = pt.product_type_id AND p.product_id = 1; NAME NAME ------------------------------ ---------- DESCRIPTION PRICE -------------------------------------------------- ---------- Modern Science Book A description of modern science 19.95
明天五一,放了一天的小长假,准时下班先,后天回来接着写......
回来了,go on
3.使用case表达式而不是用多条查询语句(Use CASE Expressions Rather than Multiple Queries)
当要在一个行上进行许多去处时,使用case表达式而不是多条查询语句
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Use CASE expressions rather than
multiple queries when you need to
perform many calculations on the
same rows in a table
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 -- BAD (three separate queries when one CASE statement would work) 2 SELECT COUNT(*) 3 FROM products 4 WHERE price < 13; 5 COUNT(*) 6 ---------- 7 2 8 SELECT COUNT(*) 9 FROM products 10 WHERE price BETWEEN 13 AND 15; 11 COUNT(*) 12 ---------- 13 5 14 SELECT COUNT(*) 15 FROM products 16 WHERE price > 15; 17 COUNT(*) 18 ---------- 19 5 20 Rather than using three queries, you should write one query that uses CASE expressions. This 21 is shown in the following good example: 22 -- GOOD (one query with a CASE expression) 23 SELECT 24 COUNT(CASE WHEN price < 13 THEN 1 ELSE null END) low, 25 COUNT(CASE WHEN price BETWEEN 13 AND 15 THEN 1 ELSE null END) med, 26 COUNT(CASE WHEN price > 15 THEN 1 ELSE null END) high 27 FROM products; 28 LOW MED HIGH 29 ---------- ---------- ---------- 30 2 5 5
4.对表添加索引(Add Indexes to Tables)
索引就像书的目录一样。我们看书可以通过目录查找某一项内容,也可以对书从头到尾查找一遍我们要看的内容。显然后者更费时间。
4.1 书中给了一个建议:当表很大时,查询的结果小于等于总结果10%时添加索引。
4.2 Oracle会对表中的主键或者有唯一约束的列自动添加索引。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/* you should create an index on a column when you are retrieving a small number of rows from a table containing many rows. Create an index when a query retrieves <= 10 percent of the total rows in a table) */
4.3 对唯一值的列进行索引
4.4 对于小的表,但是那个列经常在where语句中用到,则要考虑使用位图索引(bitmap index)
4.5 索引的缺点:当我们插入一个值的时候,会需要一些额外的时间去更新索引
5.使用where而不是having(Use WHERE Rather than HAVING)