本系列文章导航
[Oracle]高效的PL/SQL程序设计(一)--伪列ROWNUM使用技巧
[Oracle]高效的PL/SQL程序设计(二)--标量子查询
[Oracle]高效的PL/SQL程序设计(三)--Package的优点
[Oracle]高效的PL/SQL程序设计(四)--批量处理
[Oracle]高效的PL/SQL程序设计(五)--调用存储过程返回结果集
[Oracle]高效的PL/SQL程序设计(六)--%ROWTYPE的使用
--1.
取前
10
行
select
*
from
hr.employees
where
rownum
<=
10
--2.
按照
first_name
升序,取前
10
位
--
正确方法
oracle
处理机制
: --> hr.employees
全表扫描
-->
SORT ORDER BY STOPKEY
只排序前
10
行,作为一个矩阵结构
-->
剩下的行与第
10
行进行比较,合适的进入矩阵
,
否则抛弃
--
优点:
RAM
中少量排序,速度快
(
不需要在内存或者
temp
表空间进行全表排序
),
并不真正排序整个结果集,但概念上做了整个结果集的排序
--
注意第一
,
二个
rownum
的区别
select
rownum,t.
*
from
(
select
rownum,employees.
*
from
hr.employees
order
by
first_name) t
where
rownum
<=
10
--
执行计划
SELECT STATEMENT, GOAL = CHOOSE Cost=5 Cardinality=10 Bytes=15622
COUNT STOPKEY
VIEW Object owner=SCOTT Cost=5 Cardinality=107 Bytes=15622
SORT ORDER BY STOPKEY Cost=5 Cardinality=107 Bytes=7276
COUNT
TABLE ACCESS FULL Object owner=HR Object name=EMPLOYEES Cost=2 Cardinality=107 Bytes=7276
COUNT STOPKEY
VIEW Object owner=SCOTT Cost=5 Cardinality=107 Bytes=15622
SORT ORDER BY STOPKEY Cost=5 Cardinality=107 Bytes=7276
COUNT
TABLE ACCESS FULL Object owner=HR Object name=EMPLOYEES Cost=2 Cardinality=107 Bytes=7276
--
返回的结果
ROWNUM ROWNUM EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
1 22 121 Adam Fripp AFRIPP
2 97 196 Alana Walsh AWALSH
3 48 147 Alberto Errazuriz AERRAZUR
4 4 103 Alexander Hunold AHUNOLD
5 16 115 Alexander Khoo AKHOO
6 86 185 Alexis Bull ABULL
7 59 158 Allan McEwen AMCEWEN
8 76 175 Alyssa Hutton AHUTTON
9 68 167 Amit Banda ABANDA
10 88 187 Anthony Cabrio ACABRIO
1 22 121 Adam Fripp AFRIPP
2 97 196 Alana Walsh AWALSH
3 48 147 Alberto Errazuriz AERRAZUR
4 4 103 Alexander Hunold AHUNOLD
5 16 115 Alexander Khoo AKHOO
6 86 185 Alexis Bull ABULL
7 59 158 Allan McEwen AMCEWEN
8 76 175 Alyssa Hutton AHUTTON
9 68 167 Amit Banda ABANDA
10 88 187 Anthony Cabrio ACABRIO
--3.
取第
11-20
行
--
推荐方法
COUNT STOPKEY -->
当查询到
20
行时
,
执行了查询中止的命令
Cardinality=20
select t.* from (select /*+ First_rows */ rownum rnum,employees.* from hr.employees where rownum<=20) t where rnum>10
SELECT STATEMENT, GOAL = CHOOSE Cost=2 Cardinality=20 Bytes=2920
VIEW Object owner=SCOTT Cost=2 Cardinality=20 Bytes=2920
COUNT STOPKEY
TABLE ACCESS FULL Object owner=HR Object name=EMPLOYEES Cost=2 Cardinality=107 Bytes=7276
VIEW Object owner=SCOTT Cost=2 Cardinality=20 Bytes=2920
COUNT STOPKEY
TABLE ACCESS FULL Object owner=HR Object name=EMPLOYEES Cost=2 Cardinality=107 Bytes=7276
--
不推荐方法
COUNT -->
建立了整个结果集
Cardinality=107
select
t.
*
from
(
select
rownum rnum,employees.
*
from
hr.employees) t
where
rnum
between
11
and
20
SELECT STATEMENT, GOAL = CHOOSE Cost=2 Cardinality=107 Bytes=15622
VIEW Object owner=SCOTT Cost=2 Cardinality=107 Bytes=15622
COUNT
TABLE ACCESS FULL Object owner=HR Object name=EMPLOYEES Cost=2 Cardinality=107 Bytes=7276
VIEW Object owner=SCOTT Cost=2 Cardinality=107 Bytes=15622
COUNT
TABLE ACCESS FULL Object owner=HR Object name=EMPLOYEES Cost=2 Cardinality=107 Bytes=7276
-
4
.
-
当使用内嵌视图时
, ORACLE
优化程序可能会整合视图
,
看下面那句
sql
语句的
explain
select
*
from
(
select employees. * from hr.employees
) emp,
( select departments. * from hr.departments
) dept
where emp.department_id = dept.department_id
select employees. * from hr.employees
) emp,
( select departments. * from hr.departments
) dept
where emp.department_id = dept.department_id
SELECT STATEMENT, GOAL = CHOOSE Cost=5 Cardinality=106 Bytes=9328
HASH JOIN Cost=5 Cardinality=106 Bytes=9328
TABLE ACCESS FULL Object owner=HR Object name=DEPARTMENTS Cost=2 Cardinality=27 Bytes=540
TABLE ACCESS FULL Object owner=HR Object name=EMPLOYEES Cost=2 Cardinality=107 Bytes=7276
HASH JOIN Cost=5 Cardinality=106 Bytes=9328
TABLE ACCESS FULL Object owner=HR Object name=DEPARTMENTS Cost=2 Cardinality=27 Bytes=540
TABLE ACCESS FULL Object owner=HR Object name=EMPLOYEES Cost=2 Cardinality=107 Bytes=7276
--
如果觉得
ORACLE
所走的
access path
不是你想要或者说执行时间过长
,
可以尝试在内嵌视图中增加
rownum,
这个时候内嵌视图会被实体化
(
当作一个实体表
),
这种情况下
ORACLE
优化程序无法整合视图
!
可能会带来性能上的提升
@!
select
*
from
(
select employees. * ,rownum from hr.employees
) emp,
( select departments. * ,rownum from hr.departments
) dept
where emp.department_id = dept.department_id
select employees. * ,rownum from hr.employees
) emp,
( select departments. * ,rownum from hr.departments
) dept
where emp.department_id = dept.department_id
SELECT STATEMENT, GOAL = CHOOSE Cost=5 Cardinality=106 Bytes=22790
HASH JOIN Cost=5 Cardinality=106 Bytes=22790
VIEW Object owner=TEST Cost=2 Cardinality=27 Bytes=1863
COUNT
TABLE ACCESS FULL Object owner=HR Object name=DEPARTMENTS Cost=2 Cardinality=27 Bytes=540
VIEW Object owner=TEST Cost=2 Cardinality=107 Bytes=15622
COUNT
TABLE ACCESS FULL Object owner=HR Object name=EMPLOYEES Cost=2 Cardinality=107 Bytes=7276
HASH JOIN Cost=5 Cardinality=106 Bytes=22790
VIEW Object owner=TEST Cost=2 Cardinality=27 Bytes=1863
COUNT
TABLE ACCESS FULL Object owner=HR Object name=DEPARTMENTS Cost=2 Cardinality=27 Bytes=540
VIEW Object owner=TEST Cost=2 Cardinality=107 Bytes=15622
COUNT
TABLE ACCESS FULL Object owner=HR Object name=EMPLOYEES Cost=2 Cardinality=107 Bytes=7276
博文来源:http://blog.csdn.net/huanghui22/archive/2007/03/20/1535688.aspx