3:18 AM
Deep Dey
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.
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:
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.
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.
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.
| ||
|
0 comments:
Post a Comment