Oracle数据库SQL基础用法与优化总结

前言................................................................................................................................. 2

基础用法.......................................................................................................................... 2

一、 SELECT语句 ....................................................................................... 2 二、

三、

四、

五、

六、

七、

八、

九、

十、 多表关联.............................................................................................. 2 子查询 ................................................................................................. 3 级联查询(Hierachical Querys) ................................................................ 5 INSERT语句........................................................................................ 6 UPDATE语句 ...................................................................................... 7 DELETE语句....................................................................................... 7 集合操作符 .......................................................................................... 8 DISTINCT............................................................................................ 8 聚集查询的用法 ..................................................... 错误！未定义书签。

十一、 ROLLUP 、CUBE、Grouping SETS用法.............................................. 9

十二、 Flashback闪回技术............................................................................... 9

十三、 伪列（Pseudocolumns）...................................................................... 10

十四、 MERGE ............................................................................................. 10

SQL语句调优 ................................................................................................................ 12

一、

二、

三、

四、

五、

六、

七、

八、

九、 避免使用’＊’ ................................................................................... 12 删除重复记录..................................................................................... 12 计算记录条数..................................................................................... 12 减少对表的查询 ................................................................................. 12 EXISTS 与IN使用 ............................................................................ 13 使用索引............................................................................................ 13 使用where代替having语句 ............................................................... 15 使用表的别名..................................................................................... 15 表记录删除 ........................................................................................ 16

十、 Decode函数使用 ................................................................................ 16

十一、 尽量少用子查询 ................................................................................. 17

十二、 尽量少用视图（View） ...................................................................... 17

十三、 减少不必要的排序.............................................................................. 17

十四、 调优方法( Explain Plan) ...................................................................... 17

PL/SQL的使用............................................................................................................... 18

一、

二、 简介................................................................................................... 18 PL/SQL特点 ...................................................................................... 19

前言

本文档分为三部分，第一部分为SQL语句的一些基础用法，第二部分为SQL语句的调优，第三部分简要介绍一下PL/SQL

第一部分，SQL语句的基础用法部分主要是针对常用的SQL语句语法进行整理，包括一些常用语法以及一些实用的SQL技术，可为开发人员充实SQL基础知识

第二部分，SQL语句调优，对于系统数据库调优包括硬件调优、操作系统调优、数据库配置调优、数据建模调优以及SQL语句调优，在这里主要是针对与开发有关的SQL语句调优进行一下整理与总结，希望能对开发人员写出高效SQL语句或对SQL进行调优有所帮助。

第三部分，简要介绍一下PL/SQL，并列举一些PL/SQL的特点 因本人能力有限，在整理文档过程中难免有错误或疏漏，还请大家批评指正。 基础用法

一、 SELECT语句

1、 语法

Select 列表

From 表名

Where 条件

Group by 列表

Order by 列表

对于Select语句后可跟For update

二、 多表关联

在多表关联查询时，使用表关联关系进行数据查询，其结果按关联方式不同而有差异，下面就对各种关联方式进行说明

1、 Cross-Join

SELECT * FROM division, managerof;

结果：两表的笛卡尔乘积

2、 Join (Inner join) 内连接

SELECT * FROM division a

JOIN managerof on b on a.id = b.id ;

结果：两表中满足关联条件的行被取出

Join USING(字段) 用法

SELECT gl.generalledger_id, coa.coa#, t.text AS type,

st.text AS subtype, coa.text as coa, gl.dr, gl.cr, gl.dte

FROM type t JOIN coa USING(type)

JOIN subtype st USING(subtype)

