fc2ブログ
 
Technology へようこそ
ここは技術者の「経験」と「ノウハウ」のブログです

2010年03月25日

バーチャル・カラム・パーティション

Oracle Database 11g では、バーチャル・カラムを
パーティション・キーとしてパーティション・テーブルを作成できます。

例)
パーティション・キーを日付(yyyymmddhh24mmss形式の文字列)カラムの
col_dateから月の部分を引用して'01'～'12'で振り分けた場合
--- テーブル作成
create table sample_table (
  col_date  char(14) not null,
  col_1     number(3) not null,
  col_2     varchar2(10) null,
  partitionkey as (substr(col_date, 5, 2)) virtual
)
partition by list (partitionkey) (
  partition p_1 values ('01'),
  partition p_2 values ('02'),
  partition p_3 values ('03'),
  partition p_4 values ('04'),
  partition p_5 values ('05'),
  partition p_6 values ('06'),
  partition p_7 values ('07'),
  partition p_8 values ('08'),
  partition p_9 values ('09'),
  partition p_10 values ('10'),
  partition p_11 values ('11'),
  partition p_12 values ('12')
);
  
--- サンプルデータ追加
insert into sample_table (col_date, col_1, col_2)
values (
  to_char(sysdate - interval '1' month, 'yyyymmddhh24miss'),
  1,
  'test1'
);
insert into sample_table (col_date, col_1, col_2)
values (
  to_char(sysdate - interval '3' month, 'yyyymmddhh24miss'),
  2,
  'test2'
);
 
--- サンプルテーブル参照
select * from sample_table;
  
COL_DATE            COL_1 COL_2      PART
-------------- ---------- ---------- ----
20100225115232          1 test1      02
20091225115232          2 test2      12
最近携わった仕事では、
Oracle 10gを使用したシステムにおいて数百万件のデータが保持されるテーブルを
パーティション・テーブルとして定義、パーティション・キーのカラムをchar(2)で設け、
データが持つ日付情報の日('01'～'31')にて振り分けるようにしています。
仮にOracle 11gであればバーチャル・カラムを設けてパーティション・キーとして
扱うことができたと思います。この場合、データを投入する側はパーティション・キーの
カラムを意識せずにデータ投入できます。ただしINSERT文でカラムを明示的に指定している
ことが前提となります。またバーチャル・カラムを用いた場合のINSERTに掛かる負荷が
どの程度か検証は必要かと思います。

[ posted by kami ]
スポンサーサイト



13:44 | Oracle | トラックバック (0) | コメント (0) |


2009年07月24日

クエリー・リライト

マテリアライズド・ビューを作成すると初期化パラメータのguery_rewrite_enabledが
標準値(TRUE)となっている場合にクエリー・リライト機能が利用できます。

クエリー・リライト機能とは、SQL文でマテリアライズド・ビューの参照元テーブルを
検索する場合、参照元テーブルを検索する場合のコストとマテリアライズド・ビューを
検索する場合のコストを比較し、マテリアライズド・ビューを検索した方が
コスト的に低いと判断された場合、SQL文が自動的に変換されるというものです。

マテリアライズド・ビューとはSELECT文の結果を保存する
オブジェクト(セグメントを保持する永続オブジェクト)です。
作成例)
create materialized view sample_mv
using index refresh force on demand enable query rewrite as
select col1, sum(col2) as cnt from sample_table group by col1
マテリアライズド・ビューの参照元テーブルとマテリアライズド・ビューの同期は
リフレッシュ処理で取ります。クエリー・リライトの初期化パラメータquery_rewrite_integrityの
標準値(enforced)では整合性が保障され同期が取れている場合のみリライトが行われます。

仕事ではマテリアライズド・ビューを作成するシチュエーションがありませんでしたが
使う機会があればこういった機能も考慮しておきたいです。

[ posted by kami ]
09:30 | Oracle | トラックバック (0) | コメント (0) |


2009年05月19日

ＤＢ設計時の冗長項目について

