« 定期的なインデックス再作成を自動化 | トップページ | Informixでの日付データ文字列変換 »

2014.04.06

定期的なインデックス再作成を自動化(解題)

昨日エントリーしたのはずばりそのまま利用できるbashスクリプトであったが、このスクリプトを書くにあたっていくつか身についたこともあるので、利用されているTIPSとかメモとかを書き連ねてみたい。

◆インデックスの種別判定(PostgreSQL)

SELECT
CASE WHEN EXISTS
(SELECT * FROM information_schema.table_constraints i
WHERE pg_indexes.indexname = i.constraint_name
AND i.constraint_type='PRIMARY KEY') THEN 'PK'
ELSE 'INDEX'
END
, indexdef
FROM pg_class
INNER JOIN pg_indexes ON pg_class.relname = pg_indexes.indexname
WHERE relkind = 'i'
AND relnamespace = ${USROID}
インデックス情報はpg_indexesテーブルに存在している。
あれ、そうなるとpg_class.relkind='i' というのは冗長な検索条件かもしれない(^^;
pg_classはテーブル/ビュー/インデックスが混在しているので、インデックスだけを取得しようとしたのだが、pg_indexesとジョインすることで実際には絞り込まれるはず。

で、ミソはinformation_schemaカタログにあるtable_constraintsからPRIYMARY KEYとしてそのインデックスが存在しているかを確認して、CASE式内で値を返しているところである。
-pg_class.relkindの存在
-information_schema.table_constraints.constraint_type='PRIMARY KEY'
-CASE式内でEXISTSでデータ分岐
-indexdefカラムにインデックスのCREATE文が格納されている
あたりが技術的チェックポイント。

◆COPYでのデリミタ' '(スペース)指定(PostgreSQL)

COPY [query] TO '${TARGET_INDEX_FILE}' DELIMITER ' ';
COPYコマンドでクエリー結果をファイルに出力できる。
またこの際にDELIMITERオプションを使うことでCSVなどにすることも可能である。
今回は' '(スペース)にした。これはクエリー内で指定しているindexdefカラム内の情報がスペースで区切られていることを逆利用し、後続のシェル内の配列処理を行なう際にPRIMARY KEYかどうかという区分も同一次元の配列にいれてしまうのに活用している。

◆psqlにファイルでSQLを渡す(PostgreSQL)

psql -U ${USRID} ${DBNAME} -f ${TARGET_INDEX_LIST}
-fオプションで別途定義してあるSQL文をpsqlアプリケーションに渡して、一括で処理させることができる。

◆空白区切りになっているデータを配列にする(bash)

IFS=" "
set - $LINE
ELEMENTS=${#}
args=("$@")
IFS変数はデリミタを定義する。
set - $LINEで入力された$LINEを$IFSで決められた区切り文字で分割する(split的な挙動)。
set - 変数は引数変数に値を返す。今回のケースならばsetの後にecho ${1}とすれば、"PK"または"INDEX"が返される。
${#}はsetで取得された引数がいくつあるのかを示している。
ただこのままでは扱いづらいのでargs=("$@")によって、分割されて戻ってきたデータをすべてargs配列変数に格納してしまう。
配列の要素数は${#var_name[*]}でも取得できるので
ELEMENTS=${#args[*]}
のほうが素直かもしれない。

◆ロックしないでインデックスを作成する(PostgreSQL)

echo "CREATE ${PRESTATEMENT} INDEX CONCURRENTLY ${IDX_NAME}_new ON ${TBL_NAME}" > ${SQL_TMP_HEADER}
CREATE INDEX CONCURRENTLYオプションである。
また今回はREINDEXの代わりなので、"_new"というサフィックスで一時的にインデックスを作成した後、古いインデックスはドロップし、新しいインデックスを古いインデックスと同じ名前に戻す、ということをこの後実行している。
ただしCONCURRENTLYオプションは1件でもトランザクションが存在していると、そのトランザクション内にindex付与しようとしているテーブルが存在しなくても待機されてしまう。
このためロングトランザクションなどがある場合は、いつまでたっても処理が終わらないという危険性もあるので、このあたりの挙動には注意が必要。

◆新旧インデックス交代(通常のインデックス)(PostgreSQL)

DROP INDEX ${IDX_NAME};
ALTER INDEX ${IDX_NAME}_new RENAME TO ${IDX_NAME};
まず元々あったインデックスをDROPし、ALTER INDEX RENAMEにて名称を差し替えている。

◆新旧インデックス交代(主キー)(PostgreSQL)

ALTER TABLE ${TBL_NAME} DROP CONSTRAINT ${IDX_NAME};
ALTER INDEX ${IDX_NAME}_new RENAME TO ${IDX_NAME};
ALTER TABLE ${TBL_NAME}
ADD PRIMARY KEY USING INDEX ${IDX_NAME};
ただ主キーの場合はDROP INDEXすることはできないため、ALTER TABLE内のDROP CONSTRAINTで主キー制約を削除する。
その後で新インデックスを旧インデックス名に戻しALTER TABLE ADD PRIMARY KEYにてこのインデックスを主キーに昇格させているのである。

◆ファイルの結合処理(Linux)

cat ${SQL_TMP_HEADER} ${SQL_TMP_DETAIL} > ${SQL_FILE}
複数ファイルを結合する場合はcatコマンドを使う。
cat 結合元1 結合元2 ..... > 結合後のファイル
とすることで、catの後に書かれた順番にファイルが結合されていく。
リダイレクトを行なわない場合は結合結果が標準出力に出るだけである。
昔はcatってDOSのtypeコマンドと同じ、と勘違いしていたけど、本来はファイルを順編成で読んで標準出力に返す+リダイレクトのあわせ技でsplitしたファイルを復元するために準備されていたらしい(出典不明)。

|

« 定期的なインデックス再作成を自動化 | トップページ | Informixでの日付データ文字列変換 »

コメント

コメントを書く



(ウェブ上には掲載しません)




トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/2022/59410122

この記事へのトラックバック一覧です: 定期的なインデックス再作成を自動化(解題):

« 定期的なインデックス再作成を自動化 | トップページ | Informixでの日付データ文字列変換 »