JOIN generalledger gl ON(gl.coa# = coa.coa#);

若两表中的关联字段相同，可使用Using 用法

3、 Natural Join 自然连接

自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果

SELECT * FROM division NATURAL JOIN managerof;

结果：就是按两个表中所有类型与列名相同的字段进行关联，满足条件的记录返回 Natural 关键字也适用于外连接

4、 Left join (Left Outer Join ) 左外连接

Select * from a

Left join b on a.id = b.id

结果：a 表中数据全部返回，b表中满足关联条件的数据关联返回

5、 Right join (Right Outer Join) 右外连接

Select * from a

Right join b on a.id = b.id

结果：b 表中数据全部返回，a表中满足关联条件的数据关联返回

6、 Full Outer Join全外连接

Select * from a

Full Outer join b on a.id = b.id

结果：a、b满足关联条件的记录关联在一起返回，a、b两表中不满足关联关系的记录也返回，对应的关联表字段为空

7、 多表、复杂关联

一个关联语句有多个表、多种关联关系

Select * from a

Inner join b on a.id = b.id

Left join c on b.name = c.name 注：关键字Natural也可用在外关联上

三、 子查询

子查询也是SQL语句中常用的技术方法，通常有如下几种分类

1、 单行子查询

返回一行的子查询

2、 多行子查询

返回多行的子查询

3、 多列子查询

返回多列的子查询

4、 规则子查询

子查询与外层语句无关

SELECT * FROM department WHERE division_id IN (SELECT division_id FROM division);

5、 关联子查询

子查询与外层语句有关联

SELECT * FROM division WHERE EXISTS

(SELECT division_id FROM department

WHERE division_id = division.division_id);

子查询用途

1、 在SELECT语句中

SELECT p.project_id,

(SELECT projecttype_id FROM projecttype

WHERE projecttype_id = p.projecttype_id)

FROM project p;

2、 在WHERE语句中

SELECT project_id FROM project

WHERE projecttype_id IN

(SELECT projecttype_id FROM projecttype);

SELECT division_id FROM division

WHERE division_id IN

(SELECT division_id FROM department

WHERE department_id IN

(SELECT department_id FROM department));

3、 在Order by 语句中

SELECT name FROM projecttype

ORDER BY (SELECT ‘name’ FROM DUAL);

4、 在Having 语句中

SELECT name, COUNT(name) FROM projecttype GROUP BY name

HAVING name = ANY (SELECT name FROM projecttype);

5、 在From 语句中

SELECT COUNT(*) FROM (

SELECT di.name, de.name, pr.name

FROM division di

JOIN department de ON (di.division_id = de.division_id)

JOIN project pr ON (de.department_id = pr.department_id));

6、 在INSERT语句

INSERT INTO projecttype(projecttype_id, name)

VALUES((SELECT MAX(projecttype_id)+1 FROM projecttype)

,’A new project type’);

7、 在UPDATE语句

UPDATE department SET name = (SELECT name FROM division

WHERE division_id = department.division_id);

8、 在CASE语句

9、 作为函数参数

SELECT LPAD(‘xxx’,10,(SELECT * FROM DUAL)) FROM DUAL;

四、 级联查询(Hierachical Querys)

所谓级联查询，就是在同一个表内记录有层次的数据（层次为树形关系），记录的方式是通过两个字段进行关联，使用级联查询可取出满足需求的分层次数据

语法如下：

SELECT语句 [[START WITH condition1] CONNECT BY condition2]

解释：

START WITH condition1

指定级联数据的根记录(一条或多条)，所有满足条件1的记录都将被当成是根纪录，假如我们不给定START WITH子句，所有的纪录都会被当成是根纪录，通常这不是我们想要的结果。condition1可以是一个子查询。

CONNECT BY condition2

指定级联数据中父纪录和子纪录之间的关系，这里的关系被表示成一个表达式，当前纪录的字段会和对应的父纪录的某个字段进行比较。condition2必须跟着一个PRIOR操作符，该操作符用于标明父纪录的字段。condtion2不能包含子查询

PRIOR是Oracle的一个内建操作符，仅用于级联查询。当我们在级联查询的CONNEC

T BY条件中使用了PRIOR操作符时，位于其后的表达式被当成是当前纪录的父纪录进行比较。

示例：

select * from employee start with id = 2 connect by prior id = manager_id order by id; connect by prior id = manager_id

意思是从当前根记录开始，查找所有符合条件的记录：他们的manager_id必须等于当前记录的id。也就是说查找所有manager_id=2的记录及其子记录

select * from employee start with id = 2 connect by id = prior manager_id order by id; connect by id = prior manager_id

意思是从当前根记录开始，查找所有符合条件的记录：他们的id必须等于当前记录的manager_id。也就是说查找所以id=1的记录

这种语法适合于不明确数据层次关系的情况下使用，如果层数明确，并且在表中记录有层级，则可使用表自连接的关联方式实现多级数据查询

五、 INSERT语句

1、 标准语法

INSERT INTO [schema.]table [ ( column [, column …] ) ]

VALUES ( { expression | DEFAULT | (subquery) }

[, { expression | DEFAULT | (subquery) } ... ] )

[ RETURNING expression [, expression ] INTO variable [,

variable ] ];

示例

INSERT INTO projecttype

VALUES (projecttype_seq.NEXTVAL, ‘A new project type’);

2、 多表插入

INSERT { FIRST | ALL }

[ WHEN condition THEN ] INTO table [ VALUES (…) ]

[ WHEN condition THEN ] INTO table [ VALUES (…) ]

ELSE INTO table [ VALUES (…) ]

subquery; ALL 只要满足WHEN 条件的就执行 FIRST 执行第一个满足WHEN条件的 ELSE 如果所有条件都不满足，则执行ELSE中内容

示例1：

insert FIRST

when 1=2 then into ta

when 2=2 then into tb

when 3=3 then into tc

else

into ttest

select * from ttest ;

结果是将ttest表中的数据插入到tb表中，第2个条件与第3个条件均满足，但FIST关键词决定执行第2个插入操作

示例1：

insert ALL

when 1=2 then into ta

when 2=2 then into tb

when 3=3 then into tc

else

into ttest

select * from ttest ;

结果是将ttest表中的数据插入到tb、tc表中，第2个条件与第3个条件均满足，ALL关键词决定执行第2个与第3个插入操作

六、 UPDATE语句

UPDATE [schema.]table

SET column = expression | (subquery) | DEFAULT

| ( column [, column … ] ) = (subquery)

[ WHERE ... ]

[ RETURNING expression [, expression ] INTO variable [,

variable ] ) ];

