Oracleで大量データ扱うとき気を付けたこと

　大量データを扱うときによく話題になることのまとめ

パーティション化

最初から大量のデータが蓄積されると予想がついている場合は、パーティション・テーブルにしておくと便利。
データが増えて消さないといけない場合でも、パーティション化していればパーティション単位でDROP、TRUNCATEできるため管理が容易になる。
パーティション化してない場合、削除するときにDELETEだとUNDO領域やアーカイブ領域など枯渇しないように考えないといけなくなる。

CREATE文で日付ごとのレンジ・パーティション作成

CREATE TABLE ＜テーブル名＞ (
    ＜列名１＞        ＜データ型＞,
    ＜列名２＞        ＜データ型＞,
    ＜列名３＞        ＜データ型＞,
    ＜列名４＞        ＜データ型＞,    
    ＜列名５＞        ＜データ型＞)
    LOGGING
    PCTFREE    ＜空き領域割合＞
    PARTITION BY RANGE (＜列名１＞) (    
        PARTITION ＜パーティション名１＞ VALUES LESS THAN ( TO_DATE('＜年月日１＞','YYYYMMDD')) TABLESPACE ＜表領域名１＞,
        PARTITION ＜パーティション名２＞ VALUES LESS THAN ( TO_DATE('＜年月日２＞','YYYYMMDD')) TABLESPACE ＜表領域名２＞,
        PARTITION ＜パーティション名３＞ VALUES LESS THAN ( TO_DATE('＜年月日３＞','YYYYMMDD')) TABLESPACE ＜表領域名３＞,
        PARTITION ＜パーティション名４＞ VALUES LESS THAN ( TO_DATE('＜年月日４＞','YYYYMMDD')) TABLESPACE ＜表領域名４＞,
        PARTITION ＜パーティション名５＞ VALUES LESS THAN ( TO_DATE('＜年月日５＞','YYYYMMDD')) TABLESPACE ＜表領域名５＞)
    ENABLE ROW MOVEMENT;

統計情報の再収集

Oracleは表・索引の統計情報をもとに実行計画を立てるので、試験時など大量のデータを一度に投入したときなど実態と統計情報がかけ離れてしまい、実態に合わない実行計画でSQLが発行されてしまうことがある。この場合レスポンスが悪くなってしまう。
データが増えたときは統計情報を再取集して最新化しておくとこれを回避できる。統計情報はANALYZEコマンドで収集できる。

ANALYZEコマンドでテーブル全体から統計情報収集

analyze table テーブル名 compute statistics;

表領域の使用率を下げる

表領域不足で使用率を減らしたいときはDELETEしても減らない。
ハイウォーターマーク（最高水位標）の概念があり過去最大の領域を記録している。
DELETEしてデータを消しても一度確保された表領域はハイウォーターマークのところまで未使用領域として残存してしまう。
未使用領域を減らして使用率を下げるには以下の方法がある。

・TRUNCATEしバックアップからデータを復元

TRUNCATEで表を一旦まっさらな状態にして、予めEXPORTなどでバックアップしておいたデータを復元することで、無駄な未使用領域がなくなり使用率が下がる。

expコマンドで表データのバックアップ

exp ＜ユーザ名＞/＜パスワード＞ tables=＜テーブル名＞ file=＜出力ファイル名＞

TRUNCATEでデータ削除

TRUNCATE TABLE ＜テーブル名＞;

impコマンドで表データのリストア

imp ＜ユーザ名＞/＜パスワード＞ tables=＜テーブル名＞ file=＜出力ファイル名＞

・ALTER SHRINK TABLE SPACEで断片化を解消

未使用領域を解放し縮小できる。※Oracle10g以降で可能
CASCADEオプションで索引などの表に依存するオブジェクトの断片化も解消される。

ALTER文で断片化解消

ALTER TABLE ＜テーブル名＞ ENABLE ROW MOVEMENT; 
ALTER TABLE ＜テーブル名＞ SHRINK SPACE CASCADE;
ALTER TABLE ＜テーブル名＞ DISABLE ROW MOVEMENT;

　

　
表領域の使用状況はビューで確認できる

DBA_TABLESPACE_USAGE_METRICSビューで確認

SELECT TABLESPACE_NAME,
       TABLESPACE_SIZE/1024/1024/1024 “TOTAL(GB)”,
       USED_SPACE/1024/1024/1024 “USED(GB)”,
       USED_PERCENT “USED(%)”
FROM DBA_TABLESPACE_USAGE_METRICS;