Oracle Exadata 19c多次同表连接查询的高效调优方案咨询

我正在使用Oracle Exadata 19c，现有一条SELECT查询需将TRAN表与ADDR表按不同条件进行多次左外连接，返回多组分支（BRANCH）和地址（ADDR）列数据。我曾尝试用OR条件和多CASE语句改写该查询，虽成本较低但执行时间更长。想咨询：

1. 该查询是否还能进一步调优？
2. 针对所需输出，是否必须8次引用ADDR表？

CREATE TABLE TRAN (
    C1 VARCHAR2(50) NOT NULL,
    C2 VARCHAR2(50),
    C3 VARCHAR2(50),
    C4 VARCHAR2(50),
    C5 VARCHAR2(50),
    C6 VARCHAR2(50),
    C7 VARCHAR2(50),
    C8 VARCHAR2(50),
    C9 VARCHAR2(50)
);

CREATE TABLE ADDR (
    C1 VARCHAR2(50) NOT NULL,
    C2 VARCHAR2(50),
    BRANCH VARCHAR2(50),
    ADDR VARCHAR2(50)
);

insert into TRAN (C1, C2, C3, C4, C5, C6, C7, C8, C9) values ('A111', 'Q1', 'Q1', 'Q1', 'Q1', 'Q1', 'Q1', 'Q1', 'Q1');
insert into TRAN (C1, C2, C3, C4, C5, C6, C7, C8, C9) values ('A222', 'Q2', 'Q2', 'Q2', null, null, null, null, 'Q2');
insert into TRAN (C1, C2, C3, C4, C5, C6, C7, C8, C9) values ('A333', 'Q3', 'Q3', 'Q3', 'Q3', 'Q3', 'Q3', 'Q3', 'Q3');
insert into TRAN (C1, C2, C3, C4, C5, C6, C7, C8, C9) values ('A444', null, null, null, null, 'Q4', 'Q4', 'Q4', 'Q4');

insert into ADDR (C1, C2, BRANCH, ADDR) values ('A111', 'Q1', 'CHN', 'INDIA');
insert into ADDR (C1, C2, BRANCH, ADDR) values ('A222', 'Q2','BLR', 'USA');
insert into ADDR (C1, C2, BRANCH, ADDR) values ('A444', 'Q4', 'HYD', 'UK');

 commit;

WITH T1 as (SELECT tran.* FROM tran),
   T2 as (SELECT ADDR.* FROM ADDR)

SELECT 
    T1.C1,
    T21.BRANCH AS BRANCH1,
    T21.ADDR AS ADDR1,
    
    T22.BRANCH AS BRANCH2,
    T22.ADDR AS ADDR2,
    
    T23.BRANCH AS BRANCH3,
    T23.ADDR AS ADDR3,

    T24.BRANCH AS BRANCH4,
    T24.ADDR AS ADDR4,

    T25.BRANCH AS BRANCH5,
    T25.ADDR AS ADDR5,

    T26.BRANCH AS BRANCH6,
    T26.ADDR AS ADDR6,

    T27.BRANCH AS BRANCH7,
    T27.ADDR AS ADDR7,

    T28.BRANCH AS BRANCH8,
    T28.ADDR AS ADDR8

FROM T1
LEFT OUTER JOIN T2 T21 ON T1.C1 = T21.C1 AND T1.C2 = T21.C2
LEFT OUTER JOIN T2 T22 ON T1.C1 = T22.C1 AND T1.C3 = T22.C2
LEFT OUTER JOIN T2 T23 ON T1.C1 = T23.C1 AND T1.C4 = T23.C2
LEFT OUTER JOIN T2 T24 ON T1.C1 = T24.C1 AND T1.C5 = T24.C2
LEFT OUTER JOIN T2 T25 ON T1.C1 = T25.C1 AND T1.C6 = T25.C2
LEFT OUTER JOIN T2 T26 ON T1.C1 = T26.C1 AND T1.C7 = T26.C2
LEFT OUTER JOIN T2 T27 ON T1.C1 = T27.C1 AND T1.C8 = T27.C2
LEFT OUTER JOIN T2 T28 ON T1.C1 = T28.C1 AND T1.C9 = T28.C2;

1. 是否必须8次引用ADDR表？ #

不需要。你可以通过将TRAN表的C2-C9列转成行，再与ADDR表做一次连接，最后将结果转回列的方式，仅引用ADDR表一次就能得到目标输出。这种方式避免了多次左连接的冗余处理，数据量越大优势越明显。

2. 查询调优方案 #

方案一：转置+连接+逆转置（推荐）

利用Oracle的UNPIVOT将TRAN的C2-C9列拆分为多行，关联ADDR后再用PIVOT转回多列，示例代码如下：

SELECT
    C1,
    BRANCH1, ADDR1,
    BRANCH2, ADDR2,
    BRANCH3, ADDR3,
    BRANCH4, ADDR4,
    BRANCH5, ADDR5,
    BRANCH6, ADDR6,
    BRANCH7, ADDR7,
    BRANCH8, ADDR8
FROM (
    SELECT
        t.C1,
        seq,
        a.BRANCH,
        a.ADDR
    FROM (
        -- 将TRAN的C2-C9转成行，生成序号标记列位置
        SELECT
            C1,
            col_val,
            ROW_NUMBER() OVER (PARTITION BY C1 ORDER BY col_name) AS seq
        FROM TRAN
        UNPIVOT (
            col_val FOR col_name IN (
                C2 AS 'C2', C3 AS 'C3', C4 AS 'C4', C5 AS 'C5',
                C6 AS 'C6', C7 AS 'C7', C8 AS 'C8', C9 AS 'C9'
            )
        )
    ) t
    LEFT JOIN ADDR a ON t.C1 = a.C1 AND t.col_val = a.C2
)
PIVOT (
    MAX(BRANCH) AS BRANCH, MAX(ADDR) AS ADDR
    FOR seq IN (
        1 AS "1", 2 AS "2", 3 AS "3", 4 AS "4",
        5 AS "5", 6 AS "6", 7 AS "7", 8 AS "8"
    )
)
ORDER BY C1;

方案二：优化原查询的基础性能

如果倾向保留原多次连接结构，可做以下优化：

• 移除冗余CTE：原查询中T1和T2完全冗余，直接引用表即可，减少解析开销。
• 添加复合索引：在ADDR表上创建(C1, C2)的复合索引，并覆盖BRANCH和ADDR列，语句为：CREATE INDEX idx_addr_c1_c2 ON ADDR(C1, C2) INCLUDE (BRANCH, ADDR);，让每次连接都能通过索引快速获取数据，避免全表扫描。
• 利用Exadata特性：确保统计信息最新，让CBO生成最优执行计划；开启Smart Scan特性提升大表扫描效率。

性能对比说明 #

• 转置连接方式：数据量较大时，比8次左连接效率更高，因为仅需扫描ADDR表一次，减少I/O次数。
• 优化后的原查询：小数据量场景下性能可接受，但数据量增长后，多次连接的开销会显著上升。

内容的提问来源于stack exchange，提问作者Naveen K Reddy