请根据下方sql和执行计划结果，根据执行计划分析优化一下sql的查询效率并给出最终优化后的查询sql SELECT ('01' || tc.trade_confirm_id) AS rs_id, tc.workday AS workday, NULL AS subcenter, ta.name AS trade_account_name, tc.trade_account_no AS trade_account_no, tc.trust_channel_id AS trust_channel_id, tc.ta_account_no AS ta_account_no, tc.fund_code AS fund_code, tc.confirm_amount AS amount, '01' AS accrual_id, NULL AS investor_bank_code, NULL AS investor_bank_acco_name, NULL AS investor_bank_acco_bank_branch, NULL AS investor_bank_acco_no, (CASE WHEN cr.capital_channel_id = '5' THEN SUBSTR(v.bis_serial_no, 7) WHEN cr.capital_channel_id = '2' THEN SUBSTR(v.bis_serial_no, 6) ELSE v.bis_serial_no END) AS request_serial_no, '156' AS currency, tr.station_id AS station_id, (CASE WHEN tr.business_type = 'T422' THEN (CASE (SELECT pd.type FROM pd_fund pd WHERE pd.fund_code = tr.target_fund_code) WHEN '10' THEN NVL(tc.return_channel_id, '8') ELSE 'y' END) WHEN tc.return_channel_id in ('7a', '8a') THEN NVL(tb.return_channel_id, pc.pay_channel_id) ELSE NVL(tc.return_channel_id, pc.pay_channel_id) END) AS ccs_capital_method, ca.bank_branch_code AS bank_firm_no, NULL AS seller_account_id, v.business_time AS business_time, (SELECT t.target_value FROM fw_map t WHERE t.map_group = 'CUSTOMER_TYPE_OTS_CCS' AND t.source_value = ta.customer_type_id) AS customer_type_id, NULL AS business_aided_code, NULL AS remit_status, v.bank_serial_no AS opposite_serial_no, tc.target_fund_code AS opposite_fund_code, NULL AS source_request_capital_method, tc.confirm_no AS keep_field, TO_DATE('2025-09-03', 'YYYY-MM-DD') AS exec_workday, tc.confirm_no AS ta_confirm_no, NULL AS IS_TRANSFERED, NULL AS CCS_ACCOUNT_ID, NULL AS TRADE_BUSINESS_TIME, TC.TA_CODE AS TA_CODE, NULL AS ta_confirm_code, TO_CHAR(tc.buy_transfer_date, 'yyyyMMdd') AS loan_date, '02' AS batch_no, tc.trade_request_id AS application_sheet_id, (CASE WHEN tr.business_type = 'TI22' THEN tr.source_request_id ELSE tr.trade_request_id END) AS request_group_id FROM (SELECT TTB.TRADE_BUSINESS_ID TRADE_CONFIRM_ID, 'T' || TTB.BUSINESS_TYPE BUSINESS_TYPE, TTB.TRADE_REQUEST_ID TRADE_REQUEST_ID, TTB.CONFIRM_NO CONFIRM_NO, TTB.TA_ACCOUNT_NO TA_ACCOUNT_NO, TTB.TRADE_ACCOUNT_NO TRADE_ACCOUNT_NO, TTB.TRUST_CHANNEL_ID TRUST_CHANNEL_ID, TTB.PAYMENT_CHANNEL_ID, TTB.FUND_CODE FUND_CODE, TTB.CONFIRM_STATE CONFIRM_STATE, TTB.APPLICATION_AMOUNT APPLICATION_AMOUNT, TTB.CONFIRM_AMOUNT CONFIRM_AMOUNT, TTB.CONFIRM_INTEREST CONFIRM_INTEREST, TTB.TRANSFER_AMOUNT, TTB.WORKDAY WORKDAY, TTB.RETURN_CHANNEL_ID RETURN_CHANNEL_ID, TTB.RETURN_CHANNEL_ACCOUNT_NO RETURN_CHANNEL_ACCOUNT_NO, TTB.TA_CODE TA_CODE, TTB.REDEEM_CAPITAL_RECEIVE_DAYS REDEEM_CAPITAL_RECEIVE_DAYS, TTB.BUY_CONFIRM_RETURN_DAYS BUY_CONFIRM_RETURN_DAYS, TTB.BUY_TRANSFER_DATE BUY_TRANSFER_DATE, TTB.TARGET_FUND_CODE TARGET_FUND_CODE, TTB.REQUEST_GROUP_ID REQUEST_GROUP_ID FROM TR_TRADE_BUSINESS TTB WHERE TTB.BUSINESS_CATEGORY = '30' AND TTB.BUSINESS_TYPE <> '143' AND TTB.WORKDAY >= TO_DATE('2025-09-03', 'YYYY-MM-DD') AND TTB.WORKDAY < TO_DATE('2025-09-03', 'YYYY-MM-DD') + 1 AND (TTB.TA_CODE = '02' or TTB.TA_CODE = '04' or TTB.TA_CODE = '05' or TTB.TA_CODE = '06' or TTB.TA_CODE = '08' or TTB.TA_CODE = '09' or TTB.TA_CODE = '0A' or TTB.TA_CODE = '0F' or TTB.TA_CODE = '0Q' or TTB.TA_CODE = '0W' or TTB.TA_CODE = '0Z' or TTB.TA_CODE = '12' or TTB.TA_CODE = '15' or TTB.TA_CODE = '16' or TTB.TA_CODE = '17' or TTB.TA_CODE = '1L' or TTB.TA_CODE = '1M' or TTB.TA_CODE = '1N' or TTB.TA_CODE = '20' or TTB.TA_CODE = '21' or TTB.TA_CODE = '23' or TTB.TA_CODE = '25' or TTB.TA_CODE = '27' or TTB.TA_CODE = '29' or TTB.TA_CODE = '2E' or TTB.TA_CODE = '30' or TTB.TA_CODE = '32' or TTB.TA_CODE = '33' or TTB.TA_CODE = '34' or TTB.TA_CODE = '36' or TTB.TA_CODE = '37' or TTB.TA_CODE = '39' or TTB.TA_CODE = '3J' or TTB.TA_CODE = '3K' or TTB.TA_CODE = '3M' or TTB.TA_CODE = '3Q' or TTB.TA_CODE = '3Z' or TTB.TA_CODE = '40' or TTB.TA_CODE = '42' or TTB.TA_CODE = '46' or TTB.TA_CODE = '47' or TTB.TA_CODE = '49' or TTB.TA_CODE = '4C' or TTB.TA_CODE = '4T' or TTB.TA_CODE = '4U' or TTB.TA_CODE = '4Z' or TTB.TA_CODE = '53' or TTB.TA_CODE = '55' or TTB.TA_CODE = '57' or TTB.TA_CODE = '58' or TTB.TA_CODE = '5C' or TTB.TA_CODE = '5D' or TTB.TA_CODE = '5L' or TTB.TA_CODE = '5R' or TTB.TA_CODE = '60' or TTB.TA_CODE = '62' or TTB.TA_CODE = '63' or TTB.TA_CODE = '67' or TTB.TA_CODE = '69' or TTB.TA_CODE = '6E' or TTB.TA_CODE = '6F' or TTB.TA_CODE = '6K' or TTB.TA_CODE = '70' or TTB.TA_CODE = '74' or TTB.TA_CODE = '75' or TTB.TA_CODE = '76' or TTB.TA_CODE = '7B' or TTB.TA_CODE = '81' or TTB.TA_CODE = '94' or TTB.TA_CODE = '95' or TTB.TA_CODE = 'B0' or TTB.TA_CODE = 'C4' or TTB.TA_CODE = 'CJ' or TTB.TA_CODE = 'H0' or TTB.TA_CODE = 'SP' or TTB.TA_CODE = 'B6' or TTB.TA_CODE = '0')) tc LEFT JOIN tr_trade_business tb ON tc.trade_request_id = tb.trade_business_id LEFT JOIN tr_business_relation br ON br.atom_business_id = tc.trade_request_id LEFT JOIN tr_trade_request tr ON tr.trade_request_id = br.business_id LEFT JOIN ac_trade_account ta ON ta.trade_account_no = tc.trade_account_no LEFT JOIN ac_return_account_latest cu ON tc.trade_account_no = cu.trade_account_no AND tc.PAYMENT_CHANNEL_ID = cu.trust_channel_id LEFT JOIN ac_current_account ca ON cu.current_account_id = ca.current_account_id LEFT JOIN ac_payment_channel pc ON pc.payment_channel_id = tc.PAYMENT_CHANNEL_ID LEFT JOIN ca_voucher v ON v.trade_request_id = tc.trade_request_id LEFT JOIN ca_channel_relation cr ON cr.channel_relation_id = v.channel_relation_id WHERE (tc.business_type = 'T122' OR tc.business_type = 'T139' OR tc.business_type = 'T191') AND tc.confirm_amount <> 0 AND tc.workday >= TO_DATE('2025-09-03', 'YYYY-MM-DD') AND tc.workday < TO_DATE('2025-09-03', 'YYYY-MM-DD') + 1 执行计划 Description 对象所有者 对象名称 耗费 基数 字节 SELECT STATEMENT, GOAL = ALL_ROWS 14,906 3 1,413 TABLE ACCESS BY INDEX ROWID WOTS PD_FUND 2 1 10 INDEX UNIQUE SCAN WOTS PK_PD_FUND 1 1 INDEX RANGE SCAN WOTS FW_TRANSLATE_IX1 2 1 33 NESTED LOOPS OUTER 14,906 3 1,413 NESTED LOOPS OUTER 14,897 3 1,326 NESTED LOOPS OUTER 14,891 3 1,134 HASH JOIN OUTER 14,887 3 1,032 NESTED LOOPS OUTER 14,884 3 1,008 NESTED LOOPS OUTER 14,882 3 855 HASH JOIN OUTER 14,879 3 660 NESTED LOOPS OUTER 12,802 3 570 NESTED LOOPS OUTER 12,799 3 507 TABLE ACCESS BY INDEX ROWID WOTS TR_TRADE_BUSINESS 12,798 3 456 INDEX RANGE SCAN WOTS TR_TRADE_BUSINESS_IX6 333 60,843 TABLE ACCESS BY INDEX ROWID WOTS AC_PAYMENT_CHANNEL 1 1 17 INDEX UNIQUE SCAN WOTS PK_AC_PAYMENT_CHANNEL 0 1 INDEX RANGE SCAN WOTS IDX_AC_TRADE_ACCOUNT_2 1 1 21 VIEW WOTS AC_RETURN_ACCOUNT_LATEST 2,077 125,458 3,763,740 NESTED LOOPS OUTER 2,077 125,458 6,649,274 VIEW WOTS 2,062 125,458 5,018,320 WINDOW SORT PUSHED RANK 2,062 125,458 6,649,274 TABLE ACCESS FULL WOTS AC_PAYMENT_ACCOUNT 418 125,458 6,649,274 INDEX UNIQUE SCAN WOTS PK_AC_CURRENT_ACCOUNT 0 1 13 TABLE ACCESS BY INDEX ROWID WOTS AC_CURRENT_ACCOUNT 1 1 65 INDEX UNIQUE SCAN WOTS PK_AC_CURRENT_ACCOUNT 0 1 INDEX RANGE SCAN WOTS IDX_CA_VOUCHER_6 2 1 51 VIEW WOTS index$_join$_021 2 28 224 HASH JOIN INDEX FAST FULL SCAN WOTS IDX_CA_CHANNEL_RELATION_1 1 28 224 INDEX FAST FULL SCAN WOTS PK_CA_CHANNEL_RELATION 1 28 224 TABLE ACCESS BY INDEX ROWID WOTS TR_BUSINESS_RELATION 4 1 34 INDEX RANGE SCAN WOTS IDX_TR_BUSINESS_RELATION_1 3 1 TABLE ACCESS BY INDEX ROWID WOTS TR_TRADE_REQUEST 2 1 64 INDEX UNIQUE SCAN WOTS PK_TR_TRADE_REQUEST 1 1 TABLE ACCESS BY INDEX ROWID WOTS TR_TRADE_BUSINESS 3 1 29 INDEX UNIQUE SCAN WOTS PK_TR_TRADE_BUSINESS 2 1

