Skip to Main Content
  • Questions
  • Performance imact of adding 200+ columns to a table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, sridhar kumar.

Asked: August 04, 2016 - 11:22 am UTC

Last updated: August 15, 2016 - 9:24 am UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Hi Team,

I have doubt on number of columns on a table. currently we have 66 columns in a table and we want to add extra 204 columns in that table. My question is it will impact the DB performance and lead to row chaining or not?

The DB block size is 16K. Please confirm the consequences of it.

Regards,
Sridhar

and Chris said...

The maximum number of columns Oracle can store in a row piece is 255. So your 270 columns will be split over two row pieces.

http://docs.oracle.com/database/121/CNCPT/logical.htm#CNCPT1055

These may be in different blocks or they may be in the same block. It depends on the values you're inserting and how much space there is left in the block at the time!

So if you select the whole row or columns at the "end" of the table, there will be extra work compared to a table with 255 columns or less.

You can see this in the example below. I create a 300 column table, insert one row and add an index.

Selecting just the first columns in the table gives two gets (one for the index, one for the row).

But selecting the whole row or just the columns at the end gives three gets (one for the index, two for the two row pieces).

declare
  tab_sql varchar2(4000);
  ins_sql varchar2(4000);
begin
with rws as (
  select 'create table t ( ' 
  || listagg('col'||rownum || ' int', ',') within group (order by rownum) 
  || ')' tab,
  'insert into t values ( ' 
  || listagg('1', ',') within group (order by rownum) 
  || ')' ins
  from dual connect by level <= 300
)
  select *
  into   tab_sql, ins_sql
  from rws;
  
  execute immediate tab_sql;
  execute immediate ins_sql;
end;
/
create index i on t(col1);

SQL> set autotrace trace stat
SQL> select /*+ index(t(col1))*/col1, col2, col3
  2  from t
  3  where  col1 = 1;


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        486  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> select /*+ index(t(col1))*/* from t
  2  where  col1 = 1;


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
      17929  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> select /*+ index(t(col1))*/col300
  2  from t
  3  where  col1 = 1;


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        374  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

So there will be a small amount of extra work for some queries.

You can't really avoid this. Say you instead of altering your existing table you create a new one instead. Now you can have less than 255 rows in each.

But!

If you need to access all the columns you now need to join the two tables. So you've still got two gets to access the blocks from both tables. And you've probably got an extra index lookup for the new table too. So you've saved yourself one issue and created another!

As always test in your environment. Add the columns, see what impact it has. Consider alternatives if you need to based on the outcome of these tests.

Rating

  (2 ratings)

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

Comments

Extra Warning

Jonathan Lewis, August 14, 2016 - 9:50 am UTC

A detail that's not in the manuals and is often overlooked is that the split for a row definition of more than 255 columns takes place from the END, not the beginning. So a row with 270 columns will (at least for simple inserts that include the 270th column) consist of a head-piece of 15 columns and a chained piece of 255 columns.

There are other nasty side-effects to consider beyond the "litle bit of extra work" when you select a single row.

http://jonathanlewis.wordpress.com/2015/02/19/255-columns/

Regards
Jonathan Lewis

Connor McDonald
August 15, 2016 - 9:24 am UTC

Thanks for the extra details Jonathan.

Every time excellent explanation

sridhar kumar sahu, August 16, 2016 - 6:20 am UTC

As usual no matter what type of doubts i used to ask on this blog i get a fully practical explanation.

I really appreciate this help.

Regards,
Sridhar

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions