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