有这么一条语句
SELECT *
FROM (SELECT bc.billno, bc.billtype, bc.usercardid, bc.custname,
bc.detailaddr, bc.booktime, to_char(bc.createtime,
'YYYY-MM-DD HH24:MI:SS'), (SELECT max(opername)
FROM v_workman
WHERE operid = bc.acceptman), (SELECT deptname
FROM ptdept
WHERE deptid = bc.workdep), (SELECT max(opername)
FROM v_workman
WHERE operid = bc.workman), bc.billstatus,
rownum myrownum
FROM v_billcontrol bc /*这是一个视图,3个表的union all*/
WHERE 1 = 1
AND bc.usercardid = '0601617') aaa
WHERE aaa.myrownum > 0
AND aaa.myrownum <= 60
执行速度很慢,大约30秒 但是其中的子查询
SELECT bc.billno, bc.billtype, bc.usercardid, bc.custname,
bc.detailaddr, bc.booktime, to_char(bc.createtime,
'YYYY-MM-DD HH24:MI:SS'), (SELECT max(opername)
FROM v_workman
WHERE operid = bc.acceptman), (SELECT deptname
FROM ptdept
WHERE deptid = bc.workdep), (SELECT max(opername)
FROM v_workman
WHERE operid = bc.workman), bc.billstatus,
rownum myrownum
FROM v_billcontrol bc /*这是一个视图,3个表的union all*/
WHERE 1 = 1
AND bc.usercardid = '0601617'
语句单独执行,速度很快,不到一秒的时间,不知道这是什么原因,请高手指教,谢谢!
SELECT *
FROM (SELECT bc.billno, bc.billtype, bc.usercardid, bc.custname,
bc.detailaddr, bc.booktime, to_char(bc.createtime,
'YYYY-MM-DD HH24:MI:SS'), (SELECT max(opername)
FROM v_workman
WHERE operid = bc.acceptman), (SELECT deptname
FROM ptdept
WHERE deptid = bc.workdep), (SELECT max(opername)
FROM v_workman
WHERE operid = bc.workman), bc.billstatus,
rownum myrownum
FROM v_billcontrol bc /*这是一个视图,3个表的union all*/
WHERE 1 = 1
AND bc.usercardid = '0601617') aaa
WHERE aaa.myrownum > 0
AND aaa.myrownum <= 60
执行速度很慢,大约30秒 但是其中的子查询
SELECT bc.billno, bc.billtype, bc.usercardid, bc.custname,
bc.detailaddr, bc.booktime, to_char(bc.createtime,
'YYYY-MM-DD HH24:MI:SS'), (SELECT max(opername)
FROM v_workman
WHERE operid = bc.acceptman), (SELECT deptname
FROM ptdept
WHERE deptid = bc.workdep), (SELECT max(opername)
FROM v_workman
WHERE operid = bc.workman), bc.billstatus,
rownum myrownum
FROM v_billcontrol bc /*这是一个视图,3个表的union all*/
WHERE 1 = 1
AND bc.usercardid = '0601617'
语句单独执行,速度很快,不到一秒的时间,不知道这是什么原因,请高手指教,谢谢!
SELECT bc.billno, bc.billtype, bc.usercardid, bc.custname,
bc.detailaddr, bc.booktime, to_char(bc.createtime,
'YYYY-MM-DD HH24:MI:SS'), (SELECT max(opername)
FROM v_workman
WHERE operid = bc.acceptman), (SELECT deptname
FROM ptdept
WHERE deptid = bc.workdep), (SELECT max(opername)
FROM v_workman
WHERE operid = bc.workman), bc.billstatus,
ROW_NUMBER() OVER (ORDER BY bc.billno) AS RN
WHERE 1 = 1
AND bc.usercardid = '0601617'
)
SELECT * FROM Q
WHERE Q.RN BETWEEN 0 AND 60
bc.detailaddr, bc.booktime, to_char(bc.createtime,
'YYYY-MM-DD HH24:MI:SS'), (SELECT max(opername)
FROM v_workman
WHERE operid = bc.acceptman), (SELECT deptname
FROM ptdept
WHERE deptid = bc.workdep), (SELECT max(opername)
FROM v_workman
WHERE operid = bc.workman), bc.billstatus,
ROW_NUMBER() OVER (ORDER BY bc.billno) AS RN
WHERE 1 = 1
AND bc.usercardid = '0601617'
)
SELECT * FROM Q
WHERE Q.RN BETWEEN 0 AND 60
FROM (SELECT bc.billno, bc.billtype, bc.usercardid, bc.custname,
bc.detailaddr, bc.booktime, to_char(bc.createtime,
'YYYY-MM-DD HH24:MI:SS'), (SELECT max(opername)
FROM v_workman
WHERE operid = bc.acceptman), (SELECT deptname
FROM ptdept
WHERE deptid = bc.workdep), (SELECT max(opername)
FROM v_workman
WHERE operid = bc.workman), bc.billstatus
FROM v_billcontrol bc /*这是一个视图,3个表的union all*/
WHERE 1 = 1
AND bc.usercardid = '0601617') aaa
WHERE rownum <= 60
bc.detailaddr, bc.booktime, to_char(bc.createtime,
'YYYY-MM-DD HH24:MI:SS'), (SELECT max(opername)
FROM v_workman
WHERE operid = bc.acceptman), (SELECT deptname
FROM ptdept
WHERE deptid = bc.workdep), (SELECT max(opername)
FROM v_workman
WHERE operid = bc.workman), bc.billstatus,
rownum myrownum
FROM v_billcontrol bc /*这是一个视图,3个表的union all*/
WHERE 1 = 1
AND bc.usercardid = '0601617'
AND rownum <= 60