SQL语句运行速度特别慢，里面嵌套很多，而且全都是用的or。怎么能让速度快起来呢？急急急

Question

INSERT /*+APPEND*/
INTO stage_two.LATREE
(AGENTCODE,
AgentGrade,
AGENTGRADERSN,
AGENTGROUP,
AgentKind,
AgentLastGrade,
AgentLastSeries,
AGENTLEVEL,
AGENTLINE,
AgentSeries,
ASCRIPTSERIES,
AssessType,
ASTARTDATE,
BRANCHCODE,
BranchType,
BranchType2,
ConnManagerState,
CONNSUCCDATE,
DIFFICULTY,
EDUMANAGER,
InitGrade,
InsideFlag,
INTROAGENCY,
INTROBREAKFLAG,
INTROCOMMEND,
INTROCOMMSTART,
isConnMan,
JOINTRAINFLAG,
LECTUERFLAG,
LECTUERGRADE,
MAKEDATE,
MAKETIME,
MANAGECOM,
MODIFYDATE,
MODIFYTIME,
MSTARTDATE,
OLDENDDATE,
OLDMENDDATE,
OLDMSTARTDATE,
OLDSTARTDATE,
OPERATOR,
OTHUPAGENT,
PROMOTETRAINFLAG,
REARBREAKFLAG,
REARCOMMEND,
REARCOMMSTART,
SPECIFLAG,
STARTDATE,
STARTDATE1,
AgentState,
TOLLFLAG,
TOLLSCOPE,
UPAGENT,
VIPPROPERTY,
WORKGROUP,
DTL_CAPXTIMESTAMP,
DTL_CAPXACTION,
DTL_SEQ)
SELECT /*+PARALLEL(a,4)*/
AGENTCODE,
NVL((SELECT code_id
FROM ods.code
WHERE code_scheme_id = 302000
AND (code_value_text =
(SELECT DISTINCT SUBSTR(MANAGECOM, 1, 4)
FROM STAGE_ONE.LABranchGroup
WHERE AGENTGROUP = A.AGENTGROUP) || TRIM(a.AGENTGRADE) OR(code_value_text IS NULL AND (a.AGENTGRADE IS NULL) OR Length(Trim(a.AGENTGRADE)) = 0))),
302000 + 1),
AGENTGRADERSN,
AGENTGROUP,
NVL((SELECT code_id
FROM ods.code
WHERE code_scheme_id = 303000
AND (code_value_text = (SELECT DISTINCT SUBSTR(MANAGECOM, 1, 4)
FROM STAGE_ONE.LABranchGroup
WHERE AGENTGROUP = A.AGENTGROUP) ||
TRIM(a.agentgrade) || TRIM(a.AGENTKIND) OR
(code_value_text IS NULL AND (a.AGENTKIND IS NULL) OR
Length(Trim(a.AGENTKIND)) = 0))),
303000 + 1),
NVL((SELECT code_id
FROM ods.code
WHERE code_scheme_id = 302000
AND (code_value_text = (SELECT DISTINCT SUBSTR(MANAGECOM, 1, 4)
FROM STAGE_ONE.LABranchGroup
WHERE AGENTGROUP = A.AGENTGROUP) ||
TRIM(a.AGENTLASTGRADE) OR
(code_value_text IS NULL AND (a.AGENTLASTGRADE IS NULL) OR
Length(Trim(a.AGENTLASTGRADE)) = 0))),
302000 + 1),
NVL((SELECT code_id
FROM ods.code
WHERE code_scheme_id = 309000
AND (code_value_text = TRIM(a.AGENTLASTSERIES) OR
(code_value_text IS NULL AND (a.AGENTLASTSERIES IS NULL) OR
Length(Trim(a.AGENTLASTSERIES)) = 0))),
309000 + 1),
AGENTLEVEL,
AGENTLINE,
NVL((SELECT code_id
FROM ods.code
WHERE code_scheme_id = 309000
AND (code_value_text = TRIM(a.AGENTSERIES) OR
(code_value_text IS NULL AND (a.AGENTSERIES IS NULL) OR
Length(Trim(a.AGENTSERIES)) = 0))),
309000 + 1),
ASCRIPTSERIES,
NVL((SELECT code_id
FROM ods.code
WHERE code_scheme_id = 310000
AND (code_value_text = TRIM(a.ASSESSTYPE) OR
(code_value_text IS NULL AND (a.ASSESSTYPE IS NULL) OR
Length(Trim(a.ASSESSTYPE)) = 0))),
310000 + 1),
ASTARTDATE,
BRANCHCODE,
NVL((SELECT code_id
FROM ods.code
WHERE code_scheme_id = 305000
AND (code_value_text = TRIM(a.BRANCHTYPE) OR
(code_value_text IS NULL AND (a.BRANCHTYPE IS NULL) OR
Length(Trim(a.BRANCHTYPE)) = 0))),
305000 + 1),
NVL((SELECT code_id
FROM ods.code
WHERE code_scheme_id = 306000
AND (code_value_text = TRIM(a.BRANCHTYPE2) OR
(code_value_text IS NULL AND (a.BRANCHTYPE2 IS NULL) OR
Length(Trim(a.BRANCHTYPE2)) = 0))),
306000 + 1),
NVL((SELECT code_id
FROM ods.code
WHERE code_scheme_id = 314000
AND (code_value_text = TRIM(a.CONNMANAGERSTATE) OR
(code_value_text IS NULL AND (a.CONNMANAGERSTATE IS NULL) OR
Length(Trim(a.CONNMANAGERSTATE)) = 0))),
314000 + 1),
CONNSUCCDATE,
DIFFICULTY,
EDUMANAGER,
NVL((SELECT code_id
FROM ods.code
WHERE code_scheme_id = 312000
AND (code_value_text =
(SELECT DISTINCT SUBSTR(MANAGECOM, 1, 4)
FROM STAGE_ONE.LABranchGroup
WHERE AGENTGROUP = A.AGENTGROUP) || TRIM(a.INITGRADE) OR
(code_value_text IS NULL AND (a.INITGRADE IS NULL) OR
Length(Trim(a.INITGRADE)) = 0))),
312000 + 1),
NVL((SELECT code_id
FROM ods.code
WHERE code_scheme_id = 313000
AND (code_value_text = TRIM(a.INSIDEFLAG) OR
(code_value_text IS NULL AND (a.INSIDEFLAG IS NULL) OR
Length(Trim(a.INSIDEFLAG)) = 0))),
313000 + 1),
INTROAGENCY,
INTROBREAKFLAG,
INTROCOMMEND,
INTROCOMMSTART,
NVL((SELECT code_id
FROM ods.code
WHERE code_scheme_id = 322000
AND (code_value_text = TRIM(a.ISCONNMAN) OR
(code_value_text IS NULL AND (a.ISCONNMAN IS NULL) OR
Length(Trim(a.ISCONNMAN)) = 0))),
322000 + 1),
JOINTRAINFLAG,
LECTUERFLAG,
LECTUERGRADE,
MAKEDATE,
MAKETIME,
MANAGECOM,
MODIFYDATE,
MODIFYTIME,
MSTARTDATE,
OLDENDDATE,
OLDMENDDATE,
OLDMSTARTDATE,
OLDSTARTDATE,
OPERATOR,
OTHUPAGENT,
PROMOTETRAINFLAG,
REARBREAKFLAG,
REARCOMMEND,
REARCOMMSTART,
SPECIFLAG,
STARTDATE,
STARTDATE1,
NVL((SELECT code_id
FROM ods.code
WHERE code_scheme_id = 311000
AND (code_value_text = TRIM(a.STATE) OR
(code_value_text IS NULL AND (a.STATE IS NULL) OR
Length(Trim(a.STATE)) = 0))),
311000 + 1),
TOLLFLAG,
TOLLSCOPE,
UPAGENT,
VIPPROPERTY,
WORKGROUP,
DTL_CAPXTIMESTAMP,
DTL_CAPXACTION,
DTL_SEQ
FROM stage_one.LATREE a
WHERE 1 = 1;

