CTAS VS create table and then insert

很多情况下我们都会需要复制源表数据以达到冗余数据的目的,那么到底是使用CREATE TABLE AS SELECT的CTAS方式,还是先建好表的结构然后再插入数据好呢?

我们来看看这2种方式的不同表现:

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2

/* 数据库处于归档模式下 */

SQL> select ss.name,ms.value  from v$mystat ms,v$sysstat ss
  2  where
  3  ms.statistic#=ss.statistic#
  4  and ss.name in ('undo change vector size','redo size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                                 0
undo change vector size                                                   0

SQL> create table YOUYUS as select * from dba_objects;
Table created.

SQL> select ss.name,ms.value  from v$mystat ms,v$sysstat ss
  2  where
  3  ms.statistic#=ss.statistic#
  4  and ss.name in ('undo change vector size','redo size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           5783384
undo change vector size                                               15408

/* CTAS方式产生了少量的undo,可以猜想其使用直接路径方式插入,Oracle仅产生维护数据字典的undo */

SQL> drop table YOUYUS;
Table dropped.

SQL> conn / as sysdba
Connected.

/* 清理现场 */

SQL> create table YOUYUS as select * from dba_objects where 0=1;
Table created.

SQL> select ss.name,ms.value  from v$mystat ms,v$sysstat ss
  2  where
  3  ms.statistic#=ss.statistic#
  4  and ss.name in ('undo change vector size','redo size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             19492
undo change vector size                                                5680

/* 建立空表YOUYUS,同样需要维护数据字典  */

SQL> insert into YOUYUS select * from dba_objects;

50729 rows created.

SQL> commit;

Commit complete.

SQL> select ss.name,ms.value  from v$mystat ms,v$sysstat ss
  2  where
  3  ms.statistic#=ss.statistic#
  4  and ss.name in ('undo change vector size','redo size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           5743540
undo change vector size                                              203904

/* 普通insert操作产生了远大于CTAS的undo */

SQL> drop table YOUYUS;
Table dropped.

SQL> conn / as sysdba
Connected.

SQL> select ss.name,ms.value  from v$mystat ms,v$sysstat ss
  2  where
  3  ms.statistic#=ss.statistic#
  4  and ss.name in ('undo change vector size','redo size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                                 0
undo change vector size                                                   0


SQL> create table YOUYUS as select * from dba_objects where 0=1;
Table created.

SQL> insert /*+ append */ into YOUYUS select * from dba_objects;
50729 rows created.

SQL> commit;

Commit complete.

SQL> select ss.name,ms.value  from v$mystat ms,v$sysstat ss
  2  where
  3  ms.statistic#=ss.statistic#
  4  and ss.name in ('undo change vector size','redo size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           5781712
undo change vector size                                               14808


/* 建表后直接路径插入方式与CTAS产生的redo和undo数量大致相仿 */

从资源消耗的角度讲CTAS或直接路径插入方式有一定优势,如果启用nologging选项的话这种优势会更加显著。

Comments

  1. Extent Sizes for Sort, Direct Load and Parallel Operations (PCTAS & PDML)

    Purpose
    ~~~~~~~
    This note describes how storage is allocated for sort, direct and
    parallel operations in Oracle7 and Oracle8.

    Each main operation is described in its own section with a summary of
    when and where space is allocated.

    Scope & Application
    ~~~~~~~~~~~~~~~~~~~
    It is intended to clarify how and when temporary space is allocated
    during processing.

    NOTE: Within this note the ‘Logging’ information indicates whether redo is
    generated for the operation or not. This is independent of the database
    logging mode (ARCHIVELOG or NOARCHIVELOG mode). If the database is
    in NOARCHIVELOG mode then any redo generated will be lost when the
    online logs wrap.

    Please note this article does NOT currently discuss LOCALLY MANAGED
    tablespaces which are available from Oracle8i onwards.
    These are discussed in Note:93771.1

    Sort (Temporary) Space
    ~~~~~~~~~~~~~~~~~~~~~~
    TARGET TABLESPACE:
    A users ‘TEMPORARY TABLESPACE’ attribute determines which tablespace is
    used for sorting. If the tablespace is type PERMANENT (in DBA_TABLESPACES)
    or the release is before Oracle 7.3 then we use ‘a’ below. If the
    tablespace is TEMPORARY we use ‘b’ below. In either case the following
    applies to both types of temporary segment.

    LOGGING:
    Undo: Only dictionary operations are logged to undo.
    Redo: Only dictionary operations are logged to redo.

    IO:
    Read: Reads use DIRECT READS (where available)
    These generally show up as multi-block reads
    Write: Writes may go through the buffer cache or direct,
    depending on
    NB: Sort blocks may be written twice with the same Inc/Seq
    Sort blocks may be fractured during write (It is possible
    to update a sort block which is being written)
    Sort blocks never include checksum information.

    REFERENCES and VIEWS:

    shows temporary segments but NOT the actual users
    shows users of segments in the TEMPORARY tablespace
    in Oracle8 onwards

    unable to EXTEND temp segment by 8 (blocks) in tablespace X
    unable to create INITIAL extent for segment in tablespace X
    max # extents (4) reached in temp segment in tablespace X

    NOTES:
    Note that when choosing extent sizes for TEMPORARY tablespace
    the extent size should be large enough to accomodate multiblock
    writes for both SORT operations and HASH JOIN operations.
    Eg: if the extent size is less than
    then an ORA-3232 error may be raised on hash joins when using TEMPORARY
    type tablespaces

    See Note:125271.1 for further details.

    a. Standard Sort Space (PERMANENT TABLESPACE)
    ~~~~~~~~~~~~~~~~~~~~~~~
    STORAGE:
    Allocated in: Users TEMPORARY tablespace (ALTER USER TEMPORARY TABLESPACE)
    Initial: Default INITIAL from the temporary tablespace
    Next: Default NEXT from the temporary tablespace
    PctIncrease: Default PCTINCREASE from the temporary tablespace
    (0 recommended)
    MaxExtents: Default MAXEXTENTS

    RECOVERY:
    As the tablespace can potentially hold objects normal tablespace recovery
    rules apply.

    b. Temporary Tablespace Sort Space
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    STORAGE:
    Allocated in: Users TEMPORARY tablespace (ALTER USER TEMPORARY TABLESPACE)
    Initial: Default NEXT (not INITIAL) from the temporary tablespace
    In Oracle8 the MINIMUM size we use for INITIAL is
    db_block_size * db_file_multiblock_read_count
    and this is then subject to normal rounding of extent sizes.
    (ie: multiple of 5 blocks)
    Next: Default NEXT from the temporary tablespace
    PctIncrease: 0 always.
    MinExtents: 1
    MaxExtents: UNLIMITED regardless of default storage settings

    RECOVERY:
    At a recovery level Oracle does not recognise any difference between a
    file in a TEMPORARY tablespace and a normal datafile. The important
    difference is that a TEMPORARY tablespace cannot contain permanent
    objects (tables etc..). If there is no backup of files in a TEMPORARY
    TABLESPACE then the files can be offlined for recovery and the tablespace
    DROPPED when the database is open. The TEMPORARY tablespace can then be
    recreated.

    NOTES:
    If you change a tablespace from TEMPORARY to PERMANENT then the TEMPORARY
    SEGMENT remains. SMON keeps the TS lock for the segments and you cannot
    convert the tablespace back to TEMPORARY again until all users of the
    segment have released their sort handles (session exited etc…) and the
    segment has been freed.

    *WARNING* As PCTINCREASE is set to 0 and MAXEXTENTS to UNLIMITED take
    great care in setting NEXT or you may end up with a very large
    number of extents.

    However, do not set a really large value for NEXT as the
    unit of space allocation is a single EXTENT which in turn
    places a limit on the number of concurrent sort operations
    in a single tablespace.
    eg: If you set NEXT to 100Mb and only have a 200Mb
    TEMPORARY tablespace it can only support 2 concurrent
    sort operations.

    ————————————————————————-
    Create Table As Select (CTAS)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    TARGET TABLESPACE:
    A CTAS statements generally includes a target TABLESPACE in the command,
    otherwise the users ‘DEFAULT’ tablespace is used.

    LOGGING:
    Undo: CTAS writes undo for dictionary operations only
    Redo: Writing of redo depends on:
    CTAS has an ‘UNRECOVERABLE’ – No redo
    Target tablespace is ‘NOLOGGING’ (Oracle8) – No redo
    Dictionary changes are always written to redo.

    IO:
    Read: Taken from the SELECT row source so depends on the
    select statement.
    Write: Table blocks are written direct to disk.

    RECOVERY:
    If created NOLOGGING or UNRECOVERABLE then a backup should be taken
    after the CTAS and any associated index builds have been performed.
    Recovery from a backup taken prior to the CTAS will soft corrupt the table
    blocks such that they report ORA-1578 when accessed.

    If the CTAS is logged then standard recovery applies. The table will
    either exist or not after recovery depending on the point in time
    recovered to.

    a. Simple Select
    ~~~~~~~~~~~~~~~~
    STORAGE:
    Allocated in: CTAS clause else users DEFAULT tablespace
    Initial: As supplied else from target tablespace INITIAL
    NB: In Oracle8 the tablespace MINIMUM EXTENT size applies
    Next: As supplied else from the target tablespace NEXT
    NB: In Oracle8 the tablespace MINIMUM EXTENT size applies
    PctIncrease: As supplied else from the target tablespace
    MinExtents: ”
    MaxExtents: ”

    HOW IT WORKS:
    The CTAS creates a data segment in the target tablespace and marks this
    segment as TEMPORARY in dictionary. On completion of the CTAS the
    dictionary type is changed from TEMPORARY to TABLE.

    NOTES:
    As blocks are direct load built they are NOT put on the freelist even
    if they are only partly full.

    b. CTAS with Select which sorts
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    CTAS part of the operation is identical to the above.
    In addition if the SELECT performs a SORT operation temporary space may
    be used as for a standard select.

    NOTES:
    The target segment is not created until the row source is available.
    Eg: if you get ‘ORA-01652: unable to extend temp’ … on the SORT
    tablespace no TEMPORARY segment should yet exist in the target
    tablespace.

    c. Parallel Create Table as Select (PCTAS)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    The space allocated for PCTAS depends on the Oracle release. The basic
    operation of the PCTAS is the same though.

    Oracle8:
    ~~~~~~~~
    STORAGE:
    Allocated in: PCTAS tablespace clause else users DEFAULT tablespace
    Initial: The NEXT value from the supplied storage clause is used,
    otherwise the NEXT value from the tablespace default.
    INITIAL in the dictionary is also set from the NEXT clause.
    NB: The tablespace MINIMUM EXTENT size applies
    Next: NEXT as supplied else NEXT from tablespace default.
    PctIncrease: As supplied else from the target tablespace
    MinExtents: ”
    MaxExtents: ”

    HOW IT WORKS:
    Each slave builds its own data segment (marked as TEMPORARY in the
    dictionary) from the row source which feeds it.
    On completion one segment is chosen as the segment to merge “INTO” –
    this segment is NOT trimmed. All other temporary segments are trimmed, but
    extents are NOT trimmed below the MINIMUM EXTENT size for the tablespace.
    The segments are then merged into the base segment.

    NOTES:
    Segment headers for each segment are placed on the freelist.
    Empty blocks in the trimmed extents are placed on the freelist.

    Oracle 7:
    ~~~~~~~~
    STORAGE:
    Allocated in: PCTAS clause else users DEFAULT tablespace
    Initial: The INITIAL value from the supplied storage clause is used,
    otherwise the INITIAL value from the tablespace default.
    Each PQ slave will use INITIAL for its temporary segment.
    Next: NEXT as supplied else NEXT from tablespace default.
    PctIncrease: As supplied else from the target tablespace
    MinExtents: ”
    MaxExtents: ”

    HOW IT WORKS:
    This is similar to Oracle8 apart from the space allocation and trimming:
    Each slave creates a segment with INITIAL of size INITIAL and NEXT as per
    the NEXT value. On merge trailing extents for each slave are trimmed
    except for the segment we are merging into.

    ————————————————————————-
    SQL*LOADER:
    ~~~~~~~~~~~
    TARGET TABLESPACE:
    When loading an existing table the target table already exists.

    LOGGING:
    Undo: Direct operations only write undo for dictionary operations
    Redo: Writing of redo depends on whether the UNRECOVERABLE clause
    is supplied to SQL*LOADER and on whether the target object
    has the NOLOGGING attribute set (Oracle8)
    Dictionary changes are always written to redo.
    Conventional path loads always write redo.

    IO:
    Read: By the loader process as instructed in the control file
    Write: Table blocks are written direct to disk in DIRECT mode only.

    a. Normal load (APPEND)
    ~~~~~~~~~~~~~~~~~~~~~~~
    A normal SQLLOAD is the same as an SQL insert statement.

    RECOVERY:
    Conventional path load cannot use the UNRECOVERABLE option and ignores
    NOLOGGING table attributes so the load is fully logged. Hence normal
    recovery rules apply.

    b. Direct load (APPEND)
    ~~~~~~~~~~~~~~~~~~~~~~~
    SYNTAX:
    sqlload x/x control=xx direct=true

    STORAGE:
    Existing tables storage information applies.

    RECOVERY:
    Depends on the UNRECOVERABLE option of SQLLOADER and the NOLOGGING
    attribute of the table being loaded (Oracle8). If an unlogged load occurs
    then recovery from a backup prior to the load will leave the loaded blocks
    marked as corrupt.

    HOW IT WORKS:
    Direct load inserts data above the high water mark (HWM) and then moves
    the HWM to include the new blocks. This may require the allocation of
    new extents.

    NOTES:
    The load session needs the TM lock on the target table in mode 6
    (otherwise an ORA-54 is signaled). This lock prevents concurrent sessions
    from updating the table.

    At most ‘rows’ (command line SQLLOAD parameter) rows are placed in each
    block.

    Blocks not put on free-list, even if the block is not full

    Indexes:
    ~~~~~~~~
    Index keys are merged with the existing index to build a NEW index.

    The NEW index will be built using the index storage clause but will
    be in a different location on disk.

    Space allocation is thus:
    During the load new keys are placed in a TEMPORARY segment in the
    users TEMPORARY TABLESPACE (this can be a TEMPORARY TS or normal)
    Once the rows are loaded a NEW INDEX segment is created with
    type TEMPORARY in the dictionary in the same tablespace as the
    original index.
    To this index we write a merge of the existing and TEMP indexes.

    If index build fails the index is silently left UNUSABLE
    (DBA_INDEXES.STATUS)

    c. Parallel Direct Load
    ~~~~~~~~~~~~~~~~~~~~~~~
    SYNTAX:
    sqlload scott/tiger control=pipe direct=true parallel=true

    STORAGE:
    The STORAGE details for each TEMPORARY segment is from the LOADER OPTIONS
    or from the target tables NEXT value. Ie: We use NEXT for INITIAL and apply
    PCTINCREASE for subsequent extents

    HOW IT WORKS:
    Each loader session creates its own single TEMPORARY segment to load data
    into. Parallelism is achieved by manually running several such sessions on
    different input files.

    Each load session merges its segment with the table as it completes.
    The temporary segment is trimmed before it is merged.

    NOTES:
    Parallel load needs the TM lock on the table in mode 4 otherwise an ORA 54
    is signaled. This will block normal DML operations which also require
    the TM lock.

    In Oracle8 the MINIMUM EXTENT tablespace attribute can be used to prevent
    fragmentation.

    The TEMPORARY segment header block is placed on the free list.

    In Oracle8 empty untrimmed blocks are placed on the freelist.

    Indexes:
    ~~~~~~~~
    ORA-26002 is signaled if there is an index on the target table.
    Parallel direct load cannot populate indexes.

    The command line parameter SKIP_INDEX_MAINTENANCE=TRUE marks indexes
    UNUSABLE (See DBA_INDEXES) rather than having to drop them.

    ————————————————————————-
    Insert using the APPEND hint
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    SYNTAX:
    INSERT /*+ APPEND */ into d2 select …

    STORAGE:
    As per target table storage clause.

    RECOVERY:
    Depends on whether the table is NOLOGGING or not.

    HOW IT WORKS:
    The INSERT behaves like SQLLOAD direct load – blocks are written direct to
    disk (direct writes) – eg: Blocks not put on freelist etc..

    NOTES:
    Oracle 8 only

    APPEND is ignored for INSERT … VALUES statements

    APPEND need NOT be the first statement in a transaction but it should
    be the first DML. (Although it seems to work if there is preceding DML)

    The INSERT MUST be followed by COMMIT or ROLLBACK else you get :
    ORA-12831: Must COMMIT or ROLLBACK after executing INSERT with APPEND hint

    May encounter ORA-01659 – unable to allocate MINEXTENTS beyond in tablespace ;
    error during this operation. Extents will be applied beyond the high watermark in the table, and
    may result in the table needing to extend / allocate more extents.

    The hint may be SILENTLY ignored in a number of cases.
    Eg: Hint ignored for GLOBAL indexes on PARTITIONED tables
    Hint ignored for any index on non-PARTITIONED tables
    Hint ignored if table has TRIGGERS on it

    Parallel Insert
    ~~~~~~~~~~~~~~~
    SYNTAX:
    ALTER session enable parallel dml;
    INSERT /*+ APPEND PARALLEL(d2 4) */ into d2 SELECT …

    HOW IT WORKS:
    Each slave creates a TEMPORARY segment in target tablespace with
    INITIAL=NEXT from the tables storage clause.
    Each slave then populates its own segment in a similar manner to parallel
    SQLLOAD except that the rows are taken from the SELECT row source.

    On completion of the INSERT segments are MERGED one TEMPORARY segment with
    all trailing extents being trimmed – Note: we DO trim the temporary segment
    that we are merging into.

    The temporary segment remains waiting to be merged into table segment
    on COMMIT or ROLLBACK

    COMMIT merges the TEMPORARY segment with the base table.
    ROLLBACK drops the temporary segment.

    NOTES:
    The same rules as the APPEND hint apply. Eg: You must COMMIT / ROLLBACK
    as the next statement, no triggers etc..

    On merge all segment header blocks are placed on the free list.

    All free blocks in untrimmed extents are placed on the free list.

    Parellel DML must be enabled within the session to allow parallel inserts.

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号