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;

  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

sqlfile=imp_ind_con.sql

logfile=imp_ind_con.log

tables=yz.a1,yz.a2

include=index,constraint

--執行生成建立索引語句（實際并不會導入）

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

*****************************************************替換效果***************************************

[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;

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 ;

TABLESPACE "USERS" PARALLEL 16 ;

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

$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

--執行建索引的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