SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Calculated Avg Row Length differs from Oracle estimated avg row length (Toad)

Ora_DB_LearnerFeb 8 2017 — edited Feb 9 2017

Hi Champs,

Following are the queries to calculate the avg row length for a particular table.

SELECT avg(nvl(vsize(NOTE_ID),0)+nvl(vsize(NOTE_TITLE),0)+nvl(vsize(NOTE_DESC),0)+nvl(vsize(CREATE_TS),0)+nvl(vsize(CREATED_BY_USER),0)+nvl(dbms_lob.getlength(SMARTS_NOTE_DESC),0)) FROM notes -- 1157 Bytes

Break up of first select statement is following:

SELECT avg(nvl(vsize(NOTE_ID),0)+nvl(vsize(NOTE_TITLE),0)+nvl(vsize(NOTE_DESC),0)+nvl(vsize(CREATE_TS),0)+nvl(vsize(CREATED_BY_USER),0)) FROM notes -- 23 bytes

SELECT avg(nvl(dbms_lob.getlength(SMARTS_NOTE_DESC),0)) FROM notes -- 1134 Bytes

So the average row length is 23 Bytes + 1134 Bytes = 1157 Bytes, however Toad shows avg row length is 477 bytes.

Could you help me understand why there is a significant difference ?

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 9 2017

Added on Feb 8 2017

9 comments

4,427 views