sql优化总结篇

一、查看表字段名或随机少量数据时，不要使用SELECT * FROM TABLENAME

用DESC TABLENAME或SELECT * FROM TABLENAME WHERE 1 = 2、SELECT * FROM TABLENAME WHERE ROWNUM < 1等命令查看表结构信息，尽量不要直接执行SELECT * FROM TABLENAME，然后kill会话。

二、SELECT 子句中避免使用*

在SELECT子句中列出所有的列时，使用*很方便，但是效率低。因为ORACLE在解析过程中会查询数据字典，将*依次转换成所有的列名。所以，直接在SELECT子句中写出想要显示的列。

三、查询总记录数时，尽量不要用COUNT(*)，而要指定一个有索引的字段。

例如主键列为INDEX，使用COUNT(INDEX)能利用索引。

四、对分区表进行查询时，尽量把分区键作为查询条件的第一个条件

五、无条件删除表中数据时，用TRUNCATE代替DELETE

使用DELETE删除表中记录未提交时，系统会用回滚段存放被删除信息。而TRUNCATE命令不使用回滚段，当命令执行后，数据不能被恢复，因此很少的资源被调用，执行时间也会更短。

六、查询语句中尽量使用表的索引字段，避免做大表的全表扫描

例如：WHERE子句中有联接的列，即使最后的联接值为一个静态值，也不会使用索引。

SELECT *

FROM EMPLOYEE

WHERE FIRST_NAME || '' || LAST_NAME = 'Beill Cliton';

这条语句没有使用基于LAST_NAME创建的索引。当采用下面这种SQL语句的编写，ORACLE系统就可以采用基于LAST_NAME创建的索引。

SELECT *

FROM EMPLOYEE

WHERE FIRST_NAME = 'Beill'

AND LAST_NAME = 'Cliton';

七、带通配符（%）的LIKE语句

例如：SQL语句：

SELECT * FROM EMPLOYEE WHERE LAST_NAME LIKE '%cliton%';

由于通配符（%）在词首出现，所以Oracle系统不使用LAST_NAME的索引。如此使用通配符会降低查询速度。当通配符出现在字符串其他位置时，优化器就能利用索引。例如：

SELECT * FROM EMPLOYEE WHERE LAST_NAME LIKE 'c%';

八、用EXISTS替代IN

在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。

低效：

SELECT *

FROM EMP

WHERE EMPNO > 0

AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB');

高效：

SELECT *

FROM EMP

WHERE EMPNO > 0

AND EXISTS (SELECT 'X'

FROM DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO

AND LOC = 'MELB');

九、用NOT EXISTS替代NOT IN

在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为避免使用NOT IN ，可以把它改写成外连接(Outer Joins)或NOT EXISTS。例如：

SELECT *

FROM EMP

WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT = 'A');

为了提高效率。改写为：

(方法一：高效)

SELECT *

FROM EMP A, DEPT B

WHERE A.DEPT_NO = B.DEPT(+)

AND B.DEPT_NO IS NULL

AND B.DEPT_CAT(+) = 'A';

(方法二：最高效)

SELECT *

FROM EMP E

WHERE NOT EXISTS (SELECT 'X'

FROM DEPT D

WHERE D.DEPT_NO = E.DEPT_NO

AND DEPT_CAT = 'A');

十、尽可能用UNION ALL替换UNION

当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION ALL的方式被合并， 然后在输出最终结果前进行排序。

如果用UNION ALL替代UNION，就不需排序，提高了查询效率。例如：

低效：

SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = '31-DEC-95'

UNION

SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = '31-DEC-95';

高效：

SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = '31-DEC-95'

UNION ALL

SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = '31-DEC-95';

十一、ORDER BY语句建议

ORDER BY语句决定了ORACLE如何将返回的查询结果排序。ORDER BY语句对要排序的列没有特别限制，也可以将函数加入列中。在ORDER BY语句中使用非索引项或有计算表达式都将降低查询速度。当ORDER BY中所有的列定义为非空时会用到索引，例如：

T1表的ID列存在索引，且非空。则以下查询用到索引：

SELECT * FROM T1 ORDER BY ID;

十二、避免使用NOT

在查询时经常在WHERE子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用AND（与）、OR（或）以及NOT（非）。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子：

... WHERE NOT (STATUS ='VALID')

如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中，这就是不等于（<>）运算符。换句话说，即使不在查询WHERE子句中显式地加入NOT词，NOT仍在运算符中，见下例：

... WHERE STATUS <>'INVALID';

再看下面这个例子：

SELECT * FROM EMPLOYEE WHERE SALARY <> 3000;

对这个查询，可以改写为不使用NOT：

SELECT * FROM EMPLOYEE WHERE SALARY < 3000 OR SALARY > 3000;

虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许ORACLE对SALARY列使用索引，而第一种查询则不能使用索引。

十三、使用DECODE函数减少处理时间

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。例如：

SELECT COUNT(*) ，SUM(SAL)

