分区的情况下，对insert速度影响的测试
2024-07-21 02:37:50
供稿：网友
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
结论：感觉应该影响不大
