数据库优化方案

最新推荐文章于 2023-08-29 00:08:26 发布

清儿风儿

最新推荐文章于 2023-08-29 00:08:26 发布

阅读量1.6k

点赞数

分类专栏： SQL

SQL 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

SQL语句优化方案

1. 高效地进行SQL语句设计：

通常情况下，可以采用下面的方法优化SQL对数据操作的表现：
（1）减少对数据库的查询次数，即减少对系统资源的请求，使用快照和显形图等分布式数据库对象可以减少对数据库的查询次数。
（2）尽量使用相同的或非常类似的SQL语句进行查询，这样不仅充分利用SQL共享池中的已经分析的语法树，要查询的数据在SGA中命中的可能性也会大大增加。
（3）避免不带任何条件的SQL语句的执行。没有任何条件的SQL语句在执行时，通常要进行FTS，数据库先定位一个数据块，然后按顺序依次查找其它数据，对于大型表这将是一个漫长的过程。
（4）如果对有些表中的数据有约束，最好在建表的SQL语句用描述完整性来实现，而不是用SQL程序中实现。

一、操作符优化：

1、IN操作符

用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的，从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：

ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。在业务密集的SQL当中尽量不采用IN操作符。

优化sql时，经常碰到使用in的语句，一定要用exists把它给换掉，因为Oracle在处理In时是按Or的方式做的，即使使用了索引也会很慢。

2、 NOT IN操作符

强列推荐不使用的，因为它不能应用表的索引。用NOT EXISTS或（外连接+判断为空）方案代替

3、IS NULL或IS NOT NULL操作

判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。

用其它相同功能的操作运算代替，a is not null改为 a>0或a>’’等。

不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。

避免在索引列上使用IS NULL和IS NOT NULL避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录.对于复合索引，如果每个列都为空，索引中同样不存在此记录.如果至少有一个列不为空，则记录存在于索引中．举例:如果唯一性索引建立在表的A列和B列上,并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入).然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空.因此你可以插入1000条具有相同键值的记录,当然它们都是空!因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.

低效: (索引失效)

SELECT …FROM DEPARTMENTWHERE DEPT_CODE ISNOTNULL;

高效: (索引有效)

SELECT …FROM DEPARTMENTWHERE DEPT_CODE >=0;

4、>及 <操作符（大于或小于操作符）

大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段 A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。
用>=替代>
高效:

SELECT …FROM DEPARTMENTWHERE DEPT_CODE >=0;

低效:

SELECT*FROM EMPWHERE DEPTNO>3

两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPT NO=3的记录并且向前扫描到第一个DEPT大于3的记录.

5、LIKE操作符：

LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’这种查询不会引用索引，而LIKE‘X5400%’则会引用范围索引。一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号YY_BH LIKE‘%5400%’ 这个条件会产生全表扫描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’
则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。

6、用EXISTS替换DISTINCT：
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT.一般可以考虑用EXIST替换, EXISTS使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.
例子：
(低效):

SELECTDISTINCT DEPT_NO,DEPT_NAMEFROM DEPT D , EMP EWHERE D.DEPT_NO= E.DEPT_NO

(高效):

SELECT DEPT_NO,DEPT_NAMEFROM DEPT DWHEREEXISTS
　　(SELECT'X'FROM EMP EWHERE E.DEPT_NO = D.DEPT_NO);

如：
用EXISTS 替代IN、用NOT EXISTS替代NOT IN：
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOT EXISTS)通常将提高查询的效率.在子查询中,NOT IN子句将执行一个内部的排序和合并.无论在哪种情况下,NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历).为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
例子：
(高效):

SELECT*FROM EMP (基础表)WHERE EMPNO >0ANDEXISTS
　　(SELECT'X'FROM DEPTWHERE DEPT.DEPTNO= EMP.DEPTNOAND LOC='MELB')

(低效):

SELECT*FROM EMP (基础表)WHERE EMPNO >0AND DEPTNOIN
　　(SELECT DEP TNOFROM DEPTWHERE LOC ='MELB')

7、用UNION替换OR (适用于索引列)

通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果.对索引列使用OR将造成全表扫描.注意,以上规则只针对多个索引列有效.如果有column没有被索引,查询效率可能会因为你没有选择OR而降低.在下面的例子中, LOC_ID和REGION上都建有索引.
(高效):

