同样的sql在oracle下执行很快，在达梦下执行超级慢，因为业务需要，不好再优化sql了
SELECT *
FROM (SELECT MX.ID,
MX.FILE_TITLE,
MX.FILE_ID,
MX.CREATE_ORG,
MX.CREATORNAME,
MX.CURRENTUSERNAME,
MX.FLOWTYPE,
MX.QUERY_DATE,
MX.CREATE_DATE,
MX.DEAL_DATE,
MX.RECEIVE_DATE,
MX.RECEIVE_TYPE,
MX.RECEIVE_FILE_NO,
MX.COME_FILE_NO,
MX.SEND_FILE_ORG
FROM (SELECT T.ID AS ID,
T.FILE_TITLE,
T.FILE_ID,
T.CREATE_ORG,
(SELECT CURRENT_DEAL_NAME
FROM (SELECT P.NAME CURRENT_DEAL_NAME
FROM R$USER U, R$PERSON P, T_FLOW_LOG FL
WHERE U.PERSON_ID = P.PERSON_ID
AND FL.FLOW_USER = U.USER_ID
AND FL.FLOW_FILE_ID = T.FILE_ID
AND FL.FLOW_POINT = ‘拟办’
ORDER BY FL.FLOW_DATE DESC)
WHERE ROWNUM = 1) CREATORNAME,
‘’ AS CURRENTUSERNAME,
‘收文管理’ FLOWTYPE,
T.CREATE_DATE AS QUERY_DATE,
TO_CHAR(T.CREATE_DATE, ‘yyyy-mm-dd HH24:mi:ss’) AS CREATE_DATE,
TO_CHAR(FUNC_GET_DEAL_DATE(T.ID),
‘yyyy-mm-dd HH24:mi:ss’) AS DEAL_DATE,
TO_CHAR(T.RECEIVE_DATE, ‘yyyy-mm-dd’) AS RECEIVE_DATE,
T.RECEIVE_TYPE,
T.RECEIVE_FILE_NO,
T.COME_FILE_NO,
T.SEND_FILE_ORG
FROM T_OA_RECEIVE_FILE T
WHERE T.SEQ_ID = (SELECT MAX(C.SEQ_ID)
FROM T_OA_RECEIVE_FILE C
WHERE C.FILE_ID = T.FILE_ID)
AND T.FILE_ID IN
(SELECT A.FILE_ID
FROM T_OA_RECEIVE_FILE A
WHERE A.IF_DELETE = 0
AND (A.STATE < 3 OR A.STATE > 3)
AND EXISTS (SELECT 1
FROM T_OA_SEND_FILE_AUDIT DD
WHERE DD.OPERATOR =
‘1a213ba3-e487-467f-ad9a-453c6cfa6f3e’
AND DD.ID = A.ID))
AND T.IF_DELETE = 0
AND (T.STATE < 3 OR T.STATE > 3)
UNION ALL
SELECT T.ID AS ID,
T.FILE_TITLE,
T.FILE_ID,
T.CREATE_ORG,
(SELECT CURRENT_DEAL_NAME
FROM (SELECT P.NAME CURRENT_DEAL_NAME
FROM R$USER U, R$PERSON P, T_FLOW_LOG FL
WHERE U.PERSON_ID = P.PERSON_ID
AND FL.FLOW_USER = U.USER_ID
AND FL.FLOW_FILE_ID = T.FILE_ID
AND FL.FLOW_POINT = ‘拟办’
ORDER BY FL.FLOW_DATE DESC)
WHERE ROWNUM = 1) CREATORNAME,
‘’ AS CURRENTUSERNAME,
‘收文管理’ FLOWTYPE,
T.CREATE_DATE AS QUERY_DATE,
TO_CHAR(T.CREATE_DATE, ‘yyyy-mm-dd HH24:mi:ss’) AS CREATE_DATE,
TO_CHAR(FUNC_GET_DEAL_DATE(T.ID),
‘yyyy-mm-dd HH24:mi:ss’) AS DEAL_DATE,
TO_CHAR(T.RECEIVE_DATE, ‘yyyy-mm-dd’) AS RECEIVE_DATE,
T.RECEIVE_TYPE,
T.RECEIVE_FILE_NO,
T.COME_FILE_NO,
T.SEND_FILE_ORG
FROM T_OA_RECEIVE_FILE T,
R$USER U,
T_OA_FILE_RECEIVE_MULTI M
WHERE T.SEQ_ID = (SELECT MAX(C.SEQ_ID)
FROM T_OA_RECEIVE_FILE C
WHERE C.FILE_ID = T.FILE_ID)
AND U.USER_ID = ‘1a213ba3-e487-467f-ad9a-453c6cfa6f3e’
AND M.RCV_FILE_ID = T.FILE_ID
AND M.ROLE_ID = U.USER_ID
AND M.ROLE_AUDITON IS NOT NULL
AND T.IF_DELETE = 0
AND (T.STATE < 3 OR T.STATE > 3)
UNION ALL
SELECT T.ID AS ID,
T.FILE_TITLE,
T.FILE_ID,
T.CREATE_ORG,
(SELECT CURRENT_DEAL_NAME
FROM (SELECT P.NAME CURRENT_DEAL_NAME
FROM R$USER U, R$PERSON P, T_FLOW_LOG FL
WHERE U.PERSON_ID = P.PERSON_ID
AND FL.FLOW_USER = U.USER_ID
AND FL.FLOW_FILE_ID = T.FILE_ID
AND FL.FLOW_POINT = ‘拟办’
ORDER BY FL.FLOW_DATE DESC)
WHERE ROWNUM = 1) CREATORNAME,
‘’ AS CURRENTUSERNAME,
‘收文管理’ FLOWTYPE,
T.HISTORY_DATE AS QUERY_DATE,
TO_CHAR(T.CREATE_DATE, ‘yyyy-mm-dd HH24:mi:ss’) AS CREATE_DATE,
‘’ AS DEAL_DATE,
TO_CHAR(T.RECEIVE_DATE, ‘yyyy-mm-dd’) AS RECEIVE_DATE,
T.RECEIVE_TYPE,
T.RECEIVE_FILE_NO,
T.COME_FILE_NO,
T.SEND_FILE_ORG
FROM T_OA_RECEIVE_FILE_HISTORY T,
T_OA_SEND_FILE_AUDIT_HISTORY S
WHERE T.FILE_ID = S.ID
AND S.OPERATOR = ‘1a213ba3-e487-467f-ad9a-453c6cfa6f3e’
UNION ALL
SELECT T.ID AS ID,
T.FILE_TITLE,
T.FILE_ID,
T.CREATE_ORG,
(SELECT CURRENT_DEAL_NAME
FROM (SELECT P.NAME CURRENT_DEAL_NAME
FROM R$USER U, R$PERSON P, T_FLOW_LOG FL
WHERE U.PERSON_ID = P.PERSON_ID
AND FL.FLOW_USER = U.USER_ID
AND FL.FLOW_FILE_ID = T.FILE_ID
AND FL.FLOW_POINT = ‘拟办’
ORDER BY FL.FLOW_DATE DESC)
WHERE ROWNUM = 1) CREATORNAME,
‘’ AS CURRENTUSERNAME,
‘收文管理’ FLOWTYPE,
T.HISTORY_DATE AS QUERY_DATE,
TO_CHAR(T.CREATE_DATE, ‘yyyy-mm-dd HH24:mi:ss’) AS CREATE_DATE,
‘’ AS DEAL_DATE,
TO_CHAR(T.RECEIVE_DATE, ‘yyyy-mm-dd’) AS RECEIVE_DATE,
T.RECEIVE_TYPE,
T.RECEIVE_FILE_NO,
T.COME_FILE_NO,
T.SEND_FILE_ORG
FROM T_OA_RECEIVE_FILE_HISTORY T,
T_OA_FILE_RECEIVE_MULTI S
WHERE T.FILE_ID = S.RCV_FILE_ID
AND S.ROLE_ID = ‘1a213ba3-e487-467f-ad9a-453c6cfa6f3e’) MX
GROUP BY MX.ID,
MX.FILE_TITLE,
MX.FILE_ID,
MX.CREATE_ORG,
MX.CREATORNAME,
MX.CURRENTUSERNAME,
MX.FLOWTYPE,
MX.QUERY_DATE,
MX.CREATE_DATE,
MX.DEAL_DATE,
MX.RECEIVE_DATE,
MX.RECEIVE_TYPE,
MX.RECEIVE_FILE_NO,
MX.COME_FILE_NO,
MX.SEND_FILE_ORG) F
WHERE (1 = 1)
SQL优化要具体根据业务 还有SQL的执行计划 还有数据库参数等等方面进行优化。最好联系对应的技术人员进行调优
–SQL够复杂的，执行以下sql建索引、更新统计信息、调整参数试试效果
CREATE INDEX IDX_ROLE_ID_S ON T_OA_FILE_RECEIVE_MULTI(ROLE_ID);
STAT 100 ON T_OA_FILE_RECEIVE_MULTI(ROLE_ID);
CREATE INDEX IDX_OPERATOR_S ON T_OA_SEND_FILE_AUDIT_HISTORY(OPERATOR);
STAT 100 ON T_OA_SEND_FILE_AUDIT_HISTORY(OPERATOR);
CREATE INDEX IDX_FLOW_DATE_FL ON T_FLOW_LOG(FLOW_DATE DESC,FLOW_USER,FLOW_FILE_ID,FLOW_POINT);
STAT 100 ON T_FLOW_LOG(FLOW_DATE);
STAT 100 ON T_FLOW_LOG(FLOW_USER);
STAT 100 ON T_FLOW_LOG(FLOW_FILE_ID);
STAT 100 ON T_FLOW_LOG(FLOW_POINT);
SP_SET_PARA_VALUE(1,‘TOP_ORDER_OPT_FLAG’,1);
CREATE INDEX IDX_USER_ID_U ON R$USER(USER_ID);
STAT 100 ON R$USER(USER_ID);
create index idx_OPERATOR_DD ON T_OA_SEND_FILE_AUDIT(OPERATOR);
STAT 100 ON T_OA_SEND_FILE_AUDIT(OPERATOR);
–然后执行以下sql取一下执行计划，注意要取文本的执行计划
explain
SELECT
*
FROM
(
SELECT
MX.ID ,
MX.FILE_TITLE ,
MX.FILE_ID ,
MX.CREATE_ORG ,
MX.CREATORNAME ,
MX.CURRENTUSERNAME,
MX.FLOWTYPE ,
MX.QUERY_DATE ,
MX.CREATE_DATE ,
MX.DEAL_DATE ,
MX.RECEIVE_DATE ,
MX.RECEIVE_TYPE ,
MX.RECEIVE_FILE_NO,
MX.COME_FILE_NO ,
MX.SEND_FILE_ORG
FROM
(
SELECT
T.ID AS ID ,
T.FILE_TITLE,
T.FILE_ID ,
T.CREATE_ORG,
(
SELECT
CURRENT_DEAL_NAME
还有一个办法，可以在技术社区的云适配中心申请云数据库，把相关的表和数据导入进去，我们可以安排专家远程协助优化。http://eco.dameng.com/community/compatibility#/
联系客服
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
这个建议找对应项目的技术支持吧。
一般需要结合建表情况、索引情况、数据量，分析sql执行计划，找到慢的地方。
不能修改sql语句的情况下，看看能否通过创建合适的索引、或更新统计信息；还有看数据库配置参数是否合理，是否需要调整。