Robert -- Thanks for the question regarding "Order of columns. Any benefit to keeping a logical order/grouping after the fact?", version 10.2.0.2

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 we 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)

Reviews

old practices & myths   December 31, 2007 - 8am Central time zone Bookmark | Bottom | Top Reviewer: Chris Gould from London UK This is useful to know. Are there any other old practices you'd now recommend giving up for 2008? One that springs to mind is using CHAR(1) for columns which represent single-character indicator fields (like Y/N, M/F etc) instead of using VARCHAR2(1). Is there still benefit in using CHAR over VARCHAR2 ? Followup   January 1, 2008 - 6pm Central time zone: ... Is there still benefit in using CHAR over VARCHAR2 ? ... there never was, unless you wanted a FIXED LENGTH FIELD that was blank padded. Debunking one more myth...   January 1, 2008 - 9am Central time zone Bookmark | Bottom | Top Reviewer: Davide from Florence, Italy Chris, in Expert Oracle Database Architecture on chapter 12 I found: "There are four basic character strings types in Oracle, namely CHAR,VARCHAR2,NCHAR, and NVARCHAR2. All of the strings are stored in the same format in Oracle. On the database block, they will have a leading length field of 1 to 3 bytes followed by the data..." So, having a CHAR column doesn't help you saving a single byte. For the issues relating comparisons and fixed space consumption, i think that CHAR is only kept for backward compatibility and should never (almost never, no rules of thumb) be used again.

---

Write a Review