示例1：

DEFAULT 按列的默认值给列赋值

RETURNING 按更新的行返回一个表达式的值给变量，此用法可适用于insert、update、delete

多列的更新，通常是按每列分别赋值，但也可使用子查询同时给多个列赋值 Update 表名1

Set (column1,column2,column3) = (select value1,value2,value3 from 表名2 where 条件 ) Where 条件

七、 DELETE语句

语法：

DELETE [ FROM ] [schema.]table

[ WHERE ... ]

[ RETURNING expression [, expression ] INTO variable [,

variable ] ) ];

示例：

DELETE from department where id = 8879 returning dname into :name;

八、 集合操作符

对于集合操作符有UNION ALL、UNION、INTERSECT、MINUS

1. UNION ALL

返回两个结果集的全部记录，也包括重复的记录

例如A结果集有100条，B结果集有100条，不管是否有重复的，最后结果是返回200条

2. UNION

返回两个结果集的全部有区别的记录，对于两个结果集中重复的会进行合并后返回，

例如A结果集有100条记录，B结果集有100条记录，其中B结果集中有20条与A结果集中有重复，则返回的结果集为180条（将B中重复的20条去掉了）

因此UNION会比UNION ALL增加大量的计算，所以不是必要的情况下一定不要使用UNION，而使用UNION ALL

3. INTERSECT

返回两个结果集中重复的行，类似于一个内连接，例如A结果集有100条，B结果集有100条，其中重复的为20条，则使用INTERSECT返回的结果集为20条

4. MINUS

返回一个结果集减去另一个结果集后剩下的数据，所谓减就是将重复的部分减去 例如，A结果集有100条，B结果集有100条，其中重复的为20条，则

A

MINUS

B

的结果为80条(A中的)

九、 DISTINCT

DISTINCT是将结果集中的重复值去掉，即在所获取的结果集中，若有重复的记录则只返回一条，所谓重复的记录即每个字段均相同

Select distinct column …… from ……

如果不是必须使用则尽量少使用

十、 ROLLUP 、CUBE、Grouping SETS用法

ROLLUP、CUBE与Grouping SETS是用在分组查询中，通常用在数据仓库的计算上，但对于我们的业务系统中某些统计报表也是很有效的，下面就简要介绍一下这两个用法 ROLLUP 是按汇总列进行上卷

语法示例：

Select A,B,C,sum(D)

From 表

group by Rollup (A,B,C) \\A,B,C为分组列，D为汇总列

如果不加Rollup关键字，其结果是按A，B，C三列计算出D的汇总，加了Rollup之后，得出结果是先按（A，B，C）分组计算出汇总，再按（A，B）计算出汇总，再按（A）计算出汇总，最后计算出全部记录的汇总，并按层次关系进行排序，适用于逐级展开汇总查询

CUBE 是按组合进行汇总计算

语法示例：

Select A,B,C,sum(D)

From 表

group by Cube(A,B,C)

