Oracle インデックスは有効なのか

本件のタイトルが意味するところは２つあり、

１つはインデックスを作成してみたはいいもののそのインデックスが

オプティマイザで有効になっているか否かを確認する。

もう１つは高速化を狙ったインデックスが意味を成していないのではないか、というものである。

前者を確認するにはSQLの冒頭に「explain plan for」と記述して、

その後にOracleの実行計画を参照する。

SQL> explain plan for

  2  select

  3    *

  4  from

  5    ZZZ_TABLE

  6  where

  7    CLM01 = '001' AND

  8    CLM02 = '1';

解析されました。

SQL> 

SQL> @C:\oracle\product\10.2.0\db_2\RDBMS\ADMIN\UTLXPLS.SQL

PLAN_TABLE_OUTPUT                                                                                   

----------------------------------------------------------------------------------------------------

Plan hash value: 2565712139                                                                         

                                                                                                    

-------------------------------------------------------------------------------------------         

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |         

-------------------------------------------------------------------------------------------         

|   0 | SELECT STATEMENT            |             |     1 |   510 |     1   (0)| 00:00:01 |         

|   1 |  TABLE ACCESS BY INDEX ROWID| ZZZ_TABLE   |     1 |   510 |     1   (0)| 00:00:01 |         

|*  2 |   INDEX UNIQUE SCAN         | ZZZ_TABLEI0 |     1 |       |     0   (0)| 00:00:01 |         

-------------------------------------------------------------------------------------------         

                                                                                                    

Predicate Information (identified by operation id):                                                 

PLAN_TABLE_OUTPUT                                                                                   

----------------------------------------------------------------------------------------------------

---------------------------------------------------                                                 

                                                                                                    

   2 - access("CLM01"='001' AND "CLM02"=1)                                        

14行が選択されました。

上記の例を見ると、実行計画に「INDEX UNIQUE SCAN」とあるが、

これはインデックスを利用したことを表していて、

これが「TABLE ACCESS FULL」となっていると、インデックスが利用されずに

テーブルを総なめしているということになる。

Oracleのオプティマイザの判断により、インデックスを利用したSQLを発行したにも

関わらずそのような結果になることもある。

では、Oracleのオプティマイザのジャッジは正しかったのか。

ヒント文を利用すれば、明示的にインデックス検索や全表検索が可能となる。

select

  /*+ INDEX(ZZZ_TABLE ZZZ_TABLEI0) */ *

from

  ZZZ_TABLE

where

  CLM01 = '001' AND

  CLM02 = '1';

select

  /*+ FULL ZZZ_TABLE */ *

from

  ZZZ_TABLE

where

  CLM01 = '001' AND

  CLM02 = '1';

Oracle 統計情報

ある仕様変更の案件で、大量の業務データを一括で作成する要件があった。

また、ユーザからある程度のレスポンスを期待されていた。

以上のことから、処理方式をオンラインバッチ（擬似リアル）として、

業務処理をストアドプロシージャでの実装とする提案をした。

※業務ロジックは、基本的にJavaで実装されていたが、

JavaからDBにアクセスする際のオーバーヘッドが大きくなることを懸念して。

だが、出来上がったプログラムを実行しても処理が一向に終わらない。

すると、３０分掛かってようやく終了。

こんなに時間が掛かるわけがないので、調査をしてみると、

「データが大量に増加したこと」

が起因していることが判明した。

Oracleは統計情報の収集を行っていて、

それを元に実行計画を立てている。

つまり、今回の事象を明示的に教えてやらなくてはいけないのだ。

統計情報を収集すると、僅か１分で処理が完了する結果となった。

                     ANALYZE TABLE テーブル名 COMPUTE STATISTICS;

Oracle 改行コード

OSによって、改行コードは以下のように異なります。

                      Unix→（\n）

                      Windows→（\r\n）

                      Macintosh→（\r）

では、DBにはどのように格納されているのかというと、

                      →CHR(13)

                      →CHR(10)

では、Windows改行コードを検索するには、

                      where

                          column LIKE '%' || CHR(13) || CHR(10) || '%'

Oracle マテリアライズドビュー

DBの高速化における技法として、ビューやマテリアライズドビューを利用することがある。

前者は実態が無いのに対して、後者は実態がある。

つまり、前者は構成元となるテーブルの内容をリアルタイムで反映した結果を得ることが出来、

