oracle和sql语句,oracle——SQL语句的编写原则和SQL语句的优化

一：不要让Oracle做得太多

1.避免复杂的多表关联

2.避免使用 ‘ * ‘

(1)当你想在SELECT子句中列出所有的COLUMN时,使用动态 SQL列引用 ‘*’ 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转 换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间

(2)只提取你所要使用的列

(3)使用别名能够加快解析速度

3.避免使用耗费资源的操作

带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的 SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次 排序.

例如,一个UNION查询,其中每个查询都带有GROUP BY子句 , GROUP BY会触发嵌入排序(NESTED SORT) ; 这样, 每个 查询需要执行一次排序, 然后在执行UNION时, 又一个唯一 排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入 排序结束后才能开始执行. 嵌入的排序的深度会大大影响查 询的效率.

通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以 用其他方式重写.

4.用EXISTS替换DISTINCT

低效:

SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E

WHERE D.DEPT_NO = E.DEPT_NO

高效:

SELECT DEPT_NO,DEPT_NAME FROM DEPT D

WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO)

5.用UNION-ALL 替换UNION ( if possible)

低效：

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’

二：给优化器更明确的命令

1.自动选择索引

如果表中有两个以上(包括两个)索引，其中有一个唯一性 索引，而其他是非唯一性． 在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯 一性索引． 举例:

SELECT ENAME FROM EMP WHERE EMPNO = 2326 AND DEPTNO = 20

这里，只有EMPNO上的索引是唯一性的，所以EMPNO索 引将用来检索记录

2.至少要包含组合索引的第一列

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

3.避免在索引列上使用函数

低效：

SELECT … FROM DEPT WHERE SAL * 12 > 25000

高效:

SELECT … FROM DEPT WHERE SAL > 25000/12

4.避免使用前置通配符

WHERE子句中, 如果索引列所对应的值的第一个字符由通 配符(WILDCARD)开始, 索引将不被采用.

SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES

WHERE USER_NO LIKE '%109204421'

在这种情况下，ORACLE将使用全表扫描

5.避免在索引列上使用NOT

通常，我们要避免在索引列上使用NOT, NOT会产生在和在 索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就 会停止使用索引转而执行全表扫描.

低效: (这里,不使用索引)

SELECT … FROM DEPT WHERE DEPT_CODE NOT = 0;

高效: (这里,使用了索引)

SELECT … FROM DEPT WHERE DEPT_CODE > 0;

6.避免在索引列上使用 IS NULL和IS NOT NULL

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

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

因为空值不存在于索引列中,所以WHERE子句中对索引列进行 空值比较将使ORACLE停用该索引.

任何在where子句中使用is null或is not null的语句优化器是 不允许使用索引的。

7.避免出现索引列自动转换

当比较不同数据类型的数据时, ORACLE自动对列进行简单 的类型转换.

假设EMP_TYPE是一个字符类型的索引列.

SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES

WHERE USER_NO = 109204421

这个语句被ORACLE转换为:

SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES

WHERE TO_NUMBER(USER_NO) = 109204421

因为内部发生的类型转换, 这个索引将不会被用到!

8.在查询时尽量少用格式转换

三：减少访问次数

1.减少访问数据库的次数

当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少 ORACLE的工作量

2.使用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%’

3.减少对表的查询

在含有子查询的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)

四：细节上的影响

1.WHERE子句中的连接顺序

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原 理, 当在WHERE子句中有多个表联接时，WHERE子句中排 在最后的表应当是返回行数可能最少的表，有过滤条件的子 句应放在WHERE子句中的最后。

如：设从emp表查到的数据比较少或该表的过滤条件比较确定，能大大缩小查询范围，则将最具有选择性部分放在WHERE子句中的最后：

select * from emp e,dept d where d.deptno >10 and e.deptno =30 ;

如果dept表返回的记录数较多的话，上面的查询语句会比下面的查询语句响应快得多

select * from emp e,dept d where e.deptno =30 and d.deptno >10 ;

2.WHERE子句 ——函数、表达式使用

最好不要在WHERE子句中使用函或表达式，如果要使用的话，最好统一使用相同的表达式或函数，这样便于以后使用合理的索引

3.Order by语句

ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。

4.联接列

