SQL和PL/SQL的性能优化之五---排序、分组与集合
1、排序操作---可能需要ORACLE排序数据的操作包括：
A、创建一个索引
B、通过GROUP BY，UNIQUE或DISTINCT关键字对数据进行分组或聚合
C、ORDER BY
D、使用排序-合并方法联结表或结果集
E、使用集合操作UNION，INTERSECT或MINUS
F、执行特定的子查询
查询可能需要可观的资源，具体说明如下：
A、CPU总是要消耗的，需要CPU的数量和需要排序的结果集大小成正比。
B、ORACLE分配一块内存用来排序。这块内存来自程序全局区(PGA)。可用的PGA总量通常取决于MEMORY_TARGET或PGA_AGGREGATE_TARGET的值。
C、如果内存区域不足于完成排序，ORACLE会分配一个或多个临时段到临时表空间。这就叫作磁盘排序。磁盘排序会增加额外的开销，包括在临时段中分配空间和把数据写入临时表空间以及磁盘读出的IO消耗。
如果供排序使用的内存量足以使排序完成，则性能将是最佳的--最优排序；如果没有足够内存，则需要排序的次数越多，排序中所包含的IO就越多，性能就越差。
通过联结V$SQL_WORKAREA和V$SQL，我们可以发现拥有排序最多的SQL语句：
WITH sql_workarea as
(select sql_id||'-'||child_number sql_child, operation_type opert, last_execution last_exec, round(active_time/1000000,2) seconds,
optimal_executions||'/'||onepass_executions||'/'||multipasses_executions olm,
' '||substr(sql_text,1,155) sql_text, rank() over(order by active_time desc) ranking
from v$sql_workarea join v$sql using(sql_id,child_number))
select sql_child, seconds, opert, last_exec, olm, sql_text
from sql_workarea where ranking <= 2
order by ranking;
----如果你要求使用MEMSTATS参数，DBMS_XPLAN.DISPLAY_CURSOR将显示与排序和散列操作相关的统计信息
select * from table(dbms_xplan.display_cursor(:sql_id, :child_number, 'MEMSTATS'));
10032跟踪事件可以用来得到关于会话排序的详细统计信息。要调用此跟踪，需要提交如下的命令：
ALTER session set events '10032 trace name context forvevr, level 1';
跟踪的结果文件（通常在USER_DUMP_DEST所指的位置）包含排序参数和发生在会话执行期间的每一个排序的统计信息。
----使用索引规避排序
如果在ORDER BY子句里的列相同的列上，ORACLE可以直接从索引来按照要求的顺序获取记录，因此也避免了排序操作。
当检索第一条记录时，使用索引而不是排序会带来更好的性能。然而，当检索所有记录时，排序通常比索引查找更出色。
相对于使用排序操作，使用索此对记录排序需要的内存更少；如果内存很紧张，索此查找的效率可能比排序操作更优。
2、分组（Grouping）与聚合（Aggregate）
A、聚合操作（如SUM和AVERAGE）必须处理输入数据的每一行记录。（一般全表扫描）
select sum(quantity-sold) from sales;
如果被聚合列上存在索引，对这个索引的快速全扫描通常会更高效。
B、如果在相关列上存在索引，MAX和MIN操作并不需要读取每一行记录。（3-5个逻辑读)。然而如果我们同时要查最大值和最小值，则ORACLE要花费很高的逻辑读来扫描整个索此项。实际上分别提交MAX和MIN查询，然后将结果合并到一起是一种更好的方法（两个子查询）
C、在ORACLE 10G前，GROUP BY是通过按照GROUP BY中的列对记录进行排序实现的。10G后，HASH GROUP BY 操作通过在读取表时将聚合计算在散列表中避免了排序。理论上HASH GROUP BY 应该比SORT GROUP BY 速度快很多，且消耗更少的内存。然而，HASH GROUP BY 在初始实现有不少严重的BUG，甚至包含错误的结果(!),低劣的性能，或过高的内存消耗等，随着补丁及新版本发布，问题似乎解决了。然而，如果遇到了和HASH GROUP BY 相关的问题，可尝试关闭HASH GROUP BY 。可以通过将参数_GBY_HASH_AGGREGATION_ENABLE设置为FALSE。
10G以前，GROUP BY 会按顺序返回记录，ORDER BY 子句不起作用，10G以后，ORDER BY 会抑制HASH GROUP BY 方法的使用。因此可能会降低性能
D、永远不应该用HAVING 替代HWERE，HAVING是在聚合完成之后对记录进行排除，参与聚合的记录数越少，效果越好。HAVING子句只和GROUP函数配合使用。
3、集合操作
A、UNION 与UNION ALL
如果你不需要在UNION操作中对结果去重，可以用UNION ALL替代UNION。这样可以避免潜在的昂贵的排序操作。
B、INTERSECT（类似排序合并联结）--返回同时出现在两张表或两个结果集里的记录。考虑将其转化为表联结；这样可以用上更加高效的嵌套循环联结和散列联结。
C、MINUS--返回所有出现在第一个SELECT结果后又不包含在第二个SELECT结果集中的记录。建议你将语句转化成使用NOT IN或NOT EXISTS的子查询，这样可以使用返联结。
