创建4张表：
create table small(a varchar2(10),b varchar2(10),c varchar2(10));
create table big1(a varchar2(2000),b varchar2(10),c varchar2(10));
create table big2(a varchar2(10),b varchar2(2000),c varchar2(10));
create table big3(a varchar2(10),b varchar2(10),c varchar2(2000));
检查每个segment的大小：
col segment_name for a10;
select segment_name,bytes/1024 KB from dba_segments where owner='LDY' and segment_name in ('SMALL','BIG1','BIG2','BIG3');
SEGMENT_NA KB
---------- ----------
SMALL 64
BIG1 64
BIG2 64
BIG3 64
向4张表分别插入记录，每张表的记录都相同。
insert into small values('aaaaaaaa','bbbbbbbb','cccccccc');
insert into big1 select * from small;
insert into big2 select * from small;
insert into big3 select * from small;
发现他们所占空间都相同。
SEGMENT_NA KB
---------- ----------
SMALL 59392
BIG1 59392
BIG2 59392
BIG3 59392
记录基本具有同一个rowid，也许会导致相同大小的现象，将数据离散分布。
insert into small
SELECT
dbms_random.string('A', 8) a,dbms_random.string('A', 8) b,dbms_random.string('A', 8) c
FROM dba_objects;
SQL> select count(*) from small;
COUNT(*)
----------
124740
数据相同的情况下，三张表的大小也相同
SEGMENT_NA KB
---------- ----------
SMALL 5120
BIG1 5120
BIG2 5120
BIG3 5120
重新建立4张表，包含number字段。
create table small(a number(10),b varchar2(10),c varchar2(10));
create table big1(a varchar2(2000),b varchar2(10),c number(10));
create table big2(a number(10),b varchar2(2000),c varchar2(10));
create table big3(a number(10),b varchar2(10),c varchar2(2000));
begin
for i in 1..10 loop
insert into small
SELECT
trunc(dbms_random.VALUE(9999999,100000000)) a,dbms_random.string('A', 8) b,dbms_random.string('A', 8) c
FROM dba_objects;
commit;
end loop;
end;
/
SQL> select count(*) from small;
COUNT(*)
----------
113440
SQL> select segment_name,bytes/1024 KB from dba_segments where owner='LDY' and segment_name in ('SMALL','BIG1','BIG2','BIG3');
SEGMENT_NA KB
---------- ----------
SMALL 4096
BIG1 64
BIG2 64
BIG3 64
begin
for i in 1..10 loop
insert into big1
SELECT
dbms_random.string('A', 8) a,dbms_random.string('A', 8) b,trunc(dbms_random.VALUE(9999999,100000000)) c
FROM dba_objects;
commit;
end loop;
end;
/
begin
for i in 1..10 loop
insert into big2
SELECT
trunc(dbms_random.VALUE(9999999,100000000)) a,dbms_random.string('A', 8) b,dbms_random.string('A', 8) c
FROM dba_objects;
commit;
end loop;
end;
/
begin
for i in 1..10 loop
insert into big3
SELECT
trunc(dbms_random.VALUE(9999999,100000000)) a,dbms_random.string('A', 8) b,dbms_random.string('A', 8) c
FROM dba_objects;
commit;
end loop;
end;
/
4张表记录相同，存储大小相同。
SEGMENT_NA KB
---------- ----------
SMALL 4096
BIG1 4096
BIG2 4096
BIG3 4096
使用char代替varchar2：
create table small(a number(10),b char(10),c char(10));
create table big1(a char(2000),b char(10),c number(10));
create table big2(a number(10),b varchar(2000),c char(10));
create table big3(a number(10),b char(10),c varchar(2000));
还是通过上面的存储过程生成数据，数据量相同的情况下：
SQL> select count(*) from small;
COUNT(*)
----------
113480
SEGMENT_NA KB
---------- ----------
SMALL 5120
BIG1 311296
BIG2 4096
BIG3 4096
使用varchar2比使用char更节省空间。
