Shulou(Shulou.com)06/01 Report--

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.50Execution 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 | 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 versionStatistics -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.09Execution Plan- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) |-| 0 | SELECT STATEMENT | | 661 | 75354 | 217 ( 2) | | 1 | VIEW | | 75354 | 217 (2) | | 2 | WINDOW SORT | | 661 | 64117 | 217 (2) | | 3 | TABLE ACCESS FULL | HH | 64117 | 216 (1) |-- -- Note--'PLAN_TABLE' is old versionStatistics--- 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.