对于有联接的列，即使最后的联接值为一个静态值，优化器 是不会使用索引的。

select * from employss 　 where 　 first_name||''||last_name ='Beill Cliton';

系统优化器对基于last_name创建的索引没有使用。 当采用下面这种SQL语句的编写，Oracle系统就可以采用基 于last_name创建的索引。

select * from employee  where first_name ='Beill' and last_name ='Cliton';

5.带通配符(%)的like语句

通配符(%)在搜寻词首出现，Oracle系统不使用 last_name的索引。

select * from employee where last_name like '%cliton%';

在很多情况下可能无法避免这种情况，但是一定要心中有底 ，通配符如此使用会降低查询速度。然而当通配符出现在字 符串其他位置时，优化器就能利用索引。

在下面的查询中索引得到了使用：

select * from employee where last_name like 'c%';

6.用Where子句替换HAVING子句

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果 集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限 制记录的数目,那就能减少这方面的开销.

低效:

SELECT REGION，AVG(LOG_SIZE)

FROM LOCATION GROUP BY REGION HAVING REGION REGION != ‘SYDNEY’ AND REGION != ‘PERTH’

高效：

SELECT REGION，AVG(LOG_SIZE)

FROM LOCATION WHERE REGION REGION != ‘SYDNEY’ AND REGION != ‘PERTH’ GROUP BY REGION 顺序 WHERE > GROUP > HAVING

7.用NOT EXISTS 替代 NOT IN

在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况 下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 使用NOT EXISTS 子句可以有效地利用索引。尽可能使用NOT EXISTS 来代替NOT IN，尽管二者都使用了NOT(不能使用索引而降低速度)， NOT EXISTS要比NOT IN查询效率更高

语句1

SELECT dname, deptno FROM dept

WHERE deptno NOT IN (SELECT deptno FROM emp);

语句2

SELECT dname, deptno FROM dept

WHERE NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);

2要比1的执行性能好很多。 因为1中对emp进行了full table scan,这是很浪费时间的操作。而且1中没有用到emp的index， 因为没有where子句。而2中的语句对emp进行的是缩小范围的查询。

8.用索引提高效率

索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使 用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表 扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以 提高效率. 另一个使用索引的好处是,它提供了主键(primary key) 的唯一性验证。

通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小 表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提 高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要 定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也 会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为 此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理, 那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引 是有必要的。

9.避免在索引列上使用计算

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

低效： SELECT … FROM DEPT WHERE SAL * 12 > 25000;

高效: SELECT … FROM DEPT WHERE SAL > 25000/12;

10.用>= 替代 >

如果DEPTNO上有一个索引。

高效: SELECT * FROM EMP WHERE DEPTNO >=4

低效: SELECT * FROM EMP WHERE DEPTNO >3

11.通过使用>=、<=等，避免使用NOT命令

select * from employee where salary <> 3000;

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

select * from employee where salary<3000 or salary>3000;

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

12.外部联接"+"的用法

外部联接"+"按其在"="的左边或右边分左联接和右联接。若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行，则前者的行与后者中的一个空行相匹配并被返回。利用外部联接"+"，可以替代效率十分低下的 not in 运算，大大提高运行速度。

例如，下面这条命令执行起来很慢：

select a.empno from emp a where a.empno not in (select empno from emp1 where job='SALE');

利用外部联接，改写命令如下:

select a.empno from emp a ,emp1 b where a.empno=b.empno(+) and b.empno is null and b.job='SALE';

这样运行速度明显提高.

13.尽量多使用COMMIT

事务是消耗资源的，大事务还容易引起死锁

COMMIT所释放的资源:

(1)回滚段上用于恢复数据的信息.

(2)被程序语句获得的锁

(3)redo log buffer 中的空间

(4)ORACLE为管理上述3种资源中的内部花费

14.用TRUNCATE替代DELETE

当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有 COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的 信息.当命令运行后,数据不能被恢复.因此很少的资源被调用, 执行时间也会很短

15.计算记录条数

和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可 以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)

16.字符型字段的引号

比如有的表PHONE_NO字段是CHAR型,而且创建有索引， 但在WHERE条件中忘记了加引号，就不会用到索引。

WHERE PHONE_NO=‘13920202022’

WHERE PHONE_NO=13920202022