一、查看表字段名或随机少量数据时,不要使用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)