高效SQL执行的基本概念及基本原则

. SQL基本调整
1.1. SQL处理顺序
SQL语句处理分两个或三个阶段，每个语句从用户进程传给服务器进程进行分析然后执行。如果是select 语句，则还需要将结果返回给用户。
1.1.1. 分析(PARSE)
分析是SQL语句处理的第一步。主要进行：
 检查语法和根据字典来检查表名、列名。
 确定用户执行语句的权限。
 为语句确定最优的执行计划。
 从SQL区中找出语句。
1.1.2. 执行(Execute）
Oracle执行阶段执行的是被分析过的 语句。对于UPDATE、DELETE语句，Oracle先锁住有关的行。Oracle还要查找数据是否在数据缓冲区里。如果不在还得从数据文件中将数据读到数据缓冲区里来。
1.1.3. 检索(Fetch ）
如果是select 语句，还要进行检索操作。执行结束，将数据返回给用户。
1.1.4. Select 语句处理
一般select 语句处理要经过下面步骤：
执行顺序是：
1） 建立光标。
2） 分析语句。
3） 定义输出：指定位置，类型，结果集的数据类型。
4） 绑定变量：如果查询使用变量的话，Oracle就要知道变量的值。
5） 是否能并行运行。
6） 执行查询。
7） 检索出数据。
8） 关闭光标。
1.1.5. DML语句处理
一般INSERT,UPADTE,DELETE 语句处理执行顺序是：
1) 建立光标：Oracle建立一个隐含的光标。
2) 分析语句。
3) 绑定变量：如果语句用了变量，Oracle要知道变量的值。
4) 看语句是否能以并行方式运行（如果有多个服务器时）。
5) 执行语句。
6) 通知用户，语句已执行完毕。
7) 关闭光标。
1.1.6. COMMIT语句处理顺序
当事务提交时，Oracle分配一个唯一的顺序号SCN(System Change Number)给事务。 数据库 恢复总是基于该SCN号来进行处理。SCN号是记录在控制文件、数据文件、块头及重做日志文件中。
1．COMMIT处理步骤：
Oracle 在下面情况提交事务：
 发出一个COMMIT语句。
 执行DDL语句时。
 离开Oracle时。
2．Oracle处理COMMIT的顺序：
1） 服务器为每个COMMIT产生一个SCN。使改变永久化。
2） LGWR进程将日志缓冲区数据并带有SCN一起写到重做日志文件。
3） 服务器释放表级和行级锁。
4） 用户被提示COMMIT完成。
5） 服务器使事务已完成。
1.1.7. 处理ROLLBACK的顺序
当下面情况发生时执行回滚：
 发出ROLLBACK命令。
 服务器进程放弃地终止。
 会话被DBA终止。
ROLLBACK 是对数据库的操作进行撤消，步骤有：
1) 服务器进程不做任何的改变。
2)服务器释放表级和行级锁。
3) 服务器使事务已完成。

1.2. SQL语句优化
1.2.1. SQL语句的优化方法
在提高SQL语句效率上，可以考虑下面几个方面：
 重新构造语句；
 修改或禁止触发器；
 重新构造数据；
 及时统计CBO所用信息。
1.2.2. 重新构造语句
在重构索引之后，你可以试着重构语句。即重写那些效率差的语句。如果你对语句的意图了解的话，就很容易对语句进行修改。
重新构造语句
由于SQL语言的灵活性，应用中多个语句更适合应用的需要。虽然两个SQL语句可以产生同样的结果，但Oracle处理一个语句比处理多个组合而成的语句更快。你可以使用EXPLAIN PLAN语句的结果进行比较，从而确定哪个语句更有效。
举例说明：
选择有利的连接顺序
连接顺序对执行很有意义。SQL语句调整的主要目标是避免执行不必要的工作，这样就考虑三个规则：
 如果通过索引可以得到行的话，就要避免全表扫描；
 如果你能使用的索引取到100行的话，就避免使用取到 10,000 行的索引；
 选择连接要注意选择连接较少行的表。
下面的例子说明连接顺序的效率：
1). 选择驱动表和驱动索引。
前三个条件是对单个表进行筛选，后两个条件是连接条件。筛选条件主要是表和索引的选择。
2).选择正确的索引
当了解所要驱动的表后，就要选择有效的索引。否则就可能出现全表扫描。
3).选择最好的连接顺序
使用非转换列值
可以在where 子句中使用非转换的列值，如：WHERE a.order_no = b.order_no
这样的使用比下面的用法更好：
WHERE TO_NUMBER (SUBSTR(a.order_no, instr(b.order_no, ’.’) - 1))
= TO_NUMBER (SUBSTR(a.order_no, instr(b.order_no, ’.’) - 1))
建议不要在where 后使用SQL 函数。
1.2.3. 条件中尽量少用函数
条件中尽量避免对表字段进行函数操作,除非该字段有对应的函数索引,对于带有substr字段的条件尽量使用有前导字符的like运算符代替.这是因为有前导符的like(如’8834%’)可以使用索引,而substr将使对应字段的索引无效,导致数据库对该表进行全表扫描.
如: select distinct a.taxregcode,
a.taxpayername,
a.regaddress,
a.jurpname,
d.taxpayerstatusname
from v_reg_taxpayerbasicinfo a,
tb_lvy_levydata b,
tb_lvy_levydetaildata c,
TC_Reg_TaxpayerStatus d
where a.taxregcode = b.taxregcode
and b.levydataid = c.levydataid
and b.negativeflag <> :"SYS_B_0"
and a.businesstradecode like :"SYS_B_1"
and c.taxtypecode like :"SYS_B_2"
and to_char(b.taxstatusdate, :"SYS_B_3") between :"SYS_B_4" and :"SYS_B_5"
and a.taxpayerstatuscode = d.taxpayerstatuscode
该语句由于对日期字段使用了函数进行转换,导致对表tb_lvy_levydata进行全表扫描.极大影响执行效率,故可以对该字段修改为:
and b.taxstatusdate between :"SYS_B_4" and :"SYS_B_5"
从而是执行计划可以走索引,避免进行全表扫描从而提高速度,减轻对数据库压力
1.2.4. 使用绑带变量提高效率
由于SQL执行的过程中首先要进行分析,如果数据库的缓存中存在对应的SQL语句就会直接提取该语句进行执行操作,避免分析过程从而提高语句执行的效率,同时也减少数据库的缓存空间.故尽量使用绑带变量方式对SQL进行操作.
1.2.5. SELECT语句中避免使用*
当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.
1.2.6. (NOT) IN
对于条件中带有IN、NOT IN的子句如果条件语句中查询出来的结果集比较大的话，尽量使用EXISTS 或者NOT EXISTS语句代替。
对于NOT IN，在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下在数据量比较多的情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.。
1.2.7. 使用索引提高查询效率
对于经常大量执行的SQL语句，可以根据查询条件建立相应的索引或者组合索引从而提高查询速度，同时也大量减少物理读和逻辑读而提高数据库的性能。

后注racle9i及以后版本趋向对SQL语句进行Cost Based Optimizer(简称CBO) 基于统计信息的优化.故及时收集表的信息对于SQL执行是非常有好处的.上面的文档说明有些是RBO的规则下.
当然最重要的是数据库的设计!数据库的设计好坏将决定一个数据库性能的60%甚至更多! [@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/122642/viewspace-901113/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/122642/viewspace-901113/