Clever Geek Handbook

セクショニング。 セクションを自動的に追加する

バージョン11gでは、範囲が範囲外になると自動的にパーティションを作成するためのいくつかのエキサイティングな新しいパーティションスキーム（間隔パーティションの便利な機能など）が導入されました。

11gより前のバージョンでは、定期的に手動でセクションを追加するか、デフォルトのセクションを分割する必要があります。 つまり、このようなテーブルのステータスを常に監視する必要があります。 この記事では、このようなパーティション分割タスクを自動化するためのソリューションを共有します。

まず、11gの例を示します。

  1. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );



  2. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );



  3. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );



  4. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );



  5. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );



  6. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );



  7. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );



  8. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );



  9. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );



  10. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );



  11. create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );





このスクリプトは、res_id列の値が1〜100の範囲にあるレコードのp1セクションを作成します。 res_id列の値が101未満のレコードが挿入されると、それらはp1セクションに配置され、新しいレコードのこの列の値が101以上の場合、Oracle Database 11gは新しいセクションを作成し、その名前はシステムによって生成されます。 Oracle Magazineのロシア語版のArup Nandaによる記事の翻訳で、この例と他の新しいパーティション化スキームの詳細を確認できます 。

以下で説明するソリューションは、パーティションの自動追加をサポートしない他のDBMSに適用できます。



隙間なく均一に増加するパーティションキーのソリューション



テストテーブルを作成します。

  1. テーブル test_partの作成 （
  2. ID番号が nullではありません
  3. name varchar2（100） not null 、
  4. 所有者varchar2（100） null以外 、
  5. タイプvarchar2（100） not null 、
  6. 作成日 が nullではない 、
  7. 制約 test_part_pk
  8. 主 キー （id）
  9. ）
  10. 範囲（id） によるパーティション（パーティションp1 値 は （10000） 未満 ）;


そのようなテーブルにギャップがない場合、パーティションキーが最大セクションの境界に近づく前に新しいセクションを作成することが望ましいと考えられます。 境界に残したキー値の数、簡単な式に従って簡単に決定できます： partition_size-（key-start_key_in_partition） 、ここでkeyは現在のパーティションキー、start_key_in_partitionはこのセクションに含まれる最初のキー、partition_sizeはセクション内のキーの数、 and％は整数除算（div）の演算です。 通常、このようなパーティション化は等しいセクションに実行され、これを念頭に置いて、この式を次のように単純化できます：partition_size-key％partition_size。

これにより得られるもの：瞬間を知って、このイベントが発生したときにセクションを追加するトリガーを作成できます。

このトリガーを作成します。

  1. トリガー tr_test_partを作成 または置換します
  2. test_part に 挿入 する 前
  3. 行 ごとに
  4. when （mod（ NEW .id、10000）= 6000）
  5. 宣言する
  6. l_part_name番号。
  7. l_maxvalue番号。
  8. l_exist番号;
  9. l_partition_exists 例外 ;
  10. PRAGMA EXCEPTION_INIT（l_partition_exists、-14074）;
  11. PRAGMA AUTONOMOUS_TRANSACTION;
  12. 始める
  13. l_part_name：= ceil（： NEW .ID / 10000）+1;
  14. 開始
  15. すぐに 「alter table xtender.test_part add partition p」を 実行し ます || l_part_name || '（より小さい値（' || l_maxvalue || '）' ;
  16. 例外
  17. l_partition_existsがnullの 場合 。
  18. 終了
  19. end tr_test_part;




このトリガーは、自律型トランザクションを使用して、「P」という名前とセクション番号10,000の新しいセクションを自動的に作成します。IDがセクションキーである場合、セクションの境界まで4,000個の値が残ります（10000-4000 = 6000、つまりID = 6000、 16000、26000など）、ただし、最初に特定のセクションが既に存在するかどうかが確認されます（これは、たとえば、6000番目のレコードを再追加するか、セクションを手動で追加することによって発生します）。 パーティショニングパラメータ-10000および4000。特定の状況に基づいて選択する必要がありますが、境界線（この例では4000）は一度に追加されるレコードの最大数よりも大きくする必要があることに注意してください。 そうでない場合、データ挿入トランザクションの時点で、トランザクションは新しいセクションを「認識」しません。 トランザクションの開始時には存在していなかったため、このキーにマッピングされたセクションが存在しないという苦情とともにデータは挿入されません。 これは、後で説明するalter table split default_partitionを使用して回避できますが、これはランタイムに影響します。

セクションに記入してトリガーを確認します。

insert into xtender.test_part

select rownum, o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE, o.CREATED

from all_objects o

where rownum<1000;







さらに、キャッシュのために「ステップ」するシーケンスを使用する場合、セクションの最後から4000〜3900レコードの値のセットに対して実行されるようにトリガーを順番に変更することはできません。

状態を置き換える 

  when（mod（NEW.id、10000）= 6000）
に 

  when（mod（NEW.id、10000）between 6000 and 6100）




その他の解決策



