avg_row_len and Oracle 10.1.0.2.0
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: avg_row_len and Oracle 10.1.0.2.0

  1. #1
    Join Date
    Jul 2003
    Location
    Sofia, Bulgaria
    Posts
    91

    avg_row_len and Oracle 10.1.0.2.0

    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 ...

  2. #2
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    You are supprised that (1+2+3)/3 = (3+1+2)/3?
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  3. #3
    Join Date
    Mar 2002
    Posts
    534
    Did you leave the last column empty? Which column where empty?
    What did you try to achiev by reordering your columns?

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jul 2003
    Location
    Sofia, Bulgaria
    Posts
    91
    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 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.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Jul 2003
    Location
    Sofia, Bulgaria
    Posts
    91
    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?
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width