Oracle数据泵导入的时候创建索引是否会使用并行?

一、疑问,Oracle数据泵导入的时候创建索引是否会使用并行?

某客户需要使用数据泵进行迁移,客户咨询导入的时间能不能加快一点。

那么如何加快导入的速度呢? 多加一些并行,那么创建索引内部的索引并行度是否会调整呢?

带着这些疑问看看Oracle数据泵并行参数与导入后创建索引的并行度是否有关系!

二、实验测试

2.1测试数据准备

Oracel11.2.0.4

--分区表创建
create user yz identified by yz;
grant dba to yz;
conn yz/yz
create table a1(id number,
deal_date date, area_code number, contents varchar2(4000))
partition by range(deal_date)
(
partition p1 values less than(to_date('2019-02-01','yyyy-mm-dd')),
partition p2 values less than(to_date('2019-03-01','yyyy-mm-dd')),
partition p3 values less than(to_date('2019-04-01','yyyy-mm-dd')),
partition p4 values less than(to_date('2019-05-01','yyyy-mm-dd')),
partition p5 values less than(to_date('2019-06-01','yyyy-mm-dd')),
partition p6 values less than(to_date('2019-07-01','yyyy-mm-dd')),
partition p7 values less than(to_date('2019-08-01','yyyy-mm-dd')),
partition p8 values less than(to_date('2019-09-01','yyyy-mm-dd')),
partition p9 values less than(to_date('2019-10-01','yyyy-mm-dd')),
partition p10 values less than(to_date('2019-11-01','yyyy-mm-dd')),
partition p11 values less than(to_date('2019-12-01','yyyy-mm-dd')),
partition p12 values less than(to_date('2020-01-01','yyyy-mm-dd')),
partition p13 values less than(to_date('2020-02-01','yyyy-mm-dd')),
partition p14 values less than(to_date('2020-03-01','yyyy-mm-dd')),
partition p15 values less than(to_date('2020-04-01','yyyy-mm-dd')),
partition p16 values less than(to_date('2020-05-01','yyyy-mm-dd')),
partition p17 values less than(to_date('2020-06-01','yyyy-mm-dd')),
partition p18 values less than(to_date('2020-07-01','yyyy-mm-dd')),
partition p19 values less than(to_date('2020-08-01','yyyy-mm-dd')),
partition p20 values less than(to_date('2020-09-01','yyyy-mm-dd')),
partition p31 values less than(to_date('2020-10-01','yyyy-mm-dd')),
partition p32 values less than(to_date('2020-11-01','yyyy-mm-dd')),
partition p33 values less than(to_date('2020-12-01','yyyy-mm-dd')),
partition p34 values less than(to_date('2021-01-01','yyyy-mm-dd')),
partition p35 values less than(to_date('2021-02-01','yyyy-mm-dd')),
partition p36 values less than(to_date('2021-03-01','yyyy-mm-dd')),
partition p37 values less than(to_date('2021-04-01','yyyy-mm-dd')),
partition p38 values less than(to_date('2021-05-01','yyyy-mm-dd')),
partition p39 values less than(to_date('2021-06-01','yyyy-mm-dd')),
partition p40 values less than(to_date('2021-07-01','yyyy-mm-dd'))
); insert into a1 (id,deal_date,area_code,contents)
select rownum,
to_date(to_char(sysdate-900,'J')+ trunc(dbms_random.value(0,200)),'J'),
ceil(dbms_random.value(590,599)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit; create table a2(id number,
deal_date date, area_code number, contents varchar2(4000))
partition by range(deal_date)
(
partition p1 values less than(to_date('2019-02-01','yyyy-mm-dd')),
partition p2 values less than(to_date('2019-03-01','yyyy-mm-dd')),
partition p3 values less than(to_date('2019-04-01','yyyy-mm-dd')),
partition p4 values less than(to_date('2019-05-01','yyyy-mm-dd')),
partition p5 values less than(to_date('2019-06-01','yyyy-mm-dd')),
partition p6 values less than(to_date('2019-07-01','yyyy-mm-dd')),
partition p7 values less than(to_date('2019-08-01','yyyy-mm-dd')),
partition p8 values less than(to_date('2019-09-01','yyyy-mm-dd')),
partition p9 values less than(to_date('2019-10-01','yyyy-mm-dd')),
partition p10 values less than(to_date('2019-11-01','yyyy-mm-dd')),
partition p11 values less than(to_date('2019-12-01','yyyy-mm-dd')),
partition p12 values less than(to_date('2020-01-01','yyyy-mm-dd')),
partition p13 values less than(to_date('2020-02-01','yyyy-mm-dd')),
partition p14 values less than(to_date('2020-03-01','yyyy-mm-dd')),
partition p15 values less than(to_date('2020-04-01','yyyy-mm-dd')),
partition p16 values less than(to_date('2020-05-01','yyyy-mm-dd')),
partition p17 values less than(to_date('2020-06-01','yyyy-mm-dd')),
partition p18 values less than(to_date('2020-07-01','yyyy-mm-dd')),
partition p19 values less than(to_date('2020-08-01','yyyy-mm-dd')),
partition p20 values less than(to_date('2020-09-01','yyyy-mm-dd')),
partition p31 values less than(to_date('2020-10-01','yyyy-mm-dd')),
partition p32 values less than(to_date('2020-11-01','yyyy-mm-dd')),
partition p33 values less than(to_date('2020-12-01','yyyy-mm-dd')),
partition p34 values less than(to_date('2021-01-01','yyyy-mm-dd')),
partition p35 values less than(to_date('2021-02-01','yyyy-mm-dd')),
partition p36 values less than(to_date('2021-03-01','yyyy-mm-dd')),
partition p37 values less than(to_date('2021-04-01','yyyy-mm-dd')),
partition p38 values less than(to_date('2021-05-01','yyyy-mm-dd')),
partition p39 values less than(to_date('2021-06-01','yyyy-mm-dd')),
partition p40 values less than(to_date('2021-07-01','yyyy-mm-dd'))
); insert into a2 (id,deal_date,area_code,contents)
select rownum,
to_date(to_char(sysdate-900,'J')+ trunc(dbms_random.value(0,200)),'J'),
ceil(dbms_random.value(590,599)),
rpad('*',400,'*')
from dual
connect by rownum <= 200000;
commit; alter table a1 add constraint pk_id primary key (id);
alter table a2 add constraint pk_id_time primary key(id,deal_date); SQL> create index cc_id on a1(id);
create index cc_id on a1(id)
*
ERROR at line 1:
ORA-01408: such column list already indexed SQL> select index_name,status from user_indexes where table_name in('A1','A2');
INDEX_NAME STATUS
------------------------------ --------
PK_ID_TIME VALID
PK_ID VALID Alter table a1 drop constraint pk_id;
Alter table a2 drop constraint pk_id_time;
create index cc_id on a1(id) LOCAL;
alter table a1 add constraint pk_id primary key (id) USING INDEX cc_id ;
ORA-14196: Specified index cannot be used to enforce the constraint.
DROP INDEX CC_ID;
create index cc_id on a1(id) ;
alter table a1 add constraint pk_id primary key (id) USING INDEX cc_id ;
create index cc_id_DATE on a2(id,DEAL_DATE) LOCAL;
alter table a2 add constraint pk_id_DATE primary key (id,DEAL_DATE) USING INDEX cc_id_DATE ; https://www.cnblogs.com/lvcha001/p/10218318.html
索引可以认为分3种,非分区索引,全局XX索引,可以是全局范围分区、全局哈希分区,这种情况会根据规则将数据打散,而不是根据实际表的数据进行打散!
本地索引,完全根据分区表的子分区,一个子分区一个索引!但是我们观察重建索引其实是一样的操作,本次测试为了后续重建索引,创建不同的分区类型,非分区索引使用主键! create index ind_hash on a1(id,0) global partition by hash (id) partitions 8 online; SQL> select index_name,status from user_indexes where table_name in('A1','A2');
INDEX_NAME STATUS
------------------------------ --------
CC_ID VALID
CC_ID_DATE N/A
IND_HASH N/A
select index_name,PARTITION_NAME,HIGH_VALUE,STATUS,TABLESPACE_NAME from dba_ind_partitions
where index_owner='YZ' and index_name IN('CC_ID_DATE','IND_HASH');

2.2 导入SQL文件测试

nohup time expdp \'/ as sysdba\' directory=dump dumpfile=yang%u.dmp logfile=yang.log tables=yz.a1,yz.a2 FLASHBACK_SCN=1017463 parallel=2 &
情况一、导出并行度2,导入并行度2,观察SQL脚本
nohup time impdp \'/ as sysdba\' directory=dump dumpfile=yang%u.dmp logfile=yang.log tables=yz.a1,yz.a2 parallel=2 sqlfile=table01.sql &
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Job "SYS"."SYS_SQL_FILE_TABLE_01" successfully completed at Wed Aug 11 07:00:04 2021 elapsed 0 00:00:03 -- new object type path: TABLE_EXPORT/TABLE/TABLE
-- CONNECT SYS
CREATE TABLE "YZ"."A2"
( "ID" NUMBER,
······
-- new object type path: TABLE_EXPORT/TABLE/INDEX/INDEX
-- CONNECT YZ
CREATE INDEX "YZ"."CC_ID_DATE" ON "YZ"."A2" ("ID", "DEAL_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL
(PARTITION "P1"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P2"
······
PARTITION "P40"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ) PARALLEL 1 ;
ALTER INDEX "YZ"."CC_ID_DATE" NOPARALLEL;
CREATE INDEX "YZ"."CC_ID" ON "YZ"."A1" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" PARALLEL 1 ;
ALTER INDEX "YZ"."CC_ID" NOPARALLEL;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
CREATE INDEX "YZ"."IND_HASH" ON "YZ"."A1" ("ID", 0)
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" GLOBAL PARTITION BY HASH ("ID")
(PARTITION "SYS_P41"
TABLESPACE "USERS" ,
PARTITION "SYS_P42"
TABLESPACE "USERS" ,
PARTITION "SYS_P43"
TABLESPACE "USERS" ,
PARTITION "SYS_P44"
TABLESPACE "USERS" ,
PARTITION "SYS_P45"
TABLESPACE "USERS" ,
PARTITION "SYS_P46"
TABLESPACE "USERS" ,
PARTITION "SYS_P47"
TABLESPACE "USERS" ,
PARTITION "SYS_P48"
TABLESPACE "USERS" ) PARALLEL 1 ;
ALTER INDEX "YZ"."IND_HASH" NOPARALLEL;
-- new object type path: TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
-- CONNECT SYS
ALTER TABLE "YZ"."A1" ADD CONSTRAINT "PK_ID" PRIMARY KEY ("ID")
USING INDEX "YZ"."CC_ID" ENABLE;
ALTER TABLE "YZ"."A1" ADD SUPPLEMENTAL LOG GROUP "GGS_87350" ("ID") ALWAYS;
ALTER TABLE "YZ"."A1" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER TABLE "YZ"."A1" ADD SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS;
ALTER TABLE "YZ"."A1" ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
ALTER TABLE "YZ"."A2" ADD CONSTRAINT "PK_ID_DATE" PRIMARY KEY ("ID", "DEAL_DATE")
USING INDEX "YZ"."CC_ID_DATE" ENABLE;
ALTER TABLE "YZ"."A2" ADD SUPPLEMENTAL LOG GROUP "GGS_87381" ("ID", "DEAL_DATE") ALWAYS;
ALTER TABLE "YZ"."A2" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER TABLE "YZ"."A2" ADD SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS;
ALTER TABLE "YZ"."A2" ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
······

并行度1!
情况二、导出并行度2,导入并行度4,观察SQL脚本

$ nohup time impdp \'/ as sysdba\' directory=dump dumpfile=yang%u.dmp logfile=yang.log tables=yz.a1,yz.a2 parallel=4 sqlfile=table02.sql &

$ cat dump/table02.sql |grep PARALLEL

TABLESPACE "USERS" ) PARALLEL 1 ;

ALTER INDEX "YZ"."CC_ID_DATE" NOPARALLEL;

TABLESPACE "USERS" PARALLEL 1 ;

ALTER INDEX "YZ"."CC_ID" NOPARALLEL;

TABLESPACE "USERS" ) PARALLEL 1 ;

ALTER INDEX "YZ"."IND_HASH" NOPARALLEL;

通过测试,我们可以得知数据泵导入创建索引并行度就是1!!!  这种情况除非数据库参数设置对象是AUTO选择并行,如果是Manual的情况则无法使用并行加快速度!

2.3 如何对数据泵导入使用并行创建索引加快速度!

参考

https://blog.51cto.com/wyzwl/2333565

?为什么脚本要排除约束?    感兴趣的小伙伴可以测试一下。
目标端创建用户、授权后,导入表数据!

*****************************************************数据导入**************************************

cat >imp_data.par <<EOF
userid='/ as sysdba'
directory=dump
dumpfile=yang%u.dmp
logfile=imp_data.log
cluster=no
parallel=2
exclude= index,constraint
EOF
--排除索引和约束,执行导入
nohup impdp parfile=imp_data.par > imp_data.out &

*****************************************************索引及约束导入**************************************

--通过sqlfile参数生成创建索引语句
cat >imp_ind_con.par <<EOF
userid='/ as sysdba'
directory=dump
dumpfile=yang%u.dmp
sqlfile=imp_ind_con.sql
logfile=imp_ind_con.log
cluster=no
parallel=2
tables=yz.a1,yz.a2
include=index,constraint
EOF

--执行生成创建索引语句(实际并不会导入)
nohup impdp parfile= imp_ind_con.par > imp_ind_con.out &
--修改创建索引的并行度,并行度建议不超过CPU核数的1.5倍
--LINUX环境使用
sed -i 's/PARALLEL 1/PARALLEL 16/g' imp_ind_con.sql
--因AIX环境sed没有-i参数,可以使用如下两种方法:
perl -pi -e 's/ PARALLEL 1/PARALLEL 16/g' imp_ind_con.sql
或者
vi imp_ind_con.sql << EOF
:%s/ PARALLEL 1/PARALLEL 16/g
:wq
EOF

*****************************************************替换效果***************************************

[oracle@t2 dump]$ cat imp_ind_con.sql|grep PARALLEL
TABLESPACE "USERS" ) PARALLEL 1 ;
ALTER INDEX "YZ"."CC_ID_DATE" NOPARALLEL;
TABLESPACE "USERS" PARALLEL 1 ;
ALTER INDEX "YZ"."CC_ID" NOPARALLEL;
TABLESPACE "USERS" ) PARALLEL 1 ;
ALTER INDEX "YZ"."IND_HASH" NOPARALLEL;
[oracle@t2 dump]$ sed -i 's/PARALLEL 1/PARALLEL 16/g' imp_ind_con.sql
[oracle@t2 dump]$ cat imp_ind_con.sql|grep PARALLEL
TABLESPACE "USERS" ) PARALLEL 16 ;
ALTER INDEX "YZ"."CC_ID_DATE" NOPARALLEL;
TABLESPACE "USERS" PARALLEL 16 ;
ALTER INDEX "YZ"."CC_ID" NOPARALLEL;
TABLESPACE "USERS" ) PARALLEL 16 ;
ALTER INDEX "YZ"."IND_HASH" NOPARALLEL;

