11G R2引入了Deferred Segment Creation的新特性,在建立空表时并不分配空间.这样对于一些系统能节省许多空间。但是也可能遇到一些问题，我把这段时间看到关于这个方面的信息做一个汇总。
1.sys用户不受这个限制：
测试例子：
sqlplus sys as sysdba
SQL> create table t1 (a number);
Table created.
SQL> select table_name ,segment_created from user_tables where table_name='T1';
TABLE_NAME SEG
------------------------------ ---
T1 YES
SQL> select segment_name ,segment_type ,bytes from user_segments where segment_name='T1' and segment_type='TABLE';
SEGMENT_NAME SEGMENT_TYPE BYTES
--------------------------------------------------------------------------------- ------------------ ----------
T1 TABLE 65536
可以发现,SYS用户建立的表不受限制！
2.导致使用exp/imp这些空表无法导入:
测试例子参考http://space.itpub.net/267265/viewspace-695835。
3.如果把表做一个move操作,将会建立相应的段。
测试例子
sqlplus scott/xxxx
SQL> create table t1 (a number);
Table created.
SQL> select table_name ,segment_created from user_tables where table_name='T1';
TABLE_NAME SEG
------------------------------ ---
T1 NO
SQL> select segment_name ,segment_type ,bytes from user_segments where segment_name='T1' and segment_type='TABLE';
no rows selected
SQL> alter table scott.t1 move tablespace users;
Table altered.
SQL> select table_name ,segment_created from user_tables where table_name='T1';
TABLE_NAME SEG
------------------------------ ---
T1 YES
SQL> select segment_name ,segment_type ,bytes from user_segments where segment_name='T1' and segment_type='TABLE';
SEGMENT_NAME SEGMENT_TYPE BYTES
--------------------------------------------------------------------------------- ------------------ ----------
T1 TABLE 65536
可以发现move后会建立相应的extent。
4.如果索引属性修改为unusable,该索引占用的段会消失。
SQL> drop table t1 purge;
SQL> create table t1 (a number);
Table created.
SQL> create index i_t1_a on t1(a);
Index created.
SQL> insert into t1 values (1);
1 row created.
SQL> commit ;
Commit complete.
SQL> select table_name ,segment_created from user_indexes where table_name='T1';
TABLE_NAME SEG
------------------------------ ---
T1 YES
SQL> select segment_name ,segment_type ,bytes from user_segments where segment_name='I_T1_A' and segment_type='INDEX';
SEGMENT_NAME SEGMENT_TYPE BYTES
--------------------------------------------------------------------------------- ------------------ ----------
I_T1_A INDEX 65536
SQL> alter index i_t1_a unusable;
Index altered.
SQL> select table_name ,segment_created from user_indexes where table_name='T1';
TABLE_NAME SEG
------------------------------ ---
T1 NO
SQL> select segment_name ,segment_type ,bytes from user_segments where segment_name='I_T1_A' and segment_type='INDEX';
no rows selected
利用这个特性，我们可以建立一些分区索引，保留我们自己需要的，不需要的索引修改为unusable.
这里有一个很好的例子
http://richardfoote.wordpress.com/2011/02/27/oracle11g-zero-sized-unusable-indexes-part-ii-nathan-adler/
[也许需要翻墙才能看!]
例子:
create table t2 (a number, b varchar2(10),status varchar2(1));
insert into t2 select rownum, 'test','1'from dual connect by level update t2 set status = '0' where a in (9990, 9992, 9994, 9996, 9998);
commit;
假设status的取值仅仅'1','0'. 查询正常业务仅仅查询='0'的情况,而且status='0'的值很少！正常如果建立索引status会随着记录的添加索引逐步变大,而实际上我们仅仅需要索引status='0'的情况，这个需要dba与开发人员协商好，程序员写的sql代码如下:
SQL> select * from t2 where decode(status,'1',NULL,status)='0';
A B S
---------- ---------- -
9990 test 0
9992 test 0
9994 test 0
9996 test 0
9998 test 0
建立decode(status,'1',NULL,status)的函数索引。但是这个需要dba与程序员协商好！语句也要这样写。
create index scott.if_status on t2 (decode("status",'1',null,"status"));
select * from t2 where decode(status,'1',NULL,status)='0';
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID 4nsvdy41fymmr, child number 1
-------------------------------------
select * from t2 where decode(status,'1',NULL,status)='0'
Plan hash value: 1171039187
----------------------------------------------------------
| Id | Operation | Name | E-Rows |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 5 |
|* 2 | INDEX RANGE SCAN | IF_STATUS | 5 |
----------------------------------------------------------
SQL> create index I_T2_STATUS on T2(STATUS) global partition by range (STATUS) (
partition STATUS0 values less than ('1'),
partition STATUS_OTHER values less than (MAXVALUE)
) unusable;
Index created.
SQL> select table_name ,segment_created from user_indexes where table_name='T2';
TABLE_NAME SEG
------------------------------ ---
T1 N/A
SQL> select segment_name,partition_name,segment_type ,bytes from user_segments where segment_name='I_T2_STATUS' ;
no rows selected
--建立索引，仅仅rebuild我们需要的哪部分.
SQL> alter index i_t2_status rebuild partition status0;
Index altered.
SQL> select table_name ,segment_created from user_indexes where table_name='T2';
TABLE_NAME SEG
------------------------------ ---
T2 N/A
SQL> select segment_name ,PARTITION_NAME,segment_type ,bytes from user_segments where segment_name='I_T2_STATUS' ;
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES
--------------- ------------------------------ ------------------ ----------
I_T2_STATUS STATUS0 INDEX PARTITION 65536
可以发现仅仅status=0的分区索引，占用空间明显减少!
SQL> select * from t2 where status='0';
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID 36k4a41s20ac8, child number 0
-------------------------------------
select * from t2 where status='0'
Plan hash value: 4122182659
-------------------------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | PARTITION RANGE SINGLE | | 5000 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 5000 |
|* 3 | INDEX RANGE SCAN | I_T2_STATUS | 5000 |
-------------------------------------------------------------
统计信息不准，重新分析，Cascade=>false,：
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'SCOTT'
,TabName => 'T2'
,Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
,Method_Opt => 'FOR ALL COLUMNS SIZE auto '
,Degree => 1
,Cascade => false
,No_Invalidate => TRUE);
END;
/
Execution Plan
----------------------------------------------------------
Plan hash value: 4122182659
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 55 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 5 | 55 | 1 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 5 | 55 | 1 (0)| 00:00:01 | | |
|* 3 | INDEX RANGE SCAN | I_T2_STATUS | 5 | | 1 (0)| 00:00:01 | 1 | 1 |
------------------------------------------------------------------------------------------------------------
5.配额问题(quotas)：
因为建立空表以及索引是并没有分配空间，这样建立的表以及索引没有配额问题，但是一旦插入数据就可能报错！
SQL> create user test identified by 1234 default tablespace users temporary tablespace temp profile default account unlock;
SQL> grant create session to test;
SQL> grant create table to test;
sqlplus test/1234
SQL> create table t3 ( a number) ;
Table created.
SQL> insert into t3 values (100);
insert into t3 values (100)
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
可以发现建立表没有问题，这时不占用空间，一旦有数据插入，问题才会暴露出来！
总结:
大概发现这些，也许还有许多特性与问题。