【Analytic】使用分析函式ROW_NUMBER輔助完成外來鍵的索引批量建立

secooler發表於2010-01-17
函式索引
之前談到了很多關於分析函式的用法，今天我們來使用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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章