MOVE和CTAS比较

最新推荐文章于 2021-02-01 15:15:32 发布

转载最新推荐文章于 2021-02-01 15:15:32 发布 · 623 阅读

文章标签：

#Oracle #CTAS #MOVE

高级管理专栏收录该内容

12 篇文章

订阅专栏

发表于 2012 年 01 月 10 日由惜分飞

联系：手机(13429648788) QQ(107644445)

标题：MOVE和CAST比较

1.创建模拟表

 
        SQL> 
        select 
         * 
        from 
         v$version;
       
        BANNER
       
        --------------------------------------------------------------------------------
       
        Oracle 
        Database 
         11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
       
        PL/SQL Release 11.2.0.3.0 - Production
       
        CORE    11.2.0.3.0      Production
       
        TNS 
        for 
         Linux: Version 11.2.0.3.0 - Production
       
        NLSRTL Version 11.2.0.3.0 - Production
       
        SQL> 
        create 
         table 
         chf.t_xifenfei_move
       
        2  
        as
       
        3  
        select 
         * 
        from 
         dba_objects;
       
        Table 
         created.
       
        SQL> 
        select 
         count
        (*) 
        from 
         chf.t_xifenfei_move;
       
        COUNT
        (*)
       
        ----------
       
        73585
       
        SQL> 
        create 
         table 
         chf.t_xifenfei_move
       
        2  
        as
       
        3  
        select 
         * 
        from 
         dba_objects;
       
        Table 
         created.
       
        SQL> 
        select 
         count
        (*) 
        from 
         chf.t_xifenfei_move;
       
        COUNT
        (*)
       
        ----------
       
        73585
       
        SQL> 
        DECLARE
       
        2     i NUMBER;
       
        3      
        BEGIN
       
        4        
        FOR 
         i 
        IN 
         1..100 LOOP
       
        5          
        INSERT 
         INTO 
         chf.t_xifenfei_move 
       
        6          
        select 
         * 
        from 
         dba_objects;
       
        7          
        END 
         LOOP;
       
        8          
        COMMIT
        ;
       
        9      
        END
        ;
       
        10      /
       
        PL/SQL 
        procedure 
         successfully completed.
       
        SQL> 
        exec 
         dbms_stats.gather_table_stats(
        'CHF'
        ,
        'T_XIFENFEI_MOVE'
        );
       
        PL/SQL 
        procedure 
         successfully completed.
       
        SQL> 
        select 
         bytes 
        from 
         dba_segments 
        where 
         segment_name=
        'T_XIFENFEI_MOVE'
        ;
       
        BYTES
       
        ----------
       
        872415232

2.测试move
2.1)执行move操作，记录时间

 
        SQL> 
        alter 
         system flush buffer_cache;
       
        System altered.
       
        SQL> 
        SET 
         TIMING 
        ON
        ;   
       
        SQL> 
        alter 
         session 
        set 
         events
       
        2  
        '10046 trace name context forever,level 1'
        ;
       
        Session altered.
       
        Elapsed: 00:00:00.00
       
        SQL> 
        ALTER 
         TABLE 
         CHF.T_XIFENFEI_MOVE 
        MOVE 
         TABLESPACE USERS;
       
        Table 
         altered.
       
        Elapsed: 00:02:11.77
       
        SQL> 
        alter 
         session 
        set 
         events
       
        2  
        '10046 trace name context off'
        ;
       
        Session altered.
       
        Elapsed: 00:00:00.04
       
        SQL> 
        select 
         d.value||
        '/'
        ||
        lower
        (rtrim(i.instance,chr(0)))||
        '_ora_'
        ||p.spid||
        '.trc' 
         trace_file_name 
        from
       
        2  (
        select 
         p.spid 
        from 
         v$mystat m, v$session s,v$process p 
        where 
         m.statistic# = 1 
        and 
         s.sid = m.sid 
        and 
         p.addr = s.paddr ) p,
       
        3  (
        select 
         t.instance 
        from 
         v$thread t,v$parameter v 
        where 
         v.
        name 
         = 
        'thread' 
         and
        (v.value = 0 
        or 
         t.thread# = to_number(v.value))) i,
       
        4  (
        select 
         value 
        from 
         v$parameter 
        where 
         name 
         = 
        'user_dump_dest'
        ) d;
       
        TRACE_FILE_NAME
       
        --------------------------------------------------------------------------------
       
        /opt/oracle/diag/rdbms/chf/chf/trace/chf_ora_4765.trc

从这里可以看出，move操作执行了00:02:11.77

2.2)查看trace内容

 
        [oracle@node1 ~]$ tkprof  
        /opt/oracle/diag/rdbms/chf/chf/trace/chf_ora_4765
        .trc 
        /tmp/xifenfei_move
        .txt
       
        TKPROF: Release 11.2.0.3.0 - Development on Tue Jan 10 10:57:59 2012
       
        Copyright (c) 1982, 2011, Oracle and
        /or 
         its affiliates.  All rights reserved.
       
        ********************************************************************************
       
        SQL ID: c1yk5pv0v1wg1 Plan Hash: 2931676921
       
        ALTER TABLE CHF.T_XIFENFEI_MOVE MOVE TABLESPACE USERS
       
        call     count       cpu    elapsed       disk      query    current        rows
       
        ------- ------  -------- ---------- ---------- ---------- ----------  ----------
       
        Parse        1      0.00       0.05          7          8          0           0
       
        Execute      1     11.29     131.23     105584     106275     115654     7432085
       
        Fetch        0      0.00       0.00          0          0          0           0
       
        ------- ------  -------- ---------- ---------- ---------- ----------  ----------
       
        total        2     11.30     131.29     105591     106283     115654     7432085
       
        Misses 
        in 
         library cache during parse: 1
       
        Optimizer mode: ALL_ROWS
       
        Parsing user 
        id
        : SYS
       
        Number of plan statistics captured: 1
       
        Rows (1st) Rows (avg) Rows (max)  Row Source Operation
       
        ---------- ---------- ----------  ---------------------------------------------------
       
        0          0          0  LOAD AS SELECT  (cr=117799 
        pr
        =105602 pw=105585 
        time
        =131351005 us)
       
        7432085    7432085    7432085   TABLE ACCESS FULL T_XIFENFEI_MOVE (cr=105591 
        pr
        =105586 pw=0 
        time
        =4735560 us cost=23453 size=720912245 card=7432085)
       
        ********************************************************************************

从这里可以看出执行move，其实本质是全表扫描表，然后append方式插入数据，而不是真的数据块拷贝

3.测试CAST
3.1).CAST插入数据过程

