char类型设置过长:浪费表空间、消耗dml操作时间和索引影响

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);

-- 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;

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;

耗时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;

耗时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>

 

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27573546/viewspace-762647/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27573546/viewspace-762647/