其结果是按如下组合形成汇总结果 (A，B，C)（A，B）（A，C）（A）（B，C）（B）（C）再加上全部记录的汇总

Grouping Sets 按指定组合进行汇总

语法示例：

Select A,B,C,sum(D)

From 表

group by Grouping sets((A,B,C) ,(A,B),(C))

其结果是按(A,B,C) ,(A,B),(C)三种分组形成汇总结果

类似于多个汇总结果集UNION ALL

十一、 Flashback闪回技术

Oracle 的闪回技术可以解决短时间内被误删除数据的问题，可恢复的时间根据数据库的参数设置有关

语法：SELECT … AS OF { SCN | TIMESTAMP } expression

可以有两种方式取回历史数据，一种是通过SCN，一种是使用TIMESTAMP，使用SCN方式比较复杂，通常我们使用TIMESTAMP方式即可

示例：如果不小心删除了表salb25中的数据，则可通过如下语句取回

select * from salb25 as of timestamp to_timestamp(‘2011-05-22 15:00:00’,’yyyy-mm-dd hh24:mi:ss’ ;

to_timestamp函数是指明取哪个时间点的数据

十二、 伪列（Pseudocolumns）

伪列是Oracle数据库提供的查询结果附带的信息，是虚列

1、 CURRVAL、NEXTVAL

序列中的伪列

Sequence.CURRVAL

Sequence.NEXTVAL

2、 ROWID

ROWID提供一种快速访问数据的方法，是直接对应的一条记录的物理地址，在取出表中的一条记录后，在结果集中会存在ROWID这个伪列，使用这个值可最快速的访问表中记录，每条记录有一个ROWID

3、 ROWNUM

ROWNUM是一个查询语句返回记录的顺序号，此顺序号是在Where语句生效后产生的，如果Where后面还有Order by排序语句，则原顺序不变

4、 LEVEL

LEVEL是级联查询中会产生的伪列，表示每条记录的层次 (从1开始) 十三、 MERGE

MERGE是被用在两个表间插入新行并更新存在的行

语法：

MERGE into 表1

USING 表2 on (两表关联条件 )

WHEN MATCHED THEN UPDATE语句

WHEN NOT MATCHED THEN INSERT语句

示例：

MERGE INTO bonuses D

USING (SELECT employee_id, salary, department_id FROM employees WHERE department_id = 80) S

ON (D.employee_id = S.employee_id)

WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)

VALUES (S.employee_id, S.salary*0.1);

十四、 CASE用法

CASE可以作为一个表达式来使用，也可作为PL/SQL程序控制结构来使用

1、表达式

作为表达式来使用有如下两种用法

语法1：

Case

When 表达式1 then 结果1

When 表达式2 then 结果2

……

Else 结果n

End

返回第一个表达式为 true后面对应的结果，若没有表达式为true，则返回else 后面结果

示例：

语法2：

Case 表达式

When 值1 Then 结果1

When 值2 Then 结果2

……

Else 结果n

End

返回第一个与表达式相等的值后面的结果，若没有相等则返回else 后的结果 此表达式可以在SQL语句中来使用

2、PL/SQL程序结构

作为控制结构来使用有如下两种用法

语法1：

Case

When 表达式1 then

语句块1;

When 表达式2 then

语句块2;

……

Else

语句块n;

End Case;

执行第一个表达式为true的后面的语句块，若没有则执行else 后面的语句块 示例：

语法2：

Case 表达式

When 值1 Then

语句块1

When 值2 Then

语句块2

……

Else

语句块n

End Case;

计算出表达式的值，按第一个匹配上的值执行后面的语句块，若没有相等则执行 else 后的语句块

SQL语句调优

一、 避免使用’＊’

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

二、 删除重复记录

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

DELETE FROM EMP E

WHERE E.ROWID > (SELECT MIN(X.ROWID)

FROM EMP X

WHERE X.EMP_NO = E.EMP_NO)

Oracle的ORWID用来唯一标识表中的一条记录,是这条数据在数据库中存放的物理地址

三、 计算记录条数

计算记录数一般count(*),count(1),count(索引列)三种写法，这三种方法实际的效率差不多

四、 减少对表的查询

在写SQL语句时要减少对表的查询，即能通过对表一次查询取得结果就不要查两次。 例1：

Select * from clmb01c

Where cjxsdm = (select max(cjxsdm) from clmb10)

