Re: different avg_row_len in 9i and 10g+ at table with LOB

Your guess is correct. See bug 1954150 "DBMS_STATS.GATHER_TABLE_STATS CALCULATES AVG_ROW_LEN FOR CLOBS INCORRECTLY". It's fixed in 10gR2. I can reproduce it in 10gR1. Tablespace can be ASSM as well.

Yong Huang

> 9.2.0.8:
> =====
> TABLE_NAME AVG_ROW_LEN
> ------------------------------ -----------
> LOBTEST 2039
>
> ...
>
> 10.2.0.4:
> ======
> TABLE_NAME AVG_ROW_LEN
> -------------------------------- -----------
> LOBTEST 3204
> ...
> In 9i it looks as if AVG_ROW_LEN is simply the average length of all
> rows except these LOBs.
> I'm not 100% sure what happens in 10g. Maybe it's the average length
> of all rows excelt these LOBs in the dedicated LOB Segments.
      

--
http://www.freelists.org/webpage/oracle-l