随笔- 154  文章- 0  评论- 41 

ORACLE存储过程获取索引信息-转为MySQL索引创建语句

背景:因为在使用DataPipeline做数据同步(oracle到TiDB[语法与MySQL基本一致的数据库])的时候发现oracle数据库的索引是没有被一起同步过来的,在查询数据的时候会很慢,所以需要手动在TiDB中创建索引,这个就很麻烦啦.... 如果一个一个的手工创建,且oracle那边没有办法直接将索引创建语句导出,表多且每张表的索引也很多,这种情况下,我们还是写个存储过程 获取oracle表的索引信息,在存储过程里直接拼凑为TiDB(MySQL)的索引创建语句。

 

关于ORACLE的索引信息:

1.查看当前表的索引信息

select * from user_indexes where table_name='tablename' ; -- 将tablename替换为你的表名就即可 ,也可以不指定表名,直接查询出所有表的索引信息

 

2. 根据索引名称,查询到表的索引列信息

select * from user_ind_columns where index_name=upper('I1PBASEPAPER');  -- I1PBASEPAPER是上面那条语句查询出来的 INDEX_NAME 值

 

3.一条sql语句查询出索引相关信息 ,将tablename替换为需要的表名即可

select
  a.index_name
 , a.table_name
 , a.column_name
 ,b.index_type
 ,b.uniqueness
FROM all_ind_columns a, all_indexes b
WHERE a.index_name=b.index_name
AND a.table_name = upper('tablename')
ORDER BY a.table_name, a.index_name, a.column_position
;

 

4.来咯,上重点啦,存储过程 取出oracle表中的所有索引并且拼凑为MYSQL的索引创建脚本输出

思路很简单,借用上面的查询索引语句,循环取出我们要的索引信息(索引名称,索引类型(唯一/非唯一),索引所属的表名),然后手动拼凑sql即可

需要注意的点:

  唯一索引需要单独判断

  组合索引写法有点不一样,需要单独拼接

先来一段简单的,看看索引信息(第一段for里面的in (T1APL,tablename2)指定表名信息即可,多张表用逗号分割)

declare
begin
       for curidx in (select index_name,TABLE_NAME from user_indexes where table_name in('T1APL','tablename2')) loop
           dbms_output.put('索引:【表:'||curidx.TABLE_NAME||',     索引: '||curidx.index_name||',    索引列:');
           for curidxcol in (select * from user_ind_columns where index_name=upper(curidx.index_name)) loop
               dbms_output.put_line(curidxcol.column_name||'】');
           end loop;
       end loop;   
end;

执行上面的语句,我们会看到类似下面的数据输出,这里组合索引列输出会有点乱,下面的脚本会统一整理,这里只做索引基本信息获取然后迭代出来的演示

 

 

5.来咯,放大招啦哈哈哈哈,实实在在的使用的脚本:(你只需要替换你的tablename即可使用啦)

declare
 idx_cnt int(10) ; -- 用于保存索引列的数量
begin
       for curidx in (select index_name,table_name,uniqueness from user_indexes where table_name in('tablename','T1APL')) loop
           select count(*) into idx_cnt from user_ind_columns where index_name=upper(curidx.index_name);
           if idx_cnt >1  -- 组合索引
           then
                if curidx.uniqueness = 'UNIQUE' then -- 判断是否为 唯一索引如果是就在创建索引的时候添加 unique 关键字
                     dbms_output.put('ALTER  TABLE  or_lifepro.'||curidx.table_name||' add '||curidx.uniqueness||' index '||curidx.index_name||' (');
                else  
                     dbms_output.put('ALTER  TABLE  or_lifepro.'||curidx.table_name||' add  index '||curidx.index_name||'(');
                end if;     
                for curidxcol in (select * from user_ind_columns where index_name=upper(curidx.index_name)) loop
                     idx_cnt := idx_cnt -1; -- 当前循环每执行一次,变量次数减1
                     if idx_cnt=0 -- 当前循环为最后一次循环时 使用括号结束索引创建语句
                     then 
                         dbms_output.put_line(curidxcol.column_name||');');
                     else 
                         dbms_output.put(curidxcol.column_name||',');
                     end if; 
                end loop; 
           else   -- 单列索引
                for curidxcol in (select * from user_ind_columns where index_name=upper(curidx.index_name)) loop
                    if curidx.uniqueness = 'UNIQUE' then -- 判断是否为 唯一索引如果是就在创建索引的时候添加 unique 关键字
                       dbms_output.put_line('ALTER  TABLE  or_lifepro.'||curidx.table_name||' add '||curidx.uniqueness||' index '||curidx.index_name||'('||curidxcol.column_name||');');
                    else  
                       dbms_output.put_line('ALTER  TABLE  or_lifepro.'||curidx.table_name||' add  index '||curidx.index_name||'('||curidxcol.column_name||');');
                    end if;     
                
                end loop; 
           end if;
       end loop;
        
end;

 

在PLSQL里面执行脚本(注意这个脚本中我添加了库名前缀 or_lifepro[这是我TiDB里面的库名],可以看自己喜好选择修改为你的库名或者删除哦)

 

执行结果如下图:我们可以看到这里的组合索引都是正常的输出哦,且唯一索引也都加了UNIQUE关键字 

到这里我们的ORACLE索引转换为MySQL就执行完了,接下来将输出里面的内容复制到MySQL数据库中执行一遍即可啦!!!

 

关于MySQL的索引信息

1.mysql查看表索引语句

show index from tablename; 

 

2.添加索引

ALTER table table_name ADD INDEX index_name (column1,column2,column3); -- 添加组合索引
ALTER table table_name ADD INDEX index_name (columnname); -- 添加普通索引
ALTER table table_name ADD UNIQUE INDEX index_name (columnname); -- 添加唯一索引

 

3.分析表,使得索引生效

analyze table tablename;

 

4.批量删除mysql表索引

先将表索引信息删除语句拼凑好,执行如下sql,更改你的tablename即可

SELECT i.TABLE_NAME, i.COLUMN_NAME, i.INDEX_NAME, 
CONCAT('ALTER TABLE ',i.TABLE_NAME,' DROP INDEX ',i.INDEX_NAME,' ;') 
FROM INFORMATION_SCHEMA.STATISTICS i where i.TABLE_NAME='tablename'
;

 

 posted on 2020-04-23 19:23  阿叮339  阅读(182)  评论(2编辑  收藏