使用DataPump的Sample参数来进行数据抽样

数据泵Datapump是Oracle推出的一项非常方便的数据逻辑备份还原工具。同传统的Exp/Imp相比，Datapump无论在性能、功能上都提供了很多优势进步。在实际工作中，大数据抽样是一个经常出现的业务需求。对一个海量数据表，也许抽取有代表性的数据量就可以完成相应工作。在select语句中，我们的确可以使用sample语句去实现对应的一些功能。借助Datapump，我们也可以实现采样方式的数据表备份处理。1、环境说明我们选择Oracle 11gR2进行测试实验，具体版本为11.2.0.4。SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE 11.2.0.4.0 ProductionTNS for Linux: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 – Production创建数据表SM_TEST。SQL> create table scott.sm_test as select * from dba_objects;Table createdSQL> select count（*） from scott.sm_test; COUNT（*）----------1333912、测试实验对Expdp的Sample参数，官方的解释是很少的。SAMPLEPercentage of data to be exported.Datapump是可以工作在三个模式下，全库模式、Schema模式和表模式。针对三种模式，我们都可以使用Sample参数来进行设置。下面通过最简单的比例值指定来设置采样比例。[oracle@localhost ~]$ expdp "/ as sysdba" dumpfile=TEST_1.dmp tables=scott.sm_test sample=50Export: Release 11.2.0.4.0 - Production on Mon Nov 23 13:50:02 2015Copyright （c） 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" dumpfile=TEST_1.dmp tables=scott.sm_test sample=50 Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 8 MBProcessing object type TABLE_EXPORT/TABLE/TABLE. . exported "SCOTT"."SM_TEST" 6.402 MB 66587 rowsMaster table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /u01/app/admin/sicsdb/dpdump/TEST_1.dmpJob "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Nov 23 13:50:11 2015 elapsed 0 00:00:07指定导出的数据表名称的表模式中，我们通过sample的百分比参数，来确定特定表的内容。从导出数据6万左右的情况，正好是13万总数据的50%，是合理的结果。如果运行在schema工作模式下，直接指定比例份额，结果如何呢？SQL> select count（*） from scott.emp; COUNT（*）---------- 14[oracle@localhost ~]$ expdp "/ as sysdba" dumpfile=TEST_2.dmp schemas=scott sample=50Export: Release 11.2.0.4.0 - Production on Mon Nov 23 13:51:31 2015Copyright （c） 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" dumpfile=TEST_2.dmp schemas=scott sample=50 Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 8.093 MBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/VIEW/VIEWProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "SCOTT"."SM_TEST" 6.406 MB 66611 rows. . exported "SCOTT"."DEPT" 5.859 KB 1 rows. . exported "SCOTT"."EMP" 8.359 KB 9 rows. . exported "SCOTT"."SALGRADE" 5.835 KB 3 rows. . exported "SCOTT"."BONUS" 0 KB 0 rowsMaster table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /u01/app/admin/sicsdb/dpdump/TEST_2.dmpJob "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 23 13:51:48 2015 elapsed 0 00:00:16采样比例在Schema下所有数据表范围内生效，所有数据表基本上以50%的比例进行采样处理。最后，如果我们导出多张数据表，只对某些数据表进行采样处理，那么如何指定sample参数呢？[oracle@localhost ~]$ expdp "/ as sysdba" dumpfile=TEST_3.dmp schemas=scott sample=scott.sm_test:30Export: Release 11.2.0.4.0 - Production on Mon Nov 23 13:52:50 2015Copyright （c） 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" dumpfile=TEST_3.dmp schemas=scott sample=scott.sm_test:30 Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 4.987 MBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/VIEW/VIEWProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "SCOTT"."SM_TEST" 3.861 MB 40146 rows. . exported "SCOTT"."DEPT" 5.960 KB 5 rows. . exported "SCOTT"."EMP" 8.562 KB 14 rows. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows. . exported "SCOTT"."BONUS" 0 KB 0 rowsMaster table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /u01/app/admin/sicsdb/dpdump/TEST_3.dmpJob "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 23 13:53:00 2015 elapsed 0 00:00:09从上面实验中，如果只是针对特定数据表进行采样处理，需要通过数据表名:采样比例方式来指定。3、结论根据笔者的猜测，sample参数在expdp中主要是传导到select语句的sample语句来实现。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址