SQL> alter system flush buffer_cache;

System altered.

SQL> SET TIMING ON;
SQL> alter session set events
2 ’10046 trace name context forever,level 1′;

Session altered.

Elapsed: 00:00:00.01
SQL> create table chf.t_xifenfei_move_new tablespace test_ocp
2 as
3 select * from chf.t_xifenfei_move;

Table created.

Elapsed: 00:01:59.22
SQL> alter session set events
2 ’10046 trace name context off’;

Session altered.

Elapsed: 00:00:00.00
SQL> select d.value||’/'||lower(rtrim(i.instance,chr(0)))||’_ora_’||p.spid||’.trc’ trace_file_name from
2 (select p.spid from v$mystat m, v$session s,v$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr ) p,
3 (select t.instance from v$thread t,v$parameter v where v.name = ‘thread’ and(v.value = 0 or t.thread# = to_number(v.value))) i,
4 (select value from v$parameter where name = ‘user_dump_dest’) d;

TRACE_FILE_NAME
——————————————————————————–
/opt/oracle/diag/rdbms/chf/chf/trace/chf_ora_5121.trc

从这里看出cast操作用时:00:01:59.22，比move稍微少，但是cast要实现move完全的功能，还需要表重命名，表授权，编译无效对象等。

3.2)查看trace内容

 
        [oracle@node1 ~]$ tkprof  
        /opt/oracle/diag/rdbms/chf/chf/trace/chf_ora_5121
        .trc 
        /tmp/xifenfei_create
        .txt
       
        TKPROF: Release 11.2.0.3.0 - Development on Tue Jan 10 11:08:19 2012
       
        Copyright (c) 1982, 2011, Oracle and
        /or 
         its affiliates.  All rights reserved.
       
        ********************************************************************************
       
        create table chf.t_xifenfei_move_new tablespace test_ocp
       
        as
       
        select 
         * from chf.t_xifenfei_move
       
        call     count       cpu    elapsed       disk      query    current        rows
       
        ------- ------  -------- ---------- ---------- ---------- ----------  ----------
       
        Parse        1      0.00       0.02          1          4          0           0
       
        Execute      1      9.85     118.37     105587     106097     112387     7432085
       
        Fetch        0      0.00       0.00          0          0          0           0
       
        ------- ------  -------- ---------- ---------- ---------- ----------  ----------
       
        total        2      9.85     118.40     105588     106101     112387     7432085
       
        Misses 
        in 
         library cache during parse: 1
       
        Optimizer mode: ALL_ROWS
       
        Parsing user 
        id
        : SYS
       
        Number of plan statistics captured: 1
       
        Rows (1st) Rows (avg) Rows (max)  Row Source Operation
       
        ---------- ---------- ----------  ---------------------------------------------------
       
        0          0          0  LOAD AS SELECT  (cr=106631 
        pr
        =105592 pw=105585 
        time
        =118338607 us)
       
        7432085    7432085    7432085   TABLE ACCESS FULL T_XIFENFEI_MOVE (cr=105591 
        pr
        =105586 pw=0 
        time
        =2935008 us cost=23453 size=720912245 card=7432085)
       
        ********************************************************************************

通过这个可以看出，CAST其实本质也是全表扫描，然后append方式插入数据

4.比较move和cast
4.1)通过比较执行时间，cast稍微少，但是还有后续操作需要时间
4.2)通过比较执行计划，两者是一样的
4.3)move操作在整个过程中都会锁表,而cast不会锁住原表(select+where可以减少停业务时间)
4.4)move操作会一次性处理好权限,plsql/view等有效,而cast在rename之后，相关对象可能需要重新编译，重新授权等操作
4.5)cast操作index需要新建(create),而move操作index需要重建(rebuild)
4.6)cast完成后，需要对表重命名，删除原表操作操作，而这个操作move不用

5.选择使用谁
5.1)如果停业务时间够长，建议使用move操作
5.2)如果停业务时间不能太长，可以使用cast+where实现
5.3)如果数据库版本>=10g,且表空间使用local管理，那么可以考虑在不停业务的情况下使用shrink实现类此功能

至于MOVE和CAST在执行过程中，关于产生的redo和undo的比较，请见下篇:MOVE和CAST比较(续)