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