No Hakan factor for IOT

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 <= 100000;

100000 rows created.

SQL> insert into t2 select rownum,123,123,123,123,123,123,123,123,123,123 from dual connect by level <= 100000;

100000 rows created.

SQL> 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 <= 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;

20000 rows merged.

SQL> 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 <= 20000
 15        ) m
 16  on ( t1.x = m.x )
 17  when matched then
 18  update
 19  set
 20  t1.y1 = m.y1,
 21  t1.y2 = m.y2,
 22  t1.y3 = m.y3,
 23  t1.y4 = m.y4,
 24  t1.y5 = m.y5,
 25  t1.y6 = m.y6,
 26  t1.y7 = m.y7,
 27  t1.y8 = m.y8,
 28  t1.y9 = m.y9,
 29  t1.y0 = m.y0;

20000 rows merged.

SQL> 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)

Advertisements

One thought on “No Hakan factor for IOT

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s