***************************************************************************************************

$more 观察SQL脚本

-- new object type path: TABLE_EXPORT/TABLE/INDEX/INDEX
-- CONNECT YZ
CREATE INDEX "YZ"."CC_ID_DATE" ON "YZ"."A2" ("ID", "DEAL_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255
······
-- new object type path: TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
-- CONNECT SYS
ALTER TABLE "YZ"."A1" ADD CONSTRAINT "PK_ID" PRIMARY KEY ("ID")
USING INDEX "YZ"."CC_ID" ENABLE;

先创建索引在整约束!

--等导入完数据之后,执行建索引的SQL:
$vi imp_ind_con.sh
sqlplus / as sysdba <<EOF
set timing on
set echo on
set verify on
spool imp_ind_con.log
@imp_ind_con.sql
spool off
exit
EOF
--执行建索引的SQL
nohup sh imp_ind_con.sh> imp_ind_con.out &

疑问一、导出表的dump有创建用户的语句吗? 如何导出创建用户的SQL语句

--从只导出表的dump,导入create user 提示Not found user
$ nohup time impdp \'/ as sysdba\' directory=dump dumpfile=yang%u.dmp logfile=yang.log include=user parallel=1 sqlfile=user01.sql &
ORA-39168: Object path USER was not found.
--从导出整个schema dump再次测试!
$nohup time expdp \'/ as sysdba\' directory=dump dumpfile=yanga%u.dmp logfile=yang.log SCHEMAS=yz parallel=2
$ scp /home/oracle/script/dump/yanga*.dmp t2:/home/oracle/script/dump/.
$ nohup time impdp \'/ as sysdba\' directory=dump dumpfile=yanga%u.dmp logfile=yang.log include=user parallel=1 sqlfile=user02.sql &
$ cat user02.sql
-- CONNECT SYSTEM
CREATE USER "YZ" IDENTIFIED BY VALUES 'S:C9A5297B9802EBB85A3BE800929ECE1BFCCB00146E58E0FBB055A937869F;86EF13A1088170F5'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";

疑问二、数据泵导出用户后,导入这个用户,这个用户之前在DB不存在,Oracle会自动创建这个用户吗?

 Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=dump dumpfile=yanga%u.dmp logfile=yang.log SCHEMAS=yz parallel=2 
nohup time impdp \'/ as sysdba\' directory=dump dumpfile=yanga%u.dmp logfile=yanga.log parallel=4 &

SQL> select username,account_status from dba_users where username='YZ';

no rows selected

SQL>
SQL> r
1* select username,account_status from dba_users where username='YZ'

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
YZ OPEN

是可以自动创建用户的!

Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "YZ"."A2":"P3" 12.53 MB 30997 rows

. . imported "YZ"."A2":"P40" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Wed Aug 11 12:47:16 2021 elapsed 0 00:00:17

上一篇:handlebars.js 用
替换掉 内容的换行符


下一篇:HTML DOM节点