[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