In my Oracle database I have a table with more than 100 columns, what would be the impact of adding more columns?

by Harish
(Noida)

Hi,


I am using Oracle database and i want to upgrade my table with more than 150 columns in it, will you please articulate me that what would be the impact of it on my application or in database.


Regards,
Harish

In general terms, the bigger a table the more work Oracle has to do behind the scenes to maintain it, but there are many factors that determine the size of the table - not just the number of columns. A table with just 1 CHAR column 200 bytes long would be approximately the same size as a table with 100 CHAR columns each 2 bytes long. Therefore the number of columns in a table doesn't by itself mean much.

There is an absolute limit on the number columns a table can have that is imposed by Oracle and the types of columns in the table may affect this as stated in the Oracle SQL Language Reference:

"The absolute maximum number of columns in a table is 1000. When you create an object table or a relational table with columns of object, nested table, varray, or REF type, Oracle Database maps the columns of the user-defined types to relational columns, in effect creating hidden columns that count toward the 1000-column limit."

As long as your table is within that limit you can have as many columns as you like.

A few factors that could make a difference to the performance of your Oracle database when adding more columns to a table are:
  • the size of the columns added
  • the number of new constraints added
  • the number of new indexes added
  • the number and complexity of triggers added
  • and the frequency of DML (updates, inserts, deletes) on the table.
If the columns are large VARCHAR2 columns or large objects (LOBs) that are initially created empty or are later updated and become much bigger than their original size, you may get block splitting and chaining which may affect performance.

Another aspect that may be affected by a large increase in the size of table is that of backup and recovery of your Oracle database. Obviously if add 150 LOBs each of several gigabytes in size (the maximum size of a lob is:
(4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage)
) to your table then backup and recovery could take a lot longer. This may mean that you need to reconsider the backup strategy and invest in more disk space and possibly RAM.

For more help with performance tuning see our
Oracle performance tuning overview or this question on the steps involved in performance tuning.

Contact us for details of our hands-on, instructor-led training in the UK and the US or our classroom-quality, online Oracle training.

Comments for In my Oracle database I have a table with more than 100 columns, what would be the impact of adding more columns?

Average Rating starstarstarstarstar

Click here to add your own comments

May 05, 2011
Rating
starstarstarstarstar
How row and column size affect performance
by: Vishal D. Kolekar

Now you come to know that increase is number of rows or columns will increase size of that table.

When you access any table then it get fetched from database to your RAM for further processing.
Let me tell you with example:

SQL> select * from employees;

when you execute this query then it will take 4-6 sec in general and when you execute next same query then it take 1-3 sec minimum.

So the question arises why is it faster the second time. The answer is because the second time Oracle gets table directly from RAM cache.

In case of large table system uses virtual memory that's why performance get decreased.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.