驗證append插入資料的額外收穫

to_be_Dba發表於2012-09-12

實驗目的:驗證append方式插入資料時,表的高水位線增加;普通插入方式先查詢空閒塊進行插入;

實驗準備:
1.檢視錶所佔的空間:
select segment_name, segment_type, bytes, blocks, extents from dba_segments where segment_name='T';
select segment_name,extent_id,bytes from user_extents where segment_name='T'; --此方法得到結果更加具體
2.表統計資訊收集:
exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'T',method_opt => 'for all indexed columns size auto',cascade=>true);

實驗步驟:
(1)清除表資訊
9:34:02 SQL> truncate table t;

Table truncated

(2)檢視空間使用情況
9:35:05 SQL> select segment_name,extent_id,bytes from user_extents where segment_name='T';

SEGMENT_NAME                                                                      EXTENT_ID      BYTES
-------------------------------------------------------------------------------- ---------- ----------
T                                                                                         0      65536

(3)普通方式插入資料
9:35:12 SQL> insert  into t select * from dba_objects where rownum<1000;

999 rows inserted

9:35:28 SQL> commit;

Commit complete

9:35:30 SQL> select segment_name,extent_id,bytes from user_extents where segment_name='T';

SEGMENT_NAME                                                                      EXTENT_ID      BYTES
-------------------------------------------------------------------------------- ---------- ----------
T                                                                                         0      65536
T                                                                                         1      65536

(4)清除資料
9:35:36 SQL> delete from t;

999 rows deleted

9:35:47 SQL> commit;

Commit complete

9:35:53 SQL> select segment_name,extent_id,bytes from user_extents where segment_name='T';

SEGMENT_NAME                                                                      EXTENT_ID      BYTES
-------------------------------------------------------------------------------- ---------- ----------
T                                                                                         0      65536
T                                                                                         1      65536

(4)普通方式插入資料
9:36:00 SQL> insert  into t select * from dba_objects where rownum<1000;

999 rows inserted

9:36:18 SQL> commit;

Commit complete

9:36:21 SQL> select segment_name,extent_id,bytes from user_extents where segment_name='T';

SEGMENT_NAME                                                                      EXTENT_ID      BYTES
-------------------------------------------------------------------------------- ---------- ----------
T                                                                                         0      65536
T                                                                                         1      65536
可以看到空間未增加,表示普通插入方式是在現有空間內首先查詢可用塊並插入。

(5)刪除資料,進行append方式的插入
9:36:25 SQL> delete from t;

999 rows deleted

9:36:38 SQL> commit;

Commit complete

9:37:05 SQL> insert /*+append*/  into t select * from dba_objects where rownum<1000;

999 rows inserted

9:37:16 SQL> commit;

Commit complete

9:37:19 SQL> select segment_name,extent_id,bytes from user_extents where segment_name='T';

SEGMENT_NAME                                                                      EXTENT_ID      BYTES
-------------------------------------------------------------------------------- ---------- ----------
T                                                                                         0      65536
T                                                                                         1      65536
T                                                                                         2      65536
T                                                                                         3      65536
可以看到空間增加了,驗證了append方式是在現有使用空間的基礎上進行直接插入。

 

 

 

=======================================================================================================
然而,當extent滿了的時候會出現不一樣的結果:
(1)清空表格,向其中插入較多資料
9:43:06 SQL> truncate table t;

Table truncated

9:43:16 SQL> insert /*+append*/  into t select * from dba_objects where rownum<10000;

9999 rows inserted

9:43:26 SQL> commit;

Commit complete

9:43:28 SQL> select segment_name,extent_id,bytes from user_extents where segment_name='T';

SEGMENT_NAME                                                                      EXTENT_ID      BYTES
-------------------------------------------------------------------------------- ---------- ----------
T                                                                                         0      65536
T                                                                                         1      65536
T                                                                                         2      65536
T                                                                                         3      65536
T                                                                                         4      65536
T                                                                                         5      65536
T                                                                                         6      65536
T                                                                                         7      65536
T                                                                                         8      65536
T                                                                                         9      65536
T                                                                                        10      65536
T                                                                                        11      65536
T                                                                                        12      65536
T                                                                                        13      65536
T                                                                                        14      65536
T                                                                                        15      65536
T                                                                                        16    1048576

17 rows selected

(2)刪除資料而不釋放空間
9:43:36 SQL> delete from t;c

9999 rows deleted

9:44:09 SQL> commit;

Commit complete

9:44:14 SQL>  select segment_name,extent_id,bytes from user_extents where segment_name='T';