SELECT LOC_ID,LOC_DESC,REGIONFROM LOCATIONWHERE LOC_ID =10
　　UNIONSELECT LOC_ID , LOC_DESC , REGIONFROM LOCATIONWHERE REGION ='MELBOURNE'

(低效):

SELECT LOC_ID,LOC_DESC,REGIONFROM LOCATIONWHERE LOC_ID= 10OR REGION= 'MELBOURNE'

如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

8、用IN来替换OR

这是一条简单易记的规则，但是实际的执行效果还须检验，在ORACLE8i下，两者的执行路径似乎是相同的．
低效:

SELECT….FROM LOCATIONWHERE LOC_ID =10OR LOC_ID=20OR LOC_ID=30

高效:

SELECT…FROM LOCATIONWHERE LOC_IN IN (10,20,30);

二、SQL语句结构优化

1、SELECT子句中避免使用‘ * ‘：

2、用TRUNCATE替代DELETE：

用TRUNCATE替代DELETE删除全表记录：（大数据量的表用次方法）
当删除表中的记录时,在通常情况下,回滚段(rollback segments )用来存放可以被恢复的信息.如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息.

3、用Where子句替换HAVING子句：

避免使用HAVING 子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的， where也应该比having快点的

4、sql语句用大写

因为oracle 总是先解析sql语句，把小写的字母转换成大写的再执行。

5、在java代码中尽量少用连接符“＋”连接字符串！

6、避免改变索引列的类型.:

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

SELECT … FROM EMP WHERE EMPNO = ‘123'实际上,经过ORACLE类型转换,语句转化为:

