Move rows from one table to another

— Problem

as shown below this procedure “INSERT .. SELECT” + “DELETE” moves off some rows from a source table into a logging table:

create table source_t (rec_id number, rec_value varchar2(30)) nologging;

create table logging_t nologging
as select * from source_t where 1=0;

insert into source_t select object_id, object_type from dba_objects;

commit;

begin
    insert /*+ append */ into logging_t select * from source_t where rec_value like '%INDEX%';

    -- when inserting rows in production environment, the source_t table could have been updated,
    -- a sequential delete, even with same conditions, can delete different set of rows.
    delete from source_t where rec_value like '%INDEX%';

    commit;

end;
/

However, this procedure has potential inconsistent-read problem. As the source table is updated frequently, when inserting records into logging table extracted from source table, it could has been already updated with new records, as a result when calling a sequential DELETE, even with a same condition, the DELETE could operate on a different set of rows. That means some rows could be deleted without inserting into logging table. It can be demonstrated as:

Session 1 Session 2
T1 start session 1
T2 start session 2
T3
select 'source_t' tablename, count(*) from source_t
union all
select 'logging_t', count(*) text from logging_t;

TABLENAME   COUNT(*)
--------- ----------
source_t        75524
logging_t          0
T4
select 'source_t' tablename, count(*) from source_t
union all
select 'logging_t', count(*) text from logging_t;

TABLENAME   COUNT(*)
--------- ----------
source_t        75524
logging_t          0
T5
begin
insert /*+ append */ into logging_t select * from source_t where rec_value like '%INDEX%';

-- reserve 60 secs to be able to insert new records into source table from session 2
dbms_lock.sleep(60);

delete from source_t where rec_value like '%INDEX%';

commit;

end;
/
T6 — while procedure from T5 is running …

insert into source_t values(9999, 'NEW_INDEX');

commit;

select 'source_t' tablename, count(*) from source_t
union all
select 'logging_t', count(*) text from logging_t;

TABLENAME   COUNT(*)
--------- ----------
source_t        71075
logging_t       4450
 ----------
sum            75525
T7 — Procedure from T5 finishes here

select 'source_t' tablename, count(*) from source_t
union all
select 'logging_t', count(*) text from logging_t;

TABLENAME   COUNT(*)
--------- ----------
source_t        71074
logging_t       4450
 ----------
sum            75524

as you can see the new row inserted at T6 is deleted from source table but not recorded in logging table.

First Attempt: “set transaction isolation level serializable”

The first thing came into my mind to fix the problem is to “set transaction isolation level serializable” (another option is “READ COMMIT”) which means that session should see database as “frozen” at time when it calls “set transaction isolation level serializable”;

I demonstrate the steps as follow:

Session 1 Session 2
T1 start session 1
T2 start session 2
T3

drop table source_t;
drop table logging_t;

create table source_t (rec_id number, rec_value varchar2(30)) nologging;

create table logging_t nologging as select * from source_t where 1=0;

insert into source_t select object_id, object_type from dba_objects;

commit;

select 'source_t' tablename, count(*) from source_t
union all
select 'logging_t', count(*) text from logging_t;

TABLENAME   COUNT(*)
--------- ----------
source_t        75524
logging_t          0
T4
select 'source_t' tablename, count(*) from source_t
union all
select 'logging_t', count(*) text from logging_t;

TABLENAME   COUNT(*)
--------- ----------
source_t        75524
logging_t          0
T5
begin

set transaction isolation level serializable;

insert /*+ append */ into logging_t select * from source_t where rec_value like '%INDEX%';

-- reserve 60 secs to be able to insert new records into source table from session 2
dbms_lock.sleep(60);

delete from source_t where rec_value like '%INDEX%';

commit;

end;
/
T6 — while procedure from T5 is running …

insert into source_t values(9999, 'NEW_INDEX');

commit;

select 'source_t' tablename, count(*) from source_t
union all
select 'logging_t', count(*) text from logging_t;

TABLENAME   COUNT(*)
--------- ----------
source_t        71075
logging_t       4450
 ----------
sum            75525
T7 — Procedure from T5 ends with error here

begin
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction
ORA-06512: at line 10

As shown at the beginning of transaction at T5 I set transaction isolation level to “serializable”, when it calls DELETE statement, it found conflicts in resultset returned by a same condition and issued an error ORA-08177.

The attempt with “set transaction isolation level serializable” fails.

Second attempt: using flashback query

This method works with potential caveats!

As the database is 10.2.0.4, it’s possible to use flashback query feature to fetch a set of rows specific to a certain scn or timestamp in the past and pass same set of rows to “insert” and “delete” . The procedure is demonstrated below:

Session 1 Session 2
T1 start session 1
T2 start session 2
T3

drop table source_t;
drop table logging_t;

create table source_t (rec_id number, rec_value varchar2(30)) nologging;

create table logging_t nologging as select * from source_t where 1=0;

insert into source_t select object_id, object_type from dba_objects;

commit;

select 'source_t' tablename, count(*) from source_t
union all
select 'logging_t', count(*) text from logging_t;

TABLENAME   COUNT(*)
--------- ----------
source_t        75524
logging_t          0
T4
select 'source_t' tablename, count(*) from source_t
union all
select 'logging_t', count(*) text from logging_t;

