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

-- 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

-- 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” 则会省去额外查找列的时间

-- 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表达式而不是多条查询语句

Use CASE expressions rather than
multiple queries when you need to 
perform many calculations  on the 
same rows in a table

 

-- BAD (three separate queries when one CASE statement would work)
SELECT COUNT(*)
FROM products
WHERE price < 13;
COUNT(*)
----------
2
SELECT COUNT(*)
FROM products
WHERE price BETWEEN 13 AND 15;
COUNT(*)
----------
5
SELECT COUNT(*)
FROM products
WHERE price > 15;
COUNT(*)
----------
5
Rather than using three queries, you should write one query that uses CASE expressions. This
is shown in the following good example:
-- GOOD (one query with a CASE expression)
SELECT
COUNT(CASE WHEN price < 13 THEN 1 ELSE null END) low,
COUNT(CASE WHEN price BETWEEN 13 AND 15 THEN 1 ELSE null END) med,
COUNT(CASE WHEN price > 15 THEN 1 ELSE null END) high
FROM products;
LOW MED HIGH
---------- ---------- ----------
2 5 5

 4.对表添加索引(Add Indexes to Tables)

      索引就像书的目录一样。我们看书可以通过目录查找某一项内容，也可以对书从头到尾查找一遍我们要看的内容。显然后者更费时间。

　　4.1 书中给了一个建议：当表很大时，查询的结果小于等于总结果10%时添加索引。

      4.2 Oracle会对表中的主键或者有唯一约束的列自动添加索引。

/*
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)