记录一次关于标量子查询优化SQL的案例
前言
其实写SQL优化的实战很难，主要是很多情况是需要结合业务，而我们很多开发人员为了满足业务的需求随心随意的写SQL，就产生了很多质量较差的SQL，这些SQL可能在开发测试数据量不大的环境中正常运行，可是一到生产环境就直接把整个数据库服务器资源打满。所以我们平时在写SQL过程中多做简化工作，尽量让SQL写的让别人能读懂。多考虑条件的过滤包括关键字段索引等等，查询的数据量少了，减少了全表扫描了，自然效率也就高了。最后，要满足业务的需求又要写出易懂高质量的SQL。这其实是一个需要权衡的过程、需要做取舍。
问题现象
运维人员反馈EBS系统有一个请求运行时间很长，每次运行需要10多分钟。影响数据库整体性能和业务开展。
分析SQL
通过跟踪请求，拿到sql_id:gcppkx5y05x3k，把SQL拿出来大致看一下。 初步梳理，这一条非常复杂的插入SQL。看到一共有插入23个字段，主体SQL关联7个表和4个子查询。如下：
INSERT INTO CUX_TWOVOTE_SALEINVOICE_NEW (SUPPLIERID, SALINVOICENO, SALINVDATE, SALSUMVALUE, SALINVTYPE, SALCSTCODE, SALCSTNAME, GOODS, GOODNAME, VAT_HEADER_ID, VAT_LINE_ID, PO_LINE_ID, SOURCE_PO_LINE_ID, PO_HEADER_ID, SOURCE_PO_HEADER_ID, PO_NUMBER, ORG_ID, INTER_ORG_ID, ITEM_ID, LOT_NUMBER, REQUEST_ID, ATTRIBUTE1, ATTRIBUTE2)
SELECT CAVL.DEPARTMENT_CODE,
DECODE(
(SELECT COUNT(1)
FROM CUX_SC_MINHANG_CLIENT
WHERE ACCOUNT_NUMBER = CAVH.CUSTOMER_NUMBER), 0, CAVH.VAT_NUMBER, DECODE(INSTR(CAVH.INVOICE_CATEGORY, '全电'), 0, CAVH.VAT_INVOICE_CODE || CAVH.VAT_NUMBER, CAVH.VAT_NUMBER)) VAT_NUMBER,
TO_CHAR(CAVH.VAT_DATE, 'yyyy-mm-dd') VAT_DATE,
CAVH.AMOUNT_INC_TAX SALSUMVALUE,
COPL.PROPERTY7_NAME SALINVTYPE,
CAVH.CUSTOMER_NUMBER,
CAVH.CUSTOMER_NAME,
CAVL.ITEM_CODE || '|' || CAVL.PACKAGE_NUM ITEM_CODE,
CAVL.ITEM_DESC,
CAVH.VAT_HEADER_ID,
CAVL.VAT_LINE_ID,
DECODE(
(SELECT COUNT(1)
FROM CUX_OU_PROPERTY_LINES_T COUL, PO_LINES_ALL PL, PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PH.VENDOR_ID = COUL.ITEM_ID
AND PL.PO_LINE_ID = MLN.N_ATTRIBUTE6
AND COUL.PROPERTY_TYPE = 'V'
AND NVL(COUL.PROPERTY5_NAME, 'N') <> 'N'), 0, MLN.N_ATTRIBUTE6, NVL(COMR.PO_LINE_ID, MLN.N_ATTRIBUTE6)) PO_LINE_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.N_ATTRIBUTE6
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN
(SELECT MLN_SOURCE.N_ATTRIBUTE6
FROM MTL_LOT_NUMBERS MLN_SOURCE,
CUX_OE_MTL_RESERVATIONS COMR_SOURCE
WHERE MLN_SOURCE.LOT_NUMBER = COMR_SOURCE.LOT_NUMBER
AND MLN_SOURCE.INVENTORY_ITEM_ID = COMR_SOURCE.INVENTORY_ITEM_ID
AND MLN_SOURCE.ORGANIZATION_ID = COMR_SOURCE.ORGANIZATION_ID
AND COMR_SOURCE.RELATE_PO_LINE_ID = MLN.N_ATTRIBUTE6)
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.N_ATTRIBUTE6, COMR.PO_LINE_ID)
END AS SOURCE_PO_LINE_ID,
MLN.N_ATTRIBUTE5 PO_HEADER_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.N_ATTRIBUTE5
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN
(SELECT MLN_SOURCE.N_ATTRIBUTE5
FROM MTL_LOT_NUMBERS MLN_SOURCE,
CUX_OE_MTL_RESERVATIONS COMR_SOURCE
WHERE MLN_SOURCE.LOT_NUMBER = COMR_SOURCE.LOT_NUMBER
AND MLN_SOURCE.INVENTORY_ITEM_ID = COMR_SOURCE.INVENTORY_ITEM_ID
AND MLN_SOURCE.ORGANIZATION_ID = COMR_SOURCE.ORGANIZATION_ID
AND COMR_SOURCE.RELATE_PO_LINE_ID = MLN.N_ATTRIBUTE6)
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.N_ATTRIBUTE5, COMR.PO_HEADER_ID)
END AS SOURCE_PO_HEADER_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.C_ATTRIBUTE1
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN
(SELECT MLN_SOURCE.C_ATTRIBUTE1
FROM MTL_LOT_NUMBERS MLN_SOURCE,
CUX_OE_MTL_RESERVATIONS COMR_SOURCE
WHERE MLN_SOURCE.LOT_NUMBER = COMR_SOURCE.LOT_NUMBER
AND MLN_SOURCE.INVENTORY_ITEM_ID = COMR_SOURCE.INVENTORY_ITEM_ID
AND MLN_SOURCE.ORGANIZATION_ID = COMR_SOURCE.ORGANIZATION_ID
AND COMR_SOURCE.RELATE_PO_LINE_ID = MLN.N_ATTRIBUTE6)
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.C_ATTRIBUTE1, '')
END AS PO_NUMBER,
COMR.ORG_ID,
COMR.INTERCOMPANY_ORG_ID,
CAVL.ITEM_ID,
CAVL.LOT_NUMBER,
:B1,
CAVH.VAT_INVOICE_CODE,
CAVH.VAT_NUMBER
FROM CUX_AR_VAT_HEADERS CAVH,
CUX_AR_VAT_LINES CAVL,
CUX_OE_MTL_RESERVATIONS COMR,
CUX_OE_ORDER_HEADERS COOH,
MTL_LOT_NUMBERS MLN,
CUX_MST_ITEM ISI,
CUX_FND_LOOKUP_VALUES SCP,
(SELECT ITEM_ID,
PROPERTY7_NAME,
OU_ID
FROM CUX_OU_PROPERTY_LINES_T
WHERE PROPERTY_TYPE = 'C'
AND NVL(PROPERTY7_NAME, 'N') <> 'N') COPL,
(SELECT T.TRANSACTION_TYPE_ID
FROM OE_TRANSACTION_TYPES_ALL TT,
OE_TRANSACTION_TYPES_TL T,
FND_DOCUMENT_SEQUENCES A
WHERE TT.TRANSACTION_TYPE_ID = T.TRANSACTION_TYPE_ID
AND T.LANGUAGE = USERENV('LANG')
AND TT.ATTRIBUTE1 = A.DOC_SEQUENCE_ID
AND TT.TRANSACTION_TYPE_CODE = 'ORDER'
AND TT.ATTRIBUTE15 <> '01') TT_TYPE,
(SELECT HOU.ORGANIZATION_ID,
FLV.MEANING
FROM HR_ORGANIZATION_UNITS_V HOU,
CUX_FND_LOOKUP_VALUES FLV
WHERE HOU.ATTRIBUTE6 = FLV.LOOKUP_CODE
AND FLV.LOOKUP_TYPE = 'MST-0031'
AND HOU.ATTRIBUTE6 IS NOT NULL) COM1,
(SELECT HOU.ORGANIZATION_ID,
FLV.MEANING
FROM HR_ORGANIZATION_UNITS_V HOU,
CUX_FND_LOOKUP_VALUES FLV
WHERE HOU.ATTRIBUTE6 = FLV.LOOKUP_CODE
AND FLV.LOOKUP_TYPE = 'MST-0031'
AND HOU.ATTRIBUTE6 IS NOT NULL) COM2
WHERE CAVH.VAT_HEADER_ID = CAVL.VAT_HEADER_ID
AND CAVH.VAT_HEADER_ID = COMR.VAT_HEADER_ID
AND COMR.HEADER_ID = COOH.HEADER_ID
AND COMR.SOURCE_LINE_ID = CAVL.ORDER_LINE_ID
AND COMR.INVENTORY_ITEM_ID = MLN.INVENTORY_ITEM_ID
AND COMR.ORGANIZATION_ID = MLN.ORGANIZATION_ID
AND COMR.LOT_NUMBER = MLN.LOT_NUMBER
AND COMR.INVENTORY_ITEM_ID = ISI.INVENTORY_ITEM_ID
AND COMR.ORGANIZATION_ID = ISI.ORGANIZATION_ID
AND ISI.OPERATE_SCOPE = SCP.LOOKUP_CODE
AND SCP.LOOKUP_TYPE = 'MST-0004'
AND SCP.SEGMENT4 = 'Y'
AND COOH.ORDER_TYPE_ID = TT_TYPE.TRANSACTION_TYPE_ID
AND NVL(COOH.ORDER_SOURCE_REFERENCE, 'N') NOT IN ('QUICK_ORDER',
'QUICK_RET')
AND CAVH.CUSTOMER_ID = COPL.ITEM_ID
AND CAVH.ORG_ID = COPL.OU_ID
AND COMR.ORG_ID = COM1.ORGANIZATION_ID
AND COMR.INTERCOMPANY_ORG_ID = COM2.ORGANIZATION_ID
AND CAVH.ENABLED_FLAG = 'Y'
AND UPPER(CAVH.VAT_NUMBER) NOT LIKE 'X%'
AND UPPER(CAVH.VAT_NUMBER) NOT LIKE 'D%'
AND CAVH.VAT_NUMBER IS NOT NULL
AND CAVH.AMOUNT_INC_TAX > 0
AND CAVL.AMOUNT_INC_TAX > 0
AND MLN.C_ATTRIBUTE4 NOT IN
(SELECT MEANING
FROM CUX_FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AP-013'
AND TAG = 'SUP'
AND SEGMENT1 = 'Y'
AND ENABLED_FLAG = 'Y')
AND TRUNC(CAVH.LAST_UPDATE_DATE) >= TRUNC(SYSDATE - 1)
AND NOT EXISTS
(SELECT 1
FROM CUX_TWOVOTE_SALEINVOICE_NEW
WHERE VAT_HEADER_ID = CAVH.VAT_HEADER_ID
AND TRUNC(IMPORT_DATE) >= TRUNC(SYSDATE - 1))
使用SQLHC（SQL Tuning Health-Check）工具得到执行计划如下图
由于SQL复杂，对表和字段的定义无法从业务侧得知，如何取舍优化。只能从DBA角度来添加索引或者改写SQL逐步分析。
先对SELECT的23个字段部分开始：
上图执行计划里Id从12~26，写了多个标量子查询回填同几张表（MTL_LOT_NUMBERS，CUX_OE_MTL_RESERVATIONS）：等于重复访问同一数据源。如下部分
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.N_ATTRIBUTE6
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN
(SELECT MLN_SOURCE.N_ATTRIBUTE6
FROM MTL_LOT_NUMBERS MLN_SOURCE,
CUX_OE_MTL_RESERVATIONS COMR_SOURCE
WHERE MLN_SOURCE.LOT_NUMBER = COMR_SOURCE.LOT_NUMBER
AND MLN_SOURCE.INVENTORY_ITEM_ID = COMR_SOURCE.INVENTORY_ITEM_ID
AND MLN_SOURCE.ORGANIZATION_ID = COMR_SOURCE.ORGANIZATION_ID
AND COMR_SOURCE.RELATE_PO_LINE_ID = MLN.N_ATTRIBUTE6)
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.N_ATTRIBUTE6, COMR.PO_LINE_ID)
END AS SOURCE_PO_LINE_ID,
MLN.N_ATTRIBUTE5 PO_HEADER_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.N_ATTRIBUTE5
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN
(SELECT MLN_SOURCE.N_ATTRIBUTE5
FROM MTL_LOT_NUMBERS MLN_SOURCE,
CUX_OE_MTL_RESERVATIONS COMR_SOURCE
WHERE MLN_SOURCE.LOT_NUMBER = COMR_SOURCE.LOT_NUMBER
AND MLN_SOURCE.INVENTORY_ITEM_ID = COMR_SOURCE.INVENTORY_ITEM_ID
AND MLN_SOURCE.ORGANIZATION_ID = COMR_SOURCE.ORGANIZATION_ID
AND COMR_SOURCE.RELATE_PO_LINE_ID = MLN.N_ATTRIBUTE6)
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.N_ATTRIBUTE5, COMR.PO_HEADER_ID)
END AS SOURCE_PO_HEADER_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.C_ATTRIBUTE1
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN
(SELECT MLN_SOURCE.C_ATTRIBUTE1
FROM MTL_LOT_NUMBERS MLN_SOURCE,
CUX_OE_MTL_RESERVATIONS COMR_SOURCE
WHERE MLN_SOURCE.LOT_NUMBER = COMR_SOURCE.LOT_NUMBER
AND MLN_SOURCE.INVENTORY_ITEM_ID = COMR_SOURCE.INVENTORY_ITEM_ID
AND MLN_SOURCE.ORGANIZATION_ID = COMR_SOURCE.ORGANIZATION_ID
AND COMR_SOURCE.RELATE_PO_LINE_ID = MLN.N_ATTRIBUTE6)
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.C_ATTRIBUTE1, '')
END AS PO_NUMBER,
在查看标量子查询中表的数据量非常大，会造成大量的等待消耗。
SQL> select count(1) from CUX.CUX_OE_MTL_RESERVATIONS;
COUNT(1)
----------
45514896
SQL> select count(1) from INV.MTL_LOT_NUMBERS;
COUNT(1)
----------
11859933
SQL>
解释一下什么是标量子查询当一个子查询介于SELECT与FROM之间，这种子查询就叫标量子查询。
标量子查询类似一个天然的嵌套循环，而且驱动表固定为主表，嵌套循环被驱动表的连接列必须包含在索引中。同理，标量子查询中子查询的表连接列也必须包含在索引中。
建议在实际生产工作中，尽量避免使用标量子查询。原因是：假如主表返回大量数据，主表的连接列基数又很高，那么子查询中的表会被多次扫描，从而严重影响SQL性能；如果主表数据量小，或者主表的连接列基数很低，那么这个时候也可以使用标量子查询，但要给子查询中表的连接列建立索引。
当SQL里面有标量子查询，可以将标量子查询等价改写为外连接，从而使它们可以进行HASH连接。
- 标量子查询=外层每一行都要去子表再查一次
- 外层行数一大 + 子表一大 ⇒ 执行次数爆炸
执行计划中Id第51行部分CUX_OU_PROPERTY_LINES_T全表扫描
再看WHERE条件部分Id在100行部分，查看CUX_TWOVOTE_SALEINVOICE_NEW的数据量也非常大。走全表扫描也非常消耗IO
SQL> select count(1) from CUX.CUX_TWOVOTE_SALEINVOICE_NEW;
COUNT(1)
----------
15734505
SQL>
优化SQL
在我们通过执行计划加上表数据量和索引等信息分析后，做如下处理。
把SELECT后重复关联标量子查询3个字段SOURCE_PO_LINE_ID、SOURCE_PO_HEADER_ID、PO_NUMBER，通过left join方式，避免重复的扫描，改写后重复部分的子查询作为主表，如下：
SELECT
...
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.N_ATTRIBUTE6
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN MLN_SOURCE.N_ATTRIBUTE6
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.N_ATTRIBUTE6, COMR.PO_LINE_ID)
END AS SOURCE_PO_LINE_ID,
MLN.N_ATTRIBUTE5 PO_HEADER_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.N_ATTRIBUTE5
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN MLN_SOURCE.N_ATTRIBUTE5
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.N_ATTRIBUTE5, COMR.PO_HEADER_ID)
END AS SOURCE_PO_HEADER_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.C_ATTRIBUTE1
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN MLN_SOURCE.C_ATTRIBUTE1
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.C_ATTRIBUTE1, '')
END AS PO_NUMBER,
...
FROM
...
(SELECT COMR_SOURCE.RELATE_PO_LINE_ID,
MLN_SOURCE.N_ATTRIBUTE6,
MLN_SOURCE.N_ATTRIBUTE5,
MLN_SOURCE.C_ATTRIBUTE1
FROM MTL_LOT_NUMBERS MLN_SOURCE,
CUX_OE_MTL_RESERVATIONS COMR_SOURCE
WHERE MLN_SOURCE.LOT_NUMBER = COMR_SOURCE.LOT_NUMBER
AND MLN_SOURCE.INVENTORY_ITEM_ID = COMR_SOURCE.INVENTORY_ITEM_ID
AND MLN_SOURCE.ORGANIZATION_ID = COMR_SOURCE.ORGANIZATION_ID) MLN_SOURCE
WHERE
...
AND MLN.N_ATTRIBUTE6 = MLN_SOURCE.RELATE_PO_LINE_ID(+)
CUX_OU_PROPERTY_LINES_T全表扫描
DECODE(
(SELECT COUNT(1)
FROM CUX_OU_PROPERTY_LINES_T COUL, PO_LINES_ALL PL, PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PH.VENDOR_ID = COUL.ITEM_ID
AND PL.PO_LINE_ID = MLN.N_ATTRIBUTE6
AND COUL.PROPERTY_TYPE = 'V'
AND NVL(COUL.PROPERTY5_NAME, 'N') <> 'N'), 0, MLN.N_ATTRIBUTE6, NVL(COMR.PO_LINE_ID, MLN.N_ATTRIBUTE6)) PO_LINE_ID,
可以在PROPERTY_TYPE字段上添加索引，并重新统计信息：
SQL> create index CUX.CUX_OU_PROPERTY_LINES_T_N03 on CUX.CUX_OU_PROPERTY_LINES_T (PROPERTY_TYPE)
tablespace CUXD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
); 2 3 4 5 6 7 8 9 10 11 12
Index created.
SQL> BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'CUX',
tabname => 'CUX_OU_PROPERTY_LINES_T',
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade => TRUE,
no_invalidate => FALSE
);
END;
/ 2 3 4 5 6 7 8 9 10 11
PL/SQL procedure successfully completed.
SQL>
WHERE条件后的CUX_TWOVOTE_SALEINVOICE_NEW全表扫描
AND NOT EXISTS
(SELECT 1
FROM CUX_TWOVOTE_SALEINVOICE_NEW
WHERE VAT_HEADER_ID = CAVH.VAT_HEADER_ID
AND TRUNC(IMPORT_DATE) >= TRUNC(SYSDATE - 1))
可以在VAT_HEADER_ID字段上添加索引，并重新统计信息：
SQL> create index CUX.CUX_TWOVOTE_SALEINVOICE_NEW_N4 on CUX.CUX_TWOVOTE_SALEINVOICE_NEW (VAT_HEADER_ID)
tablespace CUXD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
); 2 3 4 5 6 7 8 9 10 11 12 13
Index created.
SQL> BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'CUX',
tabname => 'CUX_TWOVOTE_SALEINVOICE_NEW',
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade => TRUE,
no_invalidate => FALSE
);
END;
/ 2 3 4 5 6 7 8 9 10 11
PL/SQL procedure successfully completed.
SQL>
改写后最终sql_id:3xb8q2021ztpv。如下：
INSERT INTO CUX_TWOVOTE_SALEINVOICE_NEW (SUPPLIERID, SALINVOICENO, SALINVDATE, SALSUMVALUE, SALINVTYPE, SALCSTCODE, SALCSTNAME, GOODS, GOODNAME, VAT_HEADER_ID, VAT_LINE_ID, PO_LINE_ID, SOURCE_PO_LINE_ID, PO_HEADER_ID, SOURCE_PO_HEADER_ID, PO_NUMBER, ORG_ID, INTER_ORG_ID, ITEM_ID, LOT_NUMBER, REQUEST_ID, ATTRIBUTE1, ATTRIBUTE2)
SELECT CAVL.DEPARTMENT_CODE,
DECODE(
(SELECT COUNT(1)
FROM CUX_SC_MINHANG_CLIENT
WHERE ACCOUNT_NUMBER = CAVH.CUSTOMER_NUMBER), 0, CAVH.VAT_NUMBER, DECODE(INSTR(CAVH.INVOICE_CATEGORY, '全电'), 0, CAVH.VAT_INVOICE_CODE || CAVH.VAT_NUMBER, CAVH.VAT_NUMBER)) VAT_NUMBER,
TO_CHAR(CAVH.VAT_DATE, 'yyyy-mm-dd') VAT_DATE,
CAVH.AMOUNT_INC_TAX SALSUMVALUE,
COPL.PROPERTY7_NAME SALINVTYPE,
CAVH.CUSTOMER_NUMBER,
CAVH.CUSTOMER_NAME,
CAVL.ITEM_CODE || '|' || CAVL.PACKAGE_NUM ITEM_CODE,
CAVL.ITEM_DESC,
CAVH.VAT_HEADER_ID,
CAVL.VAT_LINE_ID,
DECODE(
(SELECT COUNT(1)
FROM CUX_OU_PROPERTY_LINES_T COUL, PO_LINES_ALL PL, PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PH.VENDOR_ID = COUL.ITEM_ID
AND PL.PO_LINE_ID = MLN.N_ATTRIBUTE6
AND COUL.PROPERTY_TYPE = 'V'
AND NVL(COUL.PROPERTY5_NAME, 'N') <> 'N'), 0, MLN.N_ATTRIBUTE6, NVL(COMR.PO_LINE_ID, MLN.N_ATTRIBUTE6)) PO_LINE_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.N_ATTRIBUTE6
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN MLN_SOURCE.N_ATTRIBUTE6
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.N_ATTRIBUTE6, COMR.PO_LINE_ID)
END AS SOURCE_PO_LINE_ID,
MLN.N_ATTRIBUTE5 PO_HEADER_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.N_ATTRIBUTE5
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN MLN_SOURCE.N_ATTRIBUTE5
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.N_ATTRIBUTE5, COMR.PO_HEADER_ID)
END AS SOURCE_PO_HEADER_ID,
CASE
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 = MLN.N_ATTRIBUTE7 THEN MLN.C_ATTRIBUTE1
WHEN COMR.ORG_ID = COMR.INTERCOMPANY_ORG_ID
AND MLN.N_ATTRIBUTE1 <> MLN.N_ATTRIBUTE7 THEN MLN_SOURCE.C_ATTRIBUTE1
ELSE DECODE(COM1.MEANING, COM2.MEANING, MLN.C_ATTRIBUTE1, '')
END AS PO_NUMBER,
COMR.ORG_ID,
COMR.INTERCOMPANY_ORG_ID,
CAVL.ITEM_ID,
CAVL.LOT_NUMBER,
:B1,
CAVH.VAT_INVOICE_CODE,
CAVH.VAT_NUMBER
FROM CUX_AR_VAT_HEADERS CAVH,
CUX_AR_VAT_LINES CAVL,
CUX_OE_MTL_RESERVATIONS COMR,
CUX_OE_ORDER_HEADERS COOH,
MTL_LOT_NUMBERS MLN,
CUX_MST_ITEM ISI,
CUX_FND_LOOKUP_VALUES SCP,
(SELECT ITEM_ID,
PROPERTY7_NAME,
OU_ID
FROM CUX_OU_PROPERTY_LINES_T
WHERE PROPERTY_TYPE = 'C'
AND NVL(PROPERTY7_NAME, 'N') <> 'N') COPL,
(SELECT T.TRANSACTION_TYPE_ID
FROM OE_TRANSACTION_TYPES_ALL TT,
OE_TRANSACTION_TYPES_TL T,
FND_DOCUMENT_SEQUENCES A
WHERE TT.TRANSACTION_TYPE_ID = T.TRANSACTION_TYPE_ID
AND T.LANGUAGE = USERENV('LANG')
AND TT.ATTRIBUTE1 = A.DOC_SEQUENCE_ID
AND TT.TRANSACTION_TYPE_CODE = 'ORDER'
AND TT.ATTRIBUTE15 <> '01') TT_TYPE,
(SELECT HOU.ORGANIZATION_ID,
FLV.MEANING
FROM HR_ORGANIZATION_UNITS_V HOU,
CUX_FND_LOOKUP_VALUES FLV
WHERE HOU.ATTRIBUTE6 = FLV.LOOKUP_CODE
AND FLV.LOOKUP_TYPE = 'MST-0031'
AND HOU.ATTRIBUTE6 IS NOT NULL) COM1,
(SELECT HOU.ORGANIZATION_ID,
FLV.MEANING
FROM HR_ORGANIZATION_UNITS_V HOU,
CUX_FND_LOOKUP_VALUES FLV
WHERE HOU.ATTRIBUTE6 = FLV.LOOKUP_CODE
AND FLV.LOOKUP_TYPE = 'MST-0031'
AND HOU.ATTRIBUTE6 IS NOT NULL) COM2,
(SELECT COMR_SOURCE.RELATE_PO_LINE_ID,
MLN_SOURCE.N_ATTRIBUTE6,
MLN_SOURCE.N_ATTRIBUTE5,
MLN_SOURCE.C_ATTRIBUTE1
FROM MTL_LOT_NUMBERS MLN_SOURCE,
CUX_OE_MTL_RESERVATIONS COMR_SOURCE
WHERE MLN_SOURCE.LOT_NUMBER = COMR_SOURCE.LOT_NUMBER
AND MLN_SOURCE.INVENTORY_ITEM_ID = COMR_SOURCE.INVENTORY_ITEM_ID
AND MLN_SOURCE.ORGANIZATION_ID = COMR_SOURCE.ORGANIZATION_ID) MLN_SOURCE
WHERE CAVH.VAT_HEADER_ID = CAVL.VAT_HEADER_ID
AND CAVH.VAT_HEADER_ID = COMR.VAT_HEADER_ID
AND COMR.HEADER_ID = COOH.HEADER_ID
AND COMR.SOURCE_LINE_ID = CAVL.ORDER_LINE_ID
AND COMR.INVENTORY_ITEM_ID = MLN.INVENTORY_ITEM_ID
AND COMR.ORGANIZATION_ID = MLN.ORGANIZATION_ID
AND COMR.LOT_NUMBER = MLN.LOT_NUMBER
AND COMR.INVENTORY_ITEM_ID = ISI.INVENTORY_ITEM_ID
AND COMR.ORGANIZATION_ID = ISI.ORGANIZATION_ID
AND ISI.OPERATE_SCOPE = SCP.LOOKUP_CODE
AND SCP.LOOKUP_TYPE = 'MST-0004'
AND SCP.SEGMENT4 = 'Y'
AND COOH.ORDER_TYPE_ID = TT_TYPE.TRANSACTION_TYPE_ID
AND NVL(COOH.ORDER_SOURCE_REFERENCE, 'N') NOT IN ('QUICK_ORDER',
'QUICK_RET')
AND CAVH.CUSTOMER_ID = COPL.ITEM_ID
AND CAVH.ORG_ID = COPL.OU_ID
AND COMR.ORG_ID = COM1.ORGANIZATION_ID
AND COMR.INTERCOMPANY_ORG_ID = COM2.ORGANIZATION_ID
AND MLN.N_ATTRIBUTE6 = MLN_SOURCE.RELATE_PO_LINE_ID(+)
AND CAVH.ENABLED_FLAG = 'Y'
AND UPPER(CAVH.VAT_NUMBER) NOT LIKE 'X%'
AND UPPER(CAVH.VAT_NUMBER) NOT LIKE 'D%'
AND CAVH.VAT_NUMBER IS NOT NULL
AND CAVH.AMOUNT_INC_TAX > 0
AND CAVL.AMOUNT_INC_TAX > 0
AND MLN.C_ATTRIBUTE4 NOT IN
(SELECT MEANING
FROM CUX_FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AP-013'
AND TAG = 'SUP'
AND SEGMENT1 = 'Y'
AND ENABLED_FLAG = 'Y')
AND TRUNC(CAVH.LAST_UPDATE_DATE) >= TRUNC(SYSDATE - 1)
AND NOT EXISTS
(SELECT 1
FROM CUX_TWOVOTE_SALEINVOICE_NEW
WHERE VAT_HEADER_ID = CAVH.VAT_HEADER_ID
AND TRUNC(IMPORT_DATE) >= TRUNC(SYSDATE - 1))
优化后执行计划如下
优化前后，对比平均执行时间。573s → 98s，提升近6倍。
总结
在SQL优化中经常会碰到一些复杂的、而且业务逻辑无法理解的SQL。不要急，通过执行计划一部分一部分的来优化。把全表扫描、标量子查询和驱动表的关系等搞清楚。多尝试改写的方法、尽量过滤查询数据。这样再复杂的SQL也有办法进行优化。
根因：相关标量子查询在大结果集上被重复执行，反复访问大表（4551w/1185w），放大 IO/CPU。
关键动作：把 3 处重复标量子查询抽成可复用派生表并
LEFT JOIN，减少重复扫描。配套动作：为全表扫描热点补索引并重新收集统计信息（含
VAT_HEADER_ID、PROPERTY_TYPE）。效果：平均耗时 573s → 98s（≈6x）。
最后修改时间：2025-12-09 09:40:58
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
