【Analytic】使用分析函数ROW_NUMBER辅助完成外键的索引批量创建

之前谈到了很多关于分析函数的用法，今天我们来使用ROW_NUMBER分析函数辅助完成外键上索引批量创建。
【Analytic】分析函数之MIN函数：http://space.itpub.net/519536/viewspace-624736
【Analytic】分析函数之MAX函数：http://space.itpub.net/519536/viewspace-624749
【Analytic】分析函数之AVG函数：http://space.itpub.net/519536/viewspace-624799
【Analytic】分析函数之ROW_NUMBER函数：http://space.itpub.net/519536/viewspace-624886
【Analytic】分析函数之RANK函数：http://space.itpub.net/519536/viewspace-624985
【Analytic】分析函数之DENSE_RANK函数：http://space.itpub.net/519536/viewspace-625115
【Analytic】分析函数之COUNT函数：http://space.itpub.net/519536/viewspace-625191
【Analytic】分析函数之FIRST_VALUE函数：http://space.itpub.net/519536/viewspace-625280
【Analytic】分析函数之LAST_VALUE函数：http://space.itpub.net/519536/viewspace-625287

众所周知，外键应该加索引，否则可能会出现死锁和性能问题。
我们编写一个脚本，实现在外键上批量添加索引，进而延长DBA的生命。

在这个脚本中为使索引命名符合自定义的规范化，使用到了ROW_NUMBER分析函数，细心地您慢慢体会吧。

1.脚本内容
ora10g@secDB /home/oracle$ cat Create_ForeignKey_Indexes.sql
set lines 600
set head off
set feedback off

column table_name noprint
column column_name noprint

spool Create_ForeignKey_Indexes.out

select a.table_name,
       b.column_name,
       'create index ' || 'I_' || SUBSTR (a.table_name, 3) || '_0'
       || ROW_NUMBER ()
             over (partition by a.table_name order by b.column_name)
       || ' on '
       || a.table_name
       || '('
       || b.column_name
       || ') tablespace TBS_SEC_I;' as "Create ForeignKey Indexes"
  from USER_CONSTRAINTS A, USER_CONS_COLUMNS B
 where A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
   and A.CONSTRAINT_TYPE = 'R'
   and A.TABLE_NAME in (select tname from tab)
order by 1,2
/

column table_name print
column column_name print

spool off
set feedback on
@Create_ForeignKey_Indexes.out

2.实际感受一下上面脚本的魅力
1）创建父表、子表以及子表上的外键
（1）创建附表t_parent
sec@ora10g> create table t_parent (col_parent int primary key);

Table created.

（2）创建子表t_child
sec@ora10g> create table t_child (col1_child int, col2_child int);

Table created.

（3）在子表上创建两个外键（这里简化的例子）
sec@ora10g> alter table t_child add constraint FK_t_child_01 foreign key (col1_child) references t_parent (col_parent);

Table altered.

sec@ora10g> alter table t_child add constraint FK_t_child_02 foreign key (col2_child) references t_parent (col_parent);

Table altered.

2）脚本内容分步执行结果如下
sec@ora10g> column table_name noprint
sec@ora10g> column column_name noprint
sec@ora10g>
sec@ora10g> select a.table_name,
  2         b.column_name,
  3         'create index ' || 'I_' || SUBSTR (a.table_name, 3) || '_0'
  4         || ROW_NUMBER ()
  5               over (partition by a.table_name order by b.column_name)
  6         || ' on '
  7         || a.table_name
  8         || '('
  9         || b.column_name
 10         || ') tablespace TBS_SEC_I;' as "Create ForeignKey Indexes"
 11    from USER_CONSTRAINTS A, USER_CONS_COLUMNS B
 12   where A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
 13     and A.CONSTRAINT_TYPE = 'R'
 14     and A.TABLE_NAME in (select tname from tab)
 15  order by 1,2
/
 16
Create ForeignKey Indexes
-------------------------------------------------------------------------
create index I_CHILD_01 on T_CHILD(COL1_CHILD) tablespace TBS_SEC_I;
create index I_CHILD_02 on T_CHILD(COL2_CHILD) tablespace TBS_SEC_I;

是不是很神奇？脚本带给我们的就是便捷和惊喜。

3）执行Create_ForeignKey_Indexes.sql脚本自动话完成外键索引的创建
sec@ora10g> @Create_ForeignKey_Indexes.sql

create index I_CHILD_01 on T_CHILD(COL1_CHILD) tablespace TBS_SEC_I;
create index I_CHILD_02 on T_CHILD(COL2_CHILD) tablespace TBS_SEC_I;

Index created.


Index created.

3.小结
在这个实际案例中，我们使用ROW_NUMBER分析函数自动生成了索引尾部的递增序号（欢迎更好的实现方法）。分析函数的魅力可见一斑。
使用可行的技术手段减少任务的完成时间就是珍惜DBA生命的最好体现。抛砖完毕。

Good luck.

secooler
10.01.17

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-625326/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-625326/