实验目的：验证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博客 ” ，链接：https://blog.itpub.net/26451536/viewspace-743204/ ，如需转载，请注明出处，否则将追究法律责任。
