1.展示列属性

[oracle@saas-xunzhi-db1 ~]$ sqlplus testga/testga

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 12 16:26:56 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> desc t_xifenfei;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROCESS_INSTANCE_ID                       NOT NULL NUMBER(10)
 STATUS_NEW                                         NUMBER
 PACKAGE_NAME                                       VARCHAR2(50)

SQL> desc wf_proc_inst@oldmoa
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(10)
 NAME                                               VARCHAR2(500)
 PROC_ID                                   NOT NULL NUMBER(10)
 PARE_INST_ID                              NOT NULL NUMBER(10)
 PARE_ACTI_ID                              NOT NULL NUMBER(10)
 INST_CREATOR                                       VARCHAR2(40)
 CREAT_DATE                                         DATE
 STATUS                                             CHAR(1)
 ATT1                                               NUMBER(10)
 ATT2                                               VARCHAR2(255)
 SEQVALUE                                           VARCHAR2(50)

从这里可以看出，原表的status列是char，而目标表的STATUS_NEW列是number类型

2.通过dblink查询列值

SQL> select t.inst_id as ID,
  2         case w.status
  3           when '0' then
  4            4
  5           when '2' then
  6            3
  7           else
  8            1
  9         end as  status_new,
 10         p.name as PACKAGE_NAME
 11    from wf_proc_inst@oldmoa i
 12    left join wf_proc_info_inst@oldmoa t on t.inst_id = i.id
 13    left join wf_proc_type@oldmoa p on t.type_id = p.id
 14    left join wf_proc_inst@oldmoa w on t.inst_id = w.id
 15   where t.inst_id = i.id
 16     and t.proc_id <> 53
 17     and t.is_ok = 1
 18     AND t.inst_id <= 4837 
 19     AND t.inst_id>=4735;

        ID STATUS_NEW PACKAGE_NAME
---------- ---------- --------------------------
      4755          3 呈批处理
      4836          3 公文处理

从这里可以看出来，case工作正常，传入的char类型，通过case转换为number类型

3.使用dblink插入数据并查询

SQL> INSERT INTO t_xifenfei
  2  select t.inst_id as ID,
  3         case w.status
  4           when '0' then
  5            4
  6           when '2' then
  7            3
  8           else
  9            1
 10         end as  status_new,
 11         p.name as PACKAGE_NAME
 12    from wf_proc_inst@oldmoa i
 13    left join wf_proc_info_inst@oldmoa t on t.inst_id = i.id
 14    left join wf_proc_type@oldmoa p on t.type_id = p.id
 15    left join wf_proc_inst@oldmoa w on t.inst_id = w.id
 16   where t.inst_id = i.id
 17     and t.proc_id <> 53
 18     and t.is_ok = 1
 19     AND t.inst_id <= 4837 
 20     AND t.inst_id>=4735;

2 rows created.

SQL> commit;

Commit complete.

SQL> col package_name for a10
SQL> select * from t_xifenfei;

PROCESS_INSTANCE_ID STATUS_NEW PACKAGE_NA
------------------- ---------- ----------
               4755          1 呈批处理
               4836          1 公文处理

这里出现异常，怀疑case中传入的值，都走到else那边了，怀疑是在使用dblink 的时候，char的类型发生的改变，转为了number，导致case匹配失败，所以走到else。

4.在dblink端创建视图，插入数据

SQL> CREATE VIEW GZZJ.v_xifenfei
  2  AS
  3  select t.inst_id as ID,
  4         case to_char(w.status)
  5           when '0' then
  6            4
  7           when '2' then
  8            3
  9           else
 10            1
 11         end as  status_new,
 12         p.name as PACKAGE_NAME
 13    from GZZJ.wf_proc_inst i
 14    left join GZZJ.wf_proc_info_inst t on t.inst_id = i.id
 15    left join GZZJ.wf_proc_type p on t.type_id = p.id
 16    left join GZZJ.wf_proc_inst w on t.inst_id = w.id
 17   where t.inst_id = i.id
 18     and t.proc_id <> 53
 19     and t.is_ok = 1
 20     AND t.inst_id <= 4837 
 21     AND t.inst_id>=4735;

View created.

SQL> insert into t_xifenfei 
  2  select * from v_xifenfei@oldmoa;

2 rows created.

SQL> commit;

Commit complete.

SQL> select * from t_xifenfei;

PROCESS_INSTANCE_ID STATUS_NEW PACKAGE_NA
------------------- ---------- ----------
               4755          3 呈批处理
               4836          3 公文处理

通过远程视图，接触数据类型发生改变异常(怀疑是dblink 在insert时候，char数据类型变成了number，但是未得到官方或者权威的确认)

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> select FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,
   2 SUPPLEMENTAL_LOG_DATA_MIN from v$database;

FOR SUP SUP SUPPLEME
--- --- --- --------
NO  NO  NO  NO

SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/oradata/archivelog/chf
Oldest online log sequence     1879
Next log sequence to archive   1881
Current log sequence           1881

SQL> select count(*) from t_xifenfei_move;

  COUNT(*)
----------
   7432085

SQL> select bytes/1024/1024 from user_segments where segment_name='T_XIFENFEI_MOVE';

BYTES/1024/1024
---------------
            832

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

2.常规CAST

SQL>  alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> SET TIMING ON
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            100028
undo change vector size                                               16172

Elapsed: 00:00:00.06
SQL> create table chf.t_xifenfei_move_CAST tablespace users
  2  as
  3  select * from chf.t_xifenfei_move;
 
Table created.

Elapsed: 00:01:58.10
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         873150548
undo change vector size                                              131384

Elapsed: 00:00:00.01

SQL> select 873150548-100028 "redo size" from dual;

 redo size
----------
 873050520