ＤＢ設計する際、よく正規化、正規化といわれます。
正規化はＲＤＢにおいて、データの一貫性の維持と効率的なデータアクセスを
可能にするための方法論のことですが、冗長にもよい場合があって、たとえば、
会計システムの伝票テーブルなどは伝票行で１レコード、しかもヘッダ項目も
そのなかに含まれる形式のものが多く、冗長である（つまり第１正規化しか行
われいていない）と言えるのですが、これはデータの取り回しがよいためで、
正規化がベスト！というわけではないのが現実です。

よくベンダ系の試験なんかで見かけるものだと、ＤＷＨのアクセス効率をよく
するためにはどうすればよいか？という問いに対し、

1.ビットマップインデックスを付ける
2.オプティマイズヒントを用いる
3.合計項目を追加する
4.トリガを用いる

この選択肢では、3.合計項目を追加する（冗長化する）というのが答えになるわ
けです。ＤＷＨでは特に、データは追加のみで更新などはほとんど起こらない
ことを前提にしますから、ここがポイントなわけです。

ところが、ＤＷＨではないＤＢで伝票形式で冗長性を持たせた場合にネックに
なる項目があります。
「伝票合計」です。1行でも変更があった場合、6行の伝票なら本来1行の修正で
すむところが6行分の更新が必要になります。

ORACLEにはROLLUPという便利なキーワードがあって、GROUP BY句に使用すること
で、1回のSQLで小計、合計を計算することができます。

ただ、検索結果が合計行として行追加されてくるのが難点。つまり、対象件数が
1件であっても、複数件返ってくるのです。簡単なレポートのデータ検索なんかには
よいかも。

SELECT TABLE1.COL1, TABLE2.COL2, SUM(TABLE3.COL3)
FROM TABLE1
JOIN TABLE2
ON ・・・
JOIN TABLE3
ON ・・・
WHERE ～
GROUP BY ROLLUP (TABLE1.COL1,TABLE2.COL2);


パフォーマンスはまあまあ。

通常のときはGROUP BY で結果セット（合計値）とって、ふつうにINNER JOIN
したほうがラクなんですけどね。

[ posted by H.K ]

12:27 | Oracle | トラックバック (0) | コメント (0) |


2009年04月17日

バーチャル・カラム

Oracle Database 11g では、
テーブルにバーチャル・カラムを定義できます。

例）
create table sample_table (
kingaku  number,
zeiritu  int,
zeikomi_kingaku as (kingaku * (1 + zeiritu / 100)) virtual
);	
  
insert into sample_table (kingaku, zeiritu) values ( 100,  5);
insert into sample_table (kingaku, zeiritu) values (3000,  5);  
select * from sample_table;
  
kingaku     zeiritu    zeikomi_kingaku
----------- ---------- ---------------
        100          5             105
       3000          5            3150

2007年10月31日掲載の(SQLServer2005)「機能強化されたＤＤＬステートメント」と
似たような感じはしますが、こちらはビューを作るほどでもないような
計算式を埋め込むのに利用すると便利です。

[ posted by kami ]
21:07 | Oracle | トラックバック (0) | コメント (0) |


2009年04月01日

CPU負荷の低いバッチ処理

「CPU負荷の低いバッチ処理ってあるんですかねえ・・・。」

通常、バッチ処理なんかは運用時間中のレスポンス性能を考えた場合、
誰も使用していない時間帯に実行する（いわゆる、夜間バッチってやつ）
ようにしたり、日中バッチが実行される必要性がある場合などには、「
今からバッチ流します。遅くなりますよ～」なんていうアナウンスを
したりするもんです。

ところが、今係わっているシステム、タイマー起動でバッチが流れるの
ですが、CPU負荷が低いらしく、いつやっているのかわからない。
よくある話で、バッチスケジュールのドキュメントが未整備で公開され
ていないので、あたりを付けるのにCPU負荷をトレースしたが、なんか
ピンとこない。そこで冒頭のQになったわけですが、

