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

　　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
　　结论：感觉应该影响不大