社内SEの徒然なる日記

Oracleのリストパーティションを追加(分割)してみた Part2 やってみた

■ 追加でなくて分割です

リストパーティションへの追加は、ALTER TABLE 〜 ADD PARTITION で行います。
--パーティション追加
ALTER TABLE [ユーザー名].[テーブル名]
ADD PARTITION [追加するパーティション名]
VALUES ('2013') SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 515899392 NEXT 5242880 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE [テーブルスペース];


だが、こいつでORA-14323エラー発生!

エラーコードを確認します。
状況 DEFAULT パーティションが存在する場合、ADD PARTITION できません  
原因 DEFAULT パーティションが存在する場合、ADD PARTITION 操作は実行できません。
処置 かわりにDEFAULT パーティションのSPLIT 操作を実行してください。


メッセージから考えると、「デフォルトがあるってことは、追加したいキー値のデータが既にデフォルトパーティションにある可能性がありますよね。だから追加じゃなくて分割ですよ」ってことを言っているようです。

そういうことで、ALTER TABLE 〜 SPLIT PARTITION に書き換えます。
--パーティション分割
ALTER TABLE [ユーザー名].[テーブル名]
SPLIT PARTITION [デフォルトパーティション名]
VALUES('2013')
INTO
(PARTITION [追加するパーティション名] TABLESPACE [テーブルスペース],
PARTITION [デフォルトパーティション名] TABLESPACE [テーブルスペース]);


SPLIT PARTITIONの場合、ディスク容量とかの詳細を省略するとデフォルトパーティションと同じ値になるようです。

デフォルトパーティションのサイズが大きい場合、同じサイズの領域を作りに行こうとするのでディスク容量不足で処理が失敗するかもしれないのですが、今回の対象テーブルにはデフォルトパーティションのサイズが小さかったので、このままで行きます(もちろん、詳細なサイズ指定も可能なようです)。

■ インデックスが死んだ

今度こそって実行したところ、無事に成功(一瞬で終わった)したのですが、念のために業務テストを行うと異様に遅い。っていうか使い物にならない速度になっています。

いろいろ調べると、全ての処理が遅いわけではなく、パーティション分割したテーブルにアクセスするSQLだけが遅くなっているようです。

ここで実行計画を確認して見ると、インデックスが使用されずにフルテーブルスキャンになっています。

...うーん。

統計情報かインデックスが怪しいようなので、「dba_ind_partitions」ビューを覗いてみます。
select index_name, partition_name, status 
from dba_ind_partitions
order by partition_name, index_name


すると、分割したテーブルに設定しているインデックスのstatusが全て「UNUSABLE」(使用不可)になっています。

後で確認したところ、パーティションのメンテナンスを行うとインデックスが無効になる場合が多いようです。

インデックスを使用可能にするために、再構築を行います。
alter index [索引名] rebuild partition [パーティション名];


パーティション数 × インデックス の数だけ索引があるので、全て再構築します。リビルドが終わった後で業務確認をしたところ、問題なく動くようになりました。

■ 正しいスクリプト

しかし、どうせ索引が使用不可になるのならパーティションのメンテナンス時に自動的にインデックスを再作成してくれても良い気がします。

というか、面倒だし手順が煩雑になるだけです。

流石にOracleもまずいと思ったのか、Oracle10gからはALTER TABLE文にインデックスを更新するための「UPDATE INDEXES句」ってオプションが追加されたようです。
--パーティション分割
ALTER TABLE [ユーザー名].[テーブル名]
SPLIT PARTITION [デフォルトパーティション名]
VALUES('2013')
INTO
(PARTITION [追加するパーティション名] TABLESPACE [テーブルスペース],
PARTITION [デフォルトパーティション名] TABLESPACE [テーブルスペース])
UPDATE INDEXES;


変更箇所は最後の1行だけです。
こうすると、インデックスが自動的に更新されるようになりました。

■ 後書き

この記事、さも私が対応したように書いていますが嘘です。

一連の作業で私が行ったのは、業務テストと実行計画の確認くらいで、後は他の人が対応してました。

その時に対応した一連の情報をまとめたのが、上記の記事ってことです。

前回:Oracleのリストパーティションを追加(分割)してみた Part1 環境調査

最新の記事:http://harikofu.blog.fc2.com/
投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end — スポンサードリンク

PageTop

コメント


管理者にだけ表示を許可する