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 |
© 2011, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.
相关文章 | Related posts:
- Oracle中可被并行化执行的SQL操作
- Hash Joins Implementation and Tuning
- Oracle SQL Developer 的一个Bug
- Optimizer Selects the Merge Join Cartesian Despite the Hints
- Extract SQL Plan from AWR
- Init.ora Parameter "_COMPLEX_VIEW_MERGING" Reference Note
- Init.ora Parameter "SORT_MULTIBLOCK_READ_COUNT"
- EVENT: 10060 dump predicates in optimizer (kko)
- 10g中HASH GROUP BY引起的临时表空间不足
- ORA-01652 even though there is sufficient space in RECYCLE BIN
最新评论