Thanks for the question, Tom.

Asked: December 20, 2007 - 4:58 pm UTC

Last updated: January 01, 2008 - 5:58 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

When generating table defintions I was told or read (sorry I can't remember it was years ago) that columns that are updated frequently should be added to the end of a table definition. I was told/read that the oracle engine puts the entire row starting from the first column that is updated in the rollback segment.

I have searched the Oracle documentation(Database Reference, Concepts Guide, Performance Tuning,SQL Reference, Application Develepers Guide) to try and get some clarification but have not been able to find anything.

QUESTION:
Does the location of a column in a table have any performance impacts when updating records in a table that has approximately a billion records and is also wide 184 fields(2205 bytes)? For example if the updated field is the 2nd field on the table would it be better to have that field as the last on the table.

and Tom said...

Ok, some myths here. Let us take a look at the first one "I was told/read that the oracle engine puts the entire row starting from the first column that is updated in the rollback segment"

That one is trivial to 'bust'. Load up a table with at least three columns, put some BIG columns in between the first and last, update first - measure UNDO, update last - measure UNDO and - compare...

ops$tkyte%ORA9IR2> create table t
  2  as
  3  select 1 id1, rpad('*',4000,'*') data, 2 id2
  4    from all_objects
  5  /

Table created.

ops$tkyte%ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> update t set id1 = 999999999999;

30655 rows updated.

ops$tkyte%ORA9IR2> select used_ublk from v$transaction;

 USED_UBLK
----------
       327

ops$tkyte%ORA9IR2> commit;

Commit complete.

ops$tkyte%ORA9IR2> update t set id2 = 999999999999;

30655 rows updated.

ops$tkyte%ORA9IR2> select used_ublk from v$transaction;

 USED_UBLK
----------
       327

ops$tkyte%ORA9IR2> commit;

Commit complete.

so, same amount of undo. However, Oracle does have to parse the entire row to access the last column - it only parses a bit of the row in order to get to the first (we store "length", "data", "length", "data" .... in order to get to the end, you have to read and process all of the lengths skipping over the data).

So, accessing the last column of a table takes more CPU than accessing the first...

So, thus... I'd actually say "put it first", or put it in the middle or - well, where ever.

There are infinitely larger fish to fry here...

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for the example.

Tom, December 21, 2007 - 2:04 pm UTC

Yes, there are definitely larger fish to fry, and we are frying them...

We need to create a process that affects approximately 10-50 million records (extreme and unpredictable variance) bi weekly. We want to use the merge statement to affect a single SQL statement to do the updates and inserts. Due to the variance in number of records we receive we need to ensure we limit the overhead wherever possible to fit within our scheduled job time.

Your example gives me the response I need, I may have confused "the myth" with the oracle recommendation of having trailing null columns at the end of a table definition to limit row size.

Thanks again for the great response.

Is 10gR2 different ?

Tamil, December 27, 2007 - 4:39 pm UTC

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Dec 27 16:38:10 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter user-name: tamil
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> drop table t10 purge;

Table dropped.

SQL> create table t10 (id1 number(10), big_vc varchar2(4000), id2 number(10));

Table created.

SQL> insert into t10 select 1, rpad('*',2000,'*') , 2 from all_source;

105246 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'T10');

PL/SQL procedure successfully completed.


SQL> update t10 set id1 = 9999999999;

105246 rows updated.

SQL> select used_ublk from v$transaction ;

 USED_UBLK
----------
      2310     ------------>

SQL> update t10 set id2 = 9999999999;

105246 rows updated.

SQL> select used_ublk from v$transaction ;

 USED_UBLK
----------
      3695    --------------->


It seems to be that some thing changed in 10gR2 undo mechanism. 

Tamil

Is 10gR2 different?

Tamil, December 27, 2007 - 4:46 pm UTC

My 2nd test includes "commit" after the first update:

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T10');

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> update t10 set id1 = 9999999999;

105246 rows updated.

SQL>
SQL> select used_ublk from v$transaction ;

 USED_UBLK
----------
      2310  ---> 

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> update t10 set id2 = 9999999999;

105246 rows updated.

SQL>
SQL>
SQL> select used_ublk from v$transaction ;

 USED_UBLK
----------
      2417  --->

The number of undo blocks are not same. 

Tamil

December 28, 2007 - 4:00 pm UTC

well, in my example, I had one row per block (8k block, over 4000 bytes for a row, one row per block)

you have more than one row per block, you are probably MIGRATING rows after the second update (that would happen if you update a random column - doesn't matter the position)

Let me just flip flop your results and I can show that updating the last column generates less undo :) (but only when the update to the last column does not migrate the row!!)

ops$tkyte%ORA10GR2> create table t10 (id1 number(10), big_vc varchar2(4000), id2 number(10));

Table created.

ops$tkyte%ORA10GR2> insert into t10 select 1, rpad('*',2000,'*') , 2 from all_objects;

49834 rows created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats(user,'T10');

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> update t10 set id2 = 9999999999;

49834 rows updated.

ops$tkyte%ORA10GR2> select used_ublk from v$transaction ;

 USED_UBLK
----------
       992

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> update t10 set id1 = 9999999999;

49834 rows updated.

ops$tkyte%ORA10GR2> select used_ublk from v$transaction ;

 USED_UBLK
----------
      1042

No Row Migration

Tamil, December 29, 2007 - 9:02 am UTC

>>you have more than one row per block, you are probably MIGRATING rows after the second update (that would happen if you update a random column - doesn't matter the position) 


Yes, I have more than one row per block. 

But after the 2nd update, row migration did not happen.

SQL> select count(*) from t10;

  COUNT(*)
----------
    105246

SQL> analyze table t10 list chained rows ;

Table analyzed.

SQL> select * from CHAINED_ROWS ;

no rows selected

Tamil

January 01, 2008 - 5:58 pm UTC

if not migrated, then the block probably needed some reorganization on the second update, to avoid the row migrating...

did you do it in reverse order - to "prove" that updating the last column generates LESS undo than the first did?

that is the point here - it is the SECOND UPDATE that generated SLIGHTLY more undo - not that you updated the last column....

More or less undo

Tamil, January 02, 2008 - 5:44 pm UTC

>>did you do it in reverse order - to "prove" that updating the last column generates LESS undo than the first did?

>>that is the point here - it is the SECOND UPDATE that generated SLIGHTLY more undo - not that you updated the last column....

OK, I got what you are saying..

I did the reverse test.

SQL> insert into t10 select 1, rpad('*',2000,'*') , 2 from all_source;

105246 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'T10');

PL/SQL procedure successfully completed.

SQL> update t10 set id2 = 9999999999;

105246 rows updated.

SQL> select used_ublk from v$transaction ;

 USED_UBLK
----------
      2310   --->

SQL> commit;

Commit complete.

SQL> update t10 set id1 = 9999999999;

105246 rows updated.

SQL> select used_ublk from v$transaction ;

 USED_UBLK
----------
      2417  --->

SQL> spool off

Thanks for your help. 
Tamil

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Thanks for the example.

Is 10gR2 different ?

Is 10gR2 different?

No Row Migration

More or less undo

More to Explore

PL/SQL demos

PL/SQL docs

Performance Tuning - table column order

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Thanks for the example.

Is 10gR2 different ?

Is 10gR2 different?

No Row Migration

More or less undo

More to Explore

PL/SQL demos

PL/SQL docs