drop table xt1     purge;
drop table i_stats purge;
alter session set optimizer_dynamic_sampling=0;
set serverout on;

create table xt1(a int, constraint pk_xt1 primary key(a));
insert into xt1 
   with t as (select 1 a from dual connect by level<=4e3)
   select rownum from t,t t2
   where rownum<=1e7;
commit;

analyze index pk_xt1 validate structure;
create table i_stats as 
   select 'without delete' v,st.*
   from index_stats st
   where st.name='PK_XT1';

declare 
   procedure stat_save(p_desc varchar2) is
   begin
      execute immediate 'analyze index pk_xt1 validate structure';
      insert into i_stats st 
         select p_desc,st.*
         from index_stats st
         where st.name='PK_XT1';
      commit;
   end;

   procedure p_insert_delete(n int) is
   begin
      insert into xt1
         with t as (select 1 a from dual connect by level<=1e3)
         select rownum+n*1e6 from t,t t2;
      delete from xt1 where mod(a,10)!=0;
      commit;
   end;   

   procedure p_test(p_oper varchar2) is

      procedure p_iter(i int) is
      begin
         p_insert_delete(i);
         stat_save(p_oper || i || '_' || 0);
         execute immediate 'alter index pk_xt1 '||p_oper;
         stat_save(p_oper || i || '_' || 1);
      end;
   begin
      execute immediate 'drop table xt1     purge';
      execute immediate 'create table xt1(a int, constraint pk_xt1 primary key(a))';

      for i in 1..10 loop
         p_iter(i);
      end loop;
   end;
      
begin
   p_test('coalesce');
   p_test('rebuild');
end;
/
select 
 v,height,blocks
 ,lf_rows,lf_blks,lf_rows_len
 ,br_rows,br_blks,br_rows_len
 ,btree_space,used_space,pct_used
from i_stats i;

V                  HEIGHT     BLOCKS    LF_ROWS    LF_BLKS LF_ROWS_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BTREE_SPACE USED_SPACE   PCT_USED
-------------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ----------- ----------- ---------- ----------
without delete          3      20480   10000000      19875   158888893      19874         31      236578   159248992  159125471        100
coalesce1_0             3       2048    1000000       2000    15989899       1999          4       23889    16032128   16013788        100
coalesce1_1             3       2048     100000        223     1589899        222          4        2643     1816128    1592542         88
coalesce2_0             3       3200    1099796       2223    17576534       2222          7       26643    17840224   17603177         99
coalesce2_1             3       3200     200000        446     3179798        445          7        5296     3624224    3185094         88
coalesce3_0             3       5248    1199627       2446    19163729       2445          9       29296    19640288   19193025         98
coalesce3_1             3       5248     300000        669     4769697        668          9        7949     5424288    4777646         89
coalesce4_0             3       6272    1299625       2669    20753596       2668         12       31949    21448384   20785545         97
coalesce4_1             3       6272     400000        893     6359596        892         12       10614     7240384    6370210         88
coalesce5_0             3       7936    1399610       2893    22343255       2892         14       34614    23256448   22377869         97
coalesce5_1             3       7936     500000       1116     7949495       1115         14       13266     9040448    7962761         89
coalesce6_0             3      10240    1499623       3116    23933362       3115         17       37266    25064544   23970628         96
coalesce6_1             3      10240     600000       1340     9539394       1339         17       15931    10856544    9555325         89
coalesce7_0             3      11264    1599610       3340    25523053       3339         19       39931    26872608   25562984         96
coalesce7_1             3      11264     700000       1563    11129293       1562         19       18583    12656608   11147876         89
coalesce8_0             3      13312    1699623       3563    27113160       3562         22       42583    28680704   27155743         95
coalesce8_1             3      13312     800000       1787    12719192       1786         22       21248    14472704   12740440         89
coalesce9_0             3      15360    1799610       3787    28702851       3786         24       45248    30488768   28748099         95
coalesce9_1             3      15360     900000       2010    14309091       2009         24       23900    16272768   14332991         89
coalesce10_0            3      16384    1899623       4010    30292958       4009         27       47900    32296864   30340858         94
coalesce10_1            3      16384    1000000       2234    15898990       2233         27       26565    18088864   15925555         89
rebuild1_0              3       2048    1000000       2000    15989899       1999          4       23889    16032128   16013788        100
rebuild1_1              2        256     100000        222     1589899        221          1        2630     1784032    1592529         90
rebuild2_0              3       2304    1099706       2222    17575094       2221          5       26630    17816160   17601724         99
rebuild2_1              2        512     200000        444     3179798        443          1        5272     3560032    3185070         90
rebuild3_0              3       2560    1199815       2444    19166737       2443          5       29272    19592160   19196009         98
rebuild3_1              2        768     300000        666     4769697        665          1        7914     5336032    4777611         90
rebuild4_0              3       2816    1299924       2666    20758380       2665          5       31914    21368160   20790294         98
rebuild4_1              3       1024     400000        887     6359596        886          3       10544     7120096    6370140         90
rebuild5_0              3       3072    1399630       2887    22343575       2886          6       34544    23144192   22378119         97
rebuild5_1              3       1152     500000       1109     7949495       1108          3       13184     8896096    7962679         90
rebuild6_0              3       3200    1499739       3109    23935218       3108          6       37184    24920192   23972402         97
rebuild6_1              3       1408     600000       1331     9539394       1330          3       15826    10672096    9555220         90
rebuild7_0              3       3456    1599849       3331    25526877       3330          6       39826    26696192   25566703         96
rebuild7_1              3       1664     700000       1553    11129293       1552          4       18468    12456128   11147761         90
rebuild8_0              3       3712    1699958       3553    27118520       3552          7       42468    28480224   27160988         96
rebuild8_1              3       1920     800000       1774    12719192       1773          4       21098    14224128   12740290         90
rebuild9_0              3       3968    1799664       3774    28703715       3773          7       45098    30248224   28748813         96
rebuild9_1              3       2048     900000       1996    14309091       1995          4       23738    16000128   14332829         90
rebuild10_0             3       4096    1899773       3996    30295358       3995          7       47738    32024224   30343096         95
rebuild10_1             3       2304    1000000       2218    15898990       2217          5       26380    17784160   15925370         90