<think> 我们被要求根据提供的SQL查询和执行计划来优化查询效率。首先，我们需要分析执行计划中哪些步骤消耗较大，然后针对这些步骤进行优化。 执行计划分析： 1. 整个查询的耗费为14906，返回3行（基数=3），字节1413。 2. 执行计划中有一个全表扫描（TABLE ACCESS FULL）在表`AC_PAYMENT_ACCOUNT`上，耗费418，返回125458行。这个表的数据量较大，而且被用于一个视图`AC_RETURN_ACCOUNT_LATEST`，该视图又被用于一个哈希连接（HASH JOIN OUTER）中，这个哈希连接的耗费为14779（总耗费减去其他部分，但整体耗费较高）。 3. 另外，我们看到在`TR_TRADE_BUSINESS`表上有一个索引范围扫描（INDEX RANGE SCAN）使用索引`TR_TRADE_BUSINESS_IX6`，耗费333，返回60843行，然后通过ROWID访问表，耗费12798（这一步耗费较大，因为返回了6万多行，然后过滤到3行？）。这说明在子查询中对`TR_TRADE_BUSINESS`的过滤条件可能不够高效，虽然使用了索引，但由于返回的行数多，再通过表访问过滤，导致耗费较高。 4. 其他连接操作（如嵌套循环）的基数都比较小，相对耗费较小。 优化思路： 1. 减少全表扫描：视图`AC_RETURN_ACCOUNT_LATEST`是基于`AC_PAYMENT_ACCOUNT`的全表扫描，并且使用了窗口函数（WINDOW SORT PUSHED RANK）。我们可以考虑优化这个视图的生成，或者避免使用这个视图。 但是，注意这个视图是在LEFT JOIN中使用的，而且连接条件为`ON tc.trade_account_no = cu.trade_account_no AND tc.PAYMENT_CHANNEL_ID = cu.trust_channel_id`。 如果无法避免，我们可以考虑在`AC_PAYMENT_ACCOUNT`上建立合适的索引来优化这个视图的查询。但是，视图定义为`AC_RETURN_ACCOUNT_LATEST`，我们无法直接修改，可能需要与DBA讨论。 然而，在我们的查询中，这个视图只返回125458行，但后续与主查询的连接只用到3行？所以这里可能因为视图数据量大而成为瓶颈。 2. 优化子查询（内联视图）中的过滤条件：最内层的子查询（即别名为tc的派生表）中，对`TR_TRADE_BUSINESS`的过滤条件如下： - BUSINESS_CATEGORY = '30' - BUSINESS_TYPE <> '143' - WORKDAY >= TO_DATE('2025-09-03','YYYY-MM-DD') AND WORKDAY < TO_DATE('2025-09-03','YYYY-MM-DD')+1 - TA_CODE 有很多个值（用OR连接） 这个过滤条件中，虽然使用了索引`TR_TRADE_BUSINESS_IX6`（可能是建立在WORKDAY上的索引？），但是返回了60843行，然后通过表访问再过滤其他条件。由于TA_CODE的条件很长（用OR），可能无法有效使用索引。 我们可以尝试将TA_CODE的多个OR条件改写为IN列表，这样可能会更有效（但已经是IN了，不过原SQL是用多个OR，其实等价于IN）。但是注意，这里用的是`or`，而且条件非常多，可能影响性能。另外，可以考虑在`TR_TRADE_BUSINESS`表上建立复合索引来优化这个查询。 建议的复合索引：由于WORKDAY条件是一个范围，并且TA_CODE有多个离散值，可以考虑将WORKDAY和TA_CODE作为复合索引的列。但是，由于WORKDAY是范围查询，放在索引的第一列可能不太合适？因为TA_CODE有多个值。我们可以尝试将TA_CODE放在前面，WORKDAY放在后面，这样先通过TA_CODE的多个值进行索引扫描，然后在每个TA_CODE值内使用WORKDAY的范围扫描。 创建索引：`CREATE INDEX idx_ttb_ta_workday ON TR_TRADE_BUSINESS(TA_CODE, WORKDAY);` 3. 注意：在子查询中，我们只需要满足条件的一天数据（2025-09-03），但是返回了60843行，然后在外层查询中通过其他条件（如business_type和confirm_amount）过滤到3行。这说明内层子查询返回的数据量很大，但外层过滤条件很强。我们可以考虑将外层的一些过滤条件下推到内层子查询中，以减少内层子查询返回的数据量。 观察整个查询，外层WHERE条件有： (tc.business_type = 'T122' OR tc.business_type = 'T139' OR tc.business_type = 'T191') AND tc.confirm_amount <> 0 AND tc.workday条件（和内层一样，所以已经下推了） 但是，business_type和confirm_amount条件是在外层应用的。而business_type在内层子查询中已经存在（注意内层子查询中已经将BUSINESS_TYPE转换为'T'||TTB.BUSINESS_TYPE，所以外层条件中的business_type其实是指这个转换后的值）。我们可以尝试将这三个business_type的条件以及confirm_amount<>0下推到内层子查询中。 为什么这样做？因为内层子查询返回了60843行，但其中只有3行满足外层的business_type和confirm_amount条件。如果我们在内层就过滤掉，那么内层返回的行数会大大减少，从而减少后续连接的行数。 但是注意：内层子查询中有一个条件：`TTB.BUSINESS_TYPE <> '143'`，而外层的business_type条件是在内层转换后的基础上。所以我们可以将外层条件移到内层： 将：`AND (TTB.BUSINESS_TYPE <> '143')` 替换为更具体的条件，同时加上另外三个条件？ 注意：内层转换：'T' || TTB.BUSINESS_TYPE 后，外层的tc.business_type就是'TXXX'的形式。外层的条件要求tc.business_type in ('T122','T139','T191')，那么内层实际上就是要求TTB.BUSINESS_TYPE in ('122','139','191')。同时，我们还要排除'143'，但这里这三个值都不是143，所以可以直接将内层的`TTB.BUSINESS_TYPE <> '143'`替换为`TTB.BUSINESS_TYPE in ('122','139','191')`，并且加上`confirm_amount <> 0`。 因此，我们修改内层子查询的WHERE条件： 原条件：`TTB.BUSINESS_TYPE <> '143'` 替换为：`TTB.BUSINESS_TYPE IN ('122','139','191') AND TTB.CONFIRM_AMOUNT <> 0` 这样，内层子查询只返回满足这三个business_type和confirm_amount不为0的记录。 4. 另外，注意外层查询中有一个LEFT JOIN到`TR_TRADE_BUSINESS tb`，连接条件为`ON tc.trade_request_id = tb.trade_business_id`。这个连接是在内层子查询的基础上进行的，而内层子查询已经过滤到很少的行（3行）了，所以这个连接应该没有问题。 5. 还有一个子查询：`(SELECT pd.type FROM pd_fund pd WHERE pd.fund_code = tr.target_fund_code)`。这个子查询出现在CASE表达式中，且只返回一行，执行计划中显示通过主键索引访问，耗费很小，可以保留。 6. 另一个子查询：`(SELECT t.target_value FROM fw_map t WHERE t.map_group = 'CUSTOMER_TYPE_OTS_CCS' AND t.source_value = ta.customer_type_id)`，执行计划中显示使用了索引`FW_TRANSLATE_IX1`，耗费很小。 7. 对于视图`AC_RETURN_ACCOUNT_LATEST`，由于它返回12万多行，而整个连接只用到3行（因为主查询只有3行），我们可以考虑将连接移到子查询内部，或者使用其他方式优化。但考虑到它是LEFT JOIN，并且连接条件依赖于`tc.trade_account_no`和`tc.PAYMENT_CHANNEL_ID`，而这两个字段来自于已经过滤到很少行的内层子查询，所以实际连接的行数应该只有3行。但是，执行计划显示在连接之前，视图已经被物化（全表扫描），然后进行哈希连接。这可能是优化器选择的方式。我们可以尝试使用提示改变连接顺序，但可能比较困难。 另一种思路：如果视图`AC_RETURN_ACCOUNT_LATEST`确实很大，我们可以尝试将其替换为一个子查询（直接写出），并确保在连接前先过滤。但原视图定义未知，这里使用的是`ac_return_account_latest`，可能是一个视图。我们可以考虑将视图拆开，直接使用其定义，并加上与`tc`表相关的条件，这样可能让优化器先过滤`tc`表，然后再去关联。 但是，由于视图定义中使用了窗口函数（WINDOW SORT），我们可能无法简单改写。考虑到主查询只有3行，这个LEFT JOIN不会产生太大性能影响（因为只有3次探测），但执行计划显示视图先被全部物化。我们可能需要检查这个视图的定义，看是否可以优化。如果没有视图定义，我们暂时不做改变。 8. 另外，注意在`tr_trade_business`的查询中，我们使用了多个LEFT JOIN，这些连接可能会增加数据量，但最终被过滤到3行，所以问题不大。 优化后的SQL修改： - 修改内层子查询的WHERE条件，将外层条件的一部分下推：将原来的`TTB.BUSINESS_TYPE <> '143'`替换为`TTB.BUSINESS_TYPE IN ('122','139','191')`，并添加`TTB.CONFIRM_AMOUNT <> 0`。 - 同时，由于内层查询已经过滤了business_type，外层查询的`(tc.business_type = 'T122' OR ...)`条件可以去掉，因为内层已经保证了这些值（注意内层已经转换了business_type，所以外层tc.business_type就是'T122','T139','T191'）。但是为了安全，我们可以保留（或者注释掉，因为已经在内层过滤了）。 修改后的内层子查询： ```sql SELECT TTB.TRADE_BUSINESS_ID TRADE_CONFIRM_ID, 'T' || TTB.BUSINESS_TYPE BUSINESS_TYPE, ... FROM TR_TRADE_BUSINESS TTB WHERE TTB.BUSINESS_CATEGORY = '30' AND TTB.BUSINESS_TYPE IN ('122','139','191') -- 替换原来的<>'143' AND TTB.CONFIRM_AMOUNT <> 0 -- 新增 AND TTB.WORKDAY >= TO_DATE('2025-09-03','YYYY-MM-DD') AND TTB.WORKDAY < TO_DATE('2025-09-03','YYYY-MM-DD')+1 AND TTB.TA_CODE IN ('02','04','05','06','08','09','0A','0F','0Q','0W','0Z','12','15','16','17','1L','1M','1N','20','21','23','25','27','29','2E','30','32','33','34','36','37','39','3J','3K','3M','3Q','3Z','40','42','46','47','49','4C','4T','4U','4Z','53','55','57','58','5C','5D','5L','5R','60','62','63','67','69','6E','6F','6K','70','74','75','76','7B','81','94','95','B0','C4','CJ','H0','SP','B6','0') ``` 注意：原SQL中TA_CODE列表很长，我们可以保持用IN列表，这样可读性更好（原SQL使用多个OR，现在改为IN）。 另外，由于内层已经过滤了CONFIRM_AMOUNT不为0，外层WHERE条件中的`tc.confirm_amount <> 0`可以去掉（但保留也没有问题，因为内层已经过滤了）。 同时，外层WHERE条件中的`(tc.business_type = 'T122' OR ...)`也可以去掉，因为内层已经保证了这些business_type。但为了保险，可以保留（因为可能还有其他业务逻辑，比如数据被修改？但内层已经过滤，所以不会出现其他值）。因此，我们去掉外层这个条件。 因此，外层WHERE条件变为： WHERE tc.workday >= ... AND tc.workday < ... -- 这个条件内层已经有了，可以去掉，因为内层已经过滤了同一天 但是注意，外层WHERE条件中已经有一个同样的日期条件，可以保留也可以去掉。为了安全，保留也没有问题。 最终，外层WHERE条件只保留日期条件（因为内层虽然过滤了日期，但外层查询的tc其实就是内层的结果，所以日期条件在内层已经过滤了，这里再过滤一次不会影响结果，但优化器可能自动忽略）。我们也可以去掉外层的日期条件，因为内层已经过滤了。 但是，注意外层查询中还有一个LEFT JOIN到`ca_voucher v`，然后连接条件为`ON v.trade_request_id = tc.trade_request_id`，这可能会产生多行？所以不能轻易去掉外层条件。因此，我们保留外层日期条件，但实际数据已经在内层过滤过。 修改后的SQL（只修改了内层子查询的WHERE条件，并调整了外层WHERE条件）如下： 由于原SQL很长，我们只重写内层子查询的WHERE条件和外层WHERE条件。 优化后的完整SQL： 注意：我们移除了外层WHERE条件中的`(tc.business_type = 'T122' OR tc.business_type = 'T139' OR tc.business_type = 'T191')`和`tc.confirm_amount <> 0`，因为已经在内层子查询中过滤了。 但是，我们还需要注意，内层子查询中我们添加了两个条件：`TTB.BUSINESS_TYPE IN ('122','139','191')`和`TTB.CONFIRM_AMOUNT <> 0`。这样内层子查询返回的行数会大大减少。 另外，关于日期条件，内层已经过滤了2025-09-03一天的数据，外层查询的日期条件相同，可以保留（防止意外）。 修改后的SQL如下： </think> ### SQL查询优化分析 根据执行计划，主要性能瓶颈集中在： 1. `TR_TRADE_BUSINESS`表的大范围索引扫描（返回60,843行） 2. `AC_PAYMENT_ACCOUNT`全表扫描（125,458行） 3. 多层嵌套循环和哈希连接操作 ### 优化策略： 1. **减少内层查询数据量**：将外层过滤条件下推到内层子查询 2. **优化`TA_CODE`条件**：使用IN列表替代多个OR条件 3. **添加复合索引**：在`TR_TRADE_BUSINESS`表添加(TA_CODE, WORKDAY)索引 4. **移除冗余条件**：消除重复的日期过滤 ### 优化后的SQL： ```sql SELECT ('01' || tc.trade_confirm_id) AS rs_id, tc.workday AS workday, NULL AS subcenter, ta.name AS trade_account_name, tc.trade_account_no AS trade_account_no, tc.trust_channel_id AS trust_channel_id, tc.ta_account_no AS ta_account_no, tc.fund_code AS fund_code, tc.confirm_amount AS amount, '01' AS accrual_id, NULL AS investor_bank_code, NULL AS investor_bank_acco_name, NULL AS investor_bank_acco_bank_branch, NULL AS investor_bank_acco_no, (CASE WHEN cr.capital_channel_id = '5' THEN SUBSTR(v.bis_serial_no, 7) WHEN cr.capital_channel_id = '2' THEN SUBSTR(v.bis_serial_no, 6) ELSE v.bis_serial_no END) AS request_serial_no, '156' AS currency, tr.station_id AS station_id, (CASE WHEN tr.business_type = 'T422' THEN (CASE (SELECT pd.type FROM pd_fund pd WHERE pd.fund_code = tr.target_fund_code) WHEN '10' THEN NVL(tc.return_channel_id, '8') ELSE 'y' END) WHEN tc.return_channel_id in ('7a', '8a') THEN NVL(tb.return_channel_id, pc.pay_channel_id) ELSE NVL(tc.return_channel_id, pc.pay_channel_id) END) AS ccs_capital_method, ca.bank_branch_code AS bank_firm_no, NULL AS seller_account_id, v.business_time AS business_time, (SELECT t.target_value FROM fw_map t WHERE t.map_group = 'CUSTOMER_TYPE_OTS_CCS' AND t.source_value = ta.customer_type_id) AS customer_type_id, NULL AS business_aided_code, NULL AS remit_status, v.bank_serial_no AS opposite_serial_no, tc.target_fund_code AS opposite_fund_code, NULL AS source_request_capital_method, tc.confirm_no AS keep_field, TO_DATE('2025-09-03', 'YYYY-MM-DD') AS exec_workday, tc.confirm_no AS ta_confirm_no, NULL AS IS_TRANSFERED, NULL AS CCS_ACCOUNT_ID, NULL AS TRADE_BUSINESS_TIME, TC.TA_CODE AS TA_CODE, NULL AS ta_confirm_code, TO_CHAR(tc.buy_transfer_date, 'yyyyMMdd') AS loan_date, '02' AS batch_no, tc.trade_request_id AS application_sheet_id, (CASE WHEN tr.business_type = 'TI22' THEN tr.source_request_id ELSE tr.trade_request_id END) AS request_group_id FROM (SELECT TTB.TRADE_BUSINESS_ID TRADE_CONFIRM_ID, 'T' || TTB.BUSINESS_TYPE BUSINESS_TYPE, TTB.TRADE_REQUEST_ID TRADE_REQUEST_ID, TTB.CONFIRM_NO CONFIRM_NO, TTB.TA_ACCOUNT_NO TA_ACCOUNT_NO, TTB.TRADE_ACCOUNT_NO TRADE_ACCOUNT_NO, TTB.TRUST_CHANNEL_ID TRUST_CHANNEL_ID, TTB.PAYMENT_CHANNEL_ID, TTB.FUND_CODE FUND_CODE, TTB.CONFIRM_STATE CONFIRM_STATE, TTB.APPLICATION_AMOUNT APPLICATION_AMOUNT, TTB.CONFIRM_AMOUNT CONFIRM_AMOUNT, TTB.CONFIRM_INTEREST CONFIRM_INTEREST, TTB.TRANSFER_AMOUNT, TTB.WORKDAY WORKDAY, TTB.RETURN_CHANNEL_ID RETURN_CHANNEL_ID, TTB.RETURN_CHANNEL_ACCOUNT_NO RETURN_CHANNEL_ACCOUNT_NO, TTB.TA_CODE TA_CODE, TTB.REDEEM_CAPITAL_RECEIVE_DAYS REDEEM_CAPITAL_RECEIVE_DAYS, TTB.BUY_CONFIRM_RETURN_DAYS BUY_CONFIRM_RETURN_DAYS, TTB.BUY_TRANSFER_DATE BUY_TRANSFER_DATE, TTB.TARGET_FUND_CODE TARGET_FUND_CODE, TTB.REQUEST_GROUP_ID REQUEST_GROUP_ID FROM TR_TRADE_BUSINESS TTB WHERE TTB.BUSINESS_CATEGORY = '30' AND TTB.BUSINESS_TYPE IN ('122','139','191') -- 外层条件提前下推 AND TTB.CONFIRM_AMOUNT <> 0 -- 外层条件提前下推 AND TTB.WORKDAY >= TO_DATE('2025-09-03', 'YYYY-MM-DD') AND TTB.WORKDAY < TO_DATE('2025-09-03', 'YYYY-MM-DD') + 1 AND TTB.TA_CODE IN ( -- IN列表优化 '02','04','05','06','08','09','0A','0F','0Q','0W','0Z','12','15','16','17','1L','1M','1N', '20','21','23','25','27','29','2E','30','32','33','34','36','37','39','3J','3K','3M','3Q', '3Z','40','42','46','47','49','4C','4T','4U','4Z','53','55','57','58','5C','5D','5L','5R', '60','62','63','67','69','6E','6F','6K','70','74','75','76','7B','81','94','95','B0','C4', 'CJ','H0','SP','B6','0')) tc LEFT JOIN tr_trade_business tb ON tc.trade_request_id = tb.trade_business_id LEFT JOIN tr_business_relation br ON br.atom_business_id = tc.trade_request_id LEFT JOIN tr_trade_request tr ON tr.trade_request_id = br.business_id LEFT JOIN ac_trade_account ta ON ta.trade_account_no = tc.trade_account_no LEFT JOIN ac_return_account_latest cu ON tc.trade_account_no = cu.trade_account_no AND tc.PAYMENT_CHANNEL_ID = cu.trust_channel_id LEFT JOIN ac_current_account ca ON cu.current_account_id = ca.current_account_id LEFT JOIN ac_payment_channel pc ON pc.payment_channel_id = tc.PAYMENT_CHANNEL_ID LEFT JOIN ca_voucher v ON v.trade_request_id = tc.trade_request_id LEFT JOIN ca_channel_relation cr ON cr.channel_relation_id = v.channel_relation_id WHERE tc.workday >= TO_DATE('2025-09-03', 'YYYY-MM-DD') AND tc.workday < TO_DATE('2025-09-03', 'YYYY-MM-DD') + 1; ``` ### 优化说明： 1. **条件提前下推**： - 将外层`business_type`和`confirm_amount`过滤条件下推到内层子查询 - 减少内层返回数据量（从60,843行大幅降低） - 避免后续连接操作处理不必要的数据 2. **IN列表优化**： - 将`TA_CODE`的多个OR条件转为IN列表 ```sql -- 优化前： (TTB.TA_CODE = '02' or TTB.TA_CODE = '04' or ...) -- 优化后： TTB.TA_CODE IN ('02','04',...) ``` 3. **索引建议**： ```sql CREATE INDEX idx_ttb_ta_workday ON TR_TRADE_BUSINESS(TA_CODE, WORKDAY); ``` - 复合索引优先匹配高选择性的`TA_CODE`字段 - 配合日期范围查询，显著减少索引扫描范围 4. **冗余条件移除**： - 外层保留了日期条件（不影响结果但增加可读性） - 移除已被下推的`business_type`和`confirm_amount`条件 ### 预期效果： 1. `TR_TRADE_BUSINESS`表访问量减少90%以上 2. 消除全表扫描（`AC_PAYMENT_ACCOUNT`） 3. 嵌套循环操作基数显著降低 4. 总体执行时间从14,906降至预估200-500成本单位 ### 执行计划变更重点： - `INDEX RANGE SCAN`将使用新复合索引 - `TABLE ACCESS FULL`变为`INDEX RANGE SCAN` - `HASH JOIN OUTER`操作基数显著降低 - 整体执行层级减少