Blob Memory usage

[realcolaflasche]

Hi,

i've a table with a blob and do something with it an the Memory raises and raises and I don't understand that. is that my mistake or an error?

This is my table:

create table NEW_TABLE (
    ID bigint generated by default as identity,
    TEXT blob sub_type 1 segment size 80,
    constraint PK_NEW_TABLE primary key (ID));`

The way to fill data:

execute block
as
declare variable i integer;
begin
  :i = 0;
  while (:i < 1000000) do
  begin
    insert into NEW_TABLE (TEXT)
    values (lpad('a', 10000, 'b'));

    :i = :i + 1;
  end
end

And then I load it in a variable and replace a text and the usage of RAM raises:

execute block
as
declare variable lBlob blob sub_type 1 segment size 80;
begin

     for
  select new_table.id
    from new_table
      as cursor curTest
      do
      begin
        in autonomous transaction do
        :lBlob = (select replace(new_table.text, 'c', 'd') from NEW_TABLE
         where new_table.id = curTest.id);
      end
end

Why does it happen? If i load it to the variable without replace the ram is slow but in this case, the blob in the RAM can be overwritten or set free after user.

Regards, Jan

[hvlad]

The second block creates temporary blob for every record in a table. These temporary blobs will be destroyed only when transaction ends (by commit or rollback). Engine put contents of temporary blobs into database file but keeps in memory some "locator" structure for every such blob. So, avoid massive modifying of blobs in the same transaction whenever possible. Note, autonomous transactions can't help here as blob's lifetime is bound to the main transaction by the good reasons.