Answer 1

当 SQL 查询的执行速度特别慢，并且查询中嵌套了多个 OR 条件时，可以尝试以下优化策略来提高查询性能：

使用索引：确保查询涉及的列上存在适当的索引。索引可以加速数据检索，特别是在包含 OR 条件的查询中。评估查询计划并检查是否使用了正确的索引。可以使用 EXPLAIN 或查询计划工具来分析查询执行计划，确定是否需要调整索引或添加新索引。

优化 WHERE 子句：如果查询中有多个 OR 条件，尽量将其转换为更有效的方式。可以考虑使用 IN 子句或拆分查询为多个子查询。例如，将 WHERE column = value1 OR column = value2 转换为 WHERE column IN (value1, value2)。

调整查询顺序：重新排列查询条件的顺序，将最具选择性的条件放在前面。这样可以减少不必要的记录匹配和提前结束查询过程。

使用 UNION 替代 OR：如果可能的话，将多个 OR 条件拆分为多个单独的查询，并使用 UNION 连接结果。这样可以避免 OR 条件的低效执行，并充分利用索引。

限制结果集大小：如果查询结果集非常大，但实际上只需要部分结果，可以考虑添加限制条件或分页机制来减少返回的记录数。

优化数据库配置：根据实际情况，可能需要调整数据库的配置参数，如内存分配、并发连接数等，以提高查询性能。

数据库优化技术：利用数据库的优化功能，如统计信息收集、查询缓存、分区表等，来提高查询性能。

使用适当的硬件和资源：确保数据库服务器具备足够的硬件资源（CPU、内存、磁盘等）来支持查询的高效执行。

数据库索引维护：定期进行索引维护和优化，如重新组织索引、删除冗余索引等，以保持索引的有效性。

优化 SQL 语句：仔细审查 SQL 语句，确保查询条件、连接和子查询等部分逻辑正确，避免不必要的计算和数据处理。