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

<script src="http://blog.csdn.net/count.aspx?ID=1535688&Type=Rank" type="text/javascript"></script>

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