例子摘自：《oracle高效设计》
select *
from big_table t1
where last_ddl_time = (select max(last_ddl_time)
from big_table t2
where t2.owner = t1.owner )
/
select *
from big_table t1, ( select owner, max(last_ddl_time) max_time
from big_table
group by owner ) t2
where t1.owner = t2.owner
and t1.last_ddl_time = t2.max_time
/
select owner, last_ddl_time, object_name, object_type
from ( select t1.*,
max(last_ddl_time) over (partition by owner) max_time
from big_table t1
)
where last_ddl_time = max_time
/
这是oracle高效设计（中文版P393）中的关于使用分析函数的例子，我的测试结果是使用分析函数并不会很好！big_table大小45M，记录数406032。
例子1：
Execution Plan
----------------------------------------------------------
Plan hash value: 4038969119
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 293 | 34867 | 2580 (5)| 00:00:31 |
|* 1 | HASH JOIN | | 293 | 34867 | 2580 (5)| 00:00:31 |
| 2 | VIEW | VW_SQ_1 | 24 | 624 | 1312 (7)| 00:00:16 |
| 3 | HASH GROUP BY | | 24 | 336 | 1312 (7)| 00:00:16 |
| 4 | TABLE ACCESS FULL| BIG_TABLE | 405K| 5540K| 1253 (3)| 00:00:16 |
| 5 | TABLE ACCESS FULL | BIG_TABLE | 405K| 35M| 1260 (3)| 00:00:16 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("LAST_DDL_TIME"="VW_COL_1" AND "OWNER"="T1"."OWNER")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11219 consistent gets
0 physical reads
0 redo size
60026 bytes sent via SQL*Net to client
450 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1224 rows processed
例子2与1相同：忽略
但是例子3如下：
Execution Plan
----------------------------------------------------------
Plan hash value: 4069026481
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 405K| 43M| | 6153 (2)| 00:01:14 |
|* 1 | VIEW | | 405K| 43M| | 6153 (2)| 00:01:14 |
| 2 | WINDOW SORT | | 405K| 18M| 46M| 6153 (2)| 00:01:14 |
| 3 | TABLE ACCESS FULL| BIG_TABLE | 405K| 18M| | 1260 (3)| 00:00:16 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_DDL_TIME"="MAX_TIME")
Statistics
----------------------------------------------------------
23 recursive calls
8 db block gets
5606 consistent gets
9604 physical reads
0 redo size
30144 bytes sent via SQL*Net to client
450 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
1224 rows processed
发现有许多physical reads读，检查
show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 80M
发现PGA太小，修改如下：
alter system set pga_aggregate_target=2000M scope=memory
在执行：
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 405K| 43M| | 6153 (2)| 00:01:14 |
|* 1 | VIEW | | 405K| 43M| | 6153 (2)| 00:01:14 |
| 2 | WINDOW SORT | | 405K| 18M| 46M| 6153 (2)| 00:01:14 |
| 3 | TABLE ACCESS FULL| BIG_TABLE | 405K| 18M| | 1260 (3)| 00:00:16 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_DDL_TIME"="MAX_TIME")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5606 consistent gets
0 physical reads
0 redo size
30308 bytes sent via SQL*Net to client
450 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1224 rows processed
发现physical reads为0，但是实际上逻辑读虽然减少了，但是执行的时间实际没有例子1快。我的测试发现：
例子1，2：执行时间：3XX msecs。
例子3（pga_aggregate_target=80M）下：4 secs
例子3（pga_aggregate_target=2000M）下：7xx msecs。
也就是讲在pga_aggregate_target设置适当的情况下，例子3执行才会好一些。