デフォルトセクションを指定する場合、レコードがすでにそこにあるときにそれを分離できます。質問は、これを自動的に追跡する方法です。



データディクショナリでは、 dba_tab_partitionsから選択を行うことにより、パーティションテーブルのすべてのセクションに関する情報を取得できます。partition_positionはテーブル内のセクションの順序を示し 、 high_valueはセクションパラメーターを示します。 したがって、テーブルの最後のセクションの名前を取得し、そこから選択して、その中のレコード数を取得できます。



デフォルトセクションで記録が開始されたテーブルに関する情報を受信した後、通知を送信する必要があります。 これを行うには、次のオプションを使用できます。





最初のオプションは、2つのパラメーターを持つdbms_system.ksdwrtプロシージャを使用して実装されます。

例： 

 exec dbms_system.ksdwrt（2、 'テストアラートメッセージ'）;




2番目のオプションは、 utl_mailパッケージまたはそれ以下のパッケージ-utl_smtpまたはutl_tcpを使用することです 。

utl_mailはutl_smtpのより便利なラッパーですが、使用するにはsmtp_out_serverパラメーターを設定する必要があります。 これは、セッション「ALTER SESSION SET smtp_out_server = ...」およびシステム「ALTER SYSTEM SET smtp_out_server = ...」に対してすぐに実行できます。

自宅でこのパッケージを見つけられなくても驚かないでください-最初は含まれていません。作成するには、2つのスクリプトを実行する必要があります。 

 sqlplus sys/<pwd> SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb




すべてをpkg_partitionsパッケージにまとめます

パッケージ方法：





