OK, here's my hypothesis. It would not suprise me if the entry from October 06, 2011 indeed revealed some sort of anomalous behaviour. Over time, there's been some issues with ASSM in terms of "runaway" segment growth, but I've rarely seen them on more recent releases. So I repeated the script, scaled up to 9000 iterations on both ASSM and non-ASSM. So first with ASSM
SQL> CREATE TABLE t1 (t1_key NUMBER(11), t2_col NUMBER(11)) TABLESPACE largets;
Table created.
SQL> CREATE UNIQUE INDEX pk_t1 ON t1(t1_key) TABLESPACE largets;
Index created.
SQL> ALTER TABLE t1 ADD CONSTRAINT pk_t1 PRIMARY KEY (t1_key) USING INDEX pk_t1;
Table altered.
SQL>
SQL> set serverout on
SQL> DECLARE
2 idx NUMBER (11);
3 bulk_size NUMBER(11);
4 s number;
5 t1 timestamp;
6 t2 timestamp;
7 BEGIN
8
9 EXECUTE IMMEDIATE 'TRUNCATE TABLE t1';
10
11 bulk_size := 1000;
12 idx := 0;
13
14 WHILE idx < 9000
15 LOOP
16 DELETE FROM t1;
17 COMMIT;
18
19 t1 := systimestamp;
20 INSERT INTO t1 (t1_key)
21 (SELECT LEVEL + idx * bulk_size
22 FROM DUAL
23 CONNECT BY LEVEL < bulk_size);
24 t2 := systimestamp;
25
26 COMMIT;
27
28 idx := idx + 1;
29 select bytes into s from user_segments where segment_name = 'PK_T1';
30 dbms_output.put_line(lpad(idx,8)||' - '||lpad(s,12)||', elapsed='||(t2-t1));
31 END LOOP;
32 END;
33 /
1 - 65536, elapsed=+000000000 00:00:00.008000000
2 - 65536, elapsed=+000000000 00:00:00.003000000
3 - 131072, elapsed=+000000000 00:00:00.002000000
4 - 131072, elapsed=+000000000 00:00:00.003000000
5 - 131072, elapsed=+000000000 00:00:00.002000000
6 - 131072, elapsed=+000000000 00:00:00.002000000
7 - 131072, elapsed=+000000000 00:00:00.003000000
8 - 131072, elapsed=+000000000 00:00:00.002000000
9 - 131072, elapsed=+000000000 00:00:00.003000000
10 - 131072, elapsed=+000000000 00:00:00.003000000
11 - 131072, elapsed=+000000000 00:00:00.002000000
...
...
175 - 2097152, elapsed=+000000000 00:00:00.002000000
176 - 2097152, elapsed=+000000000 00:00:00.002000000
177 - 2097152, elapsed=+000000000 00:00:00.002000000
178 - 2097152, elapsed=+000000000 00:00:00.004000000
179 - 2097152, elapsed=+000000000 00:00:00.004000000
180 - 2097152, elapsed=+000000000 00:00:00.003000000
181 - 2097152, elapsed=+000000000 00:00:00.002000000
182 - 2097152, elapsed=+000000000 00:00:00.002000000
183 - 2097152, elapsed=+000000000 00:00:00.002000000
184 - 2097152, elapsed=+000000000 00:00:00.002000000
185 - 2097152, elapsed=+000000000 00:00:00.003000000
186 - 2097152, elapsed=+000000000 00:00:00.002000000
187 - 2097152, elapsed=+000000000 00:00:00.002000000
...
...
8995 - 2097152, elapsed=+000000000 00:00:00.002000000
8996 - 2097152, elapsed=+000000000 00:00:00.002000000
8997 - 2097152, elapsed=+000000000 00:00:00.002000000
8998 - 2097152, elapsed=+000000000 00:00:00.002000000
8999 - 2097152, elapsed=+000000000 00:00:00.002000000
9000 - 2097152, elapsed=+000000000 00:00:00.003000000
PL/SQL procedure successfully completed.
SQL>
We grow to 2meg, but we never got any larger than that. And that is pretty much how I would expect ASSM to work, since thats almost a "design principle" for ASSM, namely, we're happy to be more loose with space in order to better serve concurrency/RAC etc. (The latter obviously not being tested in this scenario).
And on no-assm, we got a better storage result
SQL> CREATE TABLE t1 (t1_key NUMBER(11), t2_col NUMBER(11)) TABLESPACE no_assm;
Table created.
SQL> CREATE UNIQUE INDEX pk_t1 ON t1(t1_key) TABLESPACE no_assm;
Index created.
SQL> ALTER TABLE t1 ADD CONSTRAINT pk_t1 PRIMARY KEY (t1_key) USING INDEX pk_t1;
Table altered.
SQL>
SQL> set serverout on
SQL> DECLARE
2 idx NUMBER (11);
3 bulk_size NUMBER(11);
4 s number;
5 t1 timestamp;
6 t2 timestamp;
7 BEGIN
8
9 EXECUTE IMMEDIATE 'TRUNCATE TABLE t1';
10
11 bulk_size := 1000;
12 idx := 0;
13
14 WHILE idx < 9000
15 LOOP
16 DELETE FROM t1;
17 COMMIT;
18
19 t1 := systimestamp;
20 INSERT INTO t1 (t1_key)
21 (SELECT LEVEL + idx * bulk_size
22 FROM DUAL
23 CONNECT BY LEVEL < bulk_size);
24 t2 := systimestamp;
25
26 COMMIT;
27
28 idx := idx + 1;
29 select bytes into s from user_segments where segment_name = 'PK_T1';
30 dbms_output.put_line(lpad(idx,8)||' - '||lpad(s,12)||', elapsed='||(t2-t1));
31 END LOOP;
32 END;
33 /
1 - 1048576, elapsed=+000000000 00:00:00.006000000
2 - 1048576, elapsed=+000000000 00:00:00.002000000
3 - 1048576, elapsed=+000000000 00:00:00.002000000
4 - 1048576, elapsed=+000000000 00:00:00.002000000
5 - 1048576, elapsed=+000000000 00:00:00.002000000
6 - 1048576, elapsed=+000000000 00:00:00.002000000
...
...
8994 - 1048576, elapsed=+000000000 00:00:00.002000000
8995 - 1048576, elapsed=+000000000 00:00:00.002000000
8996 - 1048576, elapsed=+000000000 00:00:00.002000000
8997 - 1048576, elapsed=+000000000 00:00:00.002000000
8998 - 1048576, elapsed=+000000000 00:00:00.004000000
8999 - 1048576, elapsed=+000000000 00:00:00.002000000
9000 - 1048576, elapsed=+000000000 00:00:00.002000000
PL/SQL procedure successfully completed.
SQL>
but my point remains - 9,000,000 rows "exercised" and our index segment is still on 2meg under ASSM. I don't see *never ending* growth.