如题,在网上看的文章,做了下测试,不知道这是为什么,求高手指点1、创建2张表,t1用varchar2(4000),t2用varchar2(1000)
create table t1(x number, x2 varchar2(4000),x3 varchar2(4000)) SEGMENT CREATION IMMEDIATE;
create table t2(x number, x2 varchar2(1000),x3 varchar2(1000)) SEGMENT CREATION IMMEDIATE; 2、分别向表中插入相同的数据
insert into t1 select level, rpad(' ', 100, ' '),rpad('a',100,'a') from dual connect by level <= 1e5;
insert into t2 select level, rpad(' ', 100, ' '),rpad('a',100,'a') from dual connect by level <= 1e5;3、开启autotrace
SQL> insert into t1 select level, rpad(' ', 100, ' '),rpad('a',100,'a') from dual connect by level <= 1e5; 100000 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 1236776825------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | T1 | | | |
|* 2 | CONNECT BY WITHOUT FILTERING| | | | |
| 3 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter(LEVEL<=1e5)
Statistics
----------------------------------------------------------
48 recursive calls
112998 db block gets
5588 consistent gets
0 physical reads
45758768 redo size
838 bytes sent via SQL*Net to client
860 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
100000 rows processed=================================================================SQL> insert into t2 select level, rpad(' ', 100, ' '),rpad('a',100,'a') from dual connect by level <= 1e5; 100000 rows created.Execution Plan
----------------------------------------------------------
Plan hash value: 1236776825------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | T2 | | | |
|* 2 | CONNECT BY WITHOUT FILTERING| | | | |
| 3 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter(LEVEL<=1e5)
Statistics
----------------------------------------------------------
43 recursive calls
16829 db block gets
6660 consistent gets
0 physical reads
23691740 redo size
840 bytes sent via SQL*Net to client
860 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
100000 rows processed=====================================================================
4、可以看到t1产生的redo比t2产生的redo多了将近一倍,这是为什么呢?
create table t1(x number, x2 varchar2(4000),x3 varchar2(4000)) SEGMENT CREATION IMMEDIATE;
create table t2(x number, x2 varchar2(1000),x3 varchar2(1000)) SEGMENT CREATION IMMEDIATE; 2、分别向表中插入相同的数据
insert into t1 select level, rpad(' ', 100, ' '),rpad('a',100,'a') from dual connect by level <= 1e5;
insert into t2 select level, rpad(' ', 100, ' '),rpad('a',100,'a') from dual connect by level <= 1e5;3、开启autotrace
SQL> insert into t1 select level, rpad(' ', 100, ' '),rpad('a',100,'a') from dual connect by level <= 1e5; 100000 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 1236776825------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | T1 | | | |
|* 2 | CONNECT BY WITHOUT FILTERING| | | | |
| 3 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter(LEVEL<=1e5)
Statistics
----------------------------------------------------------
48 recursive calls
112998 db block gets
5588 consistent gets
0 physical reads
45758768 redo size
838 bytes sent via SQL*Net to client
860 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
100000 rows processed=================================================================SQL> insert into t2 select level, rpad(' ', 100, ' '),rpad('a',100,'a') from dual connect by level <= 1e5; 100000 rows created.Execution Plan
----------------------------------------------------------
Plan hash value: 1236776825------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | T2 | | | |
|* 2 | CONNECT BY WITHOUT FILTERING| | | | |
| 3 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter(LEVEL<=1e5)
Statistics
----------------------------------------------------------
43 recursive calls
16829 db block gets
6660 consistent gets
0 physical reads
23691740 redo size
840 bytes sent via SQL*Net to client
860 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
100000 rows processed=====================================================================
4、可以看到t1产生的redo比t2产生的redo多了将近一倍,这是为什么呢?
两次插入,前者的当前读确实远大于后者,看上去,Oracle虽然没有使用这个字段4000字节的空间,但是在块里预先分配了,以备将来update之用,所以需要读入更多的块来放置同样数目的记录。
谢谢高人,但是我还有问题,如果是预先分配了,那4000字节占用的block不应该比1000字节的多吗,但是这两个表的block都是2944啊?
还有就是为什么block都是2944的情况下,4000字节会有112998的db block gets,1000字节会有16829 的db block gets呢?明显比表的block多的多啊?SEGMENT_NAME BLOCKS
------------------------------ ----------
T1 2944
T2 2944