And csqdh = (select min(csqdh) from clmb10)

Where id = 833997

可改成

Select * from clmb01c

Where (cjxsdm, csqdh)= (select max(cjxsdm),min(csqdh) from clmb10) where id = 833997;

更新也一样

Update clmb01c set (cjxsdm,csqdh) = (select max(cjxsdm),min(csqdh) from clmb10)

Where id = 833997;

当然此例还有其它写法，在此拿出只是说明观点

例2：

Update clmb01c set cdjzt = ‘1’ where id = 833997;

Select nhjje into n_hjje from clmb01c where id = 833997;

以上两语句可合成一个

Update clmb01c set cdjzt = ‘1’ where id = 833997 returning nhjje into n_hjje;

五、 EXISTS 与IN使用

对于这两个操作符，通常认为EXISTS会比IN效率高，特别是EXISTS的子查询语句可用到索引时会比IN效率高

Oracle在处理这两个操作时处理如下：

IN：Oracle将IN后面的子查询语句调入到内存，然后排序，如果记录多会非常占用系统资源

EXISTS：在每次查询时调用一次子查询语句，所以就要保证子查询语句能使用索引，否则就会全表扫描，如果记录数很多那是很可怕的事情，会产生迪卡尔乘积

结论：IN更适用于少量的值，而Exists更适用于关联子查询，对于记录大时一定要使用EXISTS，并保存子查询使用索引

对于NOT EXISTS和NOT IN一定要选用NOT EXISTS，还可使用（外连接＋判断为空）

六、 使用索引

对于数据量大的表一定要建立适当的索引，正确的使用索引可以级大的提高数据检索效率，在写SQL语句时要考虑表索引的使用，但索引的缺点一是增加物理空间的占用，二是对表的增、删、改会增加一次索引的增、删、改操作，所以对于大表要建立并使用索引，但不能过多

使用索引要注意以下几点：

1． 要按索引列构造SQL语句的where条件才能使用到索引

2． 使用不等于操作符则不能使用索引

例：select col1,col2 from table1 where col1 <> ‘AA’

如果列col1是索引则此写法不能使用索引，同样还有not in (‘AA’)

那出现这种问题如何解决呢？

那就要改变这种写法，比如判断的条件值有’AA’，’BB’，’CC’

则条件改写成col1 = ‘BB’ or col1 = ‘CC’

3． 使用is NULL 则不能使用索引

如上例：where col1 is NULL 不会使用索引

对于索引列要避免出现NULL值

4． LIKE操作符的使用

对于LIKE使用时后面的条件前面不能有通配符，否则不能使用索引

select col1,col2 from table1 where col1 like ‘AA%’

col1是字符型索引列，上面的语句会用到索引，但下面的语句不会用到索引 select col1,col2 from table1 where col1 like ‘%AA%’

所以在使用时要注意

5． 不能对索引列合并计算使用

例：

SELECT NVL(SUM(NVL(NYFPSL, 0)), 0) FROM SALR02 WHERE CNF || CYF = ‘200608’

因为SALR02表的索引是CNF，CYF，所以CNF || CYF会造成全表扫描

应写成

SELECT NVL(SUM(NVL(NYFPSL, 0)), 0) FROM SALR02 WHERE CNF = ‘2006’ and CYF = ‘08’

如果是年份和月份是一个变量则写成

SELECT NVL(SUM(NVL(NYFPSL, 0)), 0) FROM SALR02 WHERE CNF = substrb(c_jhny,1,4) and CYF = substrb(c_jhny,5,2)

6． 对索引列使用函数也会造成系统不能使用索引

例：select col1,col2 from table1 where substrb(col1,1,2) = ‘AA’

索引列是col1，这种写法也是不能使用索引的

需要改成where col1 like ‘AA%’

其它情况就要按具体函数具体分析

7． 比较不匹配的数据类型时也会影响索引的使用

例：select col1,col2 from table1 where col1 = 88383

Col1是索引列，类型是varchar2(10)，此种情况oracle是允许的，只是oracle在处理时会自动在列col1上加个函数使条件变成where to_number(col1) = 88383 这样就不能使用索引了

8． 避免使用OR条件

在SQL语句条件如果对于索引列使用OR条件则系统不会使用索引

例：select cjxsdm from clmb01c where cjxsdm = ‘01-01F’ or csqdh = ‘200601010003’ 不管怎么建索引你都用不上了，如果表记录多那你就想别的办法吧

