ORACLE-SQL性能优化(3）

2. 给优化器更明确的命令

自动选择索引

如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性．

在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引．

举例:

SELECT ENAME FROM EMP

WHERE EMPNO = 2326 AND DEPTNO  = 20 ;

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

TABLE ACCESS BY ROWID ON EMP

INDEX UNIQUE SCAN ON EMP_NO_IDX

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

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

SQL> create table multiindexusage ( inda number , indb number , descr varchar2(10));

Table created.

SQL> create index multindex on multiindexusage(inda,indb); Index created.

SQL> set autotrace traceonly

SQL> select * from  multiindexusage where inda = 1; Execution Plan

----------------------------------------------------------

1. SELECT STATEMENT Optimizer=CHOOSE
2. 0  TABLE ACCESS (BY INDEX ROWID) OF 'MULTIINDEXUSAGE'
3. 1   INDEX (RANGE SCAN) OF 'MULTINDEX' (NON-UNIQUE) SQL> select * from  multiindexusage where indb = 1;

Execution Plan

----------------------------------------------------------

1. SELECT STATEMENT Optimizer=CHOOSE
2. 0   TABLE ACCESS (FULL) OF 'MULTIINDEXUSAGE'

很明显 当仅引用索引的第二个列时 优化器使用了全表扫描而忽略了索引

避免在索引列上使用函数

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

举例:

低效：

SELECT . FROM DEPT

WHERE SAL * 12 > 25000;

高效: SELECT .

FROM DEPT

WHERE SAL > 25000/12;

避免使用前置通配符

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

SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES

WHERE USER_NO LIKE '%109204421';

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

避免在索引列上使用NOT

通常，我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他

就

会停止使用索引转而执行全表扫描.举例:

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

FROM DEPT

WHERE DEPT_CODE NOT = 0;

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

FROM DEPT

WHERE DEPT_CODE > 0;

避免在索引列上使用 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的语句优化器是不允许使用索引的。

避免出现索引列自动转换

当比较不同数据类型的数据时, 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

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

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

 如用 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)

3.减少访问次数

减少访问数据库的次数

当执行每条SQL语句时, ORACLE在内部执行了许多工作:

解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等.

由此可见, 减少访问数据库的次数 , 就能实际上减少

ORACLE的工作量.

类比，工程实施

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

减少对表的查询

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

4. 细节上的影响

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 ;

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

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

Order by语句

 ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或

者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

 仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是

重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。

联接列

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

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';

带通配符（%）的like语句

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

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

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

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

用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

用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进行的是缩小范围的查询。

用索引提高效率

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

的唯一性验证。

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

是有必要的。

避免在索引列上使用计算

WHERE子句中，如果索引列是函数的一部分．优化器将不

使用索引而使用全表扫描．

低效：

SELECT . FROM DEPT WHERE SAL * 12 > 25000;

高效:

SELECT . FROM DEPT WHERE SAL > 25000/12;

用>= 替代 >

如果DEPTNO上有一个索引。

高效:

SELECT * FROM EMP

WHERE DEPTNO >=4

低效:

SELECT * FROM EMP

WHERE DEPTNO >3

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

 例子：

 select * from employee where salary <> 3000;

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

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

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

如果有其它办法，不要使用子查询。

外部联接"+"的用法

  外部联接"+"按其在"="的左边或右边分左联接和右联接。若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行，

则前者的行与后者中的一个空行相匹配并被返回。利用外部联接"+"，可以替代效率十分低下的 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';

这样运行速度明显提高.

尽量多使用COMMIT

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

COMMIT所释放的资源:

 回滚段上用于恢复数据的信息. 被程序语句获得的锁

 redo log buffer 中的空间

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

用TRUNCATE替代DELETE

当删除表中的记录时,在通常情况下, 回滚段

(rollback

segments ) 用来存放可以被恢复的信息. 如果你没有

COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准

确地说是恢复到执行删除命令之前的状况)

而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的

信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,

执行时间也会很短.

计算记录条数

和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可

以通过索引检索,对索引列的计数仍旧是最快的.

例如

COUNT(EMPNO)

字符型字段的引号

比如有的表PHONE_NO字段是CHAR型,而且创建有索引，

但在WHERE条件中忘记了加引号，就不会用到索引。

WHERE PHONE_NO=‘13920202022’ WHERE PHONE_NO=13920202022

优化EXPORT和IMPORT

使用较大的BUFFER(比如10MB , 10,240,000)可以提高

EXPORT和IMPORT的速度; ORACLE将尽可能地获取你所指定的内

存大小,即使在内存

不满足,也不会报错.这个值至少要和表中最大的列相当,否则

列值会被截断;