Thanks for the question, Robert.
Asked: December 27, 2007 - 2:30 pm UTC
Last updated: January 01, 2008 - 6:14 pm UTC
Version: 10.2.0.2
Viewed 1000+ times
You Asked
Tom,
Usually in the development of a new system, we take into consideration things when structuring a table, such as frequency of nulls, foreign keys, and simply business understanding when ordering the columns. Now, as systems grow and change, new columns are called for which, when added to the table will be positioned at the end of a row of data. They might have ended up grouped with other like items, if the table were built this way from the start. Is there any benefit to rebuilding the table with the columns with the above items reconsidered, or does it not really matter much what order columns appear? I've always thought that the not null, and less chance of null values went at the beginning of a row, with lesser occurring data elements to follow. This can only be an estimate in many cases, or may change over time, but would adding a new, not null foreign key to the end of an existing table, where many of the columns before it have been mostly null, be better served being positioned in the beginning of the row? Rebuild the table with columns in a particular order or just add to the end and not bother with it¿s location? Any difference too if the new column is or is not part of indexes (unindexed text values vs. FK's)? Thanks.
Bob
Not a downstroke, fistpicker.
and Tom said...
Trailing null columns in a table consume no storage.
If you add a not null column AFTER them, they will all consume storage (a flag to say "nope, nothing here, move along".
I think however in the year 2007 - the savings of having zero bytes of information verses 1 byte is overblown - nothing to consider. I'm personally not worried about trailing null columns anymore.
But the facts to consider are:
a) if a column is frequently accessed - you want it closer to the front of the table than to the rear of the table. In order to get the I'th column, we have to parse over the (I-1) columns to get to it (not a fixed width record). Therefore - it can take a little more cpu to get to column 100 than to column 1.
Funny - that means our primary keys should probably be defined LAST in the table since they are hardly ever accessed!!! (honest, sounds backwards but it is true). Consider:
select a, b, c from t where pk = ?;
we access PK in the index, we get a, b, c from the table - we never access PK in the table itself.
Not that I'm recommending anyone goes out and rebuilds their tables with the primary keys last - I'm not saying that.
b) adding a not null column after lots of nulls will increase the row width by more than adding a not null column after lots of non-null values. This in turn could lead to row migration - and that could negatively impact performance.
I would only be tempted to re-org a table if the addition of the new value were to cause a large percentage of the rows to migrate. And then, I would couple the addition of this new column in the re-org (eg: I would not add, then re-org, I'd add as PART OF the re-org using dbms_redefinition)
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment