char类型设置过长时，浪费表空间和消耗dml操作时间，对应的索引也是相同的char类型存储，很消耗存储空间
create table temp_0531varchar(idname varchar2(2000));
create table temp_0531char(idname char(2000));
create index temp_0531varchar_i on temp_0531varchar(idname);
create index temp_0531char_i on temp_0531char(idname);
create table temp_0531varchar(idname varchar2(2000));
create table temp_0531char(idname char(2000));
create index temp_0531varchar_i on temp_0531varchar(idname);
create index temp_0531char_i on temp_0531char(idname);
-- Created on 2013-5-31 by ZHONGYANMING
declare
-- Local variables here
i integer;
begin
-- Test statements here
for j in 1..1000 loop
insert into temp_0531varchar values(j);
end loop;
commit;
end;
declare
-- Local variables here
i integer;
begin
-- Test statements here
for j in 1..1000 loop
insert into temp_0531varchar values(j);
end loop;
commit;
end;
SQL> select segment_name,segment_type,header_file,header_block,bytes,blocks,extents from dba_segments where segment_name like 'TEMP_0531%';
SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
-------------------------------------------------------------------------------- ------------------ ----------- ------------ ---------- ---------- ----------
TEMP_0531CHAR TABLE 20 105459 3145728 384 18
TEMP_0531CHAR_I INDEX 20 141707 3145728 384 18
TEMP_0531VARCHAR TABLE 20 105467 65536 8 1
TEMP_0531VARCHAR_I INDEX 20 141443 65536 8 1
SQL>
-- Created on 2013-5-31 by ZHONGYANMING
declare
-- Local variables here
i integer;
begin
-- Test statements here
for j in 10001..100000 loop
insert into temp_0531varchar values(j);
end loop;
commit;
end;
declare
-- Local variables here
i integer;
begin
-- Test statements here
for j in 10001..100000 loop
insert into temp_0531varchar values(j);
end loop;
commit;
end;
耗时5.8s
SQL> select segment_name,segment_type,header_file,header_block,bytes,blocks,extents from dba_segments where segment_name like 'TEMP_0531%';
SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
-------------------------------------------------------------------------------- ------------------ ----------- ------------ ---------- ---------- ----------
TEMP_0531CHAR TABLE 20 105459 3145728 384 18
TEMP_0531CHAR_I INDEX 20 141707 3145728 384 18
TEMP_0531VARCHAR TABLE 20 105467 2097152 256 17
TEMP_0531VARCHAR_I INDEX 20 141443 4194304 512 19
-- Created on 2013-5-31 by ZHONGYANMING
declare
-- Local variables here
i integer;
begin
-- Test statements here
for j in 10001..100000 loop
insert into temp_0531varchar values(j);
end loop;
commit;
end;
declare
-- Local variables here
i integer;
begin
-- Test statements here
for j in 10001..100000 loop
insert into temp_0531varchar values(j);
end loop;
commit;
end;
耗时800.5s
SQL> select segment_name,segment_type,header_file,header_block,bytes,blocks,extents from dba_segments where segment_name like 'TEMP_0531%';
SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
-------------------------------------------------------------------------------- ------------------ ----------- ------------ ---------- ---------- ----------
TEMP_0531CHAR TABLE 20 105459 251658240 30720 101
TEMP_0531CHAR_I INDEX 20 141707 268435456 32768 103
TEMP_0531VARCHAR TABLE 20 105467 2097152 256 17
TEMP_0531VARCHAR_I INDEX 20 141443 4194304 512 19
SQL>
SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
-------------------------------------------------------------------------------- ------------------ ----------- ------------ ---------- ---------- ----------
TEMP_0531CHAR TABLE 20 105459 251658240 30720 101
TEMP_0531CHAR_I INDEX 20 141707 268435456 32768 103
TEMP_0531VARCHAR TABLE 20 105467 2097152 256 17
TEMP_0531VARCHAR_I INDEX 20 141443 4194304 512 19
SQL>
SQL> select count(0) from temp_0531varchar;
COUNT(0)
----------
100000
Executed in 0.032 seconds
SQL> select count(0) from temp_0531char;
COUNT(0)
----------
91000
Executed in 61.516 seconds
COUNT(0)
----------
100000
Executed in 0.032 seconds
SQL> select count(0) from temp_0531char;
COUNT(0)
----------
91000
Executed in 61.516 seconds
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27573546/viewspace-762647/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
