Oracle大表快速创建索引或重建索引的操作参考

Oracle大表快速创建索引或重建索引的操作参考

该方法适用于oracle所有版本。请自行测试验证匹配。
即使这样很快，但也请在业务不忙的时候操作，尽量避免对生产业务的影响。

准备脚本：c.sh

#!/bin/sh
su - oracle -c "sqlplus /nolog <<EOF
set time on
set timing on
export ORACLE_SID=xxx
conn xxx/xxx
--保留不变
alter session set parallel_force_local = true;--rac环境需要
alter session set workarea_size_policy=manual; 
alter session set sort_area_size=1073741820; 
alter session set sort_area_retained_size=1073741820; 
alter session set db_file_multiblock_read_count=128;
--保留不变
create index idx_tab on tabtest (days,framed_ipv6_address,DELEGATED_IPV6_START, DELEGATED_IPV6_END)
local online parallel 8 nologging; --注意分区表和非分区表的区别，此处是分区表的本地索引，所以有local，在线执行加online，为了加快速度，增加并行度一般逻辑CPU个数的1/2即可（cpu空闲的情况下)，nologging为了在创建过程中少产生日志
alter index idx_tab noparallel; --取消并行度
alter index idx_tab logging; --还原日志
exit;
EOF"

root后台执行脚本：（如果不用root，比如用oracle用户操作，那就修改上面的脚本，去掉su - oracle -c)

chmod +x c.sh
nohup ./c.sh > ./c.log &

查看输出日志：

tail -f c.log

脚本灵活修改下即可通用到其它场景，比如快速收集统计信息，快速创建大表ctas等。

统计信息收集：
exec dbms_stats.gather_table_stats(ownname =>'XXX',tabname =>'XXX',degree => '32',cascade=>true);
大表：
create table xxx 
parallel (degree 16)
nologging
as select * from tabletest@jyc_dblink;
alter table tabletest NOPARALLEL;
alter table tabletest LOGGING;

大表ctas实际例子：分区表需增加分区信息定义

#!/bin/sh
su - oracle -c "sqlplus /nolog <<EOF
set time on
set timing on
export ORACLE_SID=xxx
conn xxx/xxx
alter session set workarea_size_policy=manual; 
alter session set sort_area_size=1073741820; 
alter session set sort_area_retained_size=1073741820; 
alter session set db_file_multiblock_read_count=128; 

