create table as select性能测试

最新推荐文章于 2021-04-11 17:54:40 发布
最新推荐文章于 2021-04-11 17:54:40 发布
阅读量2.3k 收藏 4
点赞数
分类专栏： oracle 文章标签： 数据库 oracle

原表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
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle AS关键字 提示错误
01-21
今天运行程序时，在Oracle中输入SQL语句：select * from USERS as u ，程序报错          输入select * from USERS  u 可以正常的查询出结果。     但是在SQL Server 2005 中输入”select * from USERS as u”相同的语句就没有错，查了原因如下：SQL2规范允许关键字AS出现在表的名字和表的别名之间，虽然这使得FROM子句更容易被读懂，但是在特定的SQL产品中它可能还不被支持。     我想Oracle还不支持这样的语法。 您可能感兴趣的文章:plsql
MySQL中create table as 与like的区别分析
01-19
本文分析了MySQL中create table as 与like的区别。分享给大家供大家参考，具体如下： 对于mysql的复制相同表结构方法，有create table as 和create table like 两种，区别是什么呢？ 代码如下:create table t2 as select * from t1 where 1=2;或者 代码如下:limit 0; as创建出来的t2表（新表）缺少t1表（源表）的索引信息，只有表结构相同，没有索引。 代码如下:create table t2 like t1 ; like 创建出来的新表包含源表的完整表结构和索引信息 二者的用途： as用
Mysql 8.0.18 hash join测试(推荐)
01-21
Hash Join Hash Join 不需要任何索引来执行，并且在大多数情况下比当前的块嵌套循环算法更有效。 下面通过实例代码给大家介绍Mysql 8.0.18 hash join测试，具体内容如下所示： CREATE TABLE COLUMNS_hj as select * from information_schema.`COLUMNS`; INSERT INTO COLUMNS SELECT * FROM COLUMNS; -- 最后一次插入25万行 CREATE TABLE COLUMNS_hj2 as select * from information_schema.`COLUM
[Oracle] Create table as 速度测试nologging+parallel
cizhiyou2391的博客
10-21 2254
SQL> show user USER 为 "BRUCE" SQL> select count(*) from bruce; COUNT(*) ---------- 83324928 ...
SELECT INTO , INSERT INTO SELECT 和 CREATE TABLE AS SELECT 的区别
LiangHC
09-01 8388
作者：lianghc  在开发的过程中经常会复制表的数据，将A表的数据复制到B表中，常见的操作方式有三种： 1.SELECT INTO FROM  2.INSERT INTO SELECT  3.CREATE TABLE AS SELECT  经常容易混淆，今天来整理下区别。 1.SELECT INTO FROM  语句形式为：SELECT value1, value2 in
Insert into select 与create table as的性能测试及create table
燕窝
01-26 6332
1. 文档说明 在工作中经常用insert into select 与 create table as语句来进行表单数据的复制；当复制表的数据量很小的时候两个语句的效率区别不大，但当表的数量级别达百万级以上的时候，就会有很明显的效率区别； 本文档也也分成两个部分进行报表数度的测试； 一、 insert into select 与 create table as的效率测试；
create table ***as select ***并行度用在哪里更快
luckyman100的专栏
06-15 6187
现在需要用create table ***as select ***建个新表，大家都知道，用并行度可以加快建表的速度，但是create可以用并行度，select也可以用并行度，那么假设同样的并行度只能用在一个地方，到底用在哪里会快些了？？？我们来做个试验。。。。 1. 建基础表test1 create table as select object_id,object_name from dba
oracle 創建表的三種方式 create、insert、copy比較
vegas_zheng的专栏
07-09 1368
 Create Table…as select…是最快的，而且生成的Undo和Redo信息最少，所以只要可能，请尽量使用这种方案。但这种方案有一定的限制，即目标表必须是不存在的，不能用它向已有的目标表中追加记录。 　Insert into … select … 是最慢的，而且生成最多的Undo和Redo信息，对I/O的压力最大，优势在于大家对它比较熟悉，使用起来比较简单，适合于处理少量的数
MySQL中表复制:create table like 与 create table as select
09-10
主要介绍了MySQL中表复制:create table like 与 create table as select,需要的朋友可以参考下
【11g】创建分区
viviliving的专栏
05-25 401
Creating Partitions 创建分区表或索引与创建非分区表或索引非常相似，但是在CREATE table语句中包含了分区子句。包含的分区子句和子句取决于希望实现的分区类型。 在常规(堆组织的)表和索引组织的表上都可以进行分区，但包含长或长RAW columns的表除外。您可以在分区表上创建非分区的全局索引、范围分区或散列分区的全局索引和本地索引。 在创建(或更改)分区表时，可以指...
create table as select 和 create table 再insert into select 的差异
F1na1E的博客
07-18 785
从一个表到另一个表导入大量数据的时候 可以选择create table nologging parallel 64 as select 的结构导入 同时删除目标表的索引 这样的效率是最高的 原因： insert into select 是需要将数据写入databuffer cache中的，再刷入磁盘。 而ctas的方式则是跳过了数据缓冲区，则是写到磁盘上，直接路径读写方式。 海量数据
oracle中普通insert和直接装载的性能差别
oracel数据库、数据仓库、JAVA开发的专栏
10-09 1310
 这是以前做的一个测试，拿出来给大家作为参考吧测试表：mqqtest测试表原始数据量：0插入数据量：1000w分三种方法插入作为对比参数：1.create table as select 方式2.insert into方式3.insert /*+append*/ into方式第一种方法由于是一个ddl操作，不需写回滚段，因此耗时在dbwr第二种方法是
mysql insert as select from table_在pgsql环境下，Insert into table select…与create table as select…的比较...
weixin_39942351的博客
01-27 1722
最近时间，因为刚入职没人培训就发了一些文档，感觉坑也挺大...心里又有了跑的......好了回顾正题，因为实在太闲，于是听了一些前辈在讨论备份数据的时候，他们普遍使用insert into newTable select * from oldTable的做法(在pgsql环境下)。因为我也不太懂pgsql调优，但是问了咋不直接用create table as select...?前辈们都是nser...
Create table as select性能简介
JAVA
05-30 566
在数据库中移动数据最快的方法是将其从一个表移动到别一个表，而不是需要进入操作系统将数据从一个表移动到别一个表，有一些常见的方法可用于提高数据迁移的性能： 1．             调整表的结构（删除索引和触发器） 2．             在数据迁移期间禁用约束 3．             使用提示和选项来改进事务性能 第一个技巧是调整表的结构，它涉及禁用目标表上面的任何触发...
使用create table ...as创建表时要注意的问题
涤生大数据
03-17 4万+
     工作中有时候做hive开发了，需要对一张表进行备份。一般都会使用 create table as  select(简称:CTAS)...简单方便，但是需要注意CTAS建表产生的问题，因为CTAS建表并不一定会保存原表样式。 1.创建一个分区表 CREATE TABLE T_DEDUCT_SIGN_D( id bigint COMMENT '主键ID', sign_no str...
慎用create table as select,一定要注意默认值的问题
热门推荐
haiross的专栏
11-28 19万+
慎用create table as select,一定要注意默认值的问题 博客分类：  Oracle oraclecreate talbe as select数据库迁移  再做一些数据迁移时候，很多人会使用create table  as select * from table where id=-1的方式来年建立一摸一样的表，但是这样做有个很大的弊端，不能将原表中
oracle create table parallel,create table as及并行相关问题
weixin_39625563的博客
04-11 2122
工作中设计到更新多张千万级数据量的表，需要先统计相关信息，再将信息更新到表中，更新操作需要几个小时才能执行完成。调整思路为，先统计相关信息到中间表中C，用业务表A和C联查，用create table as方式创建表B，删除A表相关索引、主外键，创建B表索引，主外键，将A表rename为备份表，将B表rename为A。期间考虑过用Insert into select方式，比较效率后采用create ...
create table as select 字段
最新发布
01-05
根据提供的引用内容，你可以使用"create table as select"语句来创建一个新表，并从现有表中选择特定的字段。 以下是一个示例： ```sql -- 创建新表并选择字段 CREATE TABLE new_table AS SELECT field1, field2, field3 FROM old_table; ``` 上述示例中，"new_table"是你要创建的新表的名称，"old_table"是你要从中选择字段的现有表的名称。你可以根据需要选择要复制的字段，并将它们列在SELECT语句中。 请注意，使用这种方法创建的新表将不会包含原始表的主键、额外属性和自增属性。如果需要这些属性，你需要手动添加它们。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值