rownum=1000 与 rown<=1000 速度不一样哦！

最新推荐文章于 2023-07-18 16:34:55 发布

iteye_3691

最新推荐文章于 2023-07-18 16:34:55 发布

阅读量167

点赞数

按常理来讲 rownum=1000 取第1000行的数据一条

rown<=1000 取1000行数据多条。

第一个数据量远小于第二条，然而比它还慢！

select F_ADDTIME,F_PAYCODE,t.f_note,
(CASE WHEN l.F_INOUT=0 THEN F_PAYMONEY END) AS MEONYOUT,
(CASE WHEN l.F_INOUT=1 THEN F_PAYMONEY END) AS MEONYIN,
F_POUNDAGE,F_HASPAY,F_CONTENT,F_ADMINNOTE,F_ADMINNAME
from T_gather_UserPayLog L
INNER JOIN t_base_user_pay_Type T on l.f_busino=t.f_id
WHERE F_BuySuc=1
and rownum=1000

SELECT STATEMENT, GOAL = ALL_ROWSIO cost=274926Time=3425Cardinality=114351052Bytes=13950828344
COUNT
FILTER
HASH JOINIO cost=274926Time=3425Cardinality=114351052Bytes=13950828344
TABLE ACCESS FULLObject name=T_BASE_USER_PAY_TYPEIO cost=5Time=1Cardinality=725Bytes=17400
PARTITION RANGE ALLIO cost=274921Time=3409Cardinality=114351052Bytes=11206403096
TABLE ACCESS FULLObject name=T_GATHER_USERPAYLOGIO cost=274921Time=3409Cardinality=114351052Bytes=11206403096

and rownum<=1000

SELECT STATEMENT, GOAL = ALL_ROWSIO cost=9Time=1Cardinality=1000Bytes=220000
COUNT STOPKEY
HASH JOINIO cost=9Time=1Cardinality=114351052Bytes=25157231440
TABLE ACCESS FULLObject name=T_BASE_USER_PAY_TYPEIO cost=5Time=1Cardinality=725Bytes=17400
PARTITION RANGE ALLIO cost=4Time=1Cardinality=1003Bytes=98294
TABLE ACCESS FULLObject name=T_GATHER_USERPAYLOGIO cost=4Time=1Cardinality=1003Bytes=98294