Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Column Order
- Thread starter scohan
- Start date
Hi
As far as I know, there is no impact on performance (noticeable) on which order you have your columns.
However, a good rule of thumb, is to have the primary key columns in the table as the first columns. (These are least likely to be updated as well)
What causes performance impact, is repeated updates on a row after having added more rows (potentially to the same block, filling it), since you will then experience chained rows (Do an analyze on the table to see the chain count in selecting from USER_TABLES)
As far as I know, there is no impact on performance (noticeable) on which order you have your columns.
However, a good rule of thumb, is to have the primary key columns in the table as the first columns. (These are least likely to be updated as well)
What causes performance impact, is repeated updates on a row after having added more rows (potentially to the same block, filling it), since you will then experience chained rows (Do an analyze on the table to see the chain count in selecting from USER_TABLES)
Putting the variable sized columns at the end of the row can have some effect. The thinking is that as the column within a row grows/shrinks, there is less data to move around in the data block. The degree of benefit realized depends on several factors, such as how many columns you have in the table and how many rows do you have in the data block. So the bottom line is it's a good practice, but how much benefit you derive will, as always, "depend".
The Oracle Concepts manual says "In general, try to place columns that frequently contain nulls last so that rows take less space.". I gather that the reason for this is because Oracle doesn't need to store null indicators for columns that appear after the last "real" data in the row.
Another factor is the frequency that a column is used. It turns out that there is a small but noticeable performance benefit to putting frequently accessed columns near the beginning of the row. See
I have also heard the theory of putting variable length columns at the end of the table, but I've never seen the benefit quantified in any measurable way.
Naturally these factors are not always mutually compatible. Frequently accessed columns may contain a bunch of null values or be highly variable in length. Should you put them at the beginning or end of the table? Clearly there is room for judgment here. Personally I would tend to favor the slight performance boost from putting a heavily used column first over the small savings in space from putting it at the end.
Another factor is the frequency that a column is used. It turns out that there is a small but noticeable performance benefit to putting frequently accessed columns near the beginning of the row. See
I have also heard the theory of putting variable length columns at the end of the table, but I've never seen the benefit quantified in any measurable way.
Naturally these factors are not always mutually compatible. Frequently accessed columns may contain a bunch of null values or be highly variable in length. Should you put them at the beginning or end of the table? Clearly there is room for judgment here. Personally I would tend to favor the slight performance boost from putting a heavily used column first over the small savings in space from putting it at the end.
- Status