如題，在網上看的文章，做了下測驗，不知道這是為什么，求高手指點
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多了將近一倍，這是為什么呢？
uj5u.com熱心網友回復：
這個挺有意思的，varchar2長度的設計看來也不能過于隨性了。兩次插入，前者的當前讀確實遠大于后者，看上去，Oracle雖然沒有使用這個欄位4000位元組的空間，但是在塊里預先分配了，以備將來update之用，所以需要讀入更多的塊來放置同樣數目的記錄。
uj5u.com熱心網友回復：
謝謝高人，但是我還有問題，如果是預先分配了，那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
uj5u.com熱心網友回復：
這么快就打臉
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/36531.html
標籤：基礎和管理