Which SQL Operation May use Temp space?

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

Difference between parameter COMPATIBLE and OPTIMIZER_FEATURES_ENABLE

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

沪ICP备14014813号-2

沪公网安备 31010802001379号