ORACLE下大表相关测试

环境说明:

物理机: 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(６) default '',

Table altered.

 

Elapsed: 20:25:45.11　（实际花费７－８小时左右，期间归档日志满，停止）

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 completed at 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 GB 386660947 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