drop table tabletest purge ;
-- Create table
create table tabletest
partition by range (BUSINESSAREAID)
(
  partition P_SHENYANG values less than ('2102')
    tablespace RES_PART1
    pctfree 10
    initrans 100
    maxtrans 255
    storage
    (
      initial 2M
      minextents 1
      maxextents unlimited
    ),
  partition P_DALIAN values less than ('2103')
    tablespace RES_PART2
    pctfree 10
    initrans 100
    maxtrans 255
    storage
    (
      initial 2M
      minextents 1
      maxextents unlimited
    ),
  partition P_ANSHAN values less than ('2104')
    tablespace RES_PART3
    pctfree 10
    initrans 100
    maxtrans 255
    storage
    (
      initial 2M
      minextents 1
      maxextents unlimited
    ),
  partition P_FUSHUN values less than ('2105')
    tablespace RES_PART3
    pctfree 10
    initrans 100
    maxtrans 255
    storage
    (
      initial 2M
      minextents 1
      maxextents unlimited
    ),
  partition P_BENXI values less than ('2106')
    tablespace RES_PART3
    pctfree 10
    initrans 100
    maxtrans 255
    storage
    (
      initial 2M
      minextents 1
      maxextents unlimited
    ),
  partition P_DANDONG values less than ('2107')
    tablespace RES_PART3
    pctfree 10
    initrans 100
    maxtrans 255
    storage
    (
      initial 2M
      minextents 1
      maxextents unlimited
    ),
  partition P_JINZHOU values less than ('2108')
    tablespace RES_PART3
    pctfree 10
    initrans 100
    maxtrans 255
    storage
    (
      initial 2M
      minextents 1
      maxextents unlimited
    ),
  partition P_YINGKOU values less than ('2109')
    tablespace RES_PART3
    pctfree 10
    initrans 100
    maxtrans 255
    storage
    (
      initial 2M
      minextents 1
      maxextents unlimited
    ),
  partition P_FUXIN values less than ('2110')
    tablespace RES_PART4
    pctfree 10
    initrans 100
    maxtrans 255
    storage
    (
      initial 2M
      minextents 1
      maxextents unlimited
    ),
  partition P_LIAOYANG values less than ('2111')
    tablespace RES_PART4
    pctfree 10
    initrans 100
    maxtrans 255
    storage
    (
      initial 2M
      minextents 1
      maxextents unlimited
    ),
  partition P_PANJIN values less than ('2112')
    tablespace RES_PART4
    pctfree 10
    initrans 100
    maxtrans 255
    storage
    (
      initial 2M
      minextents 1
      maxextents unlimited
    ),
  partition P_TIELING values less than ('2113')
    tablespace RES_PART4
    pctfree 10
    initrans 100
    maxtrans 255
    storage
    (
      initial 2M
      minextents 1
      maxextents unlimited
    ),
  partition P_CHAOYANG values less than ('2114')
    tablespace RES_PART4
    pctfree 10
    initrans 100
    maxtrans 255
    storage
    (
      initial 2M
      minextents 1
      maxextents unlimited
    ),
  partition P_HULUDAO values less than ('2115')
    tablespace RES_PART4
    pctfree 10
    initrans 100
    maxtrans 255
    storage
    (
      initial 2M
      minextents 1
      maxextents unlimited
    ),
  partition P_OTHERS values less than (MAXVALUE)
    tablespace USERS
    pctfree 10
    initrans 100
    maxtrans 255
    storage
    (
      initial 2M
      minextents 1
      maxextents unlimited
    )
)
parallel (degree 16) 
nologging
as select * from tabletest@gistar_sczy1;

alter table tabletest NOPARALLEL;
alter table tabletest LOGGING;

create index CABLESEGMENTNO3 on tabletest (SPECLINENO)
  tablespace INDX
 parallel 16;
  
alter index jyc.CABLESEGMENTNO3 noparallel;

create index CABLE_CONNECTORHISTORYAREACODE on tabletest (ACCESSNBR, AREACODE)
  tablespace INDX
  pctfree 20
  initrans 20
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  ) parallel 16;
  
alter index jyc.CABLE_CONNECTORHISTORYAREACODE noparallel;
  
create index CAB_DATE on tabletest (DISABLED_DATE)
  tablespace INDX
  pctfree 20
  initrans 20
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  ) parallel 16;
  
alter index jyc.CAB_DATE noparallel;
  
create index IDX_CCRH_LOGICALACCESSNBR on tabletest (LOGICALACCESSNBR, AREACODE)
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  ) parallel 16;
 
alter index jyc.IDX_CCRH_LOGICALACCESSNBR noparallel;
 
create index IDX_CCRH_PHYSICALACCESSNBR on tabletest (PHYSICALACCESSNBR, AREACODE)
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  ) parallel 16;
alter index jyc.IDX_CCRH_PHYSICALACCESSNBR noparallel;
exit;
EOF"

主键创建命令参考：
SQL> -- Drop primary, unique and foreign key constraints 
SQL> alter table T drop constraint PK_T cascade;

Table altered.

SQL> alter table t add constraint pk_t primary key(id) parallel 4 nologging;

Table altered.

SQL> alter table t logging;

Table altered.

SQL> alter table T drop constraint PK_T;

Table altered.

SQL> alter table t add constraint pk_t primary key(id) parallel 4;

Table altered.
建完最好收集统计信息：
exec dbms_stats.gather_table_stats(ownname =>'JYC',tabname =>'T',ESTIMATE_PERCENT=>100,degree => 16,cascade=>true);