原sql:
select M.* from (
SELECT T.*
FROM (SELECT b.b1,
c.c1
FROM BTable b
LEFT JOIN ATable a
ON b.ID = a.BID
INNER JOIN CTable c
ON b.c2 = c.c2
WHERE (b.INS_STATUS = 26 AND
a.PA_STATUS = 1 OR
b.INS_STATUS IN
(24, 13))
AND (b.OPE_DATE > a.OPE_DATE OR
a.a.OPE_DATE IS NULL OR
a.RM_STATUS = 0)
AND b.OPE_DATE >= SYSDATE - 60
ORDER BY T.OPERATE_DATE
) T
) M
where ROWNUM<10000
之后的sql:
select M.* from (
SELECT T.*
FROM (SELECT b.b1,
c.c1
FROM BTable b
LEFT JOIN ATable a
ON b.ID = a.BID
INNER JOIN CTable c
ON b.c2 = c.c2
WHERE (b.INS_STATUS = 26 AND
a.PA_STATUS = 1 OR
b.INS_STATUS IN
(24, 13))
AND (b.OPE_DATE > a.OPE_DATE OR
a.a.OPE_DATE IS NULL OR
a.RM_STATUS = 0)
AND b.OPE_DATE >= SYSDATE - 60
AND ROWNUM>=1
) T ORDER BY T.OPERATE_DATE
) M
where ROWNUM<10000
这两条sql不同的就是我在条件里加了一句AND ROWNUM>=1并把排序放到了第二层，这样做之后查询速度变快了，有两个问题：这两个sql是等效的吗？如果等效为什么查询速度会提升？
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/912/viewspace-3131070/ ，如需转载，请注明出处，否则将追究法律责任。
