oracle执行计划赏析

大家：

好。实现了一个小需求，用了三种写法，执行计划如下：

SQL> SELECT DAY_ID,NAME,URL,CNT
  2  FROM (SELECT DAY_ID,NAME,URL,CNT,row_number() OVER(PARTITION BY DAY_ID, NAME ORDER BY CNT DESC) RANK
  3  FROM (SELECT T.DAY_ID,
  4         T.NAME,
  5         T.URL,
  6         COUNT(1) CNT
  7    FROM TEST T
  8   GROUP BY T.DAY_ID, T.NAME, T.URL) d)
  9   WHERE RANK=1;


执行计划
----------------------------------------------------------                                          
Plan hash value: 481755404                                                                          
                                                                                                    
---------------------------------------------------------------------------------                   
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |                   
---------------------------------------------------------------------------------                   
|   0 | SELECT STATEMENT         |      |    22 |  1144 |     5  (40)| 00:00:01 |                   
|*  1 |  VIEW                    |      |    22 |  1144 |     5  (40)| 00:00:01 |                   
|*  2 |   WINDOW SORT PUSHED RANK|      |    22 |   572 |     5  (40)| 00:00:01 |                   
|   3 |    HASH GROUP BY         |      |    22 |   572 |     5  (40)| 00:00:01 |                   
|   4 |     TABLE ACCESS FULL    | TEST |    22 |   572 |     3   (0)| 00:00:01 |                   
---------------------------------------------------------------------------------                   
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   1 - filter("RANK"=1)                                                                             
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."DAY_ID","T"."NAME"                              
              ORDER BY COUNT(*) DESC )<=1)                                                          
                                                                                                    
Note                                                                                                
-----                                                                                               
   - dynamic sampling used for this statement (level=2)                                             


统计信息
----------------------------------------------------------                                          
          0  recursive calls                                                                        
          0  db block gets                                                                          
          7  consistent gets                                                                        
          0  physical reads                                                                         
          0  redo size                                                                              
        729  bytes sent via SQL*Net to client                                                       
        415  bytes received via SQL*Net from client                                                 
          2  SQL*Net roundtrips to/from client                                                      
          1  sorts (memory)                                                                         
          0  sorts (disk)                                                                           
          5  rows processed                                                                         

SQL> SELECT DAY_ID,NAME,URL,CNT
  2  FROM (SELECT T.DAY_ID,
  3         T.NAME,
  4         T.URL,
  5         COUNT(1) CNT,
  6         row_number() OVER(PARTITION BY T.DAY_ID,T.NAME ORDER BY COUNT(1) DESC) RANK
  7    FROM TEST T
  8  GROUP BY T.DAY_ID,
  9         T.NAME,
 10         T.URL)
 11  WHERE RANK=1;


执行计划
----------------------------------------------------------                                          
Plan hash value: 481755404                                                                          
                                                                                                    
---------------------------------------------------------------------------------                   
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |                   
---------------------------------------------------------------------------------                   
|   0 | SELECT STATEMENT         |      |    22 |  1144 |     5  (40)| 00:00:01 |                   
|*  1 |  VIEW                    |      |    22 |  1144 |     5  (40)| 00:00:01 |                   
|*  2 |   WINDOW SORT PUSHED RANK|      |    22 |   572 |     5  (40)| 00:00:01 |                   
|   3 |    HASH GROUP BY         |      |    22 |   572 |     5  (40)| 00:00:01 |                   
|   4 |     TABLE ACCESS FULL    | TEST |    22 |   572 |     3   (0)| 00:00:01 |                   
---------------------------------------------------------------------------------                   
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   1 - filter("RANK"=1)                                                                             
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."DAY_ID","T"."NAME"                              
              ORDER BY COUNT(*) DESC )<=1)                                                          
                                                                                                    
Note                                                                                                
-----                                                                                               
   - dynamic sampling used for this statement (level=2)                                             


统计信息
----------------------------------------------------------                                          
          0  recursive calls                                                                        
          0  db block gets                                                                          
          7  consistent gets                                                                        
          0  physical reads                                                                         
          0  redo size                                                                              
        729  bytes sent via SQL*Net to client                                                       
        415  bytes received via SQL*Net from client                                                 
          2  SQL*Net roundtrips to/from client                                                      
          1  sorts (memory)                                                                         
          0  sorts (disk)                                                                           
          5  rows processed                                                                         

SQL> SELECT DAY_ID,NAME,URL,cnt
  2  FROM (SELECT T.DAY_ID,
  3         T.NAME,
  4         T.URL,
  5         COUNT(1) cnt,
  6         MAX(COUNT(1)) OVER(PARTITION BY DAY_ID,NAME) cnt1
  7    FROM TEST T
  8   GROUP BY T.DAY_ID, T.NAME, T.URL) d
  9   WHERE cnt=cnt1;


执行计划
----------------------------------------------------------                                          
Plan hash value: 1133560901                                                                         
                                                                                                    
-----------------------------------------------------------------------------                       
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |                       
-----------------------------------------------------------------------------                       
|   0 | SELECT STATEMENT     |      |    22 |  1144 |     4  (25)| 00:00:01 |                       
|*  1 |  VIEW                |      |    22 |  1144 |     4  (25)| 00:00:01 |                       
|   2 |   WINDOW BUFFER      |      |    22 |   572 |     4  (25)| 00:00:01 |                       
|   3 |    SORT GROUP BY     |      |    22 |   572 |     4  (25)| 00:00:01 |                       
|   4 |     TABLE ACCESS FULL| TEST |    22 |   572 |     3   (0)| 00:00:01 |                       
-----------------------------------------------------------------------------                       
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   1 - filter("CNT"="CNT1")                                                                         
                                                                                                    
Note                                                                                                
-----                                                                                               
   - dynamic sampling used for this statement (level=2)                                             


统计信息
----------------------------------------------------------                                          
          0  recursive calls                                                                        
          0  db block gets                                                                          
          7  consistent gets                                                                        
          0  physical reads                                                                         
          0  redo size                                                                              
        729  bytes sent via SQL*Net to client                                                       
        415  bytes received via SQL*Net from client                                                 
          2  SQL*Net roundtrips to/from client                                                      
          2  sorts (memory)                                                                         
          0  sorts (disk)                                                                           
          5  rows processed                                                                         

SQL> spool off;

可以看到第一种写法本质上和第二种是一样的，第三种写法多了在内存中排序，效果有点差