Printable View
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 ... :confused:
You are supprised that (1+2+3)/3 = (3+1+2)/3?
Did you leave the last column empty? Which column where empty?
What did you try to achiev by reordering your columns?
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.
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).Quote:
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?
Also I noticed that on 9.2.0.4 the results are the same as 10g, and in 8.1.7.4 it is "normal" - the avg_row_len is something like 200/350/450 for the above mentioned table.
Obvious question, but it has to be asked -- is there any differencein the nullability of columns between 8.1.7.4 and the other databases?
What average column lengths do you get for all these different scenarios?
The same table is imported on the 3 databases. avg_row_len is 350 on 10g and 9i, and 200/350/450 in 8.1.7 depending of the type of reorganisation of the columns.Quote:
Originally posted by slimdave
Obvious question, but it has to be asked -- is there any differencein the nullability of columns between 8.1.7.4 and the other databases?
What average column lengths do you get for all these different scenarios?