Пара простых примеров по вчерашней дискуссии после семинара

1. Пример, показывающий короткие clob'ы с "enable storage in row" будут передаваться точно так же как и обычные:

SQL> create table xt_clob(c clob) lob(c) store as securefile(enable storage in row);

Table created.

SQL> insert into xt_clob select lpad('x',100) from dual connect by level<=10;

10 rows created.

SQL> commit;

Commit complete.

SQL> set autot trace stat
SQL> select * from xt_clob;

10 rows selected.


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         24  consistent gets
          5  physical reads
          0  redo size
       6748  bytes sent via SQL*Net to client
       8585  bytes received via SQL*Net from client
         12  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

2. Пример, показывающий что строчный before триггер без всяких дополнительный манипуляций прекрасно увеличивает current'ы:

SQL> drop table xt_curr purge;

Table dropped.

SQL> create table xt_curr as select level a,level b from dual connect by level<=1e4;

Table created.

SQL> set autot trace stat;
SQL> update xt_curr set b=a;

10000 rows updated.


Statistics
----------------------------------------------------------
         50  recursive calls
        130  db block gets
         52  consistent gets
         36  physical reads
     872008  redo size
        684  bytes sent via SQL*Net to client
        590  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> roll;
Rollback complete.
SQL> set autot off;
SQL> create or replace trigger tr_xt_curr before update on xt_curr for each row
  2  begin
  3    if :new.a !=:old.a and :new.b != :old.b then
  4      :new.b := :new.b/(:old.b - :new.b);
  5    end if;
  6  end;
  7  /

Trigger created.

SQL> set autot trace stat;
SQL> update xt_curr set b=a;

10000 rows updated.


Statistics
----------------------------------------------------------
          8  recursive calls
      20376  db block gets
         54  consistent gets
         18  physical reads
    4411636  redo size
        684  bytes sent via SQL*Net to client
        590  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> roll;
Rollback complete.
SQL> set autot off echo off;