Guest User

Untitled

a guest
Oct 8th, 2012
71
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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
RAW Paste Data