[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