系统优化中很重要的方面是SQL语句的优化，对于海量数据，优质的SQL能够有效的提高系统的可用性。

总结的有点罗嗦，列个简单的目录啦~

目录

第一部分知识准备 第二部分常用sql用法和注意事项 第三部分 sql优化总结

　　　　1. sql执行过程　　　　　　　　　　　1. exists 和 in 1. 优化一般原则

　　　　2. sql 共享　　　　　　　　　　　　　2. union 和 union all 2. 具体注意事项

　　　　3. 绑定变量 　　　　3. with as

　　　　4. 数据表访问方式　　　　　　　　　 4. order by

　　　　5. sql 执行顺序　　　　　　　　　　　5. group by

　　　　6. 索引使用　　　　　　　　　　　　　6. where 和 having

　　　　　　　　　　　　　　　　　　　　　　 7. case when 和 decode

知识准备

1. sql执行过程

1）执行过程

　　当一个oracle实例接收到一条sql后，执行过程如下：

　　1） create a cursor 创建游标

　　2） parse the statement 分析语句

　　3) describe results of a query 描述查询的结果集

　　4）define output of a query 定义查询的输出数据

　　5）bind any variables 绑定变量

　　6）parallelize the statement 并行执行语句

　　7）run the statement 运行语句

　　8）fetch rows of a query 取查询结果

　　9）close the cursor 关闭游标

2.SQL 共享

　　1.为不重复解析相同的SQL语句，oracle 将执行过的SQL语句存放在内存的共享池（shared buffer pool）中，可以被所有数据库用户共享

　　2. 当执行一个SQL语句时，如果它和之前执行过的语句完全相同（注意同义词和表是不同对象），oracle就能获得已经被解析的语句；

3.bind variables绑定变量

1）解决重编译问题

eg1：

insert into tab1(col1,col2) values (val1,val2); --普通方式

values (:v1,:v2);绑定变量，只需编译一次

eg2：使用PreparedStatement

PreparedStatement ps = con.prepareStatement("insert into tab1 (col1,col2) values (?,?)");

　　2）共享游标

　　好处：减少解析；提高内存使用率；动态内存调整

　　假如输入如下两个sql：

　　这两句sql会被转化为：

4.访问数据表方式

　　1）全表扫描——顺序访问表中每条记录

　　oracle采用一个读入多个数据块的方式优化全表扫描

　　2）通过rowid访问表——rowid包含了表中记录的物理位置信息， 基于rowid访问方式可以提高访问表的效率

　　oracle通过索引实现了数据和存放数据位置rowid之间的联系，通常索引提供了快速访问rowid的方法

5. select sql执行顺序

1）select子句

(8)SELECT (9)DISTINCT  (11)<Top Num> select list>
(1)FROM <left_table3)<join_typeJOIN <right_table2)ON <join_condition4)WHERE <where_condition5)GROUP BY <group_by_list6)WITH <CUBE | RollUP7)HAVING <having_condition10)ORDER <order_by_list>

2）执行顺序说明

　　1）FROM [left table]——from前的表做笛卡尔集 ——虚拟表VT1

　　2) <join condition>——筛选——VT2

　　3) join type] JOIN right table]——连接——VT3 详细见 oracle连接

　　4) WHERE ——where筛选——VT4

　　5）BY ——按照GROUP BY子句中的列对VT4中行分组——VT5

　　6）CUBE|ROLLUP——分组，eg：ROLLUP(A,B)，首先会对(A、B)进行GROUP BY，然后对(A)进行GROUP BY，最后对全表GROUP BY

　　　　　　　　　　　　　　　　CUBE(A,B),首先对（A、B）BY,然后(A)、(B) BY;

　　　　　　　　　　　　　　　　——VT6

　　7) HAVING——HAVING筛选——VT7

　　8）SELECT——VT8

　　9) DISTINCT——移除重复的行——VT9

　　10）BY——按照order by子句中的列将VT9中列表排序，生成游标——VC10

　　11) TOP ——从VC10的开始处选择一定数量或者比例的行——VT11，返回结果

　　2. 使用了ORDER BY子句的查询不能用作表表达式（视图、内联表值函数、子查询、派生表和共用表达式），如下的语句都会产生错误

6.索引使用

　　正确使用索引可以有效提高系统性能，详细见oracle索引总结

常用sql用法和及注意事项

1.exits和in用法

1）说明：

　　1.exists对外表做循环，每次循环对内表查询；in将内表和外表做hash连接

　　2. 使用exists oracle会先检查主查询； 使用in，首先执行子查询，并将结果存储在临时表中

2）使用：

　　表class和student表

　　下面查询student中classno在class中的数据

　　1.使用exists和not exists

　　结果：

　　2.使用in 和not in

eg1:

可以更换成：

结果：

4）多个with table as 一起使用时用逗号隔开，并且只能使用一个with如下例子

with vt1 >=60),vt2 from class),vt3 from teacher)
select vt1.name,vt1.score,vt2.classname,vt3.teachername  from vt1,vt2,vt3 where vt1.classno= vt2.classno and vt1.teacherid=vt3.teacherid;

　　2. 多列升序

　　3. 多列降序

　　4. 混合

　　1. oracle在order by 时认为null是最大值，asc时排在最后，desc时排在最前

　　eg：

　　2. 使用nulls first （不管asc或者desc，null记录排在最前）或者nulls last 可以控制null的位置，eg：

结果如下：

4）将某行数据置顶（decode）

　　eg1：

eg2： （某一行置顶，其他的升序）

5）注意事项

　　1.任何在order by 语句的非索引项都将降低查询速度

　　2. 避免在order by 子句中使用表达式

5. group by

　　1.用于对where执行结果进行分组

2）简单例子：

eg1：

sum(score),deptno group by deptno;

eg2:

7. case when 和decode

　　1. decode更简洁

　　2. decode只能做等值的条件区分，case when可以使用区间的做判断

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

等价于：

IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 RETURN(翻译值2)
　　　　......
ELSIF 条件=值n RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

CASE expr WHEN comparison_expr1 THEN return_expr1
         WHEN comparison_expr2 THEN return_expr2
          WHEN comparison_exprn THEN return_exprn
          ELSE else_expr]
END

CASE
         END