「ありますよ。ループで処理されている場合は低くなりがちですね。」

という訳で、簡単なサンプルを作成してやってみました。
DBはOracle10g、AIX版です。

65万件のトラン中、50件程度の件数をアップデート。
1つはループ処理、もうひとつは一括更新。

CPU負荷をタスクマネージャで見てみました。ついでに実行時間も。

・ループ処理（レコードロック有り）
CPU負荷は以下のとおり。実行時間：1分47秒（45/650,000件更新)

loop.jpg

・一括更新処理（レコードロック有り）
CPU負荷は以下のとおり。実行時間：2秒（45/650,000件更新)

update_fine.jpg

ちなみに 50,000/650,000件更新は一括で9秒。

update_half.jpg

また 300,000/650,000件更新は一括で24秒でした。

update_half2.jpg

Oracleのループ処理って、速いイメージだったのですが。
意外にもOracleのループ処理はそんなに速くない。

[ posted by H.K ]
12:26 | Oracle | トラックバック (0) | コメント (0) |


2009年02月27日

CONTINUE文

Oracle 11g ではPL/SQLプログラム中で
ループを制御するCONTINUE文が記述できるようになったそうです。

例）
declare
  i number := 0;
begin
  loop
    i := i + 1;
 
    -- i < 10 の場合は処理をスキップ
    continue when i < 10;
 
    -- i が10以上になると以下の処理が行われる
    dbms_output.put_line('i = 10～100で実行されます');
 
    exit when i = 100;
 
  end loop;
end;
ループ処理中に特定のタイミングでスキップできるのは
IF文などで分岐するより便利で使い勝手がよさそうです。
[ posted by kami ]
10:11 | Oracle | トラックバック (0) | コメント (0) |


2008年12月22日

HASH

SQLでテーブルをJOINする際には、テーブルの結合方法として
NEST LOOP結合、MERGE結合、HASH結合の3つがあるのですが、
どうもORACLEのオプティマイザはNESTED LOOPSを使いたがる
傾向があるようで・・・。

何も指定しないとほとんどNESTED LOOP。これってどうなんで
しょう？私が使っている環境だけ？

同じ件数同士のテーブルを結合する際は一般にHASHの方が速い
ということが言われますが、（MERGEはORDER BYの分、不利だ
とも・・・）ヒントは

SELECT /*+ USE_HASH(TABLE_A TABLE_B) +*/
・・・

てな具合に指定します。
ただし、注意が必要なのは「同じ件数同士」でも

1.テーブル自体の件数が同じ
2.where条件適用後の件数が同じ

ではまるで違います。ここで言うところの「同じ件数」は2.の
ほうなので、「HASHのほうが速いはずだよ。きっと。」と、ヒン
トを指定。すると30秒かかっていたSELECT文が0.5秒に。
ところが・・・同じSQLが異なる環境で「遅い。」という結果が。

SQLDeveloperで実行プランを見ると、HASHですねえ。確かに。
はあ、1分もかかってます。こりゃだめですねえ。
そんじゃまあ、ヒント、変えてみますか。どうなるの？

あ！っという間に終了。0.5秒です。NESTED LOOPだわ。やっぱり。
しかしまてよ。指定しないほうがいいんじゃないか。という考え
がよぎり、ノーヒントで実行。実行プランはNESTED LOOP。0.5秒。
とりあえず、オプティマイザまかせにしよう、ということに。

「転ばぬ先の杖」だったはずが「蛇足」になってしまいました。
ヘタにヒント使うと思わぬしっぺ返しをくらいます。

ちなみに私はNESTED LOOP信者です。
[ posted by H.K ]
19:49 | Oracle | トラックバック (0) | コメント (0) |


2008年10月30日

NULL値の整列順

OracleにおいてNULL値を含む項目をORDER BY句で昇順(ASC)指定したい場合、
通常、NULL値は最後(≒無限大)になってしまいます。降順指定ではもちろん
最初になります。