パッケージコード：





  1. パッケージ本体の作成 または置換pkg_partitions は
  2. / **最後から2番目のセクションのパラメーターを返す関数
  3. * @param i_table_nameテーブル名
  4. * @ return varchar2
  5. * /
  6. 関数 get_penultimate_maxvalue（p_table_owner varchar2、p_table_name varchar2） return varchar2 は
  7. l_cursor integer default dbms_sql.open_cursor;
  8. l_ignore number;
  9. l_long_val varchar2（4000）;
  10. l_long_len番号;
  11. l_buflen番号：= 4000;
  12. l_curpos番号：= 0;
  13. 始める
  14. dbms_sql.parse（l_cursor、
  15. ' all.tab_partitions pからp.high_valueを選択します。p.table_ownerのようなもの：oおよびp.table_name like：xおよびp.partition_position =（select max（p1.partition_position）-1 from all_tab_partitions p1 where p.table_owner like：o and p1。 table_name like：x） '
  16. 、
  17. dbms_sql.native）;
  18. dbms_sql.bind_variable（l_cursor、 '：x' 、p_table_name）;
  19. dbms_sql.bind_variable（l_cursor、 '：o' 、p_table_owner）;
  21. dbms_sql.define_column_long（l_cursor、1）;
  22. l_ignore：= dbms_sql。 実行 （l_cursor）;
  23. if （dbms_sql.fetch_rows（l_cursor）> 0）
  24. それから
  25. dbms_sql.column_value_long（l_cursor、1、l_buflen、l_curpos、
  26. l_long_val、l_long_len）;
  27. 終了する 場合 ;
  28. dbms_sql.close_cursor（l_cursor）;
  29. return l_long_val;
  30. 終わり ;
  32. / **テーブルの名前とその最後のセクションが埋め始めた所有者を返す関数
  33. * @ リターン
  34. * table_name varchar2（4000）、
  35. * table_owner varchar2（4000）、
  36. * partitions_count番号、
  37. * partition_name varchar2（4000））;
  38. * /
  39. 関数 get_maxvalued_pa​​rtitions は、 tables_props_arrayパイプライン化を返します
  40. l_cursor integer default dbms_sql.open_cursor;
  41. l_count番号。
  42. l_ignore integer ;
  43. l_data table_props;
  44. カーソル l_partitions は
  45. 選択する
  46. pl.table_owner、
  47. pl.table_name、
  48. count （1）cnt、
  49. max （pl.partition_name）keep（dense_rank last order by （pl.partition_position））partition_name
  50. dba_tab_partitions pl から
  51. pl.table_name は 「BIN $％」と は異なります
  52. pl.table_owner、pl.table_name による グループ化
  53. カウント （1）> 1を持つ。
  54. 開始
  55. l_partitionsの一部
  56. ループ
  57. dbms_sql.parse（l_cursor、
  58. 「select count（1） from」|| part.table_owner || 「。」 || part.table_name
  59. || 'パーティション（' || part.partition_name || '）'
  60. || 'where rownum <2'
  61. 、
  62. dbms_sql.native）;
  63. dbms_sql.define_column（l_cursor、1、l_count）;
  64. l_ignore：= dbms_sql.execute_and_fetch（l_cursor）;
  65. dbms_sql.column_value（l_cursor、1、l_count）;
  66. if （l_count> 0） then
  67. l_data.table_name：= part.table_name;
  68. l_data.table_owner：= part.table_owner;
  69. l_data.partitions_count：= part.cnt;
  70. l_data.partition_name：= part.partition_name;
  71. パイプ行 （l_data）;
  72. 終了する 場合 ;
  73. ループの終了。
  74. 終了
  76. / **テーブルの名前とその所有者をhtmlの形式で返す関数。最後のセクションが埋め始めた
  77. * @ リターン
  78. * table_name varchar2（4000）、
  79. * table_owner varchar2（4000）、
  80. * partitions_count番号、
  81. * partition_name varchar2（4000））;
  82. * /
  83. function get_maxvalued_pa​​rtitions_html return varchar2 は
  84. l_cursor integer default dbms_sql.open_cursor;
  85. l_count番号。
  86. l_ignore integer ;
  87. l_data varchar2（4000）;
  88. カーソル l_partitions は
  89. 選択する
  90. pl.table_owner、
  91. pl.table_name、
  92. count （1）cnt、
  93. max （pl.partition_name）keep（dense_rank last order by （pl.partition_position））partition_name
  94. dba_tab_partitions pl から
  95. pl.table_name は 「BIN $％」と は異なります
  96. pl.table_owner、pl.table_name による グループ化
  97. カウント （1）> 1を持つ。
  98. 開始
  99. l_data：= '<html> <body> <table border = 1>'
  100. || '<tr> <th>テーブル名</ th>'
  101. || '<th>テーブルの所有者</ th>'
  102. || '<th>パーティション数</ th>'
  103. || '<th>パーティション名</ th>'
  104. || '<th>事前最大値</ th>' ;
  106. l_partitionsの一部
  107. ループ
  108. dbms_sql.parse（l_cursor、
  109. 「select count（1） from」|| part.table_owner || 「。」 || part.table_name
  110. || 'パーティション（' || part.partition_name || '）'
  111. || 'where rownum <2'
  112. 、
  113. dbms_sql.native）;
  114. dbms_sql.define_column（l_cursor、1、l_count）;
  115. l_ignore：= dbms_sql.execute_and_fetch（l_cursor）;
  116. dbms_sql.column_value（l_cursor、1、l_count）;
  117. if （l_count> 0） then
  118. l_data：= l_data || '<tr> <td>'
  119. || part.table_name
  120. || '</ td> <td>'
  121. || part.table_owner
  122. || '</ td> <td>'
  123. || part.cnt
  124. || '</ td> <td>'
  125. || part.partition_name
  126. || '</ td> </ tr>' ;
  127. 終了する 場合 ;
  128. ループ終了;
  129. l_data：= l_data || '</ table> </ body> </ html>' ;
  130. return l_data;
  131. 終了
  132. / **
  133. *テーブルの最後のセクションが埋め始めたレポートを送信する手順
  134. * /
  135. プロシージャ send_partitions_report（メールvarchar2）
  136. は
  137. msg_body varchar2（4000）;
  138. 開始
  139. pkg_partitions.get_maxvalued_pa​​rtitions_html をデュアルから msg_bodyに選択します 。
  140. -EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server =' 'our_mailserver' '' ;
  141. utl_mail.send（
  142. 送信者=> 'oracleDBA@dbdomain.com' 、
  143. 受信者=>メール、
  144. 件名=> '最大値パーティションレポート' 、
  145. メッセージ=> msg_body、
  146. mime_type => 'text / html' ）;
  147. 終了
  148. pkg_partitionsを終了します。
*このソースコードは、 ソースコードハイライターで強調表示されました。


UPD

zhekapppが正しくプロンプトを表示したため、自動統計収集を有効にするときにnum_rowsを使用できます。 統計収集を有効にするには、dbms_jobsとdbms_stats.gather_table_statsを使用してジョブを追加します。

次に、セクション内のレコード数のリクエストを削除し、リクエストを次のように変更する必要があります。

  1. 選択する
  2. pl.table_owner、
  3. pl.table_name、
  4. count （1）cnt、
  5. max （pl.num_rows）keep（dense_rank last order by （pl.partition_position））partition_rows、
  6. max （pl.partition_name）keep（dense_rank last order by （pl.partition_position））partition_name
  7. dba_tab_partitions pl から
  8. pl.table_name は 「BIN $％」と は異なります
  9. pl.table_owner、pl.table_name による グループ化


この場合の完全なパッケージコードは、 http ： //www.xt-r.com/2010/10/pkgpartitions.htmlにあります。



自動実行



自動実行を設定するためにのみ残ります。 dbms_jobでそれをやってみましょう。

たとえば、データ取得スクリプトの毎日の自動実行：

  1. 宣言する
  2. job binary_integer;
  3. 始める
  4. dbms_job.submit（
  5. 仕事、
  6. 'pkg_partitions.send_partitions_report（' 'dba@domain.ru' '）;' 、
  7. sysdate
  8. 'trunc（sysdate）+1' ）;
  9. dbms_output.put_line（ジョブ）;
  10. 終わり ;


More articles:


All Articles