oracle优化--表优化（char VS varchar2）

实验结论：

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

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

转载于:http://blog.itpub.net/31479729/viewspace-2199615/