I am trying to derive a piece of generic logic that would cut in chunks of definite size any big table. The goal is to perform update in chunks and avoid rollback too small issues. The full table scan on the update is unavoidable, since the update target every row of the table.
The BIGTABLE has 63 millions rows. The purpose of the bellow SQL to give the ROWID every two million rows. So I am using the auto row numering field 'rownum' and perfrom a test to see I could. I expected the fist chunk to have 2 millons rows but in fact it is not the case:
Here is the code +(NOTE I had many problems with quotes, so some ROWID appears without their enclosing quotes or they disappear from current output here)+:
select rn, mod, frow, rownum from (
select rowid rn , rownum frow, mod(rownum, 2000000) mod
from bigtable order by rn) where mod = 0
/
SQL> /
RN MOD FROW ROWNUM
------------------ ---------- ---------- ----------
AAATCjAA0AAAKAVAAd 0 4000000 1
AAATCjAA0AAAPUEAAv 0 10000000 2
AAATCjAA0AAAbULAAx 0 6000000 3
AAATCjAA0AAAsIeAAC 0 14000000 4
AAATCjAA0AAAzhSAAp 0 8000000 5
AAATCjAA0AABOtGAAa 0 26000000 6
AAATCjAA0AABe24AAE 0 16000000 7
AAATCjAA0AABjVgAAQ 0 30000000 8
AAATCjAA0AABn4LAA3 0 32000000 9
AAATCjAA0AAB3pdAAh 0 20000000 10
AAATCjAA0AAB5dmAAT 0 22000000 11
AAATCjAA0AACrFuAAW 0 36000000 12
AAATCjAA6AAAXpOAAq 0 2000000 13
AAATCjAA6AAA8CZAAO 0 18000000 14
AAATCjAA6AABLAYAAj 0 12000000 15
AAATCjAA6AABlwbAAg 0 52000000 16
AAATCjAA6AACBEoAAM 0 38000000 17
AAATCjAA6AACCYGAA1 0 24000000 18
AAATCjAA6AACKfBABI 0 28000000 19
AAATCjAA6AACe0cAAS 0 34000000 20
AAATCjAA6AAFmytAAf 0 62000000 21
AAATCjAA6AAFp+bAA6 0 60000000 22
AAATCjAA6AAF6RAAAQ 0 44000000 23
AAATCjAA6AAHJjDAAV 0 40000000 24
AAATCjAA6AAIR+jAAL 0 42000000 25
AAATCjAA6AAKomNAAE 0 48000000 26
AAATCjAA6AALdcMAA3 0 46000000 27
AAATCjAA9AAACuuAAl 0 50000000 28
AAATCjAA9AABgD6AAD 0 54000000 29
AAATCjAA9AADiA2AAC 0 56000000 30
AAATCjAA9AAEQMPAAT 0 58000000 31
31 rows selected.
SQL> select count(*) from BIGTABLE where rowid < AAATCjAA0AAAKAVAAd ;
COUNT(*)
----------
518712 <-- expected around 2 000 000
SQL> select count(*) from BIGTABLE where rowid < AAATCjAA0AAAPUEAAv ;
COUNT(*)
----------
1218270 <-- expected around 4 000 000
SQL> select count(*) from BIGTABLE where rowid < AAATCjAA0AAAbULAAx ;
COUNT(*)
----------
2685289 <-- expected around 6 000 000
Amzingly, This code works perfectly for small tables but fails for big tables. Does anybody has an explanation and possibly a solution to this?
Here is the full code of the SQL that is suppposed to generated all the predicates I need to add to the UPdate statements in order to cut them in piece :
select line from (
with v as (select rn, mod, rownum frank from (
select rowid rn , mod(rownum, 2000000) mod
from BIGTABLE order by rn ) where mod = 0),
v1 as (
select rn , frank, lag(rn) over (order by frank) lag_rn from v ),
v0 as (
select count(*) cpt from v)
select 1, case
when frank = 1 then ' and rowid < ''' || rn || ''''
when frank = cpt then ' and rowid >= ''' || lag_rn ||''' and rowid < ''' ||rn || ''''
else ' and rowid >= ''' || lag_rn ||''' and rowid <'''||rn||''''
end line
from v1, v0
union
select 2, case
when frank = cpt then ' and rowid >= ''' || rn || ''''
end line
from v1, v0 order by 1)
/
and rowid < AAATCjAA0AAAKAVAAd
and rowid >= 'AAATCjAA0AAAKAVAAd' and rowid < 'AAATCjAA0AAAPUEAAv''
and rowid >= 'AAATCjAA0AAAPUEAAv' and rowid < 'AAATCjAA0AAAbULAAx''
and rowid >= 'AAATCjAA0AAAbULAAx' and rowid < 'AAATCjAA0AAAsIeAAC''
and rowid >= 'AAATCjAA0AAAsIeAAC' and rowid < 'AAATCjAA0AAAzhSAAp''
and rowid >= 'AAATCjAA0AAAzhSAAp' and rowid < 'AAATCjAA0AABOtGAAa''
and rowid >= 'AAATCjAA0AAB3pdAAh' and rowid < 'AAATCjAA0AAB5dmAAT''
and rowid >= 'AAATCjAA0AAB5dmAAT' and rowid < 'AAATCjAA0AACrFuAAW''
and rowid >= 'AAATCjAA0AABOtGAAa' and rowid < 'AAATCjAA0AABe24AAE''
and rowid >= 'AAATCjAA0AABe24AAE' and rowid < 'AAATCjAA0AABjVgAAQ''
and rowid >= 'AAATCjAA0AABjVgAAQ' and rowid < 'AAATCjAA0AABn4LAA3''
and rowid >= 'AAATCjAA0AABn4LAA3' and rowid < 'AAATCjAA0AAB3pdAAh''
and rowid >= 'AAATCjAA0AACrFuAAW' and rowid < 'AAATCjAA6AAAXpOAAq''
and rowid >= 'AAATCjAA6AAA8CZAAO' and rowid < 'AAATCjAA6AABLAYAAj''
and rowid >= 'AAATCjAA6AAAXpOAAq' and rowid < 'AAATCjAA6AAA8CZAAO''
and rowid >= 'AAATCjAA6AABLAYAAj' and rowid < 'AAATCjAA6AABlwbAAg''
and rowid >= 'AAATCjAA6AABlwbAAg' and rowid < 'AAATCjAA6AACBEoAAM''
and rowid >= 'AAATCjAA6AACBEoAAM' and rowid < 'AAATCjAA6AACCYGAA1''
and rowid >= 'AAATCjAA6AACCYGAA1' and rowid < 'AAATCjAA6AACKfBABI''
and rowid >= 'AAATCjAA6AACKfBABI' and rowid < 'AAATCjAA6AACe0cAAS''
and rowid >= 'AAATCjAA6AACe0cAAS' and rowid < 'AAATCjAA6AAFmytAAf''
and rowid >= 'AAATCjAA6AAF6RAAAQ' and rowid < 'AAATCjAA6AAHJjDAAV''
and rowid >= 'AAATCjAA6AAFmytAAf' and rowid < 'AAATCjAA6AAFp+bAA6''
and rowid >= 'AAATCjAA6AAFp+bAA6' and rowid < 'AAATCjAA6AAF6RAAAQ''
and rowid >= 'AAATCjAA6AAHJjDAAV' and rowid < 'AAATCjAA6AAIR+jAAL''
and rowid >= 'AAATCjAA6AAIR+jAAL' and rowid < 'AAATCjAA6AAKomNAAE''
and rowid >= 'AAATCjAA6AAKomNAAE' and rowid < 'AAATCjAA6AALdcMAA3''
and rowid >= 'AAATCjAA6AALdcMAA3' and rowid < 'AAATCjAA9AAACuuAAl''
and rowid >= 'AAATCjAA9AAACuuAAl' and rowid < 'AAATCjAA9AABgD6AAD''
and rowid >= 'AAATCjAA9AABgD6AAD' and rowid < 'AAATCjAA9AADiA2AAC''
and rowid >= 'AAATCjAA9AADiA2AAC' and rowid < 'AAATCjAA9AAEQMPAAT''
and rowid >= 'AAATCjAA9AAEQMPAAT''
33 rows selected.
SQL> select count(*) from BIGTABLE where 1=1 and rowid < AAATCjAA0AAAKAVAAd ;
COUNT(*)
----------
518712
SQL> select count(*) from BIGTABLE where 1=1 and rowid >= 'AAATCjAA9AAEQMPAAT'' ;
COUNT(*)
----------
1846369
Nice but not accurate....