CTAS本质探讨

来源：互联网发布：app 2g网络编辑：程序博客网时间：2024/06/01 07:29

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

联系：手机(13429648788) QQ(107644445) QQ咨询惜分飞

标题：CAST本质探讨

1.试验前提

SQL>select* fromv$version;
 
BANNER
--------------------------------------------------------------------------------
OracleDatabase11g 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
TNSforLinux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
 
SQL>selectFORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,
   2 SUPPLEMENTAL_LOG_DATA_MIN fromv$database;
 
FORSUP SUP SUPPLEME
--- --- --- --------
NO NO  NO  NO
 
SQL> conn / assysdba
Connected.
SQL> archive log list;
Databaselog mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/oradata/archivelog/chf
Oldest online log sequence    1879
Nextlog sequenceto archive   1881
Currentlog sequence          1881
 
SQL>selectcount(*)fromt_xifenfei_move;
 
  COUNT(*)
----------
   7432085
 
SQL>selectbytes/1024/1024 fromuser_segments wheresegment_name='T_XIFENFEI_MOVE';
 
BYTES/1024/1024
---------------
            832

从上面信息可以看到数据库处于归档模式，强制日志和辅助日志为开启，试验测试表t_xifenfei_move有7432085条记录，占用硬盘空间832M

2.常规CAST

SQL> altersystem flush buffer_cache;
 
System altered.
 
SQL>altersystem flush shared_pool;
 
System altered.
 
SQL>SETTIMING ON
SQL>selecta.name, b.value
  2 fromv$statname a, v$mystat b
  3 wherea.statistic# = b.statistic#
  4 andlower(a.name)in
  5  ('redo size','undo change vector size');
 
NAME                                                                 VALUE
---------------------------------------------------------------- ----------
redosize                                                           100028
undo change vector size                                              16172
 
Elapsed: 00:00:00.06
SQL>createtable chf.t_xifenfei_move_CAST tablespace users
  2 as
  3 select* fromchf.t_xifenfei_move;
  
Tablecreated.
 
Elapsed: 00:01:58.10
SQL>selecta.name, b.value
  2 fromv$statname a, v$mystat b
  3 wherea.statistic# = b.statistic#
  4 andlower(a.name)in
  5  ('redo size','undo change vector size');
 
NAME                                                                 VALUE
---------------------------------------------------------------- ----------
redosize                                                        873150548
undo change vector size                                             131384
 
Elapsed: 00:00:00.01
 
SQL>select873150548-100028 "redo size" from dual;
 
 redosize
----------
 873050520
 
SQL>select131384-16172 "undo size" from dual;
 
 undosize
----------
    115212

通过这个可以得出结论，产生redo为873050520，undo为115212

3.普通INSERT

SQL> altersystem flush buffer_cache;
 
System altered.
 
Elapsed: 00:00:00.06
SQL>altersystem flush shared_pool;
 
System altered.
 
Elapsed: 00:00:00.01
SQL>selecta.name, b.value
  2 fromv$statname a, v$mystat b
  3 wherea.statistic# = b.statistic#
  4 andlower(a.name)in
  5  ('redo size','undo change vector size');
 
NAME                                                                 VALUE
---------------------------------------------------------------- ----------
redosize                                                              732
undo change vector size                                                136
 
Elapsed: 00:00:00.18
SQL>CREATETABLE t_xifenfei_move_INSERT 
  2 AS
  3 SELECT* FROMT_XIFENFEI_MOVE WHERE1=0;
 
Tablecreated.
 
Elapsed: 00:00:00.32
SQL>selecta.name, b.value
  2 fromv$statname a, v$mystat b
  3 wherea.statistic# = b.statistic#
  4 andlower(a.name)in
  5  ('redo size','undo change vector size');
 
NAME                                                                 VALUE
---------------------------------------------------------------- ----------
redosize                                                            22712
undo change vector size                                               6512
 
Elapsed: 00:00:00.02
SQL>INSERTINTO t_xifenfei_move_INSERT
  2 SELECT* FROMT_XIFENFEI_MOVE;
 
7432085rowscreated.
 
