环境说明:
物理机: 2C+4G+1TB PC机(非SERVER)
VMWARE: 2C+2G +100G
大表:
"EI_SRCDATA_HIST"."OF_05_CL" 25.20 GB 178690871 rows
OWNER
SEGMENT_NAME
SEGMENT_TYPE
TABLESPACE_NAME
BYTES/1024/1024
BLOCKS
EXTENTS
EI_SRCDATA_HIST
IDX_OF_05_CL
INDEX
TBS_EI_SRCDATA_HIST_IDX
3257.25
416928
418
EI_SRCDATA_HIST
OF_05_CL
TABLE
TBS_EI_SRCDATA_HIST
29760
3809280
649
测试内容:
1\ 更改一字段char(6)--->char(12) 耗时多久
注意以下几点:
1) 更改中会占用大量的undo tablespace;留意undo 表空间是否够用
2) 更改中会产生大量的archivelog;留意归档目录是否有空间归档.
3) 在修改的列中,有默认值\索引\约束\触发器等条件的话,耗时很长;
若修改的列中无以上条件,即使是大表,也秒杀完成。
测试结果:
SQL> alter table EI_SRCDATA_HIST.OF_05_CL modify FUNDCODE CHAR(12);
―――――――――fundcode有默认值FUNDCODE CHAR(6) default '',
Table altered.
Elapsed: 20:25:45.11(实际花费7-8小时左右,期间归档日志满,停止)
SQL> alter table EI_SRCDATA_HIST.OF_05_CL modify SEATCODE VARCHAR2(12);
――――――SEATCODE没默认值SEATCODE VARCHAR2(12)
Table altered.
Elapsed: 00:00:00.95
总结:在测试中发现对varchar字段的扩位,对number字段类型的扩位 都非常快;
但同样类型的字段缩位,都需要逐行扫描,会很慢!!!
对char类型的字段扩位,不管原来字段有没有默认值,都会非常非常慢!!
其他类型没做测试.
2\ expdp 导出此表要多久,
1.78亿条记录; 25.2GB 导出耗时 6分25秒 (注此操作非在测试机上进行,在AIX上导出的)
Export: Release 10.2.0.3.0 - 64bit Production on Wednesday, 28 July, 2010 10:10:18
Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=f_test dumpfile=EI_SRCDATA_HIST.OF_05_CL.dmp logfile=EI_SRCDATA_HIST.OF_05_CL.log tables=EI_SRCDATA_HIST.OF_05_CL
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 28.82 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "EI_SRCDATA_HIST"."OF_05_CL" 25.20 GB 178690871 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/VOL3/EI_SRCDATA_HIST.OF_05_CL.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completedat 10:16:43
EXPDP 125G的表耗时 25分钟
Export: Release 10.2.0.3.0 - 64bit Production on Wednesday, 28 July, 2010 14:54:35
Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=f_test dumpfile=EI_SRCDATA_HIST.HA_JSMX_CL.dmp logfile=EI_SRCDATA_HIST.HA_JSMX_CL.log tables=EI_SRCDATA_HIST.HA_JSMX_CL
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 144.7 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "EI_SRCDATA_HIST"."HA_JSMX_CL" 125.9 GB386660947 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/VOL3/EI_SRCDATA_HIST.HA_JSMX_CL.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 15:19:39