我这因为要做一个统计,所以写了这么一个sql语句:
select * from (select decode(a.Status,0,'受理',1,'承办环节',2,'审核环节',3,'批准环节',4,'办结环节',100,'办结',a.Status) as Status_NAME,
a.Status,a.nid,to_char(a.ISSUE_DATE,'YYYY-MM-DD') as ISSUE_DATE,a.FILE_ID,a.APP_UINT,a.APP_INFO,a.TRANSACT,
nvl(c.UserNAme,a.TRANSACT) as CURRENT_TRANSACT,a.SHENPISHIXIAN,b.name,nvl(d.bjjg,1000) as bjjg,a.EXAM_TYPE,a.ADDRESS,a.JLR,a.IS_BIGPRJ,
a.RETURN_RES,e.NAME as BJJG_NAME from EXAM_ACCEPT a,EXAM_DIC_FLOW b,
(select e.*,g.UserNAme from EXAM_LOG e,Sys_Users g where e.RECEIVER = g.usercode and NID=(select max(NID) from EXAM_LOG f where e.FILE_NUMBER=f.FILE_NUMBER)) c,
EXAM_END d,(select * from SYS_DIC where DIC_TYPE=3) e
where a.Exam_Type=b.opration_code and a.nid=c.FILE_NUMBER(+) and a.NID=d.file_id(+) and d.bjjg=e.NID(+) and a.TAG_DEL<>1 ) t where 1=1
order by t.Issue_Date desc
但是它很耗费时间,这几个表里最多记录的也就2万多条记录,但这语句一执行就要差不多一小时才出结果,造成慢的语句是第5行搜索max(NID)的那部分特费时间。不知道有什么办法优化一下不?
我试过把*换成具体列名也还是一样慢。
select * from (select decode(a.Status,0,'受理',1,'承办环节',2,'审核环节',3,'批准环节',4,'办结环节',100,'办结',a.Status) as Status_NAME,
a.Status,a.nid,to_char(a.ISSUE_DATE,'YYYY-MM-DD') as ISSUE_DATE,a.FILE_ID,a.APP_UINT,a.APP_INFO,a.TRANSACT,
nvl(c.UserNAme,a.TRANSACT) as CURRENT_TRANSACT,a.SHENPISHIXIAN,b.name,nvl(d.bjjg,1000) as bjjg,a.EXAM_TYPE,a.ADDRESS,a.JLR,a.IS_BIGPRJ,
a.RETURN_RES,e.NAME as BJJG_NAME from EXAM_ACCEPT a,EXAM_DIC_FLOW b,
(select e.*,g.UserNAme from EXAM_LOG e,Sys_Users g where e.RECEIVER = g.usercode and NID=(select max(NID) from EXAM_LOG f where e.FILE_NUMBER=f.FILE_NUMBER)) c,
EXAM_END d,(select * from SYS_DIC where DIC_TYPE=3) e
where a.Exam_Type=b.opration_code and a.nid=c.FILE_NUMBER(+) and a.NID=d.file_id(+) and d.bjjg=e.NID(+) and a.TAG_DEL<>1 ) t where 1=1
order by t.Issue_Date desc
但是它很耗费时间,这几个表里最多记录的也就2万多条记录,但这语句一执行就要差不多一小时才出结果,造成慢的语句是第5行搜索max(NID)的那部分特费时间。不知道有什么办法优化一下不?
我试过把*换成具体列名也还是一样慢。
解决方案 »
- 请问谁做过EBS中HXC模块的二次开发啊?有点是求助
- 两个日期的天数差
- 存储过程,为什么我在存储过程中不能对其他用户的表进行操作。
- SQL SERVER 客户端网络链接ORACLE 遇到的问题
- OracleConnection 连接ORACLE数据库的字符串如何写??
- 初学者问题:SQL*PLUS WORKSHEET和oracle slq*plus这两个查询窗口有什么区别?
- 可以向视图插入数据吗?
- sql中怎样等待一段时间。
- linux上的Oracle817错误ORA-12545
- PL/SQL上如何把产生的XML档用zip压缩
- 表进行分区后,没有用到这一列的查询的速度仍然无法提高的问题
- 大家帮帮解释这个pl/sql是什么意思,共4行
创建如下index,并修改代码如下:
CREATE INDEX i_EXAM_LOG_aaa ON EXAM_LOG(FILE_NUMBER,MNID DESC);SELECT *
FROM (SELECT DECODE(A.STATUS,
0,
'受理',
1,
'承办环节',
2,
'审核环节',
3,
'批准环节',
4,
'办结环节',
100,
'办结',
A.STATUS) AS STATUS_NAME,
A.STATUS,
A.NID,
TO_CHAR(A.ISSUE_DATE, 'YYYY-MM-DD') AS ISSUE_DATE,
A.FILE_ID,
A.APP_UINT,
A.APP_INFO,
A.TRANSACT,
NVL(C.USERNAME, A.TRANSACT) AS CURRENT_TRANSACT,
A.SHENPISHIXIAN,
B.NAME,
NVL(D.BJJG, 1000) AS BJJG,
A.EXAM_TYPE,
A.ADDRESS,
A.JLR,
A.IS_BIGPRJ,
A.RETURN_RES,
E.NAME AS BJJG_NAME
FROM EXAM_ACCEPT A,
EXAM_DIC_FLOW B,
(SELECT E.*, G.USERNAME
FROM EXAM_LOG E,
SYS_USERS G,
(SELECT MAX(NID) MNID, FILE_NUMBER
FROM EXAM_LOG
GROUP BY FILE_NUMBER) X
WHERE E.RECEIVER = G.USERCODE
AND E.NID = X.MNID
AND E.FILE_NUMBER = X.FILE_NUMBER) C,
EXAM_END D,
(SELECT * FROM SYS_DIC WHERE DIC_TYPE = 3) E
WHERE A.EXAM_TYPE = B.OPRATION_CODE
AND A.NID = C.FILE_NUMBER(+)
AND A.NID = D.FILE_ID(+)
AND D.BJJG = E.NID(+)
AND A.TAG_DEL <> 1) T
WHERE 1 = 1
ORDER BY T.ISSUE_DATE DESC;
中的MNID 要先声明一下吗,创建时报错“ORA-00904: "MNID": 无效的标识符”