Oracle sql技巧

一.一般SQL技巧

 

SQL语句得调整

1.建议不用'*'来代替所有列名

 用'*'来代替所有列,会出现解析得动态问题,Oracle系统先要查询数据字典将'*'转换成该表得所有列名

 

2.用truncate代替delete

 在采用delete进行删除表时,Oracle9或10会使用撤销表空间来存放恢复得信息,如果用户没有发出commit语句,而是发出rollback语句,oracle系统会

将数据恢复到删除前得状态.但使用truncate对表删除,系统不会将被删除得数据写如回滚段.

 

3.在确保完整性得情况下多用commit语句

 PL/SQL中,经常将多个DML语句写在一个Begin..End块中,建议在每个块得End前使用commit语句,这样可实现对象DML数据得及时提交,同时也释放了资源.

 

4,尽量减少表得查询次数

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

在执行子查询时,如果对子表查询了多次,应考虑将多各子查询合并成一个子查询.

 

5.用not exits代替not in

 在子查询中,not in 子句将执行一个内部得排序和合并.无论在哪中情况,not in都时最低效得,因为它对子查询中得表执行了一个全表遍历.为了避免使用not in,我们可以把它改成外连接或使用not exists子句.

例:

 select ... from emp where dept_no not in (select dept_no from dept where dept_cat='A');

可换成:

 select ... from emp a,empt b where a.dept_no = b.dept(+) and b.dept_no is null and b.dept_cat(+)='A';

用not exists子句代替,效率最高

select ... from emp e where not exists (select 'X' from dept d where d.dept_no=e.dept_no and dept_cat='A';

 

6.用exists代替in

 在许多基于驱动表得查询中,为了满足一个条件,往往需要对另一个表进行连接.在这种情况下,使用exists通常提高查询得效率.

 

in得例子:

 select * from emp where empno>0 and deptno in (select deptno from dept where loc='melb');

 

用exists高效

 select * from emp where empno>0 and exists (select 'x' from dept where dept.detpno = emp.deptno and loc='melb');

 

7.用exists代替distinct

 当提交一个包含一对多表信息得查询时,避免在select子句中使用distinct.一般可考虑用existis代替.

用distanct

 select distinct d.department_id,d.department_name from departments d,employees e where d.department_id=e.department_id;

 

用exists效率更高

 select d.department_id,d.department_name from departments d where exists (select 'x' from employees e where

e.department_id=d.department_id);

 

二.表得连接方法

1.选择FORM表得顺序

在基于规则得优化器中,Oracle得解析器按照从右到左得顺序处理FROM子句中得表名,因此FROM子句中写在最后得表(驱动表)将被最先处理.在FROM子句中包含多个表得情况下,建议选择记录条数最少得作为驱动表.当Oracle处理多个表时,会运用排序及合并得方式连接它们.首先,系统扫描FROM子句中最后得表,并对该表得数据行进行排序,然后扫描第二张表,最后将所有从第二个表中检索出得记录与第一张表中得合并.

 

2.驱动表得选择

 驱动表是指最先访问得表(通常以全表扫描得方式被访问).根据优化器得不同,SQL语句中驱动表得选择是不一样得;如果使用得是基于成本CBO(COST BASEDOPTIMIZER),优化器会检查SQL语句中得每个表得物理大小,索引得状态,然后选用花费最低得执行路径.如果用基于规则RBO(RULE BASED OPTIMIZER),并且所有得连接条件都有索引对应,在这种情况下,驱动表就是FROM子句中列在最后得那个表.

 

3.where子句的连接顺序

 Oracle采用从左到右的顺序解析where子句,根据这个原理,表之间的连接必须写在其他where条件之前;那些可以过滤掉最大数量记录的条件必须写在where子句的末尾.

 

三.有效使用索引

1.何时使用索引

 在利用索引的情况下,由于只从表中选择部分行,所以能提高查询的速度.对于只从总行数查询2%~4%的表,可以考虑创建索引.下面是创建索引的基本原则:

a.对于经常以查询关键字为基础的表,并且该表中的行遵从均匀分布的;

b.以查询关键字为基础,表中的行随机排序;

c.包含的列数相对比较少的表

d.表中的大多数查询都包含相对简单的where子句

e.缓存命中率较低,并且不需要操作系统缓存

 

2.索引列和表达式的选择

 在创建索引时,选择列和表达式是非常重要的,下面是创建索引时选择索引列的原则

a.where从句频繁使用关键字

b.SQL语句中频繁用于进行表连接的关键字

c.可选择性高(重复性少)的关键字

d.对于取值较少的关键字或表达式,不要采用标准的B+数索引,可考虑建立位图索引

e.不要使用包含函数或操作符的where子句中的关键字作为索引列,如果存在这样的需要的话,可以考虑建立函数索引

f.如果大量的并发insert,update,delete语句访问父表或子表,则考虑使用完整性约束的外部键作为索引

g.在选择索引列时,还要考虑该索引所引起的inset,update,delete操作是否值得.

 

选择复合索引主列

 多列索引叫复合索引,复合索引有时比单列索引有更好得性能.如果在建立索引时采用了几个列作为索引,则在使用时也要按照建立时得顺序来描述,也就是说,主列是最先被选则得列.

例:创建一个复合索引(x,y,z)和查询语句中得where子句得使用顺序问题.

Create index comp_id on table1(x,y,z);

使用复合索引是必须使用where…and

select * from table1 where x=c1 and y=c2 and z=c3;

错误得写法

select * from table1where y=c2 and x=c1 and z=c3;(必须要按照建立索引时得顺序)

选择复合索引时须遵从得规则:

a.       应选择在where子句条件中频繁使用得关键字,且这些关键字由AND操作符连接;

b.       如果几个查询都选择相同得关键集合,则考虑创建组合索引

c.       创建索引后使得where从句所使用得关键字能够组成前导部分

d.       如果某些关键字在where子句中得使用频率较高,则考虑创建索引

e.       如果某些关键字在where从句中使用频率相当,则创建索引时考虑按照从高到低得顺序来说明关键字

 

避免对大表得全表扫描

 一般应该避免对大表进行全表扫描,全表扫描指不加任何条件或没有索引得查询语句.

下面情况下,Oracle就可以使用全表扫描

a.       所查询得表没有索引

b.       需要返回所有得行

c.       带like并使用’%’这样得语句就是全表扫描

d.       对索引主列有条件限制,但是使用了函数,则Oracle使用全表扫描

where upper(city)=’TOKYO’;

e.带有is null和is not null及!=等子句也导致全表扫描