All Forums Database
14 Apr 2009
How do we find the typical/average space occupied by column on disk?

I know we can get the max length of a column.But when it comes to datatypes like varchar, etc where it is not necessary that it occupies all the bytes allotted to it, how do we calculate the average space occupied in that column in all the rows of a particular table.

maddy@1 6 posts Joined 05/08
14 Apr 2009

May be you can try this functionselect avg(character(trim(column_a))) from table_name;Though the column_a here is a VARCHAR type, character(trim(column_a) gives the exact length it occupies.

14 Apr 2009

Thanks a ton maddy. Your idea really helped.

rgs 106 posts Joined 02/07
14 Apr 2009

Using TRIM will truncate trailing and leading spaces in the VARCHAR string. If you want the average length it takes up on disk you don't want to trim the spaces since they take up space also. If your character set is UNICODE then each character takes up 2 bytes. So if you want to get the space taken in bytes you have multiply it by 2. If your character set is the various Kanji versions then its best to read the manual on that. Sometimes it returns bytes at other times it returns a logical character length. Also the stored VARCHAR string has a length field that takes 2 bytes which is always there even if the string is zero length.

You must sign in to leave a comment.