[20111214]汇总11GR2 Deferred Segment Creation一些相关信息.txt

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'

可以发现建立表没有问题，这时不占用空间，一旦有数据插入，问题才会暴露出来！

总结:
    大概发现这些，也许还有许多特性与问题。