Oracle Execution Plan -- Operation

Hash Group By

Starting with 10g, Oracle introduced “Hash Group By” as the default grouping by mechanism.

Oracle employs a hash algorithm to calculate the hash value for each row based on the GROUP BY columns. This allows rows with identical column values (and hence identical hash values) to be "brought together" without performing a full sort. And then the aggregation is performed upon each group. The hash group by does not guarantee the order of the output, one should use “order by” clause to ensure the order.

Sort Join

Sort join is the first operation of the “Sort Merge Join”. Before Oracle joins two row sources, both row sources have to be in order. If required, Oracle does a sort. The name is a bit misleading. All it does is a sort on the join key.

The “Sort Merge Join” consists of two steps:

•     Sort join operation. Both the inputs are sorted on the join key.
•     Merge join operation. The sorted lists are merged.

Merge Join Cartesian

笛卡尔积式的Merge Join. 只有在两个结果集都很小时，该操作是合理的。 看到它，请检查你的SQL，是否漏了join.

Buffer Sort

Oracle将某些数据放入缓存，以便重复访问。 其实没有排序发生！