SQL> select 131384-16172 "undo size" from dual;

 undo size
----------
    115212

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

3.普通INSERT

SQL>  alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.06
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               732
undo change vector size                                                 136

Elapsed: 00:00:00.18
SQL> CREATE TABLE t_xifenfei_move_INSERT 
  2  AS
  3  SELECT * FROM T_XIFENFEI_MOVE WHERE 1=0;

Table created.

Elapsed: 00:00:00.32
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             22712
undo change vector size                                                6512

Elapsed: 00:00:00.02
SQL> INSERT INTO t_xifenfei_move_INSERT
  2  SELECT * FROM T_XIFENFEI_MOVE;

7432085 rows created.

Elapsed: 00:01:59.47
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         862261580
undo change vector size                                            27980508

--redo 
SQL> select 22712-732 "create redo size" from dual;

create redo size
----------------
           21980

SQL> select 862261580-22712 "insert redo size" from dual;

insert redo size
----------------
       862238868

--undo
SQL> select 6512-136 "create undo size" from dual;

create undo size
----------------
            6376

SQL> select 27980508-6512 "insert undo size" from dual;

insert undo 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>  alter system flush buffer_cache;

System altered.

Elapsed: 00:00:25.19
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.04
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               732
undo change vector size                                                 136

Elapsed: 00:00:00.05
SQL> create table chf.t_xifenfei_move_INSERT_A tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;

Table created.

Elapsed: 00:00:00.18
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             21892
undo change vector size                                                6308

Elapsed: 00:00:00.00
SQL> INSERT /*+ append */INTO t_xifenfei_move_INSERT_A
  2  SELECT * FROM T_XIFENFEI_MOVE;

7432085 rows created.

Elapsed: 00:02:26.37
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         872792032
undo change vector size                                               87764

--redo
SQL> select 21892-732 "create redo size" from dual;

create redo size
----------------
           21160

SQL> select 872792032-21892 "insert redo size" from dual;

insert redo size
----------------
       872770140

--undo
SQL> select 6308-136 "create undo size" from dual;

create undo size
----------------
            6172

SQL> select 87764-6308 "insert undo size" from dual;

insert undo size
----------------
           81456

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

5.INSERT+NOLOGGING

SQL>  alter system flush buffer_cache;

System altered.

Elapsed: 00:00:02.21
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.06
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               780
undo change vector size                                                 136

Elapsed: 00:00:00.06
SQL> create table chf.t_xifenfei_move_INSERT_N tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;

Table created.

Elapsed: 00:00:00.22
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             22056
undo change vector size                                                6308

Elapsed: 00:00:00.00
SQL> INSERT /*+ NOLOGGING */INTO t_xifenfei_move_INSERT_N
  2  SELECT * FROM T_XIFENFEI_MOVE;

7432085 rows created.

Elapsed: 00:02:30.33
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         862123984
undo change vector size                                            27982876

--redo
SQL> select 22056-780 "create redo size" from dual;

create redo size
----------------
           21276

SQL> select 862123984-22056 "insert redo size" from dual;

insert redo size
----------------
       862101928

--undo
SQL> select 6308-136 "create undo size" from dual;

create undo size
----------------
            6172

SQL> select 27982876-6308 "insert undo size" from dual;

insert undo 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>  alter system flush buffer_cache;

System altered.

Elapsed: 00:00:23.68
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.04
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               800
undo change vector size                                                 136

Elapsed: 00:00:00.18
SQL> create table chf.t_xifenfei_move_INSERT_N_new nologging tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;

Table created.

Elapsed: 00:00:00.71
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             23296
undo change vector size                                                6756

Elapsed: 00:00:00.00
SQL> INSERT INTO t_xifenfei_move_INSERT_N_new
  2  SELECT * FROM T_XIFENFEI_MOVE;

7432085 rows created.

Elapsed: 00:02:37.51
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         862031304
undo change vector size                                            27982776

SQL> select 23296-800 "create redo size" from dual;

create redo size
----------------
           22496

SQL> select 862031304-23296 "insert redo size" from dual;

insert redo size
----------------
       862008008

SQL> select 6756-136 "create undo size" from dual;

create undo size
----------------
            6620

SQL> select 27982776-6756 "insert undo size" from dual;

insert undo 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> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:23.59
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.05
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               780
undo change vector size                                                 136

Elapsed: 00:00:00.04
SQL> create table chf.t_xifenfei_move_INSERT_NA nologging tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;

Table created.

Elapsed: 00:00:00.42
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             22000
undo change vector size                                                6308

Elapsed: 00:00:00.00
SQL> INSERT /*+APPEND */ INTO t_xifenfei_move_INSERT_NA
  2  SELECT * FROM T_XIFENFEI_MOVE;

7432085 rows created.

Elapsed: 00:01:08.92
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            602352
undo change vector size                                               82756

--redo
SQL> select 22000-780 "create redo size" from dual;

create redo size
----------------
           21220

SQL> select 602352-22000 "insert redo size" from dual;

insert redo size
----------------
          580352

--undo
SQL> select 6308-136 "create undo size" from dual;

create undo size
----------------
            6172

SQL> select 82756-6308 "insert undo size" from dual;

insert undo 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>  alter system flush buffer_cache;

System altered.

Elapsed: 00:00:03.35
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.04
SQL> 
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               732
undo change vector size                                                 136

Elapsed: 00:00:00.05
SQL> create table chf.t_xifenfei_move_cast_N nologging tablespace users
  2  as
  3  select * from chf.t_xifenfei_move;

Table created.

Elapsed: 00:00:56.41
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            769848
undo change vector size                                              124944

SQL> select 769848-732 "redo size" from dual;

 redo size
----------
    769116

SQL> select 124944-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效率较高