[20230103]COUNT STOPKEY operation.txt

--//链接:http://blog.tanelpoder.com/2010/10/25/count-stopkey-operation-the-where-rownum/
--//rownum限制仅仅显示2^32-1条,实际上12c版本已经修复了这个bug,通过例子验证.

1.环境:
SCOTT@test01p> @ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.测试:
with sq as (select null from dual connect by level <= 2000)
select count(*)
from sq a, sq b, sq c;

  COUNT(*)
----------
8000000000

with sq as (select null from dual connect by level <= 2000)
select count(*)
from sq a, sq b, sq c
where rownum <= 8000000000;

  COUNT(*)
----------
8000000000

--//11g下重复测试:
3.环境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

4.测试:
with sq as (select null from dual connect by level <= 2000)
select count(*)
from sq a, sq b, sq c;

  COUNT(*)
----------
8000000000

with sq as (select null from dual connect by level <= 2000)
select count(*)
from sq a, sq b, sq c
where rownum <= 8000000000;
  COUNT(*)
----------
4294967295

--//很明显11.2.0.4存在这个bug.