TABLENAME   COUNT(*)
--------- ----------
source_t        75524
logging_t          0
T5
declare
  l_curr_scn number;
begin
  l_curr_scn := dbms_flashback.get_system_change_number();

  insert /*+ append */ into logging_t
  select * from source_t as of scn l_curr_scn
  where rec_value like '%INDEX%';

-- sleep for 60 secs for inserting new rows into source table from session 2
  dbms_lock.sleep(60);

  delete from source_t
  where rowid in (select rowid
                    from source_t as of scn l_curr_scn
                   where rec_value like '%INDEX%');

  commit;

end;
/
T6 — while procedure from T5 is running …

insert into source_t values(9999, 'NEW_INDEX');

commit;

select 'source_t' tablename, count(*) from source_t
union all
select 'logging_t', count(*) text from logging_t;

TABLENAME   COUNT(*)
--------- ----------
source_t        71075
logging_t       4450
 ----------
sum            75525
T7 — Procedure from T5 ends here

select 'source_t' tablename, count(*) from source_t
union all
select 'logging_t', count(*) text from logging_t;

TABLENAME   COUNT(*)
--------- ----------
source_t       71075
logging_t       4450
 ----------
sum            75525

The procedure succeeds to move off records. Unfortunately,  it has a potential problem.

Caveat:

the delete relies on consistent ROWIDs across a whole transaction. However there are some circumstances in which ROWIDs can be varied online as listed below:

  • delete + insert while “enable row movement” is set to table
  • alter table shrink space compact
  • alter table move;
  • flashback table;
  • if the table is partitioned, change the partition key will physically move a row

In these cases, delete .. where rowid = (select rowid ..) will not perform on same set of rows!

Possible fix:

This can be solved by using primary key, if any, in place of rowid.

Third Attempt: open a cursor at the beginning of transaction and pass same result set to insert and delete.

this approach is very similar to 2nd method “using flashback query feature”. It may work but face same risk of strictly relying on “rowid”. It can be solved by using a primary key, if there is any in the source table.

Most of steps are same as the ones shown in Second Attempt, except the step T5 is changed to:

Session 1 Session 2
T1 ..
T2 ..
T3 ..
T4 ..
T5
declare
 type t_source_rows is table of source_t%rowtype;
 type t_rowids is table of rowid;
 l_source_rows t_source_rows;
 l_rowids t_rowids;

 cursor l_cur is
 select rowid, s.* from source_t s where rec_value like '%INDEX%';

begin

 open l_cur;
 loop
 fetch l_cur bulk collect into l_rowids, l_source_rows limit 100;
 exit when l_cur%notfound;

 forall i in l_source_rows.first .. l_source_rows.last
 insert into logging_t values l_source_rows(i);

 forall i in l_rowids.first .. l_rowids.last
 delete from source_t where rowid = l_rowids(i);

 end loop;

 commit;
end;
/
T6 ..
T7 ..

When I tried to compile the procedure, I got this error message:

ERROR at line 14:
ORA-06550: line 14, column 46:
PLS-00597: expression 'L_SOURCE_ROWS' in the INTO list is of wrong type
ORA-06550: line 14, column 6:
PL/SQL: SQL Statement ignored

It seems we can bulk collect into

  • either a table of rows
  • or a set of tables of columns

but we can not get BOTH! In this case, it refused to fetch a collection of rowids(as part of a row) + a collection of rows themselves.

I replaced

type t_source_rows is table of source_t%rowtype;

with

type t_rec_id is table of source_t.rec_id%type;
type t_rec_value is table of source_t.rec_value%type;

then compiled the procedure again, it succeeded this time and here is the result.


declare
type t_rec_ids is table of source_t.rec_id%type;
type t_rec_values is table of source_t.rec_value%type;
type t_rowids is table of rowid;
l_rec_ids t_rec_ids;
l_rec_values t_rec_values;
l_rowids t_rowids;

cursor l_cur is
select rowid, rec_id, rec_value from source_t s where rec_value like '%INDEX%';

begin

open l_cur;
loop
fetch l_cur bulk collect into l_rowids, l_rec_ids, l_rec_values limit 100;
exit when l_cur%notfound;

forall i in l_rec_ids.first .. l_rec_ids.last
insert into logging_t values (l_rec_ids(i), l_rec_values(i)) ;

forall i in l_rowids.first .. l_rowids.last
delete from source_t where rowid = l_rowids(i);

end loop;

commit;
end;
/

Fourth Attempt: delete returning buck collect into + bulk collect insert

this method does not rely on rowid at all. It just returns all deleted rows into an array and then all bulk insert into target table.

declare

type t_rec_ids is table of source_t.rec_id%type;
type t_rec_values is table of source_t.rec_value%type;
type t_rowids is table of rowid;
l_rec_ids t_rec_ids;
l_rec_values t_rec_values;
l_rowids t_rowids;</pre>
begin
delete from source_t where rec_value like '%INDEX%' 
returning  red_id, rec_value bulk collect into l_rec_ids, l_rec_values;

forall i in 1 .. l_rec_ids.count
insert into t2 values (l_rec_ids(I), l_rec_values(i));
commit;
end;
/

However, if the amount of rows to delete is huge, we would use INSERT /*+ append */SELECT into a temporary table and then rename it in place of DELETE.

Advertisements

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

%d bloggers like this: