Posted by Sadly there seems to be no concept of the Hakan factor for an IOT.
I have an application which merges into an IOT, the merge incrementally populating a swag of initially null columns, hence growing the rows in size. Some simple benchmarking shows the overhead of this versus merging into a table with pre-populated values:
SQL> create table T1
2 ( x int primary key,
3 y1 number(10),
4 y2 number(10),
5 y3 number(10),
6 y4 number(10),
7 y5 number(10),
8 y6 number(10),
9 y7 number(10),
10 y8 number(10),
11 y9 number(10),
12 y0 number(10)
13 )
14 organization index
15 /
Table created.
SQL> create table T2
2 ( x int primary key,
3 y1 number(10),
4 y2 number(10),
5 y3 number(10),
6 y4 number(10),
7 y5 number(10),
8 y6 number(10),
9 y7 number(10),
10 y8 number(10),
11 y9 number(10),
12 y0 number(10)
13 )
14 organization index
15 /
Table created.
SQL> insert into t1 (x) select rownum from dual connect by level insert into t2 select rownum,123,123,123,123,123,123,123,123,123,123 from dual connect by level commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user,'T1')
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'T2')
PL/SQL procedure successfully completed.
SQL> alter session set events = '10046 trace name context forever, level 8';
Session altered.
SQL> merge into t1
2 using ( select rownum x,
3 12 y1,
4 23 y2,
5 34 y3,
6 45 y4,
7 56 y5,
8 67 y6,
9 78 y7,
10 89 y8,
11 90 y9,
12 100 y0
13 from dual
14 connect by level merge into t2
2 using ( select rownum x,
3 12 y1,
4 23 y2,
5 34 y3,
6 45 y4,
7 56 y5,
8 67 y6,
9 78 y7,
10 89 y8,
11 90 y9,
12 100 y0
13 from dual
14 connect by level disc
So T1 was a table with mainly null columns. The trace file shows this result
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.57 0.57 0 770 56302 20000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.57 0.58 0 770 56302 20000
whereas when you compare it to T2 with the pre-populated “junk”
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.49 0.49 0 1046 20884 20000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.49 0.49 0 1046 20884 20000
Now of course you can get those better results with T1 by rebuilding the IOT with plenty of free space to hold the row expansion. Repeating the merge after a “alter table move pctfree 90” gives a trace result of:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.51 0.54 367 6396 20418 20000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.53 0.56 367 6396 20418 20000
but of course, I’ve also just smashed a PCTFREE 90 growth into all of the already merged (or “filled”) rows.
The Hakan factor would be a nice fix….but alas
SQL> alter table T1 minimize records_per_block;
alter table T1 minimize records_per_block
*
ERROR at line 1:
ORA-28601: invalid [no]MINIMIZE option
(Tested on v10, 11 and 12)
Learning is not a spectator sport 
Hi,
there are erratas in alias (t1 and t2) in merges. Example:
merge into t1
2 using ( select rownum x,
3 12 y1,
4 23 y2,
5 34 y3,
6 45 y4,
7 56 y5,
8 67 y6,
9 78 y7,
10 89 y8,
11 90 y9,
12 100 y0
13 from dual
14 connect by level <= 20000
15 ) m
16 on ( t2.x = m.x )
17 when matched then
18 update
19 set
20 t2.y1 = m.y1,
21 t2.y2 = m.y2,
22 t2.y3 = m.y3,
23 t2.y4 = m.y4,
24 t2.y5 = m.y5,
25 t2.y6 = m.y6,
26 t2.y7 = m.y7,
27 t2.y8 = m.y8,
28 t2.y9 = m.y9,
29 t2.y0 = m.y0;
on ( t2.x = m.x )
*
ERROR en línea 16:
ORA-00904: "T2"."X": identificador no válido