11GR2的新特性Deferred Segment Creation

今天在看Apress.Pro.Oracle.Database.11g.Administration.Dec.2010.pdf時候,發現11GR2下有一個新特性,叫做Deferred Segment Creation.

按照書上的例子測試看看並且摘要如下:
    Starting with Oracle Database 11g Release 2, when you create a table, the creation of the associated segment is deferred until the first row is inserted into the table. This feature has some interesting implications. For example, if you have thousands of objects that you’re initially creating for an application (such as when you first install it), no space is consumed by any of the tables (or associated indexes) until data is inserted into the application tables. This means the initial DDL runs more quickly when you create a table, but the first INSERT statement runs slightly slower.

SQL> create table inv(inv_id number, inv_desc varchar2(30));
SQL> select  table_name ,segment_created from user_tables where table_name='INV';
TABLE_NAME                     SEG
------------------------------         ---
INV                                          NO

select  segment_name ,segment_type ,bytes from user_segments where segment_name='INV' and segment_type='TABLE';
no rows selected

SQL> insert into inv values(1,'BOOK');
Rerun the query, selecting from USER_SEGMENTS, and notice that a segment has been created:
SEGMENT_NAME    SEGMENT_TYPE            BYTES
--------------- ------------------ ----------
INV             TABLE                   65536

■ Note You can disable the deferred-segment-creation feature by setting the database-initialization parameter
DEFERRED_SEGMENT_CREATION to FALSE. The default for this parameter is TRUE.

我在建立表後匯出表的定義如下:
CREATE TABLE "SCOTT"."INV"
   (    "INV_ID" NUMBER,
    "INV_DESC" VARCHAR2(30)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

插入後,在匯出如下:
CREATE TABLE "SCOTT"."INV"
   (    "INV_ID" NUMBER,
    "INV_DESC" VARCHAR2(30)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

這樣建立的表會很快,但是開始插入的時候要浪費一些時間.

另外這個還導致另外一個小問題exp/imp這些空表無法匯入:

exp scott/xxx file=test.dmp

alter table SCOTT.INV rename to INV1

imp scott/btbtms file=test.dmp full=Y
可以發現表inv沒有匯入!

alter table SCOTT.INV1 rename to INV
再執行如下:

$ exp scott/btbtms file=test.dmp tables=inv
Export: Release 11.2.0.1.0 - Production on Sat May 21 00:12:08 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
EXP-00011: SCOTT.INV does not exist
Export terminated successfully with warnings.