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