9． 可以强制使用索引

例：select /*+INDEX(table1 IK_col1)*/ col1,col2 from tabl1 ;

Table1是表名，IK_col1是索引名

强制使用并不表示最优，根据具体情况定

10． 使用函数索引，对于某些日期型字段常用字符型日期作为条件查询，这种情况

可建立函数索引

如表table1中字段dczrq是日期型date，建立函数索引IK_dczrq

Create index IK_dczrq on table1(to_char(dczrq,’yyyymmdd’))

对于函数索引在使用时，要与定义时函数的格式相同

11． 对于小表（数据量小）就不要建索引了

对于小表，因为在使用时通常用不到索引，所以索引不起作用

12． 对于数据量比较大并且频繁使用的表的索引需要定期进行重建rebuiled

索引重建的标准，通常索引中的冗余记录占总索引记录的15%以上时，要进行索引重建

索引分析：

analyze index 索引名 validate structure

按分析结果决定是否重建

select a.name,case when a.lf_rows = 0 then 0 else round(a.del_lf_rows/a.lf_rows,2) * 100 end ind_used,a.del_lf_rows,a.lf_rows

from index_stats

如果ind_used >=15 则需要进行索引重建

重建索引有两个语句 rebuild 和rebuild online

Alter index 索引名 rebuild;

Alter index 索引名 rebuild online;

两者区别，如果是使用rebuild 在重建索引时会锁表的DML语句，而使用rebuild online 在重建索引时是不影响表的DML语句，但效率低(执行时间比大约4：3) 建议使用rebuild online方式

七、 使用where代替having语句

对于有些SQL语句中having条件如果可以改成where 里去写，则要放到where里去写，因为使用having是在检索出所有结果集之后才对数据进行过滤，如果将条件放到where里就会返回少的结果集，那多好!

例：select cjxsdm,count(njsje) from clmb20

Group by cjxsdm

Having cjxsdm = ‘09-23F’

此种语句一定要避免，要改成如下写法

select cjxsdm,count(njsje) from clmb20

where cjxsdm = ‘09-23F’

Group by cjxsdm

八、 使用表的别名

在写SQL语句时最好使用表的别名，特别是多表联接时，因为表的别名会减少oracle对SQL语句的解析时间

例：select cjxsdm,csqdh,cgzdm from clmb01c,clmb01d where clmb01c.id = clmb01d.id 前两个字段是clmb01c表的，后面的字段是clmb01d表的，oracle要从数据字典里去判断哪个字段是哪个表，如果改成如下写法则会省了这一步

select a.cjxsdm,a.csqdh,b.cgzdm from clmb01c a,clmb01d b where a.id = b.id

九、 表记录删除

如果删除表中记录使用delete语句，但要删除表中全部记录还可使用truncate语句，truncate语句会比delete语句效率高，并且删除的更干净，但truncate语句一定要慎用，因为他是DDL语句，执行完truncate之后会自动提交事务，所以一定要小心噢

十、 Decode函数使用

合理使用Decode函数会减少对表的检索

例：select count(*) from clmb01c where cjxsdm = ‘01-01F’ and cshbs = ‘0’;

Select count(*) from clmb01c where cjxsdm = ‘01-01F’ and cshbs = ‘1’;

此两个语句会对表clmb01c执行两次表的扫描，使用decode函数可改成一次表扫描 Select count(decode(cshbs,’0’,1,null)) count0,count(decode(cshbs,’1’,1,null)) count1

From clmb01c where cjxsdm = ‘01-01F’ and cshbs in (‘0’,’1’)

Decode函数通常还可替代UNION

示例：

SELECT stock_id||’ Out of Stock’ FROM stock WHERE qtyonhand <=0

UNION

SELECT stock_id||’ Under Stocked’ FROM stock

WHERE qtyonhand BETWEEN 1 AND min-1

UNION

SELECT stock_id||’ Stocked’ FROM stock

WHERE qtyonhand BETWEEN min AND max

UNION

SELECT stock_id||’ Over Stocked’ FROM stock

WHERE qtyonhand > max;

可以改写成

SELECT stock_id||’ ‘||

DECODE(SIGN(qtyonhand)

,-1,’Out of Stock’,0,’Out of Stock’

,1,DECODE(SIGN(qtyonhand-min)

,-1,’Under Stocked’,0,’Stocked’

,1,DECODE(sign(qtyonhand-max)

,-1,’Stocked’,0,’Stocked’,1,’Over Stocked’

))) FROM stock;

