想问下大家怎么查询一个表里其中一列占用多少存储空间呢？

cwjl11

十分感谢啊。。。。

Yong Huang

In addition to query length() of the column and add up, you can also rely on the column level stats:

create table testcolusage (x varchar2(4000));
begin for i in 1..100 loop insert into testcolusage values (rpad('x', floor(dbms_random.value(1,4000)))); end loop; end;
/

SQL> select avg(length(x)) from testcolusage;

AVG(LENGTH(X))
--------------
       2067.22

exec dbms_stats.gather_table_stats(user, 'TESTCOLUSAGE')

SQL> select avg_col_len from user_tab_columns where table_name = 'TESTCOLUSAGE';

AVG_COL_LEN
------------
        2069

SQL> select num_rows from user_tables where table_name = 'TESTCOLUSAGE';

    NUM_ROWS
------------
         100

So the space used by this column is approximately 206KB (100 rows, one byte for one char in this characterset). You can also add 1 byte for each row to make it more accurate.

Yong Huang

[ 本帖最后由 Yong Huang 于 2011-7-14 11:46 编辑 ]

anlinew

原帖由 Yong Huang 于 2011-7-15 01:40 发表
In addition to query length() of the column and add up, you can also rely on the column level stats:

create table testcolusage (x varchar2(4000));
begin for i in 1..100 loop insert into testcolusage values (rpad('x', floor(dbms_random.value(1,4000)))); end loop; end;
/

SQL> select avg(length(x)) from testcolusage;

AVG(LENGTH(X))
--------------
       2067.22

exec dbms_stats.gather_table_stats(user, 'TESTCOLUSAGE')

SQL> select avg_col_len from user_tab_columns where table_name = 'TESTCOLUSAGE';

AVG_COL_LEN
------------
        2069

SQL> select num_rows from user_tables where table_name = 'TESTCOLUSAGE';

    NUM_ROWS
------------
         100

So the space used by this column is approximately 206KB (100 rows, one byte for one char in this characterset). You can also add 1 byte for each row to make it more accurate.

Yong Huang

user_tab_columns.avg_col_len可以大概评估出列对空间的需求，

zergduan

原帖由 Yong Huang 于 2011-7-15 01:40 发表
In addition to query length() of the column and add up, you can also rely on the column level stats:

create table testcolusage (x varchar2(4000));
begin for i in 1..100 loop insert into testcolusage values (rpad('x', floor(dbms_random.value(1,4000)))); end loop; end;
/

SQL> select avg(length(x)) from testcolusage;

AVG(LENGTH(X))
--------------
       2067.22

exec dbms_stats.gather_table_stats(user, 'TESTCOLUSAGE')

SQL> select avg_col_len from user_tab_columns where table_name = 'TESTCOLUSAGE';

AVG_COL_LEN
------------
        2069

SQL> select num_rows from user_tables where table_name = 'TESTCOLUSAGE';

    NUM_ROWS
------------
         100

So the space used by this column is approximately 206KB (100 rows, one byte for one char in this characterset). You can also add 1 byte for each row to make it more accurate.

Yong Huang

但是应该使用lengthb(x)或者vsize(x)来看column的大小(bytes)~ length得到的是字符个数

Yong Huang

原帖由 zergduan 于 2011-7-14 19:20 发表


但是应该使用lengthb(x)或者vsize(x)来看column的大小(bytes)~ length得到的是字符个数

Correct, unless you know your char takes 1 byte only.

xxx_tab_columns.avg_col_len is always in bytes so you can always use that if the stats are up-to-date.

Yong Huang

sundog315

AVG_COL_LEN靠谱，估计楼主也不是要精确数字的

woaini1987120

斑竹强悍，但是每次像dbms_stats.gather_table_stats这样的 系统函数看了之后都记不住

Yong Huang

原帖由 woaini1987120 于 2011-7-18 23:51 发表
但是每次像dbms_stats.gather_table_stats这样的 系统函数看了之后都记不住

Stare at it for 30 seconds and you'll remember it for at least 30 days.

gszoracle

新建一个不包括需要统计列的表，其他列、行数和原表完全一样，两个表所占用存储空间的差就是楼主的答案

FRLH

看表空间使用情况就成了赛