create table as select性能测试

转载 2012年03月26日 18:35:44

原表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都没有性能提升,现在还不清楚为什么。

Oracle用存储过程查询多张表的方法

前用企业库读SQL Server返回数据集没任何问题,可以返回1个也可以返回多个,读Oracle的时候返回一个数据集的时候也没问题,可是最近在用Oracle返回多个数据集的时候却出了问题,几经辗转,终...
  • jumtre
  • jumtre
  • 2014年09月28日 13:48
  • 6045

当create table as select 遇上大数据

统计24小时的红包感知专题,Nokia给出的方法是先按小时执行算法,再汇总各个小时的执行结果。 算法中包含了大量的 sum(case when)计算。 专题里有5个小节,执行计划的时候,需要跑5次wh...

hive基本的操作语句(实例简单易懂,create table XX as select XX)

创建表: hive> CREATE TABLE pokes (foo INT, bar STRING);          Creates a table called pokes with tw...

慎用create table as select,一定要注意默认值的问题---大一临时表方法

摘要: 1、再做一些数据迁移时候,很多人会使用create table  as select * from table where id=-1的方式来年建立一摸一样的表,但是这样做有个很大的弊端,不...

慎用create table as select,一定要注意默认值的问题

慎用create table as select,一定要注意默认值的问题 博客分类:  Oracle oraclecreate talbe as select数据库迁移  再做一些数据迁移...
  • haiross
  • haiross
  • 2013年11月28日 10:44
  • 79240

oracle数据库【表复制】insert into select from跟create table as select * from 两种表复制语句区别

create table  as select * from和insert into select from两种表复制语句区别 create table targer_table as select...

postgres create table as select & create table like

1. 准备   先建立一个基础表: create table mytb1(id serial,name character varying,age integer);  在那么字段上创建索引: c...

千百万以上海量连接的select、poll和epoll等网络I/O模型的性能测试与分析提纲

千百万以上海量连接的select、poll和epoll等网络I/O模型的性能测试与分析。The C10M Problem...
  • tlthm
  • tlthm
  • 2016年07月03日 23:53
  • 342

db2中create table as的建表方式

db2 中通过查询结果来create table 的3种方式 Q:想通过DB2把查询出来的结果定义为别的表,因为db2没有类似(或者说对db2还不熟悉)oracle的create table as...

mysql 如何按照索引横向拼接两张表的字段, 利用create table as, join on

我本来是建了两张表,各自都有 wind_code, date 两列索引,其它的字段不相同。现在为了数据更新方便点,我希望将两张表的字段拼接起来,最后还只保留两列索引。 假设两张表的名称是stock_...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:create table as select性能测试
举报原因:
原因补充:

(最多只允许输入30个字)