有这么一条语句
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'
语句单独执行,速度很快,不到一秒的时间,不知道这是什么原因,请高手指教,谢谢!
解决方案 »
- 游标取值问题
- 谁能推荐一些ORACLE ERP原理的书或教程....?
- 向高手请教:不重复数据的统计查询问题, 在线等,急用!
- imp时报错,该怎么解决?
- DBF数据恢复问题
- ★超急★修复了win2000后,oracle8起不来了!帮帮忙!
- 日期型转换的超级简单问题
- 我在SQL PLUS里执行一个SQL语句,执行后为什么好长时间没反映过了一会才执行.大家说说
- 帮忙看看这个游标那里写的有问题?
- select code from tb where tb.time >(select max(tb2.time) from tb2 where tb2.code = tb1.code),要求如tb2中纪录中没有对应的code,则
- 请问如何切换使用不同版本的oracle数据库?
- 如何从现有数据库中导出表关系?何种工具比较方便
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