分析函数改写SQL

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 |   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               |      |   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 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