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 |
Comment