後者はある時点での内容を反映する。

どちらが性能面で優れているかは言わずもがなである。

前者でレスポンスの向上が見込めない場合は、後者を利用することも視野に入れるとよい。

但し、定期的に以下の反映作業が必要となる。

                 execute dbms_mview.refresh('ビュー名','指定');

                       ※指定  c:完全リフレッシュ

                                     f:高速リフレッシュ

Oracle ファイルに記述したSQLの実行方法

仕様変更が発生した際、

　・テーブルの作成、削除

　・カラムの追加、変更、削除

　・データの変更

などをDBに反映することはよくあることである。

だが、SQLを直接入力するのは入力ミスなどによって失敗する危険性が高い。

ファイルにSQLの処理を纏めておけば、開発環境でのテストも可能で、

またそのファイルを証憑として残しておくことができるので利便性が高い。

1. ファイルの作成

        例)ファイル名：C:\hoge\xxx.txt

                ファイル内容：

                       insert into hogetbl values ('aaa', 'bbb');

                       insert into hogetbl values ('ccc', 'ddd');

                       commit;

2.実行

                       SQLplusを開き、@filepath;

                       例)@C:\hoge\xxx.txt;

Oracle 使えるコマンド

マシンを操作する際、基本的にはマウスを使わないという技術者は私以外にも存在すると思われます。

というのも、キーボードからマウスに操作を切り替える際に、手を移動するというオーバーヘッドが発生して時間効率が悪くなるからです。

数秒という僅かな時間ですが、塵も積もれば・・・ということです。

●ﾃｰﾌﾞﾙ一覧

SELECT TABLE_NAME FROM USER_TABLES;

●ﾃｰﾌﾞﾙの項目一覧

DESC ﾃｰﾌﾞﾙ名

●ｲﾝﾃﾞｯｸｽ一覧

SELECT * FROM USER_IND_COLUMNS;

●ﾃﾞｰﾀﾃﾞｨｸｼｮﾅﾘの検索

SELECT * FROM DICT WHERE UPPER(COMMENTS) LIKE '%検索文字列%';

●ﾀｲﾏｰ表示設定

SET TIMI ON

SQL PLUSの出力結果を整形

OracleでSQLを実行する際に欠かせないのがSQL PLUSですが、

SQL文を発行した際の出力結果が不揃いで見にくいと思うことが多々あります。

そこで出力結果のイメージを変更する設定オプションを探していると、やはり存在しました。

　　　　set linesize 1000             一行幅（左記の例では1000列）

　　　　set colsep ,                       区切り文字（左記の例ではカンマ）

　　　　set pagesize 0                  改ページ（左記の例では改ページしない）

　　　　set heading off                見出し有無（左記の例では見出し無し）

　　　　set trimspool on              右側の余白有無（左記の例では余白有り）

　　　　show linesize                   設定値の確認（左記の例ではlinesizeを確認）

Oracleでインポート、エクスポート

DB2よりも需要の高いOracleのインポート、エクスポートも紹介します。

※DOSコマンド上で実行します。

　　Export）

　　　　EXP USER/PASS@DBNAME FILE=filepath.DMP LOG=filepath.LOG                        ←全テーブル

　　　　EXP USER/PASS@DBNAME FILE=filepath.DMP LOG=filepath.LOG tables=hoge_tabale     ←テーブル指定

　　Import）

　　　　IMP USER/PASS@DBNAME FILE=filepath.DMP LOG=filepath.LOG

　　　　IMP USER/PASS@DBNAME FILE=filepath.DMP LOG=filepath.LOG tables=hoge_tabale     ←テーブル指定

MS AccessからOracleに接続

ユーザサイドのシステム部門では、データベースとしてMicrosoft Accessを利用してローカルな環境で手作業による運用を行っている場合があり、そこからシステム化するような案件も数多く存在する。

そのような場合に困るのが、データの移行である。

今回は、MS AccessからOracleに接続する為の設定方法を紹介する。

◇◇◇ MS AccessからOracleに接続 ◇◇◇

　手順１）　エクスプローラから　コントロールパネル → 管理ツール → データソース(ODBC)　の順で開く。

　手順２）　追加を押下して、データソースを追加する。

　　　　　　　　　

　手順３）　データソース名、ユーザー名、サーバーを入力して、OKを押下する。