SQL> set autotrace trace explain statistics
SQL> select client_id,service_date,other_stuff
2 from client_usage
3 where (client_id,service_date) in(
4 select client_id,min(service_date)
5 from client_usage
6 group by client_id
7 union all select client_id,max(service_date)
8 from client_usage
9 group by client_id
10 );
Elapsed: 00:00:01.14
Execution Plan
----------------------------------------------------------
Plan hash value: 133001550
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 90 | 1180 (1)| 18:25:41 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2 | 90 | 1180 (1)| 18:25:41 |
| 3 | VIEW | VW_NSO_1 | 2 | 44 | 1176 (1)| 18:21:56 |
| 4 | HASH UNIQUE | | 2 | 22 | 1176 (51)| 18:21:56 |
| 5 | UNION-ALL | | | | | |
| 6 | HASH GROUP BY | | 1 | 11 | 588 (1)| 09:10:58 |
| 7 | INDEX FAST FULL SCAN | PK_CLIENT_USAGE | 788K| 8472K| 587 (0)| 09:10:02 |
| 8 | HASH GROUP BY | | 1 | 11 | 588 (1)| 09:10:58 |
| 9 | INDEX FAST FULL SCAN | PK_CLIENT_USAGE | 788K| 8472K| 587 (0)| 09:10:02 |
|* 10 | INDEX UNIQUE SCAN | PK_CLIENT_USAGE | 1 | | 1 (0)| 00:00:57 |
| 11 | TABLE ACCESS BY INDEX ROWID| CLIENT_USAGE | 1 | 23 | 2 (0)| 00:01:53 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("CLIENT_ID"="CLIENT_ID" AND "SERVICE_DATE"="MIN(SERVICE_DATE)")
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
6210 consistent gets
0 physical reads
52 redo size
768 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SQL> set autotrace trace explain statistics
SQL> select client_id
2 ,service_date
3 ,other_stuff
4 from (
5 select client_id
6 ,service_date
7 ,other_stuff
8 ,row_number() over (partition by client_id order by service_date) rn_min
9 ,row_number() over (partition by client_id order by service_date desc) rn_max
10 from client_usage
11 )
12 where (rn_min = 1 OR rn_max = 1) ;
Elapsed: 00:00:07.52
Execution Plan
----------------------------------------------------------
Plan hash value: 690079770
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 788K| 48M| | 11313 (1)|176:40:17 |
|* 1 | VIEW | | 788K| 48M| | 11313 (1)|176:40:17 |
| 2 | WINDOW SORT | | 788K| 17M| 27M| 11313 (1)|176:40:17 |
| 3 | WINDOW SORT | | 788K| 17M| 27M| 11313 (1)|176:40:17 |
| 4 | TABLE ACCESS FULL| CLIENT_USAGE | 788K| 17M| | 589 (0)| 09:11:54 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN_MIN"=1 OR "RN_MAX"=1)
Statistics
----------------------------------------------------------
27 recursive calls
23 db block gets
3092 consistent gets
7156 physical reads
52 redo size
768 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
2 sorts (disk)
2 rows processed