Oracle 8.1.6 以上であれば、オプション指定により昇順の場合に最初に持
って来れたりします。

例) ORDER BY 項目名 NULLS FIRST

御想像のとおり最後に持ってくるのは NULLS LAST というオプションです。
SQLServerでこれをやるなら Isnull関数を使うのが手っ取り早いかなぁと
思います。

ORDER BY句のオプション指定は他にもいろいろあります。

でも、そもそもnull許容項目において昇順にしたいなんていうのは仕様が
おかしいのではないか。なかにはプライマリキー項目でnull許容がしたい
などと言う輩が居たりするのはほんとうに嘆かわしい。（できねえよ！）

相変わらず、DB設計はおざなりにされているようですねぇ。
みなさんの会社ではどーですか？

[ posted by H.K ]
15:09 | Oracle | トラックバック (0) | コメント (0) |


2008年10月21日

Oracleの便利なスクリプト

ステータスがINVALIDのオブジェクト

システムを運用いく上で、表やビュー、プロシージャなどを削除／再作成／定義変更することがあると思います。変更などのあったオブジェクトを参照するビューやプロシージャは無効（INVALID）状態となり、次回アクセス時に再コンパイルされます。
こうして無効状態となったオブジェクトが存在しないか調査し、必要であれば対策を講じます。

Oracleでは、再コンパイルによって有効（VALID）状態に戻る無効（INVALID）状態のオブジェクトに関して、一括で再コンパイルを実行できるスクリプトが標準で用意されています。

◆スクリプト：
　utlrp.sql , utlirp.sql （処理内容はどちらも同じ）

◆スクリプトの場所：
　UNIX/LINUX　－－－　$ORACLE_HOME/rdbms/admin
　WINDOWS　－－－　%ORACLE_HOME%\rdbms\admin

◆スクリプトの内容：
　FUNCTION，PACKAGE，PACKAGE BODY，PROCEDURE，TRIGGER，VIEWの各オブジェクトに再コンパイルを実行する。

◆スクリプトの使用例：
　（SYSユーザーで実行する）
　C:\>sqlplus /nolog
　SQL> connect / as sysdba
　SQL> @%ORACLE_HOME%\rdbms\admin\utlrp.sql

表の再作成などで、無効（INVALID）状態になるオブジェクトが多数発生してしまう場合などに使用すると便利です。

[ posted by h.i ]
22:21 | Oracle | トラックバック (0) | コメント (0) |


2008年09月16日

Oracle監査

結合テスト中、一瞬にしてデータが消えました。
作業ステータスを管理するテーブル。約30万件。Object Browserで確認
するとテーブルの更新日付のみ変更されています。ということは、
TRUNCATEでばっさり誰かがテーブルのデータを切り捨てたことになります。
（DROP→CREATEでは作成日付も変更され、作成日付=更新日付になる）

幸い、バックアップからデータを戻すことができたため、難は逃れましたが、
犯人は不明。

DBの監査を行うことにしました。
対象はDDL文。つまり今回の事件を教訓として CREATE,ALTER,DROP,
TRUNCATEなどを実行した記録を取ろうというわけです。

systemユーザでログインします。
ログはOS上のファイルとして書き出し、出力フォルダを指定します。

alter system set AUDIT_TRAIL=OS scope=SPFILE;
alter system set AUDIT_FILE_DEST='c:\RMDBS\log' scope=SPFILE;

監査対象ユーザに権限を与えます。

grant audit system to (ユーザ名);

監査を開始。

audit table; -- 監査の開始

監査を止める場合は・・・。

noaudit table; -- 監査を止める

DDL文が発行されるとログ出力されることを確認。アクションが数値でロギングさ
れます。その種類は？

-- 操作の種類がわかる
select * from audit_actions;

つまり、TRUNCATE TABLE なら ACTION=85 というわけです。 [by H.K]

13:33 | Oracle | トラックバック (0) | コメント (0) |


| メイン | 次のページ >>










powered by FC2ブログ