Something very interesting (I hope for you too) - in 10G the avg_row_len does not depends on the position of the emply columns - I tested with table with 298 columns, then I created several copies of the table with the columns ordered by num_distinct asc/desc from user_tab_columns and after analyze the avg_row_len is the same on the original and on the copies with reordered columns.
What the ...
Don't mock the power of The Reordered Columns, my friend. Oracle accesses the columns at the "left-hand" end faster than those on the right -- on a full-scan of a many-columned table it can really be noticeable.
Originally posted by mike9 Did you leave the last column empty? Which column where empty?
What did you try to achiev by reordering your columns?
The table is with 298 columns (it is taken from a production server and is with "REAL" data, not some artificial inserts) and the empty(200 columns)/part empty(100)/full columns(5) were distributed randomly. I have created one table with columns ordered full/not full/empty(corect) and one empty/not full/full(anti-corect).
Also I noticed that on 184.108.40.206 the results are the same as 10g, and in 220.127.116.11 it is "normal" - the avg_row_len is something like 200/350/450 for the above mentioned table.