1.old sql:
SELECT a.object_id,
a.object_name,
b.max_id,
b.min_id
FROM HH a,
( SELECT owner, MAX (object_id) AS max_id, MIN (object_id) AS min_id
FROM HH
GROUP BY owner) b
WHERE a.last_ddl_time BETWEEN TO_DATE ('2010-01-01', 'yyyy-mm-dd')
AND TO_DATE ('2012-01-01', 'yyyy-mm-dd')
AND a.owner IN ('MESDEV', 'RPTDEV')
and a.owner=b.owner;
Elapsed: 00:00:25.50
Execution Plan
----------------------------------------------------------
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 254 | 433 (2)|
| 1 | HASH GROUP BY | | 2 | 254 | 433 (2)|
| 2 | HASH JOIN | | 2 | 254 | 432 (1)|
| 3 | TABLE ACCESS FULL| HH | 2 | 194 | 216 (1)|
| 4 | TABLE ACCESS FULL| HH | 661 | 19830 | 216 (1)|
-----------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1904 consistent gets
0 physical reads
0 redo size
111609 bytes sent via SQL*Net to client
2670 bytes received via SQL*Net from client
200 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2977 rows processed
2.new sql
SELECT a.object_id,
a.object_name,
a.max_id,
a.min_id
FROM (SELECT object_id,
object_name,
last_ddl_time,
MAX (object_id) OVER (PARTITION BY owner) AS max_id,
MIN (object_id) OVER (PARTITION BY owner) AS min_id
FROM HH
WHERE owner IN ('MESDEV', 'RPTDEV')) a
WHERE a.last_ddl_time BETWEEN TO_DATE ('2010-01-01', 'yyyy-mm-dd')
AND TO_DATE ('2012-01-01', 'yyyy-mm-dd');
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 661 | 75354 | 217 (2)|
| 1 | VIEW | | 661 | 75354 | 217 (2)|
| 2 | WINDOW SORT | | 661 | 64117 | 217 (2)|
| 3 | TABLE ACCESS FULL| HH | 661 | 64117 | 216 (1)|
-----------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
952 consistent gets
0 physical reads
0 redo size
108149 bytes sent via SQL*Net to client
2670 bytes received via SQL*Net from client
200 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2977 rows processed
