oracle创建大表测试,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 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