이관 Tool인 EXPDP의 옵션 중 QUERY를 이용하여 원하는 데이터만 추출할 수 있습니다.
사용법은 아래와 같습니다.
QUERY=SCHEMA.TABLE명:'"WHERE 절"'
1.Query 옵션 미사용 시
130 rows가 추출된 것을 확인할 수 있습니다.
expdp \'/ as sysdba\' directory=DIC_DBCHK99 \
DUMPFILE=DEV_UITEM.dmp \
logfile= DEV_UITEM.log \
job_name=YSBAE_DEV_UITEM \
tables= DEV.UITEM
Export: Release 12.2.0.1.0 - Production on Thu Jan 20 13:21:34 2022
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."YSBAE_DEV_UITEM": "/******** AS SYSDBA" directory=DIC_DBCHK99 DUMPFILE=DEV_UITEM.dmp logfile=DEV_UITEM.log job_name=YSBAE_DEV_UITEM tables=DEV.UITEM
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "DEV"."UITEM" 18.79 KB 130 rows
Master table "SYS"."YSBAE_DEV_UITEM" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.YSBAE_DEV_UITEM is:
/ora_work/dump/ysbae/DEV_UITEM.dmp
Job "SYS"."YSBAE_DEV_UITEM" successfully completed at Thu Jan 20 13:21:56 2022 elapsed 0 00:00:21
2.Query 옵션 사용시
QUERY=DEV.UITEM:'"WHERE ITEM_ID IN (SELECT level as LVL FROM DUAL CONNECT BY LEVEL <= 10)"' 옵션을 사용하여 ITEM_ID가 1부터 10까지 10개의 row만 추출하게 하였고 결과적으로 10 rows만 추출된 것을 확인할 수 있습니다.
expdp \'/ as sysdba\' directory=DIC_DBCHK99 \
DUMPFILE=DEV_UITEM_Q.dmp \
logfile= DEV_UITEM_Q.log \
job_name=YSBAE_DEV_UITEM_Q \
tables= DEV.UITEM \
QUERY=DEV.UITEM:'"WHERE ITEM_ID IN (SELECT level as LVL FROM DUAL CONNECT BY LEVEL <= 10)"'
Export: Release 12.2.0.1.0 - Production on Thu Jan 20 13:20:14 2022
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."YSBAE_DEV_UITEM_Q": "/******** AS SYSDBA" directory=DIC_DBCHK99 DUMPFILE=DEV_UITEM_Q.dmp logfile=DEV_UITEM_Q.log job_name=YSBAE_DEV_UITEM_Q tables=DEV.UITEM QUERY=DEV.UITEM:"WHERE ITEM_ID IN (SELECT level as LVL FROM DUAL CONNECT BY LEVEL <= 10)"
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "DEV"."UITEM" 9.335 KB 10 rows
Master table "SYS"."YSBAE_DEV_UITEM_Q" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.YSBAE_DEV_UITEM_Q is:
/ora_work/dump/ysbae/DEV_UITEM_Q.dmp
Job "SYS"."YSBAE_DEV_UITEM_Q" successfully completed at Thu Jan 20 13:20:55 2022 elapsed 0 00:00:40
인덱스 생성
SQL> CREATE INDEX DEV.ORD_ITEM_X01
ON DEV.ORD_ITEM (ORD_DT,ORD_HMS)
TABLESPACE MY_DATA
확인
SQL> select index_name,table_owner,status from user_indexes where index_name='ORD_ITEM_X01';
TABLE_OWNER INDEX_NAME VISIBILITY
-------------- ------------- --------------------
DEV ORD_ITEM_X01 VISIBLE
EXPDP 수행
time \
expdp \'/ as sysdba\' directory=DIC_DBCHK99 \
DUMPFILE=DEV_ORD_ITEM_Q.dmp \
logfile= DEV_ORD_ITEM_Q.log \
job_name=YSBAE_ORD_ITEM_Q \
tables= DEV.ORD_ITEM \
QUERY=DEV.UITEM:'"WHERE ORD_DT BETWEEN TO_DATE('20120101','YYYYMMDD') AND TO_DATE('20121231','YYYYMMDD')"'
Export: Release 12.2.0.1.0 - Production on Thu Jan 20 13:36:22 2022
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."YSBAE_ORD_ITEM_Q": "/******** AS SYSDBA" directory=DIC_DBCHK99 DUMPFILE=DEV_ORD_ITEM_Q.dmp logfile=DEV_ORD_ITEM_Q.log job_name=YSBAE_ORD_ITEM_Q tables=DEV.ORD_ITEM QUERY=DEV.UITEM:"WHERE ORD_DT BETWEEN TO_DATE(20120101,YYYYMMDD) AND TO_DATE(20121231,YYYYMMDD)"
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "DEV"."ORD_ITEM" 5.456 GB 37100209 rows
Master table "SYS"."YSBAE_ORD_ITEM_Q" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.YSBAE_ORD_ITEM_Q is:
/ora_work/dump/ysbae/DEV_ORD_ITEM_Q.dmp
Job "SYS"."YSBAE_ORD_ITEM_Q" successfully completed at Thu Jan 20 13:37:25 2022 elapsed 0 00:01:02
real 1m4.07s
user 0m0.03s
sys 0m0.00s
2.INDEX를 INVISIBLE 상태로 만들고 동일하게 시도
1.425초정도 소요되었습니다.
SQL> ALTER INDEX ORD_ITEM_X01 INVISIBLE;
SQL> select TABLE_OWNER,INDEX_NAME,VISIBILITY from user_indexes where index_name='ORD_ITEM_X01';
TABLE_OWNER INDEX_NAME VISIBILITY
-------------- --------------- --------------------
DEV ORD_ITEM_X01 INVISIBLE
1 rows selected.
time \
expdp \'/ as sysdba\' directory=DIC_DBCHK99 \
DUMPFILE=DEV_ORD_ITEM_Q.dmp \
logfile= DEV_ORD_ITEM_Q.log \
job_name=YSBAE_ORD_ITEM_Q \
tables= DEV.ORD_ITEM \
QUERY=DEV.UITEM:'"WHERE ORD_DT BETWEEN TO_DATE('20120101','YYYYMMDD') AND TO_DATE('20121231','YYYYMMDD')"'
Export: Release 12.2.0.1.0 - Production on Thu Jan 20 13:40:14 2022
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."YSBAE_ORD_ITEM_Q": "/******** AS SYSDBA" directory=DIC_DBCHK99 DUMPFILE=DEV_ORD_ITEM_Q.dmp logfile=DEV_ORD_ITEM_Q.log job_name=YSBAE_ORD_ITEM_Q tables=DEV.ORD_ITEM QUERY=DEV.UITEM:"WHERE ORD_DT BETWEEN TO_DATE(20120101,YYYYMMDD) AND TO_DATE(20121231,YYYYMMDD)"
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "DEV"."ORD_ITEM" 5.456 GB 37100209 rows
Master table "SYS"."YSBAE_ORD_ITEM_Q" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.YSBAE_ORD_ITEM_Q is:
/ora_work/dump/ysbae/DEV_ORD_ITEM_Q.dmp
Job "SYS"."YSBAE_ORD_ITEM_Q" successfully completed at Thu Jan 20 13:41:16 2022 elapsed 0 00:01:02
real 1m4.25s
user 0m0.04s
sys 0m0.01s
출처의 사이트에서는 200GB가 넘는 테이블로 테스트를 진행하였고, 유의미한 시간차이를 보였으나, 제가 테스트를 했을때는 5.4GB정도의 사이즈로 테스트를 진행해서 그런지 유의미한 시간 차이를 보이지는 않았습니다.. 추후에 대량의 테이블을 생성해서 다시 테스트를 진행해보겠습니다.
댓글