关闭

create table as select性能测试

标签: tableinsertlessparallel测试date
8216人阅读 评论(0) 收藏 举报

原表270W数据,无照片,字段比较多,有50个左右

测试机是一个虚拟机,4CPU,内存空闲2G,windows2003,

oracle9.2.0.6 sga 1g,pga 1g

先测试非归档模式下

1. 150秒
采用create table as select结构

create table lr_jbxx_test1 as 
select * from lr_jbxx

归档模式下,耗时166秒

2.99秒
在测试1基础上增加nologging项
create table lr_jbxx_test2
nologging
 as 
select * from lr_jbxx

3.61秒
在测试2基础上增加parallel项
create table lr_jbxx_test3
nologging
parallel (degree 4) 
 as 
select * from lr_jbxx

无nologging,增加并行51秒

 create table lr_jbxx_test7
parallel (degree 4) 
 as 
select * from lr_jbxx

归档模式下,有nologging和parallel,耗时54秒

 

4.62秒
在测试3基础上,将旧表插入分区表
性能与测试3相当
create table LR_JBXX_test4
partition by range (PCS_DM_LRDWSZD)
(
  partition F1 values less than ('410200000000')
    tablespace LDRK_DATA_TS1
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition F2 values less than ('410300000000')
    tablespace LDRK_DATA_TS2
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition F3 values less than ('410400000000')
    tablespace LDRK_DATA_TS3
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition F4 values less than (MAXVALUE)
    tablespace LDRK_DATA_TS4
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    )
)
nologging
parallel (degree 4) 
 as 
select * from lr_jbxx

5.203秒
create table与insert into 分开做,先测试insert into无索引的情况
第一步:创建表结构
create table LR_JBXX_test5
(
  LRRYID         NUMBER(12) not null,
  SFZH           VARCHAR2(18),
  XM             VARCHAR2(90),
  XB             VARCHAR2(8),
  CYM            VARCHAR2(90),
  CSRQ           DATE,
  MZ             VARCHAR2(4),
  SG             NUMBER(6,2),
  CZHKDZ         VARCHAR2(300),
  PCS_DM_HJ      VARCHAR2(12),
  HKSZDLX_DM     VARCHAR2(2),
  WHCD_DM        VARCHAR2(4),
  HYZK_DM        VARCHAR2(4),
  ZY_DM          VARCHAR2(30),
  CZFWID         NUMBER(10),
  FZXM           VARCHAR2(200),
  YFZGX          VARCHAR2(300),
  LRYY_DM        VARCHAR2(10),
  JZDZ           VARCHAR2(300),
  PCS_DM_JZD     VARCHAR2(12),
  JZRQ           DATE,
  JZSY           VARCHAR2(300),
  JZCS_DM        VARCHAR2(10),
  XCSZY          VARCHAR2(30),
  XFWCS          VARCHAR2(300),
  JZZQFRQ        DATE,
  YXQX           NUMBER(4),
  ZRR            VARCHAR2(300),
  HKLB_DM        VARCHAR2(2),
  JZZBH          VARCHAR2(20),
  BZ             VARCHAR2(3000),
  TBR            VARCHAR2(30),
  TBDW           VARCHAR2(14),
  TBRQ           DATE,
  SJLY_DM        VARCHAR2(2),
  DRBZ           CHAR(1),
  BDYJBZ         VARCHAR2(20),
  BDYJSJ         DATE,
  HJID           NUMBER(12),
  XMPY           VARCHAR2(300),
  XZQH_HJ        VARCHAR2(60),
  XZQH_JZD       VARCHAR2(60),
  PCS_MC_HJ      VARCHAR2(600),
  PCS_MC_JZD     VARCHAR2(600),
  DYCS           NUMBER(4),
  DYSJ           DATE,
  TBDWMC         VARCHAR2(300),
  GAJGXZ         VARCHAR2(10),
  PCS_DM_LRDWSZD VARCHAR2(12),
  XGDW           VARCHAR2(20),
  XGSJ           DATE,
  BGDW           VARCHAR2(20),
  BGSJ           DATE,
  ZZMM           VARCHAR2(200),
  XGY            VARCHAR2(200)
)
partition by range (PCS_DM_LRDWSZD)
(
  partition F1 values less than ('410200000000')
    tablespace LDRK_DATA_TS1
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition F2 values less than ('410300000000')
    tablespace LDRK_DATA_TS2
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition F3 values less than ('410400000000')
    tablespace LDRK_DATA_TS3
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition F4 values less than (MAXVALUE)
    tablespace LDRK_DATA_TS4
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    )
);
第二步:insert
insert into LR_JBXX_test5  
select * from lr_jbxx

6 2103 
在测试5的基础上,测试索引对批量插入的影响
是没有索引的10倍
alter table LR_JBXX_test6
  add constraint PK_LR_JBXX_FQ1 primary key (LRRYID)
  using index 
  tablespace LDRK_IDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table LR_JBXX_test6
  add constraint YS_JZZBH1 unique (JZZBH)
  using index 
  tablespace LDRK_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table LR_JBXX_test6
  add constraint YS_SFZH1 unique (SFZH, XM)
  using index 
  tablespace LDRK_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index IDX_CZFWID1 on LR_JBXX_test6 (CZFWID)
  tablespace LDRK_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index IDX_LR_JBXX_JZDPCS_FQ1 on LR_JBXX_test6 (PCS_DM_JZD);
create index IDX_LR_JBXX_LRDWSZD_FQ1 on LR_JBXX_test6 (PCS_DM_LRDWSZD);
create index IDX_LR_JBXX_SFZH_FQ1 on LR_JBXX_test6 (SFZH)
  tablespace LDRK_IDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index IDX_LR_JBXX_TBDW_FQ1 on LR_JBXX_test6 (TBDW)
  tablespace LDRK_IDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index IDX_LR_JBXX_XGDW1 on LR_JBXX_test6 (XGDW)
  tablespace LDRK_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index IDX_LR_JBXX_ZRR1 on LR_JBXX_test6 (ZRR)
  tablespace LDRK_IDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

7.197秒

使用insert into /*+ append */结构

insert into /*+ append */ LR_JBXX_test7  
select * from lr_jbxx

归档模式下,255秒

8.188秒

与测试7比较,不使用hits

性能反倒更好。

在归档模式下,耗时267秒,开销增加约30%

结论:

1.create table as select结构远比先create table再insert into性能好的多,该测试性能差3倍;

2.使用nologging,性能提高一半,使用parallel性能提高一倍多;

3.索引对insert的性能影响极大,10倍以上;

4.不管是否归档,使用/*+ append */结构,对insert into都没有性能提升,现在还不清楚为什么。

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:325076次
    • 积分:3020
    • 等级:
    • 排名:第11614名
    • 原创:43篇
    • 转载:54篇
    • 译文:1篇
    • 评论:65条
    文章分类
    最新评论