效率会得到很大提高

十一、 尽量少用子查询

特别是相关子查询。因为这样会导致效率下降。

一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行 例：

Select a.njsje,(select b.nfpsl from mdac22 b where b.cjxsdm = a.cjxsdm ) nfpsl from clmb01c a

这样写法当然是不好啦，可改成关联，怎么写自己看吧

十二、 尽量少用视图（View）

在我们的业务系统中，有很多视图在使用，因为视图将一个SQL语句封装成一个对象，使用时可象使用表那样非常方便，对于Oracle视图是不存数据的。

1、 对于单表视图，视图与表的执行效率是一样的

2、 对于多表关联形成的视图，通常会消耗较多的系统资源

要适当使用视图

十三、 减少不必要的排序

对结果集排序会对系统产生较大的开销，所以在使用Order by 语句时要考虑是否是必要的，如果不是必要的就一定不要加

在子查询中通常不要加Order by 语句（除对排序有特殊需求）

在程序过程中（如存储过程）的结果集，通常不要加Order by 语句

尽量少用Distinct，因为Distinct总会产生排序

十四、 调优方法( Explain Plan)

在满足业务需求的基础上减少系统执行SQL语句的工作，就是减少系统资源消耗，执行SQL语句通常使用硬盘读、使用内存、使用CPU计算，调优就是将系统执行SQL语句所使用的资源最小，这样效率就会最高。

减少大表的全表扫描，减少排序，比较见效的

对于SQL语句调优可以采用一些有效的优化手段，最重要的是要能自己进行分析，比较有效的分析工具是Oracle提供的解释计划 Explain Plan

1、 一种方式是可以在PLSQL DEVELOPER中使用

这种方式非常简单易行，在PLSQL中输入要分析的SQL语句，然后执行F5，即进入Explain Plan窗口，显示此语句的执行计划，并对此执行计划进行分析

2、 另一种方式是使用命令

示例：

EXPLAIN PLAN SET statement_id= ‘计划标识’

FOR

SELECT语句;

到表plan_table 中查结果，此种方式通常是在命令行方式下使用，看着不是很方便 COL Cost FORMAT 9990;

COL Rows FORMAT 999990;

COL Bytes FORMAT 99999990;

COL Query FORMAT a40;

SELECT operation||’ ‘||options||’ on ‘||object_name "Query"

,cost "Cost"

,cardinality "Rows"

,bytes "Bytes"

FROM plan_table ORDER BY id;

3、 列说明

Cost ：这是综合成本，是按CPU、内存以及硬盘读写等综合计算出来的一个值

Cardinality：是估算影响的行数

Bytes：是估算影响的字节数，是按行数*本行各列所占字节数

对分析语句来说 ，通常Cost是重点，是调优的指标

在对SQL语句进行解释计划分析时，重点是要减少开销（Cost），通过调整SQL语句的实现方式，来对语句进行优化

对于解释计划中数据访问及处理方式，可参见一些相关文档，在此不尽述

PL/SQL的使用

一、 简介

PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言

我们常用的PL/SQL开发工具是PLSQL Developer 、Toad等 PL/SQL块由四个基本部分组成：声明、执行体开始、异常处理、执行体结束。 下面是四个部分的基本结构：

DECLARE—可选部分

变量、常量、游标、用户定义异常的声明

……

BEGIN—必要部分

SQL语句和PL/SQL语句构成的执行程序

……

EXCEPTION—可选部分

程序出现异常时，捕捉异常并处理异常

……

END；—必须部分

二、 PL/SQL特点

1、 PL/SQL不一定提供很好的性能，但可以降低SQL编码的复杂，使SQL过程可更

好的控制，也可更好的使用游标

2、 PL/SQL明显的好处是可以创建存储过程与触发器，显而易见，存储过程的好处是

集中控制并提升性能，存储过程代码在数据库服务器上执行，降低了网络传输，结果是提高了性能

3、 有些情况是不能使用一个独立的SQL语句完成需求，就需要使用PL/SQL

4、 将非常复杂的SQL脚本改到PL/SQL实现，变成一些简单的SQL语句，可以更方

便的对SQL语句进行调优

5、 较大的IF语句应该改成CASE语句，通常情况下CASE语句比IF语句更快