[20230103]COUNT STOPKEY operation.txt

[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.