[Oracle]高效的PL/SQL程序设计(一)--伪列ROWNUM使用技巧

本系列文章导航

--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

--返回的结果

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

--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

--不推荐方法 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

-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 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

--如果觉得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 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

博文来源：http://blog.csdn.net/huanghui22/archive/2007/03/20/1535688.aspx

Biegral Blog

[Oracle]高效的PL/SQL程序设计(一)--伪列ROWNUM使用技巧

本系列文章导航

阅读排行

分类

归档