group by

Oracle中很多SQL操作都会使用Temp Space临时空间，理想状况下OLTP环境中自动/手动管理的PGA总是能在私有内存中满足这些操作的空间需求，而在Data Warehouse数据仓库中往往我们需要配置一个巨大的临时表空间(组)来满足海量的维护/查询对临时空间的需求，那么到底有哪些SQL操作时需要用到临时空间的呢？Google了一下，似乎没有一张非常完整的列表，这里由我抛砖引玉地列出一些，当然这远远不够全面:

*SQL CODE*	*Type*
*CREATE INDEX*	*DDL*
*REBUILD INDEX*	*DDL*
*ANALYZE*	*DDL*
*CREATE PRIMARY KEY CONSTRAINT*	*DDL*
*ENABLE CONSTRAINT*	*DDL*
*CREATE TABLE AS SELECT(use permanet TBS)*	*DDL*
*SELECT DISTINCT*	*QUERY*
*ORDER BY*	*Clause*
*GROUP BY*	*Clause*
*UNION ALL*	*Clause*
*UNION*	*Clause*
*MINUS*	*Clause*
*INTERSECT*	*Clause*
*ROLLUP() FUNCTION*	*FUNCTION*
*RANK() FUNCTION*	*FUNCTION*
*CONNECT BY*	*Clause*
*TEMPORARY TABLE*	*Temporary Data*
*LOB_DATA*	*LOB*
*LOB_INDEX*	*LOB*
*HASH GROUP BY*	*Operation*
*HASH JOIN*	*Operation*
*HASH JOIN (ANTI)*	*Operation*
*HASH JOIN (SEMI)*	*Operation*
*SORT MERGE JOIN*	*Operation*
*SORT MERGE Anti-Join*	*Operation*
*SORT MERGE Semi-Join*	*Operation*
*SORT GROUP BY*	*Operation*
*IDX MAINTENANCE (SORT)*	*Operation*
*WINDOW (SORT)*	*Operation*
*ROLLUP (SORT)*	*Operation*
*CONNECT-BY (SORT)*	*Operation*
*UNION*	*Operation*
*UNION ALL*	*Operation*
*SORT AGGREGATE*	*Operation*
*SORT UNIQUE*	*Operation*

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> set linesize 200;
SQL> col name for a30;
SQL> col value for a20;

SQL> select name,value
  2    from v$system_parameter
  3   where name in ('compatible', 'optimizer_features_enable');

NAME                           VALUE
------------------------------ --------------------
compatible                     10.2.0.3.0
optimizer_features_enable      10.2.0.4

/* 10.2.0.4升级完毕后compatible参数默认值为10.2.0.3，不同于optimizer_features_enable */

[Read more…]

Which SQL Operation May use Temp space?

Difference between parameter COMPATIBLE and OPTIMIZER_FEATURES_ENABLE