Elapsed: 00:01:59.47
SQL>selecta.name, b.value
  2 fromv$statname a, v$mystat b
  3 wherea.statistic# = b.statistic#
  4 andlower(a.name)in
  5  ('redo size','undo change vector size');
 
NAME                                                                 VALUE
---------------------------------------------------------------- ----------
redosize                                                        862261580
undo change vector size                                           27980508
 
--redo
SQL>select22712-732 "create redo size" from dual;
 
createredo size
----------------
           21980
 
SQL>select862261580-22712 "insert redo size" from dual;
 
insertredo size
----------------
       862238868
 
--undo
SQL>select6512-136 "create undo size" from dual;
 
createundo size
----------------
            6376
 
SQL>select27980508-6512 "insert undo size" from dual;
 
insertundo size
----------------
        27973996

通过这个可以得出CREATE TABLE 过程中产生redo:21980,undo:6376,而INSERT 过程中产生的redo:862238868,undo:27973996,整个过程总的产生redo:862260848(862238868+21980),undo:27980372(27973996+6376)

4.INSERT+APPEND

SQL> altersystem flush buffer_cache;
 
System altered.
 
Elapsed: 00:00:25.19
SQL>altersystem flush shared_pool;
 
System altered.
 
Elapsed: 00:00:00.04
SQL>selecta.name, b.value
  2 fromv$statname a, v$mystat b
  3 wherea.statistic# = b.statistic#
  4 andlower(a.name)in
  5  ('redo size','undo change vector size');
 
NAME                                                                 VALUE
---------------------------------------------------------------- ----------
redosize                                                              732
undo change vector size                                                136
 
Elapsed: 00:00:00.05
SQL>createtable chf.t_xifenfei_move_INSERT_A tablespace users
  2 as
  3 select* fromchf.t_xifenfei_move where1=0;
 
Tablecreated.
 
Elapsed: 00:00:00.18
SQL>selecta.name, b.value
  2 fromv$statname a, v$mystat b
  3 wherea.statistic# = b.statistic#
  4 andlower(a.name)in
  5  ('redo size','undo change vector size');
 
NAME                                                                 VALUE
---------------------------------------------------------------- ----------
redosize                                                            21892
undo change vector size                                               6308
 
Elapsed: 00:00:00.00
SQL>INSERT/*+ append */INTOt_xifenfei_move_INSERT_A
  2 SELECT* FROMT_XIFENFEI_MOVE;
 
7432085rowscreated.
 
Elapsed: 00:02:26.37
SQL>selecta.name, b.value
  2 fromv$statname a, v$mystat b
  3 wherea.statistic# = b.statistic#
  4 andlower(a.name)in
  5  ('redo size','undo change vector size');
 
NAME                                                                 VALUE
---------------------------------------------------------------- ----------
redosize                                                        872792032
undo change vector size                                              87764
 
--redo
SQL>select21892-732 "create redo size" from dual;
 
createredo size
----------------
           21160
 
SQL>select872792032-21892 "insert redo size" from dual;
 
insertredo size
----------------
       872770140
 
--undo
SQL>select6308-136 "create undo size" from dual;
 
createundo size
----------------
            6172
 
SQL>select87764-6308 "insert undo size" from dual;
 
insertundo size
----------------
           81456

这个过程可以得到结论,create 表的过程产生的redo:21160,undo:6172;insert 表的过程redo:872770140,undo:81456;整个过程产生的redo:872791300(21160+872770140),undo:87628(6172+81456)

5.INSERT+NOLOGGING

SQL> altersystem flush buffer_cache;
 
System altered.
 
Elapsed: 00:00:02.21
SQL>altersystem flush shared_pool;
 
System altered.
 
Elapsed: 00:00:00.06
SQL>selecta.name, b.value
  2 fromv$statname a, v$mystat b
  3 wherea.statistic# = b.statistic#
  4 andlower(a.name)in
  5  ('redo size','undo change vector size');
 
NAME                                                                 VALUE
---------------------------------------------------------------- ----------
redosize                                                              780
undo change vector size                                                136
 
Elapsed: 00:00:00.06
SQL>createtable chf.t_xifenfei_move_INSERT_N tablespace users
  2 as
  3 select* fromchf.t_xifenfei_move where1=0;
 
