oracle之索引

背景:

两个数据库之间使用dblink进行数据同步,原表的索引没有在目标表创建

需求

需要将原表的索引在目标表进行重建

declare

v_index_name varchar2(30 char); --索引名称

v_count number(10); --索引数量

v_sql_drop_str varchar2(300 char); --删除索引动态sql

v_sql_create_str varchar2(300 char);--创建索引动态sql

cursor cur_index is 

select TBNAME,INXNAMEcount(1) as CT,MAX(DECODE(rn,1,column_name,null))||

MAX(DECODE(rn,2,column_name,null))||

MAX(DECODE(rn,3,column_name,null))||

MAX(DECODE(rn,4,column_name,null))||

MAX(DECODE(rn,5,column_name,null))||

MAX(DECODE(rn,6,column_name,null))||

MAX(DECODE(rn,7,column_name,null))||

MAX(DECODE(rn,8,column_name,null)) STR

from (select b.table_name as TBNAME,b.index_name as INXNAME,

row_number() over(partition by b.table_name,b.index_name order by b.column_position) as rn

from user_indexes a, user_ind_columns b

where a.table_name = b.table_name and a.index_name = b.index_name

and a.table_name = 'tb1'

and not exists (select 1 from all_constraint c where a.index_name=c.constraint_name and c.table_name='tb1')

order by b.index_name,b.column_position) T

group by tbname,idxname

begin

for v_index in cur_index loop

select count(1) into v_count from usre_indexes where index_name=upper(v_index.idxname);

if v_count>0 then

v_sql_drop_str := 'drop index' || v_index.idxname;

execute immediate v_sql_drop_str;

end if

end loop;

end;