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 |
Leave a Reply