FROM EMP

WHERE DEPT_NO = 0020

AND ENAME LIKE　 'SMITH%';

SELECT COUNT(*) ，SUM(SAL)

FROM EMP

WHERE DEPT_NO = 0030

AND ENAME LIKE　 'SMITH%';

可以用DECODE函数高效地得到相同结果

SELECT COUNT(DECODE(DEPT_NO, 0020, 'X', NULL)) D0020_COUNT,

COUNT(DECODE(DEPT_NO, 0030, 'X', NULL)) D0030_COUNT,

SUM(DECODE(DEPT_NO, 0020, SAL, NULL)) D0020_SAL,

SUM(DECODE(DEPT_NO, 0030, SAL, NULL)) D0030_SAL

FROM EMP

WHERE ENAME LIKE 'SMITH%';

类似的，DECODE函数也可以运用于GROUP BY 和ORDER BY子句中。

十四、删除重复记录

DELETE FROM EMP E

WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

十五、如果可以使用WHERE条件，尽量不要在HAVING中限制数据

十六、尽量不要使数据排序

带有DISTINCT、UNION、MINUS、INTERSECT、ORDER BY、GROUP BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。DISTINCT需要一次排序操作，而其他的至少需要执行两次排序，影响查询的效率。

十七、避免改变索引列类型

当比较不同数据类型的数据时，ORACLE自动对列进行简单的类型转换。假设EMPNO是一个数值类型的索引列。

SELECT * FROM EMP WHERE EMPNO = ‘123’;

实际上，经过ORACLE类型转换，语句转化为：

