分享
 
 
 

分区的情况下,对insert速度影响的测试

王朝other·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

quote:

--------------------------------------------------------------------------------

CREATE TABLE PART_TEST

(

HOST VARCHAR2(20),

GATHER_TIME VARCHAR2(10),

VGNAME VARCHAR2(20),

DEVICE VARCHAR2(20),

BUSY NUMBER(12,2),

AVQUE NUMBER(12,2),

RW_S NUMBER(12),

BLKS_S NUMBER(12),

AVWAIT NUMBER(12,2),

AVSERV NUMBER(12,2)

)

PARTITION BY RANGE(GATHER_TIME)

(

PARTITION P200309 VALUES LESS THAN ('200310'),

PARTITION P200310 VALUES LESS THAN ('200311'),

PARTITION P200311 VALUES LESS THAN ('200312'),

PARTITION P200312 VALUES LESS THAN ('200401'),

PARTITION P200401 VALUES LESS THAN ('200402')

)

-------------------------------------------------------------------------------

用sqlldr进行装载测试的角本

quote:

--------------------------------------------------------------------------------

--

-- Copyright (c) 2002 by Lou Fangxin,Blinkstar@163.net

-- Description:

-- Generated by Text EXPort Utility

-- Usage:

-- Change to actual table name

-- sqlldr user/pass@dbconn control=TEST_sqlldr.ctl log=TEST_sqlldr.log

-- Created on Wed Jul 21 19:07:20 CST 2004

--

OPTIONS(DIRECT=TRUE,ERRORS=-1,SKIP=1,ROWS=50000)

LOAD DATA

INFILE 'TAB_DISK_STATS.TXT' "STR '\r\n'"

BADFILE 'TEST.BAD'

DISCARDFILE 'TEST.DSC'

INTO TABLE PART_TEST

APPEND

FIELDS TERMINATED BY ''

TRAILING NULLCOLS

(

HOST CHAR,

GATHER_TIME CHAR,

VGNAME CHAR,

DEVICE CHAR,

BUSY CHAR,

AVQUE CHAR,

RW_S CHAR,

BLKS_S CHAR,

AVWAIT CHAR,

AVSERV CHAR

)

--------------------------------------------------------------------------------

在有索引的情况下

分区表

quote:

--------------------------------------------------------------------------------

The following index(es) on table PART_TEST were processed:

index TEST.IND_PART_TEST partition P200309 loaded sUCcessfully with 24504 keys

index TEST.IND_PART_TEST partition P200310 loaded successfully with 60604 keys

index TEST.IND_PART_TEST partition P200311 loaded successfully with 58678 keys

index TEST.IND_PART_TEST partition P200312 loaded successfully with 61782 keys

index TEST.IND_PART_TEST partition P200401 loaded successfully with 41173 keys

Table PART_TEST:

246741 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Partition P200309: 24504 Rows loaded.

Partition P200310: 60604 Rows loaded.

Partition P200311: 58678 Rows loaded.

Partition P200312: 61782 Rows loaded.

Partition P200401: 41173 Rows loaded.

Bind array size not used in direct path.

Column array rows : 100

Stream buffer bytes: 256000

Read buffer bytes: 1048576

Total logical records skipped: 1

Total logical records read: 246741

Total logical records rejected: 0

Total logical records discarded: 0

Direct path multithreading optimization is disabled

Run began on Wed Jul 21 19:13:44 2004

Run ended on Wed Jul 21 19:13:52 2004

Elapsed time was: 00:00:08.30

CPU time was: 00:00:01.64

--------------------------------------------------------------------------------

未分区表

quote:

--------------------------------------------------------------------------------

The following index(es) on table TAB_DISK_STATS were processed:

index TEST.IND_TAB_DISK_STATS loaded successfully with 246741 keys

Table TAB_DISK_STATS:

246741 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Bind array size not used in direct path.

Column array rows : 100

Stream buffer bytes: 256000

Read buffer bytes: 1048576

Total logical records skipped: 1

Total logical records read: 246741

Total logical records rejected: 0

Total logical records discarded: 0

Direct path multithreading optimization is disabled

Run began on Wed Jul 21 19:14:43 2004

Run ended on Wed Jul 21 19:14:51 2004

Elapsed time was: 00:00:08.20

CPU time was: 00:00:01.58

--------------------------------------------------------------------------------

没有索引的情况下

分区表

quote:

--------------------------------------------------------------------------------

Table PART_TEST:

246741 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Partition P200309: 24504 Rows loaded.

Partition P200310: 60604 Rows loaded.

Partition P200311: 58678 Rows loaded.

Partition P200312: 61782 Rows loaded.

Partition P200401: 41173 Rows loaded.

Bind array size not used in direct path.

Column array rows : 100

Stream buffer bytes: 256000

Read buffer bytes: 1048576

Total logical records skipped: 1

Total logical records read: 246741

Total logical records rejected: 0

Total logical records discarded: 0

Direct path multithreading optimization is disabled

Run began on Wed Jul 21 19:19:02 2004

Run ended on Wed Jul 21 19:19:06 2004

Elapsed time was: 00:00:04.76

CPU time was: 00:00:01.54

--------------------------------------------------------------------------------

未分区表

quote:

--------------------------------------------------------------------------------

Table TAB_DISK_STATS:

246741 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Bind array size not used in direct path.

Column array rows : 100

Stream buffer bytes: 256000

Read buffer bytes: 1048576

Total logical records skipped: 1

Total logical records read: 246741

Total logical records rejected: 0

Total logical records discarded: 0

Direct path multithreading optimization is disabled

Run began on Wed Jul 21 19:18:18 2004

Run ended on Wed Jul 21 19:18:23 2004

Elapsed time was: 00:00:04.87

CPU time was: 00:00:01.69

--------------------------------------------------------------------------------

两个表的索引的结构

create index ... on ... (gather_time,

host)

分区表采用local方式的索引

distinct gather_time = 3033

结论:感觉应该影响不大

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有