Rownum = 1000 is different from rown <= 1000!

Generally speaking, rownum = 1000 is used to retrieve one row of data.

Rown <= 1000 fetch more than 1000 rows of data.

The first data volume is much smaller than the second one, but it is slower than the second one!

 

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_ROWS IO cost = 274926 Time = 3425 Cardinality = 114351052 Bytes = 13950828344
COUNT
FILTER
Hash join io cost = 274926 Time = 3425 Cardinality = 114351052 Bytes = 13950828344
Table access full Object name = T_BASE_USER_PAY_TYPE IO cost = 5 Time = 1 Cardinality = 725 Bytes = 17400
Partition range all io cost = 274921 Time = 3409 Cardinality = 114351052 Bytes = 11206403096
Table access full Object name = T_GATHER_USERPAYLOG IO cost = 274921 Time = 3409 Cardinality = 114351052 Bytes = 11206403096

 

And rownum <= 1000

Select statement, GOAL = ALL_ROWS IO cost = 9 Time = 1 Cardinality = 1000 Bytes = 220000
COUNT STOPKEY
Hash join Io cost = 9 time = 1 cardinality = 114351052 bytes = 25157231440
Table access full object name = t_base_user_pay_type Io cost = 5 time = 1 cardinality = 725 bytes = 17400
Partition range all Io cost = 4 time = 1 cardinality = 1003 bytes = 98294
Table access full object name = t_gather_userpaylog Io cost = 4 time = 1 cardinality = 1003 bytes = 98294