Tablecreated.
 
Elapsed: 00:00:00.22
SQL>selecta.name, b.value
  2 fromv$statname a, v$mystat b
  3 wherea.statistic# = b.statistic#
  4 andlower(a.name)in
  5  ('redo size','undo change vector size');
 
NAME                                                                 VALUE
---------------------------------------------------------------- ----------
redosize                                                            22056
undo change vector size                                               6308
 
Elapsed: 00:00:00.00
SQL>INSERT/*+ NOLOGGING */INTOt_xifenfei_move_INSERT_N
  2 SELECT* FROMT_XIFENFEI_MOVE;
 
7432085rowscreated.
 
Elapsed: 00:02:30.33
SQL>selecta.name, b.value
  2 fromv$statname a, v$mystat b
  3 wherea.statistic# = b.statistic#
  4 andlower(a.name)in
  5  ('redo size','undo change vector size');
 
NAME                                                                 VALUE
---------------------------------------------------------------- ----------
redosize                                                        862123984
undo change vector size                                           27982876
 
--redo
SQL>select22056-780 "create redo size" from dual;
 
createredo size
----------------
           21276
 
SQL>select862123984-22056 "insert redo size" from dual;
 
insertredo size
----------------
       862101928
 
--undo
SQL>select6308-136 "create undo size" from dual;
 
createundo size
----------------
            6172
 
SQL>select27982876-6308 "insert undo size" from dual;
 
insertundo size
----------------
        27976568

这个试验过程得出,create table得到redo:21276,undo:6172,insert table 得到redo:862101928,undo:27976568,整个过程redo:862123204,undo:27982740(27976568+6172)

6.INSERT+NOLOGGING(TABLE)

SQL> altersystem flush buffer_cache;
 
System altered.
 
Elapsed: 00:00:23.68
SQL>altersystem flush shared_pool;
 
System altered.
 
Elapsed: 00:00:00.04
SQL>selecta.name, b.value
  2 fromv$statname a, v$mystat b
  3 wherea.statistic# = b.statistic#
  4 andlower(a.name)in
  5  ('redo size','undo change vector size');
 
NAME                                                                 VALUE
---------------------------------------------------------------- ----------
redosize                                                              800
undo change vector size                                                136
 
Elapsed: 00:00:00.18
SQL>createtable chf.t_xifenfei_move_INSERT_N_new nologging tablespace users
  2 as
  3 select* fromchf.t_xifenfei_move where1=0;
 
Tablecreated.
 
Elapsed: 00:00:00.71
SQL>selecta.name, b.value
  2 fromv$statname a, v$mystat b
  3 wherea.statistic# = b.statistic#
  4 andlower(a.name)in
  5  ('redo size','undo change vector size');
 
NAME                                                                 VALUE
---------------------------------------------------------------- ----------
redosize                                                            23296
undo change vector size                                               6756
 
Elapsed: 00:00:00.00
SQL>INSERTINTO t_xifenfei_move_INSERT_N_new
  2 SELECT* FROMT_XIFENFEI_MOVE;
 
7432085rowscreated.
 
Elapsed: 00:02:37.51
SQL>selecta.name, b.value
  2 fromv$statname a, v$mystat b
  3 wherea.statistic# = b.statistic#
  4 andlower(a.name)in
  5  ('redo size','undo change vector size');
 
NAME                                                                 VALUE
---------------------------------------------------------------- ----------
redosize                                                        862031304
undo change vector size                                           27982776
 
SQL>select23296-800 "create redo size" from dual;
 
createredo size
----------------
           22496
 
SQL>select862031304-23296 "insert redo size" from dual;
 
insertredo size
----------------
       862008008
 
SQL>select6756-136 "create undo size" from dual;
 
createundo size
----------------
            6620
 
SQL>select27982776-6756 "insert undo size" from dual;
 
insertundo size
----------------
        27976020

通过该试验得出,create table 产生redo:22496,undo:6620;insert into 产生redo:862008008,undo:27976020;整个过程产生redo:862030504(22496+862008008),undo:27982640(6620+27976020)

7.APPEND+NOLOGGING(TABLE)

SQL>altersystem flush buffer_cache;
 
