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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-695835/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-695835/