Pastebin launched a little side project called VERYVIRAL.com, check it out ;-) Want more features on Pastebin? Sign Up, it's FREE!
Guest

Untitled

By: a guest on Oct 8th, 2012  |  syntax: None  |  size: 4.74 KB  |  views: 29  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. SQL> set autotrace trace explain statistics
  2. SQL> select client_id,service_date,other_stuff
  3.   2    from client_usage
  4.   3    where (client_id,service_date) in(
  5.   4      select client_id,min(service_date)
  6.   5         from client_usage
  7.   6         group by client_id
  8.   7       union all select client_id,max(service_date)
  9.   8         from client_usage
  10.   9         group by client_id
  11.  10    );
  12.  
  13. Elapsed: 00:00:01.14
  14.  
  15. Execution Plan
  16. ----------------------------------------------------------
  17. Plan hash value: 133001550
  18.  
  19. ------------------------------------------------------------------------------------------------
  20. | Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
  21. ------------------------------------------------------------------------------------------------
  22. |   0 | SELECT STATEMENT             |                 |     2 |    90 |  1180   (1)| 18:25:41 |
  23. |   1 |  NESTED LOOPS                |                 |       |       |            |          |
  24. |   2 |   NESTED LOOPS               |                 |     2 |    90 |  1180   (1)| 18:25:41 |
  25. |   3 |    VIEW                      | VW_NSO_1        |     2 |    44 |  1176   (1)| 18:21:56 |
  26. |   4 |     HASH UNIQUE              |                 |     2 |    22 |  1176  (51)| 18:21:56 |
  27. |   5 |      UNION-ALL               |                 |       |       |            |          |
  28. |   6 |       HASH GROUP BY          |                 |     1 |    11 |   588   (1)| 09:10:58 |
  29. |   7 |        INDEX FAST FULL SCAN  | PK_CLIENT_USAGE |   788K|  8472K|   587   (0)| 09:10:02 |
  30. |   8 |       HASH GROUP BY          |                 |     1 |    11 |   588   (1)| 09:10:58 |
  31. |   9 |        INDEX FAST FULL SCAN  | PK_CLIENT_USAGE |   788K|  8472K|   587   (0)| 09:10:02 |
  32. |* 10 |    INDEX UNIQUE SCAN         | PK_CLIENT_USAGE |     1 |       |     1   (0)| 00:00:57 |
  33. |  11 |   TABLE ACCESS BY INDEX ROWID| CLIENT_USAGE    |     1 |    23 |     2   (0)| 00:01:53 |
  34. ------------------------------------------------------------------------------------------------
  35.  
  36. Predicate Information (identified by operation id):
  37. ---------------------------------------------------
  38.  
  39.   10 - access("CLIENT_ID"="CLIENT_ID" AND "SERVICE_DATE"="MIN(SERVICE_DATE)")
  40.  
  41.  
  42. Statistics
  43. ----------------------------------------------------------
  44.           0  recursive calls
  45.           1  db block gets
  46.        6210  consistent gets
  47.           0  physical reads
  48.          52  redo size
  49.         768  bytes sent via SQL*Net to client
  50.         519  bytes received via SQL*Net from client
  51.           2  SQL*Net roundtrips to/from client
  52.           0  sorts (memory)
  53.           0  sorts (disk)
  54.           2  rows processed
  55.  
  56. SQL>
  57. SQL> set autotrace trace explain statistics
  58. SQL> select client_id
  59.   2        ,service_date
  60.   3        ,other_stuff
  61.   4    from (
  62.   5          select client_id
  63.   6                ,service_date
  64.   7                ,other_stuff
  65.   8                ,row_number() over (partition by client_id order by service_date) rn_min
  66.   9                ,row_number() over (partition by client_id order by service_date desc) rn_max
  67.  10            from client_usage
  68.  11         )
  69.  12   where (rn_min = 1 OR rn_max = 1) ;
  70.  
  71. Elapsed: 00:00:07.52
  72.  
  73. Execution Plan
  74. ----------------------------------------------------------
  75. Plan hash value: 690079770
  76.  
  77. ---------------------------------------------------------------------------------------------
  78. | Id  | Operation            | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  79. ---------------------------------------------------------------------------------------------
  80. |   0 | SELECT STATEMENT     |              |   788K|    48M|       | 11313   (1)|176:40:17 |
  81. |*  1 |  VIEW                |              |   788K|    48M|       | 11313   (1)|176:40:17 |
  82. |   2 |   WINDOW SORT        |              |   788K|    17M|    27M| 11313   (1)|176:40:17 |
  83. |   3 |    WINDOW SORT       |              |   788K|    17M|    27M| 11313   (1)|176:40:17 |
  84. |   4 |     TABLE ACCESS FULL| CLIENT_USAGE |   788K|    17M|       |   589   (0)| 09:11:54 |
  85. ---------------------------------------------------------------------------------------------
  86.  
  87. Predicate Information (identified by operation id):
  88. ---------------------------------------------------
  89.  
  90.    1 - filter("RN_MIN"=1 OR "RN_MAX"=1)
  91.  
  92.  
  93. Statistics
  94. ----------------------------------------------------------
  95.          27  recursive calls
  96.          23  db block gets
  97.        3092  consistent gets
  98.        7156  physical reads
  99.          52  redo size
  100.         768  bytes sent via SQL*Net to client
  101.         519  bytes received via SQL*Net from client
  102.           2  SQL*Net roundtrips to/from client
  103.           0  sorts (memory)
  104.           2  sorts (disk)
  105.           2  rows processed