SEGMENT_NAME                                                                      EXTENT_ID      BYTES
-------------------------------------------------------------------------------- ---------- ----------
T                                                                                         0      65536
T                                                                                         1      65536
T                                                                                         2      65536
T                                                                                         3      65536
T                                                                                         4      65536
T                                                                                         5      65536
T                                                                                         6      65536
T                                                                                         7      65536
T                                                                                         8      65536
T                                                                                         9      65536
T                                                                                        10      65536
T                                                                                        11      65536
T                                                                                        12      65536
T                                                                                        13      65536
T                                                                                        14      65536
T                                                                                        15      65536
T                                                                                        16    1048576

17 rows selected

(3)append方式插入資料
9:44:24 SQL> insert /*+append*/  into t select * from dba_objects where rownum<1000;

999 rows inserted

9:44:50 SQL> commit;

Commit complete

9:44:53 SQL>  select segment_name,extent_id,bytes from user_extents where segment_name='T';

SEGMENT_NAME                                                                      EXTENT_ID      BYTES
-------------------------------------------------------------------------------- ---------- ----------
T                                                                                         0      65536
T                                                                                         1      65536
T                                                                                         2      65536
T                                                                                         3      65536
T                                                                                         4      65536
T                                                                                         5      65536
T                                                                                         6      65536
T                                                                                         7      65536
T                                                                                         8      65536
T                                                                                         9      65536
T                                                                                        10      65536
T                                                                                        11      65536
T                                                                                        12      65536
T                                                                                        13      65536
T                                                                                        14      65536
T                                                                                        15      65536
T                                                                                        16    1048576

17 rows selected

可以看到空間並沒有像我們預期的那樣直接增加

9:44:58 SQL> alter table t move;

Table altered

9:49:18 SQL>  select segment_name,extent_id,bytes from user_extents where segment_name='T';

SEGMENT_NAME                                                                      EXTENT_ID      BYTES
-------------------------------------------------------------------------------- ---------- ----------
T                                                                                         0      65536
T                                                                                         1      65536


如果在達到上述的資料量以後直接插入,是什麼效果呢?
9:49:24 SQL> truncate table t ;

Table truncated

9:50:43 SQL> insert   into t select * from dba_objects where rownum<10000;

9999 rows inserted

9:50:58 SQL> commit;

Commit complete

9:50:59 SQL> insert into t select * from dba_objects where rownum<1000;

999 rows inserted

9:51:04 SQL>  select segment_name,extent_id,bytes from user_extents where segment_name='T';

SEGMENT_NAME                                                                      EXTENT_ID      BYTES
-------------------------------------------------------------------------------- ---------- ----------
T                                                                                         0      65536
T                                                                                         1      65536
T                                                                                         2      65536
T                                                                                         3      65536
T                                                                                         4      65536
T                                                                                         5      65536
T                                                                                         6      65536
T                                                                                         7      65536
T                                                                                         8      65536
T                                                                                         9      65536
T                                                                                        10      65536
T                                                                                        11      65536
T                                                                                        12      65536
T                                                                                        13      65536
T                                                                                        14      65536
T                                                                                        15      65536
T                                                                                        16    1048576

17 rows selected

9:51:12 SQL> commit;

Commit complete

9:51:52 SQL>  select segment_name,extent_id,bytes from user_extents where segment_name='T';

SEGMENT_NAME                                                                      EXTENT_ID      BYTES
-------------------------------------------------------------------------------- ---------- ----------
T                                                                                         0      65536
T                                                                                         1      65536
T                                                                                         2      65536
T                                                                                         3      65536
T                                                                                         4      65536
T                                                                                         5      65536
T                                                                                         6      65536
T                                                                                         7      65536
T                                                                                         8      65536
T                                                                                         9      65536
T                                                                                        10      65536
T                                                                                        11      65536
T                                                                                        12      65536
T                                                                                        13      65536
T                                                                                        14      65536
T                                                                                        15      65536
T                                                                                        16    1048576

17 rows selected

9:51:59 SQL> insert   into t select * from dba_objects where rownum<10000;

9999 rows inserted

9:53:49 SQL> select segment_name,extent_id,bytes from user_extents where segment_name='T';

SEGMENT_NAME                                                                      EXTENT_ID      BYTES
-------------------------------------------------------------------------------- ---------- ----------
T                                                                                         0      65536
T                                                                                         1      65536
T                                                                                         2      65536
T                                                                                         3      65536
T                                                                                         4      65536
T                                                                                         5      65536
T                                                                                         6      65536
T                                                                                         7      65536
T                                                                                         8      65536
T                                                                                         9      65536
T                                                                                        10      65536
T                                                                                        11      65536
T                                                                                        12      65536
T                                                                                        13      65536
T                                                                                        14      65536
T                                                                                        15      65536
T                                                                                        16    1048576
T                                                                                        17    1048576

18 rows selected

至此,終於明白了原因。其實當插入了10000條資料後,再插入1000條,空間是增加了的,只是由於塊的成倍擴充套件,
沒有顯現出來。

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26451536/viewspace-743204/,如需轉載,請註明出處,否則將追究法律責任。

相關文章