Oracle SQL Developer：优化双表创建临时表的查询性能

针对你用CTAS创建临时表耗时2小时的问题，结合两张百万级表的结构（id列均有索引），给出以下具体优化措施：

1. 修正并行提示语法 #

原语句的并行提示写法不符合Oracle规范，正确的并行提示需要调整格式，同时建议直接为新创建的表指定并行度：

CREATE TABLE temporary
    PARALLEL 8
    AS
        SELECT /*+ PARALLEL(8) */
            T1.id,
            T2.name,
            T2.code    
        FROM TABLE1 T1
        JOIN TABLE2 T2 ON T2.id = T1.id
        WHERE T2.code IN ( 'TYPE', 'SYS' );

若要针对单表指定并行，语法应为/*+ PARALLEL(T1 8) PARALLEL(T2 8) */（表名与并行度间无逗号）。添加PARALLEL子句可让新表启用并行存储，避免后续操作性能瓶颈。

2. 新增覆盖索引减少IO #

你的过滤条件涉及T2.code，当前仅T2.id有索引，Oracle可能需要回表查询主表数据。建议创建覆盖复合索引，直接从索引获取所需列：

CREATE INDEX idx_t2_code_id ON TABLE2(code, id) INCLUDE (name);

该索引包含查询所需的code、id、name三列，无需访问主表，大幅降低磁盘IO开销。

3. 强制使用高效连接方式 #

百万级表连接时，哈希连接通常比嵌套循环更高效，可通过提示强制Oracle选择哈希连接：

CREATE TABLE temporary
    PARALLEL 8
    AS
        SELECT /*+ PARALLEL(8) USE_HASH(T1 T2) */
            T1.id,
            T2.name,
            T2.code    
        FROM TABLE1 T1
        JOIN TABLE2 T2 ON T2.id = T1.id
        WHERE T2.code IN ( 'TYPE', 'SYS' );

若T2过滤后的数据量远小于T1，可改用USE_NL(T2 T1)（小表驱动大表），但哈希连接更适配百万级数据场景。

4. 优化临时表存储配置 #

明确指定临时表类型为全局临时表，利用临时表空间存储数据，避免与永久表空间IO竞争：

CREATE GLOBAL TEMPORARY TABLE temporary
    (id NUMBER, name VARCHAR2(100), code VARCHAR2(20))
    ON COMMIT PRESERVE ROWS  -- 根据业务需求选择PRESERVE/DELETE
    PARALLEL 8
    AS
        SELECT /*+ PARALLEL(8) USE_HASH(T1 T2) */
            T1.id,
            T2.name,
            T2.code    
        FROM TABLE1 T1
        JOIN TABLE2 T2 ON T2.id = T1.id
        WHERE T2.code IN ( 'TYPE', 'SYS' );

同时确认临时表空间足够且开启自动扩展。

5. 确保统计信息与系统资源充足 #

• 并行度不要超过服务器CPU核心数的1.5倍，避免资源竞争；
• 更新两张表的统计信息，让Oracle生成最优执行计划：

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'TABLE1', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'TABLE2', CASCADE => TRUE);

内容的提问来源于stack exchange，提问作者Priya