SELECT …FROM EMPWHERE EMPNO = TO_NUMBER(‘123')

幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.现在,假设EMP_TYPE是一个字符类型的索引列.

SELECT …FROM EMPWHERE EMP_TYPE =123

这个语句被ORACLE转换为:

SELECT …FROM EMP WHERETO_NUMBER(EMP_TYPE)=123

因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE对你的SQL进行隐式的类型转换,最好把类型转换用显式表现出来.注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型

7、优化GROUP BY:

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY之前过滤掉.下面两个
查询返回相同结果但第二个明显就快了许多.
低效:

1SELECT JOB,AVG(SAL)FROM EMP GROUPby JOBHAVING JOB='PRESIDENT' OR JOB ='MANAGER'

高效:

1SELECT JOB,AVG(SAL)FROM EMP WHERE JOB ='PRESIDENT'OR JOB='MANAGER'GROUPby JOB

数据库优化方案

1. 利用表分区

分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。

2. 别名的使用

别名是大型数据库的应用技巧，就是表名、列名在查询中以一个字母为别名，查询速度要比建连接表快1.5倍。

3. 索引Index的优化设计

索引可以大大加快数据库的查询速度，索引把表中的逻辑值映射到安全的RowID，因此索引能进行快速定位数据的物理地址。对一个建有索引的大型表的查询时，索引数据可能会用完所有的数据块缓存空间，ORACLE不得不频繁地进行磁盘读写来获取数据，因此在对一个大型表进行分区之后，可以根据相应的分区建立分区索引。但是个人觉得不是所有的表都需要建立索引，只针对大数据量的表建立索引。

缺点：第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

索引需要维护：为了维护系统性能，索引在创建之后，由于频繁地对数据进行增加、删除、修改等操作使得索引页发生碎块，因此，必须对索引进行维护。

4. 调整硬盘I/O

　　这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间I/O负载均衡。在磁盘比较富裕的情况下还应该遵循以下原则：

　　将表和索引分开；

　　创造用户表空间，与系统表空间（system）分开磁盘；

　　创建表和索引时指定不同的表空间；

　　创建回滚段专用的表空间，防止空间竞争影响事务的完成；

　　创建临时表空间用于排序操作，尽可能的防止数据库碎片存在于多个表空间中。

我们在使用物化视图的过程中基本可以“把它当作一个实际的数据表来看待”，不用再担心视图本身的基础表的效率、优化等

物化视图

1.对于复杂而高消耗的查询，如果使用频繁，应建成物化视图

2.物化视图是一种典型的以空间换时间的性能优化方式

3.对于更新频繁的表慎用物化视图

4.选择合适的刷新方式

一般的视图是虚拟的，而物化视图是实实在在的数据区域，是要占据存储空间的。

当然，物化视图在创建和管理上和一般的视图有不同的地方。相比来讲，物化视图占用了一定的存储空间，另外系统刷新物化视图也需要耗费一定的资源，但是它却换来了效率和灵活性。

减少IO与网络传输次数

1.尽量用较少的数据库请求，获取到需要的数据，能一次性取出的不分多次取出

2.对于频繁操作数据库的批量操作，应采用存储过程，减少不必要的网络传输

死锁与阻塞

1.对于需要频繁更新的数据，尽量避免放在长事务中，以免导致连锁反应

2.不是迫不得已，最好不要在ORACLE锁机制外再加自己设计的锁

3.减少事务大小，及时提交事务

4.尽量避免跨数据库的分布式事务，因为环境的复杂性，很容易导致阻塞

5.慎用位图索引，更新时容易导致死锁

自动增加表分区：

该程序可以做为一个Oracle的JOB执行在每月的28日前执行(考虑2月28天的原因),自动为该用户下的分区表增加分区.

create or replace procedure guan_add_partition
/*
/*为一个用户下所有分区表自动增加分区.分区的列为date类型,分区名类似:p200706.
/*create by David
*/
as
v_table_name varchar2(50);
v_partition_name varchar2(50);
v_month char(6);
v_add_month_1 char(6);
v_sql_string varchar2(2000);
v_add_month varchar2(20);
cursor cur_part is select distinct u.table_name,max(p.partition_name) max_part_name from user_tables u,user_tab_partitions p
where u.table_name=p.table_name and u.partitioned = 'YES'
group by u.table_name;
Begin
select to_char(sysdate,'yyyymm') into v_month from dual;
select to_char(add_months(sysdate,1),'yyyymm') into v_add_month_1 from dual;
select to_char(add_months(trunc(sysdate,'mm'),2),'yyyy-mm-dd') into v_add_month from dual;
open cur_part;
loop
fetch cur_part into v_table_name,v_partition_name;
exit when cur_part%notfound;
if to_number(substr(v_partition_name,2)) <=to_number(substr(v_month,1)) then

v_sql_string :='alter table '||v_table_name||' add partition p'||v_add_month_1||
' VALUES LESS THAN ( to_date('''||v_add_month||''',''yyyy-mm-dd'') ) tablespace users';
execute immediate v_sql_string;
else
null;
end if;
end loop;
close cur_part;
end;

上文转自：

http://blog.csdn.net/axin66ok/article/details/7891386

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

Oracle数据库设计提升性能的五条法则

众所周知，数据库设计的好坏直接关系到数据库运行的效率。根据笔者的经验，对于提升数据库性能来说，合理的数据库设计，比升级服务器的硬件配置，还要来的有效。但是，笔者无论是在跟同事合作，又或者是在论坛上跟相关同行交流的时候，总是会发现有些人有一些不好的数据库设计习惯，影响了数据库的性能，增加了数据库管理员的工作量。

　　笔者认为，为了提升数据库的性能，在Oracle数据库设计的时候，要做到五戒。

　　一戒：在小型表上不要建立索引。

　　毋庸置疑，索引可以提高数据库查询的效率。但是，俗话说，过之则不及。索引也必须用在合时的地方。如果索引设置不当，不但不会提升数据库的性能，反而会起到相反的作用。如在小型数据库上设置索引，而且这些表用户更改的比较频繁。如员工基本信息表，就是简单的不超过十个字段。这个表用户需要经常的进行插入与删除操作。当进行这些变更作业的时候，需要对索引进行维护。而这个维护的工作量可能比扫描表空间消耗更多的存储空间。从而不但起步到改善数据库性能的作用，反而是在拖后腿。

　　所以，在数据库设计的时候，要做到的第一个戒条就是，不要再用户经常更改的小型表上建立索引。否则的话，是得不偿失的。

　　二戒：不要用用户的键。

　　如我们在设计一个ERP系统数据库的时候，有一张销售订单表。在这张表中，有一个销售订单号。那么我们能否利用这个单号作为关联其他表的外键呢?如在销售出货单上，需要关联到销售订单。这个时候，我们能否把销售订单单号作为跟出货单关联的关键字呢?

　　答案是可以的，但是不是最优选择。我们可以看一下ERP的后台数据库。在销售订单表上，除了销售订单号这个唯一表示销售订单纪录的字段外，还有一个字段就是销售订单ID。在前台的出货单界面上虽然显示的是销售订单号码，但是，在后台却存储着的是销售订单ID。也就是说，数据库不是以用户的键作为主键，而是采用了数据库自动维护的单据ID这个字段。

　　为什么要这么设计呢?这就是笔者今天要谈的第二个戒条，不要用用户的键。通常情况下，不要选择用户可编辑的字段作为外键或者主键。因为这会增加我们额外的工作量。

　　如果我们把销售订单号作为外键的话，则在创建销售订单纪录后还要对用户编辑字段的行为施加限制，如判断是否违反外键的强制性规则等等。有些系统把销售订单号设置为外键的话，则往往是把这个字段设置为系统自动编号，并且用户不可更改。可是，在实际工作中，企业员工往往需要编辑这个字段。员工需要编辑这些不可编辑的字段时系统缺乏灵活性的缺陷就体现出来了。而且，当用户输入完数据保存的时候再提示纪录不符合要求，则也不是很人性化的设计。

　　另外，我们还必须为此设计一些检测和纠正键冲突的方法。如考虑这个外键的直是否在其他数据表中存在等等。虽然这通常只需要我们花点时间就可以搞定。但是从数据库性能上来说，这个代价就比较大了。再则，如此的话，就不能够很好的把系统的基本数据跟企业员工的数据实现很好的隔离。

　　所以，笔者认为，不要用用户的键来作为我们数据库设计的主键或则外键。或者说，数据库设计时用到的键要让数据库系统进行自动维护，用户不得更改这个维护规则。

　　三戒：不要用商务规则来实现数据的完整性。

　　数据的完整性有好几种实现方法。如可以通过数据库约束实现数据完整性;也可以通过前台系统的商务规则来实现数据的完整性。不过，笔者这里要建议的是，在一些大型的数据库中，不要试图通过商务规则来实现数据的完整性，而尽可能的通过数据库的约束来实现。因为若通过商务规则来实现完整性，往往会出现一些莫名其妙的错误。

　　如笔者就遇到过这一个案例。在数据库设计的时候，把某个字符型字段长度限制为最长50位。而在前台应用程序中，却限制了60位。在员工数据数据的时候，在前台应用程序中，可以输入55个字符。但是，下次用户查询的时候，却发现后面几个字符没有了，只剩下前面那些内容。这主要是因为在数据保存的时候，超过了数据库的最长位数限制。数据库就会自动把后面几个字符去掉然后保存。如此，用户在前台输入数据的时候，以为可以保存。但是，实际上数据库中存储的数据是不全的。

　　所以，笔者的第三个戒条就是，不要利用商务规则在前台实现数据的完整性。只要有可能，就要尽量在数据库系统层面实现数据的完整性。在数据库层面实现数据完整性的同时，需要注意用户的友好性。

　　一是要把违反约束信息尽可能详细的回馈给用户。如销售订单号，在数据库系统中设置了唯一性约束。则当用户在建立销售订单的时候，销售订单号重复时，就需要把这个约束的详细信息反馈给前台用户。若只显示不能够保存这个错误信息往往是不够的。这会让用户摸不着头脑。

　　二是在必要的时候，可以利用触发器来实现数据的完整性。虽然在功能设计上，我们不建议在Oracle数据库中采用触发器来实现。如要把某个小写金额转换为大小金额。我们是建议通过前台应用程序实现。因为应用程序的执行效率要比数据库的触发器高。但是，在数据库数据完整性上，在必要的时候，若靠数据库约束无法实现完整性，我们仍然建议采用触发器，而不是前台应用程序来实现。在任何情况下，不要依赖于应用程序来实现数据完整性。否则的话，很难保证数据库约束与商务层约束的一致性。

　　四戒：在可能的情况下，不要让前台应用程序直接访问数据表。

　　其实，在数据库基础表与前台应用程序之间，还有一个很好的“中间人”，那就是视图。试图是数据库基础表的一个抽象。他除了可以保障数据的质量外，还可以有效的访问前台应用程序对于数据的非法访问。所以，一些安全性级别比较高的数据库系统，在数据库设计的时候，往往会在数据库和前台应用程序代码之间提供“视图”这个中间人，让其作为基础表的“新闻发言人”。如此，前台应用程序在没有必要的情况下，可以不直接访问数据表，而是通过视图来对表实现间接的访问。

　　如在进行数据导入程序设计的时候，通过视图来实现则是一个很理想的方法。如用户现在要导入产品信息，包括产品基本信息、供应商信息、价格信息等等。这些信息在数据库中往往包含在三种表中，分别为产品基本信息表、供应商信息表、产品价格信息表。若前台应用程序直接访问基础表的话，就需要分别访问这三张基础表。这不但效率比较低，而且也不容易实现数据的完整性。此时，我们就可以利用视图，把一些必须要填入的字段组合在一张可更新视图中。如此，前台应用程序只需要访问一个数据库对象即可。

　　再者，在数据库报表设计上，更是要通过视图来实现。而不要通过传递一段SQL代码来查询基础表。这对于数据的安全性会带来比较大的威胁。

　　所以，第四个戒条就是，在可能的情况下，不要让前台应用程序直接访问数据表。

　　五戒：不要一直让用户输入数据。

　　有些字段其实不一定要让用户输入，只需要用户选择即可。如在人事管理系统中，企业员工所属的民族、职位、省份等等就没有必要让用户手工输入。而可以通过列表的形式让员工来进行选择。这种手段，是提高数据完整性的最佳方式之一。若能够给用户提供一个详细的列表供其选择，这样将减少键入代码的错误，同时提高数据库的一致性;也可以提高用户界面的友好性。

　　所以，在数据库设计的时候，尽量使用列表字段来供用户选择。若连省份等等字段都要用户手工输入的话，则我们在数据库完整性所花费的时间，就要很多。如在数据保存的时候，都要去判断用户输入的数据是否合乎完整性的规则。这显然工作量要比设计一个列表字段多得多。

　　故数据库设计的第五个戒条就是不要老是让用户手工输入数据。提供列表让用户进行选择，用户不仅可以提高效率，我们也可以省事，同时，也能够保障数据的准确率。

上文转自：

http://blog.csdn.net/axin66ok/article/details/7834250

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

高效删除Oracle数据库中重复数据的方法

在对数据库进行操作过程中我们可能会遇到这种情况，表中的数据可能重复出现，使我们对数据库的操作过程中带来很多的不便，那么怎么删除这些重复没有用的数据呢?

　　重复数据删除技术可以提供更大的备份容量，实现更长时间的数据保留，还能实现备份数据的持续验证，提高数据恢复服务水平，方便实现数据容灾等。重复的数据可能有这样两种情况，第一种时表中只有某些字段一样，第二种是两行记录完全一样。Oracle数据库重复数据删除技术有如下优势：更大的备份容量、数据能得到持续验证、有更高的数据恢复服务水平、方便实现备份数据的容灾。

一、删除部分字段重复数据

　　先来谈谈如何查询重复的数据吧。

　　下面语句可以查询出那些数据是重复的：

　　select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1

　　将上面的>号改为=号就可以查询出没有重复的数据了。

　　想要删除这些重复的数据，可以使用下面语句进行删除

　　delete from 表名 a where 字段1,字段2 in

　　(select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1)

　　上面的语句非常简单，就是将查询到的数据删除掉。不过这种删除执行的效率非常低，对于大数据量来说，可能会将数据库吊死。所以我建议先将查询到的重复的数据插入到一个临时表中，然后对进行删除，这样，执行删除的时候就不用再进行一次查询了。如下：

　　CREATE TABLE 临时表 AS

　　(select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1)

　　上面这句话就是建立了临时表，并将查询到的数据插入其中。

　　下面就可以进行这样的删除操作了：

　　delete from 表名 a where 字段1,字段2 in (select 字段1，字段2 from 临时表);

　　这种先建临时表再进行删除的操作要比直接用一条语句进行删除要高效得多。

　　这个时候，大家可能会跳出来说，什么?你叫我们执行这种语句，那不是把所有重复的全都删除吗?而我们想保留重复数据中最新的一条记录啊!大家不要急，下面我就讲一下如何进行这种操作。

　　在oracle中，有个隐藏了自动rowid，里面给每条记录一个唯一的rowid，我们如果想保留最新的一条记录，

　　我们就可以利用这个字段，保留重复数据中rowid最大的一条记录就可以了。

　　下面是查询重复数据的一个例子：

　　select a.rowid,a.* from 表名 a

　　where a.rowid !=

　　(

　　select max(b.rowid) from 表名 b

　　where a.字段1 = b.字段1 and

　　a.字段2 = b.字段2

　　)

　　下面我就来讲解一下，上面括号中的语句是查询出重复数据中rowid最大的一条记录。

　　而外面就是查询出除了rowid最大之外的其他重复的数据了。

　　由此，我们要删除重复数据，只保留最新的一条数据，就可以这样写了：

　　delete from 表名 a

　　where a.rowid !=

　　(

　　select max(b.rowid) from 表名 b

　　where a.字段1 = b.字段1 and

　　a.字段2 = b.字段2

　　)

　　随便说一下，上面语句的执行效率是很低的，可以考虑建立临时表，讲需要判断重复的字段、rowid插入临时表中，然后删除的时候在进行比较。

　　create table 临时表 as

　　select a.字段1,a.字段2,MAX(a.ROWID) dataid from 正式表 a GROUP BY a.字段1,a.字段2;

　　delete from 表名 a

　　where a.rowid !=

　　(

　　select b.dataid from 临时表 b

　　where a.字段1 = b.字段1 and

　　a.字段2 = b.字段2

　　);

　　commit;

　　二、完全删除重复记录

　　对于表中两行记录完全一样的情况，可以用下面语句获取到去掉重复数据后的记录：

　　select distinct * from 表名

　　可以将查询的记录放到临时表中，然后再将原来的表记录删除，最后将临时表的数据导回原来的表中。如下：

　　CREATE TABLE 临时表 AS (select distinct * from 表名);

　　truncate table 正式表; --注：原先由于笔误写成了drop table 正式表;，现在已经改正过来

　　insert into 正式表 (select * from 临时表);

　　drop table 临时表;

　　如果想删除一个表的重复数据，可以先建一个临时表，将去掉重复数据后的数据导入到临时表，然后在从

　　临时表将数据导入正式表中，如下：

　　INSERT INTO t_table_bak

　　select distinct * from t_table;

　　三、怎样快速删除oracle数据库

　　最快的方法就进入注册表在运行..里输入regedit.

　　依次展开HKEY_LOCAL_MACHINE SOFTWARE

　　找到ORACLE节点。删除。

　　然后删除ORACLE数据文件，安装的时候选的路径。

　　最后删除oracle引导文件，在系统盘符的Program Files 里面删除oracle文件夹。

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

下文转自：http://blog.csdn.net/u011225629/article/details/50492403

一、问题的提出

在应用系统开发初期，由于开发数据库数据比较少，对于查询SQL语句，复杂视图的的编写等体会不出SQL语句各种写法的性能优劣，但是如果将应用系统提交实际应用后，随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，可见对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。

在多数情况下，Oracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引，这有助于写出高性能的SQL语句。

二、SQL语句编写注意问题

下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中，即使某些列存在索引，但是由于编写了劣质的SQL，系统在运行该SQL语句时也不能使用该索引，而同样使用全表扫描，这就造成了响应速度的极大降低。
1. 操作符优化

(a) IN 操作符

用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的，从Oracle执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：

ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。

推荐方案：在业务密集的SQL当中尽量不采用IN操作符，用EXISTS 方案代替。

(b) NOT IN操作符

此操作是强列不推荐使用的，因为它不能应用表的索引。

推荐方案：用NOT EXISTS 方案代替

(c) IS NULL 或IS NOT NULL操作（判断字段是否为空）

判断字段是否为空一般是不会应用索引的，因为索引是不索引空值的。不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

推荐方案：用其它相同功能的操作运算代替，如：a is not null 改为 a>0 或a>’’等。不允许字段为空，而用一个缺省值代替空值，如申请中状态字段不允许为空，缺省为申请。

(d) > 及 < 操作符（大于或小于操作符）

大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。

(e) LIKE操作符

LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。

一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。

带通配符(%)的like语句：

同样以上面的例子来看这种情况。目前的需求是这样的，要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句:

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

这里由于通配符(%)在搜寻词首出现，所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用:

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

(f) UNION操作符

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：

select * from gc_dfys 
union 
select * from ls_jg_dfys

这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

select * from gc_dfys 
union all 
select * from ls_jg_dfys

(g) 联接列

对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。我们一起来看一个例子，假定有一个职工表(employee)，对于一个职工的姓和名分成两列存放(FIRST_NAME和LAST_NAME)，现在要查询一个叫比尔.克林顿(Bill Cliton)的职工。

下面是一个采用联接查询的SQL语句：

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

上面这条语句完全可以查询出是否有Bill Cliton这个员工，但是这里需要注意，系统优化器对基于last_name创建的索引没有使用。当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。

where first_name ='Beill' and last_name ='Cliton';

(h) Order by语句

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

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

(i) NOT

我们在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子:

where not (status ='VALID')

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

where status <>'INVALID';

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

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

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

2. SQL书写的影响

(a) 同一功能同一性能不同写法SQL的影响。

如一个SQL在A程序员写的为 Select * from zl_yhjbqk

B程序员写的为 Select * from dlyx.zl_yhjbqk（带表所有者的前缀）

C程序员写的为 Select * from DLYX.ZLYHJBQK（大写表名）

D程序员写的为 Select * from DLYX.ZLYHJBQK（中间多了空格）

以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL 都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同，则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。

(b) WHERE后面的条件顺序影响

WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响。如：

Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1 
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下'

以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy_dj = ’1KV以下’条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。

(c) 查询表顺序的影响

在FROM后面的表中的列表顺序会对SQL执行性能影响，在没有索引及ORACLE没有对表进行统计分析的情况下，ORACLE会按表出现的顺序进行链接，由此可见表的顺序不对时会产生十分耗服物器资源的数据交叉。（注：如果对表进行了统计分析，ORACLE会自动先进小表的链接，再进行大表的链接）

3. SQL语句索引的利用

(a) 对条件字段的一些优化

采用函数处理的字段不能利用索引，如：

substr(hbs_bh,1,4)=’5400’，优化处理：hbs_bh like ‘5400%’
trunc(sk_rq)=trunc(sysdate)， 优化处理：sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)

进行了显式或隐式的运算的字段不能进行索引，如：ss_df+20>50，优化处理：ss_df>30

‘X’ || hbs_bh>’X5400021452’，优化处理：hbs_bh>’5400021542’
sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5

hbs_bh=5401002554，优化处理：hbs_bh=’ 5401002554’，注：此条件对hbs_bh 进行隐式的to_number转换，因为hbs_bh字段是字符型。

条件内包括了多个本表的字段运算时不能进行索引，如：

ys_df>cx_df，无法进行优化 
qc_bh || kh_bh=’5400250000’，优化处理：qc_bh=’5400’ and kh_bh=’250000’

4. 更多方面SQL优化资料分享

（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)：

ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

（2） WHERE子句中的连接顺序：

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

（3） SELECT子句中避免使用 ‘ * ‘：

ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。

（4）减少访问数据库的次数：

ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等。

（5）在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200。

（6）使用DECODE函数来减少处理时间：

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

（7）整合简单,无关联的数据库访问：

如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系) 。

（8）删除重复记录：

最高效的删除重复记录方法 ( 因为使用了ROWID)例子：

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

（9）用TRUNCATE替代DELETE：

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

（10）尽量多使用COMMIT：

只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少，COMMIT所释放的资源:

a. 回滚段上用于恢复数据的信息.

b. 被程序语句获得的锁

c. redo log buffer 中的空间

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

（11）用Where子句替换HAVING子句：

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where也应该比having快点的，因为它过滤数据后才进行sum，在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里。

（12）减少对表的查询：

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

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

（13）通过内部函数提高SQL效率：

复杂的SQL往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的。

（14）使用表的别名(Alias)：

当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

（15）用EXISTS替代IN、用NOT EXISTS替代NOT IN：

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。

例子：

（高效）SELECT * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  EXISTS (SELECT ‘X'  FROM DEPT  WHERE  DEPT.DEPTNO = EMP.DEPTNO  AND  LOC = ‘MELB') 
(低效)SELECT  * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  DEPTNO IN(SELECT DEPTNO  FROM  DEPT  WHERE  LOC = ‘MELB')

（16）识别’低效执行’的SQL语句：

虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法：

SELECT  EXECUTIONS , DISK_READS, BUFFER_GETS, 
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, 
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, 
SQL_TEXT 
FROM  V$SQLAREA 
WHERE  EXECUTIONS>0 
AND  BUFFER_GETS > 0 
AND  (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 
ORDER BY  4 DESC;

（17）用索引提高效率：

索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.。那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的：

ALTER  INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

（18）用EXISTS替换DISTINCT：

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 例子：

(低效): 
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);

（19） sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行。

（20）在java代码中尽量少用连接符“＋”连接字符串！

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

（22）避免在索引列上使用计算

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

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

（23）用>=替代>

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

两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。

（24）用UNION替换OR (适用于索引列)

通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.

高效: 
SELECT LOC_ID , LOC_DESC , REGION 
FROM LOCATION 
WHERE LOC_ID = 10 
UNION 
SELECT LOC_ID , LOC_DESC , REGION 
FROM LOCATION 
WHERE REGION = “MELBOURNE” 
低效: 
SELECT LOC_ID , LOC_DESC , REGION 
FROM LOCATION 
WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

（25）用IN来替换OR

这是一条简单易记的规则，但是实际的执行效果还须检验，在ORACLE8i下，两者的执行路径似乎是相同的．

低效: 
SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 
高效 
SELECT… FROM LOCATION WHERE LOC_IN  IN (10,20,30);

（26）避免在索引列上使用IS NULL和IS NOT NULL

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录. 如果至少有一个列不为空，则记录存在于索引中．举例: 如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000 条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.

低效: (索引失效) 
SELECT … FROM  DEPARTMENT  WHERE  DEPT_CODE IS NOT NULL; 
高效: (索引有效) 
SELECT … FROM  DEPARTMENT  WHERE  DEPT_CODE >=0;

（27）总是使用索引的第一个列：

如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则，当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引。

（28）用UNION-ALL 替换UNION ( 如果有可能的话)：

当SQL 语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使用UNION ALL的可行性. UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的. 下面的SQL可以用来查询排序的消耗量

低效： 
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'

（29）用WHERE替代ORDER BY：

ORDER BY 子句只在两种严格的条件下使用索引.

ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.

ORDER BY中所有的列必须定义为非空.

WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列.

例如:

表DEPT包含以下列:

DEPT_CODE PK NOT NULL 
DEPT_DESC NOT NULL 
DEPT_TYPE NULL<span style="font-size:18px;"></span><pre class="html" name="code">低效: (索引不被使用) SELECT DEPT_CODE FROM  DEPT  ORDER BY  DEPT_TYPE 高效: (使用索引) SELECT DEPT_CODE  FROM  DEPT  WHERE  DEPT_TYPE > 0

<span style="font-size:18px;">（30） 避免改变索引列的类型:

当比较不同数据类型的数据时, 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
因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型。

分析
</span><pre class="html" name="code">select   emp_name   form   employee   where   salary   >   3000

在此语句中若salary是Float类型的，则优化器对其进行优化为Convert(float,3000)，因为3000是个整数，我们应在编程时使用3000.0而不要等运行时让DBMS进行转化。同样字符和整型数据的转换。

（31）需要当心的WHERE子句:

某些SELECT 语句中的WHERE子句不使用索引. 这里有一些例子.

在下面的例子里, (1)‘!=’ 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. (2) ‘ ¦ ¦’是字符连接函数. 就象其他函数那样, 停用了索引. (3) ‘+’是数学函数. 就象其他数学函数那样, 停用了索引. (4)相同的索引列不能互相比较,这将会启用全表扫描.

（32） a. 如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高. b. 在特定情况下, 使用索引也许会比全表扫描慢, 但这是同一个数量级上的区别. 而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!

（33）避免使用耗费资源的操作：

带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强。

（34）优化GROUP BY：

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.

低效: 
SELECT JOB , AVG(SAL) 
FROM EMP 
GROUP by JOB 
HAVING JOB = ‘PRESIDENT' 
OR JOB = ‘MANAGER' 
高效: 
SELECT JOB , AVG(SAL) 
FROM EMP 
WHERE JOB = ‘PRESIDENT' 
OR JOB = ‘MANAGER' 
GROUP by JOB