SELECT * FROM EMP WHERE EMPNO = TO_NUMBER(‘123');

但是类型转换没有发生在索引列上，索引的用途没有被改变。如果，EMP_TYPE是一个字符类型的索引列。

SELECT * FROM EMP WHERE EMP_TYPE = 123;

这个语句被ORACLE转换为：

SELECT * FROM EMP WHERE TO_NUMBER(EMP_TYPE) = 123;

因为内部发生的类型转换，索引将不会被用到。

十八、避免在索引列上使用计算

WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。例如：

低效：

SELECT * FROM DEPT WHERE SAL * 12 > 25000;

高效：

SELECT * FROM DEPT WHERE SAL > 25000 / 12;

十九、避免在索引列上使用IS NULL和IS NOT NULL

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录。对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中。例如：

如果唯一性索引建立在表的A列和B列上，并且表中存在一条记录的A，B值为(123，null) ， ORACLE将不接受下一条具有相同A，B值（123，null）的记录(插入)。然而如果所有的索引列都为空，ORACLE将认为整个键值为空，而空不等于空。因此可以无限条空记录。

因空值不存在于索引列中，所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。例如：

低效：(索引失效)

SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;

高效：(索引有效)

SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;

二十、子查询改写成表连接

通常来说，采用表连接的方式比子查询更有效率，但并不是所有的子查询都可以改写成表连接的形式。只有当连接字段存在唯一性时才可以进行改写。否则重复字段会产生笛卡尔积。

例如：

T1表存在以下数据：

ID ID2

1 1

2 2

2 3

T2表存在以下数据：

ID ID2

1 1

2 2

3 2

4 2

2 3

则以下查询结果不同：

子查询：

SELECT COUNT(*) FROM T1 WHERE T1.ID IN (SELECT ID FROM T2); 返回值3

表连接：

SELECT COUNT(*) FROM T1, T2 WHERE T1.ID = T2.ID; 返回值5

二十一、使用索引的第一个列

如果索引是建立在多个列上，只有在它的第一个列(leading column)被WHERE子句引用时，优化器才会选择使用该索引。

二十二、减少对表的查询

在含有子查询的SQL语句中，要特别注意减少对表的查询。例如：

低效：

SELECT TAB_NAME

FROM TABLES

WHERE TAB_NAME = (SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604)

AND　DB_VER = (SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604);

高效：

SELECT TAB_NAME

FROM TABLES

WHERE (TAB_NAME, DB_VER) =

(SELECT TAB_NAME, DB_VER FROM TAB_COLUMNS WHERE VERSION = 604);

二十三、SQL语句中：用>=替代>

如果在ID列上建有索引，则语句SELECT * FROM EMPLOYEE WHERE ID >= 9 要比语句SELECT * FROM EMPLOYEE WHERE ID > 8高效。这是由于前者DBMS将直接跳到第一个ID等于9的记录而后者将首先定位到8的记录并且向前扫描到第一个DEPT大于9的记录。

二十四、使用提示(hints)

在ORACLE中，是通过为语句添加hints(提示)来实现干预优化器优化的目的。hints是ORACLE提供的一种机制，用来告诉优化器按照指定方式生成执行计划。可以用hints实现：

1、使用优化器的类型；

2、基于代价优化器的优化目标，是ALL_ROWS还是FIRST_ROWS；

3、表的访问路径，是全表扫描，还是索引扫描，还是直接利用ROWID；

4、表之间的连接类型；

5、表之间的连接顺序；

6、语句的并行程度

hints只应用在它们所在SQL语句块(由SELECT、UPDATE、DELETE关键字标识)上，对其它SQL语句或语句的其它部分没有影响。如对于使用UNION操作的2个SQL语句，如果只在一个SQL语句上有hints，则该hints不会影响另一个SQL语句。可以使用注释(COMMENT)来为一个语句添加hints，一个语句块只能有一个注释，而且注释只能放在SELECT、UPDATE、DELETE关键字的后面

使用hints的语法：

{DELETE|INSERT|SELECT|UPDATE}

或者

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

注解：

1、DELETE、INSERT、SELECT和UPDATE是标识一个语句块开始的关键字，包含提示的注释只能出现在这些关键字的后面，否则提示无效。

2、“+”号表示该注释是一个hints，该加号必须立即跟在”提示避免，示例语句如下：

ALTER TABLE TAB1 NOLOGGING;

INSERT INTO TAB1 SELECT * FROM TAB2;

COMMIT;

ALTER TABLE TAB1 LOGGING;

二十六、DBLINK使用

使用DBLINK进行查询，当查询包含了本地表和远端表时，ORACLE一般先把远端表的数据通过网络传送到SQL发起端，再跟本地表进行关联得到最终结果，如果远端表返回的数据较多，则会影响查询的速度。例如：

在YZDB3上执行以下语句：

SELECT *

FROM INFO.T_CI_CUSTINFO_A@YZDBBA

WHERE CUSTID IN (SELECT ATTACHCUSTID

FROM INFO.T_CI_USERINFO_E

WHERE SVCNUM = '139XXXXXXXX');

执行时间：165.078秒。若改成从A库中取数据，速度会提高很多：

SELECT *

FROM INFO.T_CI_CUSTINFO_A

WHERE CUSTID IN (SELECT ATTACHCUSTID

FROM INFO.T_CI_USERINFO_E@YZDBAB

WHERE SVCNUM = '139XXXXXXXX');

执行时间：0.297秒。或通过driving_site强制指定主驱动表，即以所指定的表为主要表，将其它表作为从表提取到驱动表所在的库进行关联运算。例如：

SELECT *

FROM INFO.T_CI_CUSTINFO_A@YZDBBA A

WHERE CUSTID IN (SELECT ATTACHCUSTID

FROM INFO.T_CI_USERINFO_E

WHERE SVCNUM = '139XXXXXXXX');

执行时间：0.031秒。

另外，DBLINK中应尽量避免调用远程的存储过程。

二十七、绑定变量使用建议

1、 Pro*C或OCI编程使用动态SQL时，必须采用绑定变量方式，已避免引起数据库性能问题。

示例：

A、非绑定变量方式的动态SQL

//处理传入的参数，构造动态SQL语句

sprintf(sztmp1,

"SELECT \

TO_CHAR(apply_date, 'yyyymmddhh24miss'), \

state, \

FROM user_smscall \

WHERE \

(gsm_user_id = '%s' ) ",//非绑定变量方式的动态SQL语句

ora_gsm_user_id);

B、绑定变量方式的动态SQL

//处理传入的参数，构造动态SQL语句

sprintf(sztmp1,

"SELECT \

TO_CHAR(apply_date, 'yyyymmddhh24miss'), \

state, \

FROM user_smscall \

WHERE \

(gsm_user_id = :v1 ) ");//绑定变量方式的动态SQL语句

EXEC SQL PREPARE sql FROM :sqlstmt; //Prepare

//定义游标

EXEC SQL declare user_smscall_cu_1 cursor for sql;

EXEC SQL OPEN user_smscall_cu_1 USING :ora_gsm_user_id;//传变量值

 

2、在cursor_sharing参数均设置为similar时，绑定变量的使用时应注意以下几点：

(1) 使用绑定变量，以动态SQL替代静态SQL；

(2) 该对象不能进行频繁的DDL操作；

(3) 相同SQL的绑定变量值的类型，类型的长度定义需要完全一致；

(4) 语句中对在收集了统计信息的列进行等于操作，不会使用绑定变量；

(5) 通过DBLINK操作远程的对象时：

SELECT语句必须使用绑定变量，能够避免较高的version_count；

INSERT语句降低高version_count的2种解决办法：

1)SQL语句中使用绑定变量，对于INSERT远程对象加上NOAPPEND的hint。

2)将应用程序中的INSERT INTO … TABLE@REMOTE ...SELECT....语句拆分为2条。

例如：

A、首先将SELECT的相关字段的值放入绑定变量。如：

SELECT ,...,

INTO :v_b1, ,:v_b2 ...... ,:v_bn

FROM

B、然后直接使用绑定变量的值对表进行插入操作。如：

INSERT INTO @（DBLINK）

(,,......)

VALUES (:v_b1,v_b2......,v_bn)