Impact of Updating a Row Using a PL/SQL Record Type

Before I talk about the topic of this post I’d like to demonstrate a bit of obvious information just to get it in the forefront of your mind. First off we create a test table holding 5000 records.

create table redo_test as
select  rownum col1
,	rpad(rownum,100) col2
,	rpad(rownum,100) col3
from dual
connect by rownum <= 5000;
create index redo_test_i on redo_test(col1);
exec dbms_stats.gather_table_stats(user,'redo_test',cascade=>true)

And if we now update a 100 character column in each of the 5000 records we see some undo/redo generated:

conn /
update	redo_test
set	col3 = col3;
commit;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
undo change vector size                                              700132
redo size                                                           1432060

And if we update the same 5000 records but more columns we see an increased amount of undo/redo generated.

conn /
update	redo_test
set	col2 = col2
,	col3 = col3;
commit;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
undo change vector size                                             2026732
redo size                                                           4943892

All as expected, though what I didn’t expect was the undo/redo statistics to be so erratic – the values above are the most frequently output (further testing required to understand that oddity).

So, now we have all been taught how to suck eggs on to “Updating the Database with PL/SQL Record Values” – a quote and example from the Oracle documentation below:

A PL/SQL-only extension of the UPDATE statement lets you update database rows using a single variable of type RECORD or %ROWTYPE on the right side of the SET clause, instead of a list of fields.

UPDATE departments SET ROW = dept_info WHERE department_id = 300;

So let’s see what happens behind the scenes when we update a row using a record type:

conn /
alter session set sql_trace = true;
declare
	redo_rec	redo_test%ROWTYPE;
begin
	select * into redo_rec from redo_test where col1 = 1;

	update	redo_test
	set	row = redo_rec
	where	col1 = 1;
	commit;
end;
/

In the TKPROF output we see the SQL used by the update. It updates all columns in the table.

UPDATE REDO_TEST SET COL1 = :B1 ,COL2 = :B2 ,COL3 = :B3
WHERE
 COL1 = 1

So – if you are using record types to update records frequently or to update tables with large record sizes then you should maybe pause for thought. Likewise if you see SQL updating every column on a table this could be the reason. See an example below changing the value of only 1 column in our 5000 row table and the undo/redo generation jumping up by 50% due to updating extra columns we don’t need to change.

conn /
begin
	for i in (select * from redo_test) loop
		update	redo_test
		set	col2 = rpad('x',100)
		where	col1 = i.col1;
	end loop;
	commit;
end;
/

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
undo change vector size                                             1082416
redo size                                                           2537112

conn /
declare
	redo_rec	redo_test%ROWTYPE;
begin
	for i in (select * from redo_test) loop
		redo_rec.col1 := i.col1;
		redo_rec.col2 := rpad('x',100);
		redo_rec.col3 := i.col3;

		update	redo_test
		set	row = redo_rec
		where	col1 = i.col1;
	end loop;

	commit;
end;
/
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
undo change vector size                                             1462416
redo size                                                           3299680
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