实验结论： 1、char和varchar2占用的磁盘空间相同； 2、char与varchar2皆会产生行迁移； 3、char与varchar2产生的io次数相同，但涉及的size大小不同（char比varchar2大一倍） 实验详情： 一、创建实验表，并各新增10000条数据 create table scott.t_char ( id number, name char(2000), name1 char(2000), name2 char(2000), name3 char(2000), name4 char(2000) ); create table scott.t_varchar ( id number, name varchar2(2000), name1 varchar2(2000), name2 varchar2(2000), name3 varchar2(2000), name4 varchar2(2000) ); SQL> begin for i in 1 .. 10000 loop insert into scott.t_char(id) values (i); end loop; end; / 2 3 4 5 6 PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> begin for i in 1 .. 10000 loop insert into scott.t_varchar(id) values (i); end loop; end; / 2 3 4 5 6 PL/SQL procedure successfully completed. SQL> commit; Commit complete. 二、使用dump查看两张表的块信息（此时皆没出现行迁移） SQL> select dbms_rowid.rowid_block_number(rowid) b,dbms_rowid.rowid_relative_fno(rowid) f from scott.t_char where id=1; B F ---------- ---------- 215 4 SQL> alter system dump datafile 4 block 215; System altered. block_row_dump: tab 0, row 0, @0x1f92 tl: 6 fb: --H-FL-- lb: 0x0 cc: 1 col 0: [ 2] c1 02 tab 0, row 1, @0x1f8c tl: 6 fb: --H-FL-- lb: 0x2 cc: 1 col 0: [ 2] c1 03 tab 0, row 2, @0x1f86 tl: 6 fb: --H-FL-- lb: 0x2 cc: 1 col 0: [ 2] c1 04 tab 0, row 3, @0x1f80 tl: 6 fb: --H-FL-- lb: 0x2 cc: 1 col 0: [ 2] c1 05 tab 0, row 4, @0x1f7a tl: 6 fb: --H-FL-- lb: 0x2 cc: 1 col 0: [ 2] c1 06 tab 0, row 5, @0x1f74 tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
SQL> select dbms_rowid.rowid_block_number(rowid) b,dbms_rowid.rowid_relative_fno(rowid) f from scott.t_varchar where id=1;
B F
---------- ----------
223 4
SQL> alter system dump datafile 4 block 223;
System altered. block_row_dump: tab 0, row 0, @0x1f92 tl: 6 fb: --H-FL-- lb: 0x0 cc: 1 col 0: [ 2] c1 02 tab 0, row 1, @0x1f8c tl: 6 fb: --H-FL-- lb: 0x2 cc: 1 col 0: [ 2] c1 03 tab 0, row 2, @0x1f86 tl: 6 fb: --H-FL-- lb: 0x2 cc: 1 col 0: [ 2] c1 04 tab 0, row 3, @0x1f80 tl: 6 fb: --H-FL-- lb: 0x2 cc: 1 col 0: [ 2] c1 05 tab 0, row 4, @0x1f7a tl: 6 fb: --H-FL-- lb: 0x2 cc: 1 col 0: [ 2] c1 06 tab 0, row 5, @0x1f74 tl: 6 fb: --H-FL-- lb: 0x2 cc: 1 三、查看执行计划（使用char的表所占字节比varchar2表大接近一倍） SQL> select * from scott.t_char where id=1; Execution Plan ---------------------------------------------------------- Plan hash value: 3241939766 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 10023 | 7 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T_CHAR | 1 | 10023 | 7 (0)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=1) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 9 recursive calls 0 db block gets 58 consistent gets 0 physical reads 0 redo size 859 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select * from scott.t_varchar where id=1; Execution Plan ---------------------------------------------------------- Plan hash value: 4100862799 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5023 | 7 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T_VARCHAR | 1 | 5023 | 7 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=1) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 10 recursive calls 0 db block gets 58 consistent gets 0 physical reads 0 redo size 859 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 四、修改两张表ID为1的数据，使用dump观察可以发现两张表皆出现行迁移。 SQL> update scott.t_char set name1=dbms_random.string('u', 2000) where id=1; 1 row updated. SQL> update scott.t_varchar set name1=dbms_random.string('u', 2000) where id=1; 1 row updated. SQL> commit; Commit complete.
SQL> select dbms_rowid.rowid_block_number(rowid) b,dbms_rowid.rowid_relative_fno(rowid) f from scott.t_char where id=1;
B F
---------- ----------
215 4
SQL> alter system dump datafile 4 block 215;
System altered. block_row_dump: tab 0, row 0, @0xdec tl: 9 fb: --H----- lb: 0x1 cc: 0 nrid: 0x010000e1.64 tl: 2016 fb: ----FL-- lb: 0x2 cc: 3 hrid: 0x010000d7.0 col 0: [ 2] c1 02 col 1: *NULL* col 2: [2000] 59 4b 4f 52 55 4f 50 4f 59 4e 46 45 42 4a 50 42 52 42 57 4e 49 42 57 59 53 55 4a 4d 48 46 45 50 57 58 43 44 58 54 47 47 52 42 55 45 45 48 56 45 4b 4e
SQL> select dbms_rowid.rowid_block_number(rowid) b,dbms_rowid.rowid_relative_fno(rowid) f from scott.t_varchar where id=1;
B F
---------- ----------
223 4
SQL> alter system dump datafile 4 block 223;
System altered. block_row_dump: tab 0, row 0, @0xdec tl: 9 fb: --H----- lb: 0x1 cc: 0 nrid: 0x010000e9.64 tl: 2016 fb: ----FL-- lb: 0x2 cc: 3 hrid: 0x010000df.0 col 0: [ 2] c1 02 col 1: *NULL* col 2: [2000] 4b 4d 50 47 44 57 5a 45 46 45 5a 46 56 48 52 54 44 44 50 4e 49 44 56 51 45 43 48 44 53 54 54 48 4c 55 43 49 51 4c 4c 54 4a 55 42 59 5a 48 44 54 4e 44 41 59 4f 42 56 50 4f 58 4a 56 50 54 4c 52 5a 42 4e 4d 53 44 4e 4d 58 52 4c 五、使用会话跟踪，可以观察到两张表的IO次数相同，但在字节方面，char表仍比varchar2表高； SQL ID: ffsfudx75qyyc Plan Hash: 3241939766 select * from scott.t_char where id=1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 23 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 23 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 TABLE ACCESS FULL T_CHAR (cr=23 pr=0 pw=0 time=265 us cost=7 size=10023 card=1) ===================================================================== SQL ID: gdvwfucd49ass Plan Hash: 4100862799 select * from scott.t_varchar where id=1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 23 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 23 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 TABLE ACCESS FULL T_VARCHAR (cr=23 pr=0 pw=0 time=193 us cost=7 size=5023 card=1) 六、通过视图user_segments查看磁盘空间占用，观察到两张表的空间占用一致 SQL> select s.segment_name,s.BYTES,s.blocks from user_segments s where s.segment_name in ('T_CHAR','T_VARCHAR'); SEGMENT_NAME -------------------------------------------------------------------------------- BYTES BLOCKS ---------- ---------- T_CHAR 196608 24 T_VARCHAR 196608 24