System altered.
 
Elapsed: 00:00:23.59
SQL>altersystem flush shared_pool;
 
System altered.
 
Elapsed: 00:00:00.05
SQL>selecta.name, b.value
  2 fromv$statname a, v$mystat b
  3 wherea.statistic# = b.statistic#
  4 andlower(a.name)in
  5  ('redo size','undo change vector size');
 
NAME                                                                 VALUE
---------------------------------------------------------------- ----------
redosize                                                              780
undo change vector size                                                136
 
Elapsed: 00:00:00.04
SQL>createtable chf.t_xifenfei_move_INSERT_NA nologging tablespace users
  2 as
  3 select* fromchf.t_xifenfei_move where1=0;
 
Tablecreated.
 
Elapsed: 00:00:00.42
SQL>selecta.name, b.value
  2 fromv$statname a, v$mystat b
  3 wherea.statistic# = b.statistic#
  4 andlower(a.name)in
  5  ('redo size','undo change vector size');
 
NAME                                                                 VALUE
---------------------------------------------------------------- ----------
redosize                                                            22000
undo change vector size                                               6308
 
Elapsed: 00:00:00.00
SQL>INSERT/*+APPEND */ INTOt_xifenfei_move_INSERT_NA
  2 SELECT* FROMT_XIFENFEI_MOVE;
 
7432085rowscreated.
 
Elapsed: 00:01:08.92
SQL>selecta.name, b.value
  2 fromv$statname a, v$mystat b
  3 wherea.statistic# = b.statistic#
  4 andlower(a.name)in
  5  ('redo size','undo change vector size');
 
NAME                                                                 VALUE
---------------------------------------------------------------- ----------
redosize                                                           602352
undo change vector size                                              82756
 
--redo
SQL>select22000-780 "create redo size" from dual;
 
createredo size
----------------
           21220
 
SQL>select602352-22000 "insert redo size" from dual;
 
insertredo size
----------------
          580352
 
--undo
SQL>select6308-136 "create undo size" from dual;
 
createundo size
----------------
            6172
 
SQL>select82756-6308 "insert undo size" from dual;
 
insertundo size
----------------
           76448

在这个试验中，create table产生redo:21220,undo:6172,insert into产生redo:580352,undo:76448;整个过程产生的redo:601572(
21220+580352),undo:82620(6172+76448)

8.CAST+NOLOGGING(TABLE)

SQL> altersystem flush buffer_cache;
 
System altered.
 
Elapsed: 00:00:03.35
SQL>altersystem flush shared_pool;
 
System altered.
 
Elapsed: 00:00:00.04
SQL>
SQL>selecta.name, b.value
  2 fromv$statname a, v$mystat b
  3 wherea.statistic# = b.statistic#
  4 andlower(a.name)in
  5  ('redo size','undo change vector size');
 
NAME                                                                 VALUE
---------------------------------------------------------------- ----------
redosize                                                              732
undo change vector size                                                136
 
Elapsed: 00:00:00.05
SQL>createtable chf.t_xifenfei_move_cast_N nologging tablespace users
  2 as
  3 select* fromchf.t_xifenfei_move;
 
Tablecreated.
 
Elapsed: 00:00:56.41
SQL>selecta.name, b.value
  2 fromv$statname a, v$mystat b
  3 wherea.statistic# = b.statistic#
  4 andlower(a.name)in
  5  ('redo size','undo change vector size');
 
NAME                                                                 VALUE
---------------------------------------------------------------- ----------
redosize                                                           769848
undo change vector size                                             124944
 
SQL>select769848-732 "redo size" from dual;
 
 redosize
----------
    769116
 
SQL>select124944-136 "undo  size" from dual;
 
undo size
----------
    124808

这个试验产生的redo:769116,undo:124808

9.通过试验得出结论
1)sql hint中的nologgging无效
2)普通的cast(不含hint)，其本质是append，无nologgging
3)nologgging(表级别)可以使得cast效率较高
4)nologgging(表级别)+append(hint)可以使得insert效率较高

原文地址：http://www.xifenfei.com/2012/01/cast%E6%9C%AC%E8%B4%A8%E6%8E%A2%E8%AE%A8.html

0 0