当前位置：首页 > news >正文

大表建/重建索引

news 2024/11/26 10:02:00

标准参考：

1.建议后台方式建索引，如果已经停业务，则不用online参数会更快，如果有业务，则使用online，但中途不要取消，取消的后果见如下链接：

程序媛记一次在线创建索引被kill案例及应对措施

踩坑!记一次终止在线创建索引引发问题!!!

心态崩了，索引失效无法重建，后面领导站了一排！！！

2.建索引过程监控着各表空间占用，快不足的情况下提前增加空间，可根据表大小和索引占用情况提前预估

vi rebuid_idx.sqlalter 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;
alter index xxx.xxx rebuild nologging online parallel 48 compute statistics;--online方式，不阻塞dml操作，不能中途取消操作
--alter index xxx.xxx rebuild nologging parallel 48 compute statistics;--非online方式，适合停业务情况
alter index xxx.xxx noparallel;
alter index xxx.xxx logging;
--重建的脚本都写上vi rebuid_idx.sh#!/bin/sh
export ORACLE_SID=XXX
sqlplus / as sysdba <<EOF
set timing on
@rebuid_idx.sql;
exit;
EOFchmod +x rebuid_idx.shnohup ./rebuid_idx.sh > ./rebuid_idx.log &tail -f rebuid_idx.log

查看表大小，索引大小准备临时表空间大小，然后就可以继续下面操作：放在root后台跑：#crontab -l25 19 * * * /rdata/oracle/r.sh$ cat r.sh
#!/bin/sh
su - oracle -c "sqlplus /nolog <<EOF
set time on
set timing on
conn /as sysdba
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;
alter index BIMSRAD.BILLMESSAGE_PK rebuild online parallel 6 compute statistics;
alter index BIMSRAD.BILLMESSAGE_PK noparallel;
exit;
EOF" >> /rdata/oracle/r.outSQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='BILLMESSAGE_PK';SUM(BYTES)/1024/1024
--------------------410SQL> select num_rows from dba_indexes where table_name='BILLMESSAGE_TABLE';NUM_ROWS
----------20009644

#!/bin/sh
su - oracle -c "sqlplus /nolog <<EOF
set time on
set timing on
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;
create index DAILYSESSION_INDIPV6 on DAILYSESSION_TABLE (days,framed_ipv6_address,DELEGATED_IPV6_START, DELEGATED_IPV6_END) local online parallel 8 nologging;
commit;
alter index DAILYSESSION_INDIPV6 noparallel;
alter index DAILYSESSION_INDIPV6 logging;
commit;
exit;
EOF" >> /740ora/oracle/jyc/idx.out

#8 15 * * * /jyc/addindex.sh[root@db]/jyc#more /jyc/addindex.sh
#!/bin/sh
su - oracle -c "sqlplus /nolog <<EOF
set time on
set timing on
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; 
create index IDX_NOWCANCEL on T_FIX_COMMUNICATION(NOWCANCEL) local online parallel 6 compute statistics;
alter index IDX_NOWCANCEL noparallel;
exit;
EOF" >> /jyc/IDX_NOWCANCEL.out
[root@db]/jyc#more /jyc/IDX_NOWCANCEL.outnohup ./addindex.sh > ./xxx.log &

4.相关参考

通过并行nologging等快速创建大表备份-CSDN博客

查看全文

http://www.mrgr.cn/news/78057.html