前言#
随着数字化转型和信创替代持续推进，越来越多业务系统开始从传统Oracle数据库迁移到国产数据库平台。数据库迁移完成并不意味着性能问题自然消失，尤其是一些在原有环境中执行正常的 SQL，迁移后可能因为优化器策略、索引设计或执行路径差异而出现明显性能下降。
本文结合一次真实的达梦慢 SQL 优化案例展开分析。某条查询语句在 Oracle 中执行仅需 0.3 秒，而在达梦中却需要 133 秒才能返回结果。通过 SQL Monitor、执行计划、表结构和索引设计分析，最终定位到问题根因是 SHOP_SALE_ORDER_DETAIL 表在缺少合适联合索引的情况下发生了大量 BLKUP2 回表操作。通过补充联合索引并重新收集统计信息，SQL 执行时间最终下降到毫秒级。本文将完整记录问题定位、原因分析和优化过程，给类似场景提供一个可复用的排查思路。
问题描述#
同样的 SQL 语句，在 Oracle 中执行仅需 0.3 秒，而在达梦中执行耗时约 134 秒。SQL语句如下：
select * from ( select TMP.*, ROWNUM as ROW_ID from ( select t.*, li.item_detail_name as statusDescIn, lo.item_detail_name as statusDescOut, li.sort_code as sortCodeIn, lo.sort_code as sortCodeOut from v_logis_shop_sale_order_detail t left join c_logistics_in li on t.logis_status_code_in = li.item_detail_code left join c_logistics_out lo on t.logis_status_code_out = lo.item_detail_code where t.SO_ID = '0002603046042426' order by SO_DET_NO asc ) TMP where ROWNUM <= 15 ) where ROW_ID > 0使用 DBMS_SQLTUNE 定位慢 SQL#
DBMS_SQLTUNE 包提供一系列实时 SQL 监控的方法。当 SQL 监控功能开启后，DBMS_SQLTUNE 包可以实时监控 SQL 执行过程中的信息，包括：执行时间、执行代价、执行用户、统计信息等情况。
ALTER SESSION SET 'MONITOR_SQL_EXEC' = 1;
<执行待优化SQL>
select DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_EXEC_ID=>1213701) from dual;先在会话级开启 SQL 监控参数 MONITOR_SQL_EXEC=1
ALTER SESSION SET 'MONITOR_SQL_EXEC' = 1;
执行待优化SQL，记住执行号下图红色部分。
select *
from (select tmp.*, rownum as row_id
from (select t.*, li.item_detail_name as statusdescin, lo.item_detail_name as statusdescout,
li.sort_code as sortcodein, lo.sort_code as sortcodeout
from v_logis_shop_sale_order_detail t
left join c_logistics_in li
on t.logis_status_code_in = li.item_detail_code
left join c_logistics_out lo
on t.logis_status_code_out = lo.item_detail_code
where t.so_id = '0002603046042426'
order by so_det_no asc) tmp
where rownum <= 15)
where row_id > 0
执行DBMS_SQLTUNE.REPORT_SQL_MONITOR，能够获取 IO 操作量，查看真实执行计划，每个操作符消耗占比和相应的花费时间，每个操作符执行的次数，双击红色部分<长文本>。如下图
select DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_EXEC_ID=>14763931) from dual;
我们可以得到DBMS_SQLTUNE完整的报告，我们另存为文本。打开文本文件如下：
报告如下：
SQL Monitoring Report
SQL Text
------------------------------
select *
from (select tmp.*, rownum as row_id
from (select t.*, li.item_detail_name as statusdescin, lo.item_detail_name as statusdescout,
li.sort_code as sortcodein, lo.sort_code as sortcodeout
from v_logis_shop_sale_order_detail t
left join c_logistics_in li
on t.logis_status_code_in = li.item_detail_code
left join c_logistics_out lo
on t.logis_status_code_out = lo.item_detail_code
where t.so_id = '0002603046042426'
order by so_det_no asc) tmp
where rownum <= 15)
where row_id > 0
Global Information
------------------------------
Status : DONE (ALL ROWS)
Session : RIS (281434248978120:157948)
SQL ID : 1596
SQL Execution ID : 14763931
Execution Started : 2026-03-17 19:26:25
Duration : 133.983008s
Program : manager.exe
Global Stats
=========================================================
| Affected | Bytes | Bytes | Physical | Logical |
| Rows | Allocate | Free | Read(page) | Read(page) |
=========================================================
| 0 | 0 | 0 | 0 | 40046153 |
=========================================================
SQL Plan Monitoring Details
=======================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
=======================================================================================================================================
| 0 | DLCK | | | | 0.000009 | +133.982851 | 2 | | 0.00 | |
| 1 | NSET2 | | 1 | 147 | 0.000145 | +133.982738 | 3 | 1 | 0.00 | |
| 2 | PIPE2 | | 1 | 147 | 0.000001 | +133.982738 | 5 | 1 | 0.00 | |
| 3 | PIPE2 | | 1 | 144 | 0.000001 | +133.982737 | 5 | 1 | 0.00 | |
| 4 | PRJT2 | | 1 | 141 | 0.000004 | +133.982736 | 4 | 1 | 0.00 | |
| 5 | SLCT2 | | 1 | 141 | 0.000020 | +133.982733 | 4 | 1 | 0.00 | |
| 6 | PRJT2 | | 15 | 141 | 0.000005 | +133.982732 | 4 | 1 | 0.00 | |
| 7 | RN | | 15 | 141 | 0.000017 | +133.982725 | 4 | 1 | 0.00 | |
| 8 | PRJT2 | | 15 | 141 | 0.000006 | +133.982723 | 4 | 1 | 0.00 | |
| 9 | TOPN2 | | 15 | 141 | 0.000031 | +133.982707 | 4 | 1 | 0.00 | |
| 10 | NLLO2 | | 300 | 141 | 0.000038 | +133.982704 | 6 | 1 | 0.00 | |
| 11 | NLLO2 | | 300 | 72 | 0.000016 | +133.982701 | 5 | 1 | 0.00 | |
| 12 | PRJT2 | | 300 | 4 | 0.000217 | +133.982699 | 4 | 1 | 0.00 | |
| 13 | IJI2 | | 300 | 4 | 0.000015 | +133.982696 | 6 | 1 | 0.00 | |
| 14 | SLCT2 | | 300 | 2 | 0.342479 | +133.982695 | 44486 | 1 | 0.26 | |
| 15 | BLKUP2 | SHOP_SALE_ORDER_DETAIL | 300 | 2 | 132.934822 | +133.982694 | 88968 | 13344613 | 99.27 | |
| 16 | SSCN | SHOP_SALE_ORDER_DETAIL | 300 | 2 | 0.630153 | +133.982692 | 44484 | 13344613 | 0.47 | |
| 17 | BLKUP2 | SHOP_GOODS | 1 | 1 | 0.000016 | +133.128555 | 4 | 1 | 0.00 | |
| 18 | SSEK2 | SHOP_GOODS | 1 | 1 | 0.000050 | +133.128555 | 2 | 1 | 0.00 | |
| 19 | PRJT2 | | 1 | 1 | 0.000001 | +133.128338 | 2 | | 0.00 | |
| 20 | SLCT2 | | 1 | 1 | 0.000001 | +133.128338 | 2 | | 0.00 | |
| 21 | IJI2 | | 1 | 1 | 0.000000 | +133.128338 | 4 | | 0.00 | |
| 22 | BLKUP2 | BASE_DATAITEM | 1 | 1 | 0.000012 | +133.128338 | 4 | 1 | 0.00 | |
| 23 | SSEK2 | BASE_DATAITEM | 1 | 1 | 0.000013 | +133.128338 | 2 | 1 | 0.00 | |
| 24 | BLKUP2 | BASE_DATAITEM_DETAIL | 1 | 1 | 0.000000 | +133.128337 | 2 | | 0.00 | |
| 25 | SLCT2 | | 1 | 1 | 0.000010 | +133.128337 | 3 | | 0.00 | |
| 26 | SSEK2 | BASE_DATAITEM_DETAIL | 1 | 1 | 0.000023 | +133.128336 | 2 | 8 | 0.00 | |
| 27 | PRJT2 | | 1 | 1 | 0.000002 | +133.128553 | 4 | 1 | 0.00 | |
| 28 | SLCT2 | | 1 | 1 | 0.000007 | +133.128552 | 4 | 1 | 0.00 | |
| 29 | IJI2 | | 1 | 1 | 0.000002 | +133.128552 | 6 | 1 | 0.00 | |
| 30 | BLKUP2 | BASE_DATAITEM | 1 | 1 | 0.000033 | +133.128552 | 4 | 1 | 0.00 | |
| 31 | SSEK2 | BASE_DATAITEM | 1 | 1 | 0.000007 | +133.128552 | 2 | 1 | 0.00 | |
| 32 | BLKUP2 | BASE_DATAITEM_DETAIL | 1 | 1 | 0.000015 | +133.128551 | 4 | 1 | 0.00 | |
| 33 | SLCT2 | | 1 | 1 | 0.000006 | +133.128551 | 4 | 1 | 0.00 | |
| 34 | SSEK2 | BASE_DATAITEM_DETAIL | 1 | 1 | 0.000017 | +133.128550 | 2 | 7 | 0.00 | |
| 35 | SPL2 | | | 3 | 0.000001 | +133.128268 | 4 | | 0.00 | |
| 36 | PRJT2 | | 1 | 3 | 0.000001 | +133.128268 | 4 | 1 | 0.00 | |
| 37 | AAGR2 | | 1 | 3 | 0.000009 | +133.128268 | 4 | 1 | 0.00 | |
| 38 | PRJT2 | | 1 | 3 | 0.000012 | +133.128265 | 4 | 6 | 0.00 | |
| 39 | SLCT2 | | 1 | 3 | 0.000014 | +133.128265 | 4 | 6 | 0.00 | |
| 40 | BLKUP2 | SHOP_LOGIS_STATUS_OUT | 2911 | 3 | 0.000065 | +133.128265 | 4 | 6 | 0.00 | |
| 41 | SSEK2 | SHOP_LOGIS_STATUS_OUT | 2911 | 3 | 0.000026 | +133.128264 | 2 | 6 | 0.00 | |
| 42 | SPL2 | | | 2 | 0.000009 | +133.128125 | 4 | | 0.00 | |
| 43 | PRJT2 | | 1 | 2 | 0.000003 | +133.128125 | 4 | 1 | 0.00 | |
| 44 | AAGR2 | | 1 | 2 | 0.000008 | +133.128124 | 3 | 1 | 0.00 | |
| 45 | PRJT2 | | 1 | 2 | 0.000001 | +133.128115 | 2 | | 0.00 | |
| 46 | SLCT2 | | 1 | 2 | 0.000001 | +133.128115 | 2 | | 0.00 | |
| 47 | BLKUP2 | SHOP_LOGIS_STATUS_IN | 2213 | 2 | 0.000001 | +133.128115 | 2 | | 0.00 | |
| 48 | SSEK2 | SHOP_LOGIS_STATUS_IN | 2213 | 2 | 0.000026 | +133.128088 | 1 | | 0.00 | |
=======================================================================================================================================这条SQL整体执行时间133s
BLKUP2 SHOP_SALE_ORDER_DETAIL = 132.93s，占 99.27%，执行 88968 次，实际累计处理量到 13344613。
也就是说，虽然你最后只取 15 行，但数据库为了拿到这 15 行，前面在 SHOP_SALE_ORDER_DETAIL 上做了非常大量的扫描 + 回表。监控里该节点的累计实际处理量到了 13344613，而且 BLKUP2 执行了 88968 次，这是非常典型的“索引不匹配 + 宽表回表”现象。
相关对象与数据规模分析#
我们分析SQL主要可以看到v_logis_shop_sale_order_detail和c_logistics_in，c_logistics_out这几个表关联查询，v_logis_shop_sale_order_detail是一个视图，记录数11341732。
select count(1) from v_logis_shop_sale_order_detail;
select count(1) from c_logistics_in;
select count(1) from c_logistics_out;
视图描述如下，视图核心表就是SHOP_SALE_ORDER_DETAIL
CREATE OR REPLACE VIEW "RIS"."V_LOGIS_SHOP_SALE_ORDER_DETAIL" ("SO_DET_ID","SO_ID","SO_CAT","SO_TYPE","SO_NO","SO_DET_NO","SO_DATE","SO_RELEASE_ID","SO_DET_STATUS","SO_DET_DESC","SO_GRP_ID","SO_SUB_GRP_ID","SUBMIT_BY","SUBMIT_DATE","LOGIS_STATUS","DOC_DEF_ID","ORDER_ID","ORDER_DET_ID","BUY_ORG_ID","STP_ORG_ID","SELL_ORG_ID","SELL_DEPT_ID","SELL_DEPT_CODE","SELL_DEPT_NAME","SELL_CST_CODE","SELL_CST_NAME","SELL_ADDR_ID","SELL_ADDR_CODE","SELL_ADDR_NAME","SELL_MEMBER_ID","SELL_MEMBER_CODE","SELL_MEMBER_NAME","SELL_GOODS_ID","SELL_GOODS_CODE","SELL_GOODS_NAME","SELL_GOODS_SPEC","SELL_GOODS_PACK_UNIT","SELL_GOODS_PACK_NUM","SELL_GOODS_BRAND","SELL_GOODS_RATIFIER","SELL_QTY","LOTO_NO","PRD_DATE","END_DATE","TAX_PRC","TAX_RATE","AMOUNT","TX_PRC","TX_AMOUNT","TAX","OUTER_NO","OUTER_DET_NO","RECIPE_NO","ORDER_FROM","INVOICE_CODE","INVOICE_NO","INVOICE_DATE","INVOICE_AMOUNT","MAP_FLAG","MAP_STATUS","MAP_CODE","MAP_DESC","IS_COLD_CHAIN","IS_TICKET","IS_MAP_TICKET","IS_MAP_BOX","TENANT_ID","CREATE_BY","CREATE_DATE","UPDATE_BY","UPDATE_DATE","EXP_STATUS","EXP_BATCHID","EXP_BY","EXP_DATE","EXP_RESULT","DET_ATTR1","DET_ATTR2","DET_ATTR3","DET_ATTR4","DET_ATTR5","LOGIS_OU_CODE","LOGIS_ORDER_NO","LOGIS_ORDER_DET_NO","GOODS_SEARCH_NAME","LOGIS_STATUS_CODE_IN","LOGIS_STATUS_CODE_OUT")
AS
select a.SO_DET_ID,
a.SO_ID,
a.SO_CAT,
a.SO_TYPE,
a.SO_NO,
a.SO_DET_NO,
a.SO_DATE,
a.SO_RELEASE_ID,
a.SO_DET_STATUS,
a.SO_DET_DESC,
a.SO_GRP_ID,
a.SO_SUB_GRP_ID,
a.SUBMIT_BY,
a.SUBMIT_DATE,
a.LOGIS_STATUS,
a.DOC_DEF_ID,
a.ORDER_ID,
a.ORDER_DET_ID,
a.BUY_ORG_ID,
a.STP_ORG_ID,
a.SELL_ORG_ID,
a.SELL_DEPT_ID,
a.SELL_DEPT_CODE,
a.SELL_DEPT_NAME,
a.SELL_CST_CODE,
a.SELL_CST_NAME,
a.SELL_ADDR_ID,
a.SELL_ADDR_CODE,
a.SELL_ADDR_NAME,
a.SELL_MEMBER_ID,
a.SELL_MEMBER_CODE,
a.SELL_MEMBER_NAME,
a.SELL_GOODS_ID,
a.SELL_GOODS_CODE,
a.SELL_GOODS_NAME,
a.SELL_GOODS_SPEC,
a.SELL_GOODS_PACK_UNIT,
a.SELL_GOODS_PACK_NUM,
a.SELL_GOODS_BRAND,
a.SELL_GOODS_RATIFIER,
a.SELL_QTY,
a.LOTO_NO,
a.PRD_DATE,
a.END_DATE,
a.TAX_PRC,
a.TAX_RATE,
a.AMOUNT,
a.TX_PRC,
a.TX_AMOUNT,
a.TAX,
a.OUTER_NO,
a.OUTER_DET_NO,
a.RECIPE_NO,
a.order_from,
a.INVOICE_CODE,
a.INVOICE_NO,
a.INVOICE_DATE,
a.INVOICE_AMOUNT,
a.MAP_FLAG,
a.MAP_STATUS,
a.MAP_CODE,
a.MAP_DESC,
a.IS_COLD_CHAIN,
a.IS_TICKET,
a.IS_MAP_TICKET,
a.IS_MAP_BOX,
a.TENANT_ID,
a.CREATE_BY,
a.CREATE_DATE,
a.UPDATE_BY,
a.UPDATE_DATE,
a.EXP_STATUS,
a.EXP_BATCHID,
a.EXP_BY,
a.EXP_DATE,
a.EXP_RESULT,
a.DET_ATTR1,
a.DET_ATTR2,
a.DET_ATTR3,
a.DET_ATTR4,
a.DET_ATTR5,
a.LOGIS_OU_CODE,
a.LOGIS_ORDER_NO,
a.LOGIS_ORDER_DET_NO,
b.GOODS_SEARCH_NAME,
(select nvl(max(to_number(status_code)),'0')
from shop_logis_status_in lsi
where /*lsi.bill_type=a.so_type*/ 1=1
and lsi.logis_ou_code = a.logis_ou_code
and lsi.logis_order_no = a.logis_order_no
and lsi.logis_order_det_no = a.logis_order_det_no
and a.so_type='21') as logis_status_code_in, --销售退货最新物流状态
(select nvl(max(to_number(status_code)),'0')
from shop_logis_status_out lso
where /*lso.bill_type=a.so_type*/ 1=1
and lso.logis_ou_code = a.logis_ou_code
and lso.logis_order_no = a.logis_order_no
and lso.logis_order_det_no = a.logis_order_det_no
and a.so_type='20') as logis_status_code_out --销售出库最新物流状态
from SHOP_SALE_ORDER_DETAIL a, SHOP_GOODS b
where a.SELL_GOODS_ID = b.GOODS_ID
;
COMMENT ON COLUMN RIS.V_LOGIS_SHOP_SALE_ORDER_DETAIL."TENANT_ID" IS '租户ID';查询SHOP_SALE_ORDER_DETAIL表记录数是13344613
查看SHOP_SALE_ORDER_DETAIL描述如下
CREATE TABLE "RIS"."SHOP_SALE_ORDER_DETAIL"
(
"SO_DET_ID" NVARCHAR(36) NOT NULL,
"SO_ID" NVARCHAR(36),
"SO_CAT" NVARCHAR(20),
"SO_TYPE" NVARCHAR(20),
"SO_NO" NVARCHAR(100),
"SO_DET_NO" NVARCHAR(100),
"SO_DATE" TIMESTAMP(0),
"SO_RELEASE_ID" NVARCHAR(100),
"SO_DET_STATUS" NUMBER(2,0),
"SO_DET_DESC" NVARCHAR(255),
"SO_GRP_ID" NVARCHAR(50),
"SO_SUB_GRP_ID" NVARCHAR(50),
"SUBMIT_BY" NVARCHAR(36),
"SUBMIT_DATE" TIMESTAMP(0),
"LOGIS_STATUS" NVARCHAR(20),
"DOC_DEF_ID" NVARCHAR(64) NOT NULL,
"ORDER_ID" NVARCHAR(50),
"ORDER_DET_ID" NVARCHAR(50),
"BUY_ORG_ID" NVARCHAR(36),
"STP_ORG_ID" NVARCHAR(36),
"SELL_ORG_ID" NVARCHAR(36),
"SELL_DEPT_ID" NVARCHAR(50),
"SELL_DEPT_CODE" NVARCHAR(50),
"SELL_DEPT_NAME" NVARCHAR(255),
"SELL_CST_CODE" NVARCHAR(50),
"SELL_CST_NAME" NVARCHAR(255),
"SELL_ADDR_ID" NVARCHAR(36),
"SELL_ADDR_CODE" NVARCHAR(100),
"SELL_ADDR_NAME" NVARCHAR(300),
"SELL_MEMBER_ID" NVARCHAR(36),
"SELL_MEMBER_CODE" NVARCHAR(50),
"SELL_MEMBER_NAME" NVARCHAR(300),
"SELL_GOODS_ID" NVARCHAR(36),
"SELL_GOODS_CODE" NVARCHAR(100),
"SELL_GOODS_NAME" NVARCHAR(255),
"SELL_GOODS_SPEC" NVARCHAR(100),
"SELL_GOODS_PACK_UNIT" NVARCHAR(50),
"SELL_GOODS_PACK_NUM" NUMBER(10,0),
"SELL_GOODS_BRAND" NVARCHAR(255),
"SELL_GOODS_RATIFIER" NVARCHAR(100),
"SELL_QTY" NUMBER(10,2) DEFAULT 0,
"LOTO_NO" NVARCHAR(100),
"PRD_DATE" TIMESTAMP(0),
"END_DATE" TIMESTAMP(0),
"TAX_PRC" NUMBER(18,6),
"TAX_RATE" NUMBER(10,6),
"AMOUNT" NUMBER(10,2),
"TX_PRC" NUMBER(18,6),
"TX_AMOUNT" NUMBER(10,2),
"TAX" NUMBER(10,2),
"OUTER_NO" NVARCHAR(100),
"OUTER_DET_NO" NVARCHAR(100),
"RECIPE_NO" NVARCHAR(90),
"ORDER_FROM" NVARCHAR(90),
"INVOICE_CODE" NVARCHAR(50),
"INVOICE_NO" NVARCHAR(50),
"INVOICE_DATE" TIMESTAMP(0),
"INVOICE_AMOUNT" NUMBER(22,2),
"MAP_FLAG" NUMBER(1,0) DEFAULT 0,
"MAP_STATUS" NVARCHAR(2) DEFAULT 0,
"MAP_CODE" NVARCHAR(50),
"MAP_DESC" NVARCHAR(400),
"IS_COLD_CHAIN" NVARCHAR(20),
"IS_TICKET" NUMBER(1,0),
"IS_MAP_TICKET" NUMBER(1,0) DEFAULT 1,
"IS_MAP_BOX" NUMBER(1,0) DEFAULT 0,
"TENANT_ID" NVARCHAR(64) DEFAULT '000',
"CREATE_BY" NVARCHAR(50),
"CREATE_DATE" TIMESTAMP(0) DEFAULT SYSDATE,
"UPDATE_BY" NVARCHAR(50),
"UPDATE_DATE" TIMESTAMP(0) DEFAULT SYSDATE,
"EXP_STATUS" NVARCHAR(2) DEFAULT 0,
"EXP_BATCHID" NVARCHAR(36),
"EXP_BY" NVARCHAR(100),
"EXP_DATE" TIMESTAMP(0),
"EXP_RESULT" NVARCHAR(500),
"DET_ATTR1" NVARCHAR(250),
"DET_ATTR2" NVARCHAR(250),
"DET_ATTR3" NVARCHAR(250),
"DET_ATTR4" NVARCHAR(250),
"DET_ATTR5" NVARCHAR(250),
"LOGIS_OU_CODE" NVARCHAR(20),
"LOGIS_ORDER_NO" NVARCHAR(100),
"LOGIS_ORDER_DET_NO" NVARCHAR(100),
"BUY_CST_CODE" NVARCHAR(40),
"BUY_CST_NAME" NVARCHAR(200),
"BUY_GOODS_ID" NVARCHAR(50),
"BUY_GOODS_CODE" NVARCHAR(100),
"BUY_GOODS_NAME" NVARCHAR(255),
"BUY_GOODS_SPEC" NVARCHAR(100),
"BUY_GOODS_UNIT" NVARCHAR(50),
"BUY_GOODS_BRAND" NVARCHAR(255),
"BUY_QTY" NUMBER(10,0) DEFAULT 0,
"BUY_TAX_PRC" NUMBER(16,6),
"BUY_ADDR_ID" NVARCHAR(100),
"BUY_ADDR_CODE" NVARCHAR(100),
"BUY_ADDR_NAME" NVARCHAR(300),
"BUY_RECEIVE_QTY" NUMBER(10,2) DEFAULT 0,
"BUY_REFUSE_QTY" NUMBER(10,2) DEFAULT 0,
"BUY_RETURN_QTY" NUMBER(10,2) DEFAULT 0,
"BUY_RECEIVE_STATUS" NVARCHAR(2) DEFAULT '00',
"BUY_RECEIVE_DESC" NVARCHAR(255),
"BUY_RECEIVE_BY" NVARCHAR(50),
"BUY_RECEIVE_DATE" TIMESTAMP(0),
"BUY_CHG" NUMBER(10,2) DEFAULT 1,
"SELL_CHG" NUMBER(10,2) DEFAULT 1,
"BUY_GOODS_RATIFIER" NVARCHAR(100),
"SO_SERIAL" NVARCHAR(100),
"BUY_CARTON_NBR" NUMBER(11,0),
"SELL_CARTON_NBR" NUMBER(11,0),
"SO_BARCODE_TYPE" NVARCHAR(8),
"SO_BARCODE" NVARCHAR(200),
"SKU_ID" NVARCHAR(36),
"SKU_CODE" NVARCHAR(60),
CONSTRAINT "PK_SHOP_SALE_ORDER_DETAIL" NOT CLUSTER PRIMARY KEY("SO_DET_ID") USING INDEX TABLESPACE "RISIDX" ) STORAGE(ON "RIS", CLUSTERBTR) ;
COMMENT ON TABLE RIS.SHOP_SALE_ORDER_DETAIL IS '采购方销售单表';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."AMOUNT" IS '含税金额';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_ADDR_CODE" IS '采购方地址码';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_ADDR_ID" IS '采购方地址码ID';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_ADDR_NAME" IS '采购方采购方地址';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_CARTON_NBR" IS '采购方每箱可装数量';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_CHG" IS '采购方转换比';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_CST_CODE" IS '采购方编码(销售方在采购方系统编码)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_CST_NAME" IS '采购方名称(销售方在采购方系统名称)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_GOODS_BRAND" IS '采购方生产厂家';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_GOODS_CODE" IS '采购方商品编码';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_GOODS_ID" IS '采购方商品ID';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_GOODS_NAME" IS '采购方商品名称';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_GOODS_RATIFIER" IS '采购方批准文号';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_GOODS_SPEC" IS '采购方规格';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_GOODS_UNIT" IS '采购方单位';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_ORG_ID" IS '购买方公司ID';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_QTY" IS '采购方数量';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_RECEIVE_BY" IS '采购方验收人';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_RECEIVE_DATE" IS '采购方验收时间';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_RECEIVE_DESC" IS '采购方验收备注';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_RECEIVE_QTY" IS '采购方实收数量';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_RECEIVE_STATUS" IS '采购方验收状态(00 待提交 10 已提交未验收 20 验收中 30 已验收 98 已作废 99 拒收)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_REFUSE_QTY" IS '采购方拒收数量';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_RETURN_QTY" IS '采购方退货数量';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."BUY_TAX_PRC" IS '采购方含税单价';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."CREATE_BY" IS '操作人';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."CREATE_DATE" IS '创建时间';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."DET_ATTR1" IS '扩展1';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."DET_ATTR2" IS '扩展2';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."DET_ATTR3" IS '扩展3';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."DET_ATTR4" IS '扩展4';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."DET_ATTR5" IS '扩展5';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."DOC_DEF_ID" IS '单据类型(DOC_DEF.DOC_DEF_ID)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."END_DATE" IS '效期';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."EXP_BATCHID" IS 'MQ发送批次号';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."EXP_BY" IS 'MQ发送人';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."EXP_DATE" IS 'MQ推送时间';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."EXP_RESULT" IS 'MQ反馈消息';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."EXP_STATUS" IS 'MQ发送状态(0未发送 1已发送 2失败)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."INVOICE_AMOUNT" IS '发票金额';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."INVOICE_CODE" IS '发票编码';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."INVOICE_DATE" IS '发票日期';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."INVOICE_NO" IS '发票号码';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."IS_COLD_CHAIN" IS '是否冷链';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."IS_MAP_BOX" IS '配送单与装箱单是否匹配(0未匹配 1已匹配';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."IS_MAP_TICKET" IS '是否匹配两票制(0不匹配 1匹配)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."IS_TICKET" IS '是否两票制(1：两票制2：非两票制3：急抢救药品)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."LOGIS_ORDER_DET_NO" IS '物流货主细单编号';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."LOGIS_ORDER_NO" IS '物流货主总单号';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."LOGIS_OU_CODE" IS '物流货主编码';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."LOGIS_STATUS" IS '物流状态';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."LOTO_NO" IS '批号';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."MAP_CODE" IS 'SCM对码--对码错误代码';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."MAP_DESC" IS 'SCM对码--数据对码结果';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."MAP_FLAG" IS 'SCM对码--购买方与销售方是否需对码(0 不需对码 1需对码)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."MAP_STATUS" IS 'SCM对码--是否已对码( 0 未匹配 1已匹配 )';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."ORDER_DET_ID" IS '采购订单明细ID';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."ORDER_FROM" IS '订单来源(10 PC、20 H5、30 IOS、40 安卓、50接口)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."ORDER_ID" IS '采购订单ID';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."OUTER_DET_NO" IS '外部销售单明细编号';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."OUTER_NO" IS '外部销售单编号(第三方配送单号)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."PRD_DATE" IS '生产日期';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."RECIPE_NO" IS '处方号';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_ADDR_CODE" IS '销售方地址码';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_ADDR_ID" IS '销售方地址ID';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_ADDR_NAME" IS '销售方地址';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_CARTON_NBR" IS '销售方每箱可装数量';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_CHG" IS '销售方转换比';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_CST_CODE" IS '销售方购买方编码(采购方在销售方系统编码)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_CST_NAME" IS '销售方购买方名称(采购方在销售方系统名称)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_DEPT_CODE" IS '销售方部门编码';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_DEPT_ID" IS '销售方部门ID';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_DEPT_NAME" IS '销售方部门编码';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_GOODS_BRAND" IS '销售方生产厂家';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_GOODS_CODE" IS '销售方商品编码';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_GOODS_ID" IS '销售方商品ID';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_GOODS_NAME" IS '销售方商品名称';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_GOODS_PACK_NUM" IS '销售方包装数量';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_GOODS_PACK_UNIT" IS '销售方包装单位';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_GOODS_RATIFIER" IS '批准文号';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_GOODS_SPEC" IS '销售方规格';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_MEMBER_CODE" IS '销售方销售员编码';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_MEMBER_ID" IS '销售方销售员ID';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_MEMBER_NAME" IS '销售方销售员姓名';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_ORG_ID" IS '销售方公司ID';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SELL_QTY" IS '销售方发货数量';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SO_BARCODE" IS '配送明细条码';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SO_BARCODE_TYPE" IS '配送明细条码类型(01:GS1条码:02 HIBC条码 99 企业自定义)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SO_CAT" IS '配送单类别(crm 医院与配送商 srm 供应商与配送商)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SO_DATE" IS '配送时间';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SO_DET_DESC" IS '备注';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SO_DET_ID" IS '配送单明细ID';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SO_DET_NO" IS '配送单明细号（ERP销售订单执行号）';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SO_DET_STATUS" IS '配送单状态( 0录入 1已提交,待审核 2已审核 9作废)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SO_GRP_ID" IS '配送单分组ID(送货单ID shop_sale_order_dispatch.dispatch_id)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SO_ID" IS '配送单ID';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SO_NO" IS '配送单号';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SO_RELEASE_ID" IS '销售订单为空；销退唯一ID';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SO_SERIAL" IS '配送明细对应的顺序号，同一个配送单下必须唯一(市平台要求)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SO_SUB_GRP_ID" IS '配送单子分组ID(扩展字段)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SO_TYPE" IS '配送单类型(20 销售单 21 销售退货单)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."STP_ORG_ID" IS '送达方公司ID(三方协议)';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SUBMIT_BY" IS '提交人';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."SUBMIT_DATE" IS '提交日期';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."TAX" IS '税额';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."TAX_PRC" IS '含税单价';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."TAX_RATE" IS '税率';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."TENANT_ID" IS '租户ID';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."TX_AMOUNT" IS '无税金额';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."TX_PRC" IS '无税单价';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."UPDATE_BY" IS '修改人';
COMMENT ON COLUMN RIS.SHOP_SALE_ORDER_DETAIL."UPDATE_DATE" IS '修改时间';
CREATE OR REPLACE INDEX "RIS"."IDX_SALE_ORDER_DETAIL_BUYORGID" ON "RIS"."SHOP_SALE_ORDER_DETAIL"("BUY_ORG_ID" ASC) STORAGE(ON "RISIDX", CLUSTERBTR) ;
CREATE OR REPLACE INDEX "RIS"."IDX_SALE_ORDER_DETAIL_GOODCODE" ON "RIS"."SHOP_SALE_ORDER_DETAIL"("SELL_GOODS_CODE" ASC) STORAGE(ON "RISIDX", CLUSTERBTR) ;
CREATE OR REPLACE INDEX "RIS"."IDX_SALE_ORDER_DETAIL_GOODID" ON "RIS"."SHOP_SALE_ORDER_DETAIL"("SELL_GOODS_ID" ASC) STORAGE(ON "RISIDX", CLUSTERBTR) ;
CREATE OR REPLACE INDEX "RIS"."IDX_SALE_ORDER_DETAIL_LOGISNO" ON "RIS"."SHOP_SALE_ORDER_DETAIL"("LOGIS_ORDER_NO" ASC,"LOGIS_ORDER_DET_NO" ASC) STORAGE(ON "RISIDX", CLUSTERBTR) ;
CREATE OR REPLACE INDEX "RIS"."IDX_SALE_ORDER_DETAIL_SODATE" ON "RIS"."SHOP_SALE_ORDER_DETAIL"("SO_DATE" ASC) STORAGE(ON "RISIDX", CLUSTERBTR) ;
CREATE OR REPLACE INDEX "RIS"."IDX_SALE_ORDER_DETAIL_SODETNO" ON "RIS"."SHOP_SALE_ORDER_DETAIL"("SO_DET_NO" ASC) STORAGE(ON "RISIDX", CLUSTERBTR) ;
CREATE OR REPLACE INDEX "RIS"."IDX_SALE_ORDER_DETAIL_SOID" ON "RIS"."SHOP_SALE_ORDER_DETAIL"("SO_ID" ASC) STORAGE(ON "RISIDX", CLUSTERBTR) ;
CREATE OR REPLACE INDEX "RIS"."IDX_SALE_ORDER_DETAIL_SONO" ON "RIS"."SHOP_SALE_ORDER_DETAIL"("SO_NO" ASC) STORAGE(ON "RISIDX", CLUSTERBTR) ;
CREATE OR REPLACE INDEX "RIS"."IDX_SALE_ORDER_DETAIL_SOTYPE" ON "RIS"."SHOP_SALE_ORDER_DETAIL"("SO_TYPE" ASC) STORAGE(ON "RISIDX", CLUSTERBTR) ;执行计划分析与根因定位#
这条 SQL 的核心部分是：
where t.so_id = '0002603046042426'
order by so_det_no asc) tmp
where rownum <= 15)但基表 SHOP_SALE_ORDER_DETAIL 现在只有：
SO_ID单列索引SO_DET_NO单列索引
没有 (SO_ID, SO_DET_NO) 这样的联合索引。
同时查的是视图 V_LOGIS_SHOP_SALE_ORDER_DETAIL，这个视图复杂，它除了从 SHOP_SALE_ORDER_DETAIL 取列，还：
- 关联
SHOP_GOODS - 对
SHOP_LOGIS_STATUS_IN做相关子查询 - 对
SHOP_LOGIS_STATUS_OUT做相关子查询
所以优化器现在很可能是在做这种事：
- 主表走的是
IDX_SALE_ORDER_DETAIL_SOID单列索引，然后立刻接了BLKUP2回表。 - 再回表取
t.* - 再排序
SO_DET_NO - 最后才截 15 行
这就是为什么 明明只查 15 行，却跑了 133 秒。
根因并不是查询结果只有 15 行，而是优化器只能利用
SO_ID单列索引，无法同时满足SO_ID过滤与SO_DET_NO排序，导致在宽表SHOP_SALE_ORDER_DETAIL上产生大量BLKUP2回表和后续排序开销。
优化方案与验证结果#
先建联合索引
CREATE INDEX RIS.IDX_SOD_SOID_DETNO_ID
ON RIS.SHOP_SALE_ORDER_DETAIL(SO_ID, SO_DET_NO, SO_DET_ID);这个索引的作用：
SO_ID：过滤SO_DET_NO：排序SO_DET_ID：后续回表/关联更顺手
这样优化器才有机会变成：
按 SO_ID + SO_DET_NO 直接有序拿前 15 个明细，再做后续处理
而不是现在这种先扫很多再排序。
手动分析表
dbms_stats.gather_table_stats(ownname => 'RIS',tabname => 'SHOP_SALE_ORDER_DETAIL' ,estimate_percent => 100 ,method_opt => 'FOR ALL COLUMNS SIZE AUTO' ,cascade => true, degree => 8);
最后执行SQL
select *
from (select tmp.*, rownum as row_id
from (select t.*, li.item_detail_name as statusdescin, lo.item_detail_name as statusdescout,
li.sort_code as sortcodein, lo.sort_code as sortcodeout
from v_logis_shop_sale_order_detail t
left join c_logistics_in li
on t.logis_status_code_in = li.item_detail_code
left join c_logistics_out lo
on t.logis_status_code_out = lo.item_detail_code
where t.so_id = '0002603046042426'
order by so_det_no asc) tmp
where rownum <= 15)
where row_id > 0如图所示
从客户端工具界面看，SQL 返回时间约为 23 毫秒；从达梦 SQL Monitor 的服务端监控结果看，SQL 执行时长为 0.001182 秒，逻辑读只有46个页。
SQL Monitoring Report
SQL Text
------------------------------
select *
from (select tmp.*, rownum as row_id
from (select t.*, li.item_detail_name as statusdescin, lo.item_detail_name as statusdescout,
li.sort_code as sortcodein, lo.sort_code as sortcodeout
from v_logis_shop_sale_order_detail t
left join c_logistics_in li
on t.logis_status_code_in = li.item_detail_code
left join c_logistics_out lo
on t.logis_status_code_out = lo.item_detail_code
where t.so_id = '0002603046042426'
order by so_det_no asc) tmp
where rownum <= 15)
where row_id > 0
Global Information
------------------------------
Status : DONE (ALL ROWS)
Session : RIS (281434248978120:157948)
SQL ID : 1596
SQL Execution ID : 14763934
Execution Started : 2026-03-17 20:52:38
Duration : 0.001182s
Program : manager.exe
Global Stats
=========================================================
| Affected | Bytes | Bytes | Physical | Logical |
| Rows | Allocate | Free | Read(page) | Read(page) |
=========================================================
| 0 | 0 | 0 | 0 | 46 |
=========================================================
SQL Plan Monitoring Details
====================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
====================================================================================================================================
| 0 | DLCK | | | | 0.000010 | +0.001082 | 2 | | 0.89 | |
| 1 | NSET2 | | 1 | 3 | 0.000100 | +0.001009 | 3 | 1 | 8.91 | |
| 2 | PIPE2 | | 1 | 3 | 0.000001 | +0.001009 | 5 | 1 | 0.09 | |
| 3 | PIPE2 | | 1 | 3 | 0.000000 | +0.001009 | 5 | 1 | 0.00 | |
| 4 | PRJT2 | | 1 | 2 | 0.000006 | +0.001009 | 4 | 1 | 0.53 | |
| 5 | SLCT2 | | 1 | 2 | 0.000017 | +0.001008 | 4 | 1 | 1.52 | |
| 6 | PRJT2 | | 1 | 2 | 0.000005 | +0.001006 | 4 | 1 | 0.45 | |
| 7 | RN | | 1 | 2 | 0.000017 | +0.000999 | 4 | 1 | 1.52 | |
| 8 | PRJT2 | | 1 | 2 | 0.000004 | +0.000998 | 4 | 1 | 0.36 | |
| 9 | SORT3 | | 1 | 2 | 0.000294 | +0.000971 | 4 | 1 | 26.20 | |
| 10 | NLLO2 | | 1 | 1 | 0.000045 | +0.000762 | 6 | 1 | 4.01 | |
| 11 | NLLO2 | | 1 | 1 | 0.000010 | +0.000761 | 5 | 1 | 0.89 | |
| 12 | PRJT2 | | 1 | 1 | 0.000176 | +0.000761 | 4 | 1 | 15.69 | |
| 13 | IJI2 | | 1 | 1 | 0.000010 | +0.000761 | 6 | 1 | 0.89 | |
| 14 | BLKUP2 | SHOP_SALE_ORDER_DETAIL | 1 | 1 | 0.000113 | +0.000760 | 4 | 1 | 10.07 | |
| 15 | SSEK2 | SHOP_SALE_ORDER_DETAIL | 1 | 1 | 0.000044 | +0.000759 | 2 | 1 | 3.92 | |
| 16 | BLKUP2 | SHOP_GOODS | 1 | 1 | 0.000011 | +0.000758 | 4 | 1 | 0.98 | |
| 17 | SSEK2 | SHOP_GOODS | 1 | 1 | 0.000016 | +0.000758 | 2 | 1 | 1.43 | |
| 18 | PRJT2 | | 1 | 1 | 0.000000 | +0.000545 | 2 | | 0.00 | |
| 19 | SLCT2 | | 1 | 1 | 0.000002 | +0.000544 | 2 | | 0.18 | |
| 20 | IJI2 | | 1 | 1 | 0.000001 | +0.000544 | 4 | | 0.09 | |
| 21 | BLKUP2 | BASE_DATAITEM | 1 | 1 | 0.000008 | +0.000544 | 4 | 1 | 0.71 | |
| 22 | SSEK2 | BASE_DATAITEM | 1 | 1 | 0.000010 | +0.000544 | 2 | 1 | 0.89 | |
| 23 | BLKUP2 | BASE_DATAITEM_DETAIL | 1 | 1 | 0.000000 | +0.000543 | 2 | | 0.00 | |
| 24 | SLCT2 | | 1 | 1 | 0.000008 | +0.000543 | 3 | | 0.71 | |
| 25 | SSEK2 | BASE_DATAITEM_DETAIL | 1 | 1 | 0.000018 | +0.000543 | 2 | 8 | 1.60 | |
| 26 | PRJT2 | | 1 | 1 | 0.000001 | +0.000756 | 4 | 1 | 0.09 | |
| 27 | SLCT2 | | 1 | 1 | 0.000004 | +0.000755 | 4 | 1 | 0.36 | |
| 28 | IJI2 | | 1 | 1 | 0.000002 | +0.000755 | 6 | 1 | 0.18 | |
| 29 | BLKUP2 | BASE_DATAITEM | 1 | 1 | 0.000004 | +0.000755 | 4 | 1 | 0.36 | |
| 30 | SSEK2 | BASE_DATAITEM | 1 | 1 | 0.000006 | +0.000755 | 2 | 1 | 0.53 | |
| 31 | BLKUP2 | BASE_DATAITEM_DETAIL | 1 | 1 | 0.000014 | +0.000754 | 4 | 1 | 1.25 | |
| 32 | SLCT2 | | 1 | 1 | 0.000004 | +0.000754 | 4 | 1 | 0.36 | |
| 33 | SSEK2 | BASE_DATAITEM_DETAIL | 1 | 1 | 0.000012 | +0.000753 | 2 | 7 | 1.07 | |
| 34 | SPL2 | | | 1 | 0.000001 | +0.000492 | 4 | | 0.09 | |
| 35 | PRJT2 | | 1 | 1 | 0.000001 | +0.000492 | 4 | 1 | 0.09 | |
| 36 | AAGR2 | | 1 | 1 | 0.000006 | +0.000491 | 4 | 1 | 0.53 | |
| 37 | PRJT2 | | 1 | 1 | 0.000009 | +0.000489 | 4 | 6 | 0.80 | |
| 38 | SLCT2 | | 1 | 1 | 0.000015 | +0.000489 | 4 | 6 | 1.34 | |
| 39 | BLKUP2 | SHOP_LOGIS_STATUS_OUT | 1 | 1 | 0.000062 | +0.000488 | 4 | 6 | 5.53 | |
| 40 | SSEK2 | SHOP_LOGIS_STATUS_OUT | 1 | 1 | 0.000020 | +0.000488 | 2 | 6 | 1.78 | |
| 41 | SPL2 | | | 1 | 0.000006 | +0.000369 | 4 | | 0.53 | |
| 42 | PRJT2 | | 1 | 1 | 0.000003 | +0.000368 | 4 | 1 | 0.27 | |
| 43 | AAGR2 | | 1 | 1 | 0.000006 | +0.000367 | 3 | 1 | 0.53 | |
| 44 | PRJT2 | | 1 | 1 | 0.000000 | +0.000361 | 2 | | 0.00 | |
| 45 | SLCT2 | | 1 | 1 | 0.000001 | +0.000360 | 2 | | 0.09 | |
| 46 | BLKUP2 | SHOP_LOGIS_STATUS_IN | 1 | 1 | 0.000000 | +0.000360 | 2 | | 0.00 | |
| 47 | SSEK2 | SHOP_LOGIS_STATUS_IN | 1 | 1 | 0.000019 | +0.000340 | 1 | | 1.69 | |
====================================================================================================================================另外在SQL整体写法上，最外层select * from (。。。) where ROW_ID > 0 其实不用再套的，这个条件没有任何过滤作用，可以删除这层嵌套。
优化前后对比表#
| 指标 | 优化前 | 优化后 |
|---|---|---|
| SQL 总执行时间 | 133.98s | 0.001182s |
| 逻辑读 | 40046153 | 46 |
| 主要瓶颈 | BLKUP2大量回表 | BLKUP2 回表代价已大幅下降，整体访问成本显著降低 |
| 访问路径 | SO_ID 单列索引 + 回表 + 排序 | 联合索引快速定位 |
总结#
本次慢 SQL 优化的关键，不在于 SQL 返回结果只有多少行，而在于数据库为了得到这 15 行结果，实际走了怎样的访问路径。通过 DBMS_SQLTUNE 监控报告可以看到，问题 SQL 的主要耗时集中在 SHOP_SALE_ORDER_DETAIL 表的 BLKUP2 操作上，说明优化器虽然使用了 SO_ID 单列索引进行过滤，但由于缺少同时支持 SO_ID 和 SO_DET_NO 的联合索引，仍然需要对宽表进行大量回表，并在之后完成排序和取前 N 行。
针对这一问题，本文通过补充联合索引 IDX_SOD_SOID_DETNO_ID，并重新收集 SHOP_SALE_ORDER_DETAIL 的统计信息，使优化器能够以更合理的方式访问数据。优化后，SQL 执行时间从 133 秒下降到毫秒级，逻辑读从 40046153 页下降到 46 页，性能提升非常明显。
这个案例说明，在达梦数据库中分析慢 SQL 时，不能只看 SQL 文本本身，更要结合 SQL Monitor、执行计划、表结构、数据规模和索引设计综合判断。对于“过滤 + 排序 + 分页”这类常见业务查询，如果缺少合适的联合索引，即使最终只返回极少量数据，也可能在底层付出极高的扫描和回表代价。掌握这类问题的分析方法，比单纯解决某一条 SQL 更有实际价值。希望本次实战分析给各位小伙伴带来帮助😀