Featured Database Articles

Does Your Oracle Table Really Need 255 or More Columns?

By David Fitzjarrell

Occasionally it may be necessary to have a table with 255 or more columns (data marts and data warehouses come immediately to mind). Oracle allows up to 1023 columns in a table but once the column list expands beyond 255 some interesting behavior appears. Let's look at a situation, using Oracle 12.2.0.1 on OEL 7, update 4, and see how things can change.

The experiment starts with a script by Jonathan Lewis (abbreviated in the output) that creates a table with 250 columns:

The redo entries will be of particular interest. The table contains 1,000 rows and generated roughly 600 K of redo, about 600 bytes per row. This is a reasonable volume of redo for this type of transaction. Let's gather statistics and get an average row length before we alter the table and update data:

Breaking the 255 column barrier increases the redo generated by almost 10 times the original volume; Oracle is now generating over 9000 bytes of redo per row, and all that was done was add columns to a table in excess of 255. Undo was also significantly increased once that 255 column barrier was breached. Each row is now basically two pieces, one piece of 255 columns and another piece of 5 columns. This now causes Oracle to perform an insert for each row updated (there are now, internally, twice as many rows as there were to start with because of the split) and even though inserts don't generate a lot of redo or undo it does affect the total volume of both that were generated. Initially there were no NULL values; after adding the ten columns, nine of those columns remained NULL after the update. I suspect that this may be due to the fact that it was a column added to the end of each row that was updated.

It's still not clear why Oracle increases the redo and undo generation so drastically where tables are created then extended past the 255 column barrier. Knowing this can occur may make it easier to track down large increases in archive log generation (more log switches due to the increased redo volume) when transaction volume tends to remain constant.