刚看了《oracle 高效设计》的讲到的,说到oracle中有标量子查询,性能要由于关联查询,但是我在SQL server中知道关联查询的性能要优于标量子查询。
我们来做个测试,看看性能:执行语句:
set
autotrace on
select a.username,count( * ) from all_users a,all_objects b
where a.username = b.owner( + )
group by a.username;
select a.username,count( * ) from all_users a,all_objects b
where a.username = b.owner( + )
group by a.username;
在SQL developer中执行,结果如下:
5356
recursive calls
0 db block gets
82152 consistent gets
0 db block gets
82152 consistent gets
再执下列语句:
set
autotrace
on
select a.username,( select count ( * ) from all_objects b where b.owner = a.username) cnt
from all_users a;
select a.username,( select count ( * ) from all_objects b where b.owner = a.username) cnt
from all_users a;
执行结果,如下:
5371
recursive calls
0 db block gets
98645 consistent gets
0 db block gets
98645 consistent gets
这时发现,通过执行关联查询的性能要优于标量子查询。估计《Oracle 高效设计》是版本比较低,现在都是用的10gR2的版本有很大改变。
执行语句如下:
set
autotrace
on
select a.username, count ( * ), avg ( object_id ) from all_users a,all_objects b
where a.username = b.owner( + ) group by a.username;
select a.username, count ( * ), avg ( object_id ) from all_users a,all_objects b
where a.username = b.owner( + ) group by a.username;
执行结果:
5371
recursive calls
0 db block gets
82157 consistent gets
0 db block gets
82157 consistent gets
执行语句:
set
autotrace
on
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 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)
执行结果:
5356
recursive calls
0 db block gets
98556 consistent gets
0 db block gets
98556 consistent gets
执行语句:
set
autotrace
on
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;
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;
执行结果:
5390
recursive calls
0 db block gets
98662 consistent gets
0 db block gets
98662 consistent gets
总结:
通过上面的测试发现,TOM的《Oracle高效设计》里谈到的标量子查询的性能要由于关联查询,在旧版本的中可能是可以的,在10gR2中是不成立的。