本系列文章导航
[Oracle]高效的PL/SQL程序设计(一)--伪列ROWNUM使用技巧
[Oracle]高效的PL/SQL程序设计(二)--标量子查询
[Oracle]高效的PL/SQL程序设计(三)--Package的优点
[Oracle]高效的PL/SQL程序设计(四)--批量处理
[Oracle]高效的PL/SQL程序设计(五)--调用存储过程返回结果集
[Oracle]高效的PL/SQL程序设计(六)--%ROWTYPE的使用
标量子查询
ORACLE允许在select子句中包含单行子查询, 使用标量子查询可以有效的改善性能,当使用到外部连接,或者使用到了聚合函数,就可以考虑标量子查询的可能性
1. 取消外部连接的使用
外部连接的做法:
select
a.username,
count
(
*
)
from
all_users a,all_objects b
where a.username = b.owner( + )
group by a.username;
where a.username = b.owner( + )
group by a.username;
改成标量子查询的做法:
select
a.username,(
select
count
(
*
)
from
all_objects b
where
b.owner
=
a.username) cnt
from all_users a;
from all_users a;
PS: 两种做法得到的结果会有些许差别,主要在all_objects没有符合条件的行时, 外部连接的count(*)=1,而标量子查询的count(*)结果=0
select
a.username,
count
(
*
),
avg
(
object_id
)
from
all_users a,all_objects b
where a.username = b.owner( + )
group by a.username;
where a.username = b.owner( + )
group by a.username;
2. 多个聚合函数的使用技巧
当同时出现count(*)/avg()时,不适合在select子句中调用两次子查询,性能上会受到影响, 可以改用下面两种做法
(1).拼接之后再拆分
select
username,to_number(substr(data,
1
,
10
)) cnt,to_number(substr(data,
11
))
avg
from
(
select a.username,( select to_char( count ( * ), ' fm0000000009 ' ) || avg ( object_id ) from all_objects b where b.owner = a.username) data
from all_users a
)
(
select a.username,( select to_char( count ( * ), ' fm0000000009 ' ) || avg ( object_id ) from all_objects b where b.owner = a.username) data
from all_users a
)
(2).创建对象类型
create
or
replace
type myType
as
object
(cnt number , avg number );
select username,a.data.cnt,a.data. avg from
(
select username,( select myType( count ( * ), avg ( object_id )) from all_objects b where b.owner = a.username) data
from all_users a
) a;
(cnt number , avg number );
select username,a.data.cnt,a.data. avg from
(
select username,( select myType( count ( * ), avg ( object_id )) from all_objects b where b.owner = a.username) data
from all_users a
) a;
博文来源:http://blog.csdn.net/huanghui22/archive/2007/04/30/1593263.aspx