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

Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *