出来る限り、
SQL文のWHERE条件の表項目に対して、Oracle変換関数を使わないこと。
例えば：TO_CHAR、SUBSTR、TO_NUMBER、TO_DATE
●現在、TO_CHAR、TO_DATEを使う場合、修正するべき例
①より大きいの比較
WHERE TO_CHAR(T01.EXPIRY_DATE, 'YYYYMMDD') > ?
⇒ WHERE T01.EXPIRY_DATE > TO_DATE(? || ' 000000','YYYYMMDD HH24MISS')
②より小さいの比較
WHERE TO_CHAR(T01.EXPIRY_DATE, 'YYYYMMDD') < ?
⇒ WHERE T01.EXPIRY_DATE < TO_DATE(? || ' 235959','YYYYMMDD HH24MISS')
③ある日に限定の場合
AND ((TO_CHAR(T01.DATE_MODIF, 'YYYYMMDD') > ? AND TO_CHAR(T01.DATE_MODIF, 'YYYYMMDD') <= ?)
⇒ AND (T01.DATE_MODIF BETWEEN(TO_DATE(? || ' 000000', 'YYYYMMDD HH24MISS'), TO_DATE(? || ' 235959', 'YYYYMMDD HH24MISS'))
④悪い使い方
AND TO_CHAR(TO_DATE(T01.SMATERIALCREATEDATE,'YYYYMMDD'),'YYYYMM') = ? \
AND TO_CHAR(TO_DATE(T01.SPAYMENTDATE,'YYYYMMDD'),'YYYYMM') = ? \
⇒ AND T01.SMATERIALCREATEDATE like ? || '%'
AND T01.SPAYMENTDATE like ? || '%'
●現在、SUBSTRを使う場合、修正するべき例
①AND SUBSTR(A.CARD_NUMBER, 1, 1) = '4' \
⇒ AND A.CARD_NUMBER like '4%' \
AND SUBSTR(T01.CARD_NUMBER, 0, 6) = '521498' \
⇒ AND T01.CARD_NUMBER like '521498%' \
AND SUBSTR(T7.CRD_TIEUP_CON_NO, 1, 8) = '05150974' \
⇒ AND T7.CRD_TIEUP_CON_NO like '05150974%' \
②SUBSTR(TABLE6.CARD_UPDATE_SIGN_1, 4, 1) = '1'
⇒ TABLE6.CARD_UPDATE_SIGN_1 like '___1%'
③上記①②以外、複雑の使い方も存在します。
上記①②は全て修正し、複雑の使い方は検討し、出来るだけ使わないようにしましょう。
④SUBSTRの悪い／間違い使い方
悪い：SUBSTR(T01.CARD_NUMBER, 0, 6) -->いっぱいある、G050053、G050054、L17C206
間違い：SUBSTR(T.SALECONTOPOSSINFO, 20, 20) = '0' -->L17C217
④も全て修正してください。漏れないように、検査してください。
----------------------------------------------------------------------
０．ソート処理が発生するSQLをなるべく使わない
SQLを遅くする要因の一つに「ソート（並び替え）」の発生があります。
実際、「ソート（並び替え）」はいつ発生するのでしょうか。。。
（１）．ORDER BY句の使用
明らかなる「ソート（並び替え）」です。順番を並び替えようとしているので。。。
ただし、ORDER BY句に指定するカラムにINDEXが張られている時は発生しません。
（２）．UNION句の使用
結果を合体させ重複は１つとして出力するキーワードですが、この「重複は１つとして」が「ソート（並び替え）」です。
できる限りUNION ALL句（結果を合体させ重複はそのまま出力）を使いましょう。
× SELECT a FROM emp_a UNION SELECT b FROM emp_b;
○ SELECT a FROM emp_a UNION ALL SELECT b FROM emp_b;
（３）．DISTINCT句の使用
重複は１つとして出力するキーワードですが、２の説明の通り「重複は１つとして」が「ソート（並び替え）」です。
できる限り使わない方が良いでしょう。
× SELECT DISTINCT a FROM emp;
といった感じです。
ソートを使うななどとは言いませんが、不必要な部分での多用は避けてください。
TOP
１．INDEXを有効に使う
大抵の予想以上に遅いSQLの要因は「INDEX（索引）」がうまく働いていないという理由です。
では、「INDEX（索引）」はどのように張れば良いのかというと。。。
（１）．INDEX列に関数は使用しない
例えば、
× SELECT a FROM emp WHERE TO_CHAR(day,'YYYMMDD') = '19800111'
と、WHEREを指定すると、例えdayにインデックスが張ってあってもそれは利用されません。
この場合は
○ SELECT a FROM emp WHERE day = TO_DATE('19800111','YYYMMDD')
とすべきです。
この他にも多くの関数がORACLEには用意されていますが、使用するのはカラムではなく値の方にしましょう。
（２）．比較条件方法の改善
比較条件詞によってはインデックスを使用しないものがあります。
下記に変更一覧を記述しますので参考にして下さい。
・「IS」を使うとインデックスを使用しない
× SELECT a FROM emp WHERE a IS NOT NULL;
× SELECT a FROM emp WHERE a IS NULL;
○ SELECT a FROM emp WHERE a >= 0;
・「NOT」「<>」「!=」を使うとインデックスを使用しない
否定形(NOT EQUAL, NOT IN)はインデックスを使用できません。
× SELECT a FROM emp WHERE a != 0;
○ SELECT a FROM emp WHERE a > 0;
・複合インデックスはその順番どおりでないとインデックスを使用しない
CREATE INDEX id_emp ON emp (a,b) UNRECOVERABLE;
× SELECT a FROM emp WHERE b = 0 AND a = 12;
○ SELECT a FROM emp WHERE a = 12 AND b = 0;
× SELECT a FROM emp WHERE b = 0;
○ SELECT a FROM emp WHERE a = 12;
・INDEX列では「LIKE '%C%'」「LIKE '%C'」の使用は避ける
× SELECT a FROM emp WHERE c LIKE '%ACCOUNT%';
× SELECT a FROM emp WHERE c LIKE '%ACCOUNT';
○ SELECT a FROM emp WHERE c LIKE 'ACCOUNT%';
・条件の左側で式を用いている
× SELECT a FROM emp WHERE a * 1.1 > 100;
○ SELECT a FROM emp WHERE a > 100 / 1.1;
・条件でORを用いている
× SELECT a FROM emp WHERE a > 100 OR b = 'abc';
どうしても使いたいならビットマップ索引を張りましょう。
（３）．範囲検索時は上限・下限を指定する
上限・下限を指定できる場合は、双方を指定する事でインデックスの無駄な読み込みを回避できます。
× SELECT a FROM emp WHERE a <= 2800;
○ SELECT a FROM emp WHERE a BETWEEN 0 AND 2800;
（４）．無駄なインデックスをベタベタ張らない
管理の容易さ、領域の節減、負荷の減少の為にもテキトーにベタベタとインデックスを張らないようにしましょう。
（５）．インデックス作成時には可能ならUNRECOVERABLE句を
UNRECOVERABLE句を指定すると作成時・作成後にソート処理およびログ取得処理が割愛され作成時間が最速になります。
しかし、ログがないため、何かあったら復元できません。。。
頻繁にバッチなどでインデックスを張り替えてる処理にはお勧めです。
（６）．コストベース・オプティマイザで運用
インデックスの能力を最大限に引き出すにはコストベースでの運用を心がけましょう。
方法はインデックス作成後にはANALYZE文もしくはDBMS_STATSパッケージによりインデックスを張った表の統計情報を取ります。
ANALYZE文には以下の2パターンあります。
・完全な統計情報取得
ANALYZE TABLE emp COMPUTE STATISTICS;
・全データの10%だけ抽出して統計情報取得
ANALYZE TABLE emp STATISTICS SAMPLE 10 PERCENT;
これだけです。
もし、全ての表をコストベースで運用すると明示的に宣言する場合は、初期化パラメータで
optimizer_mode=ALL_ROWS
と、指定します。
ANALYZE文を実行した場合は実行された表のみコストベースになります。
（７）．定期的にインデックスの再作成を行なう
インデックスは，使いつづけると断片化をおこします。
よって，断片化が発生しているかを調べて，必要であればインデックススの再作成を行いましょう。
・インデックスを分析する
ANALYZE INDEX id_emp VALIDATE STRUCTURE;
SELECT lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len FROM index_status WHERE name = 'id_emp';
LF_ROWS LF_ROWS_LEN DEL_ROWS DEL_LF_ROWS_LEN
2589 92340 97 3432
☆ 現在のインデックスが2589行分ある。
☆ その物理消費量が92340バイトとなる。
☆ 削除された行に対する空のインデックスが97個。
☆ その空が3432バイトの領域を使っている。
☆ 97/2580で3.7%のが無駄に使われている。
☆ この値が10％を越えたら，インデックスの再作成を行う。
・インデックス再作成方法
ALTER INDEX id_emp REBUILD;
もしくはDROP・CREATEするだけです。
TOP
２．結合に注意！！
これも、よく聞く事です。
結合の仕方によっては検索スピードを上げることが可能です。
（１）．条件指定には件数の少ない表を指定する
ここで、empが2,000万件、dept表が20件とします。
× SELECT * FROM emp e,dept d WHERE e.DEPTNO = d.DEPTNO AND e.DEPTNO = 40;
○ SELECT * FROM emp e,dept d WHERE e.DEPTNO = d.DEPTNO AND d.DEPTNO = 40;
（２）．条件指定はリレーションシップに従う
× SELECT * FROM emp e,dept d,no n WHERE e.DEPTNO = d.DEPTNO AND e.DEPTNO = n.DEPTNO;
○ SELECT * FROM emp e,dept d,no n WHERE e.DEPTNO = d.DEPTNO AND d.DEPTNO = n.DEPTNO AND n.DEPTNO = e.DEPTNO;
（３）．WHERE句の抽出条件は件数を絞れるものから書く
ここで、カラムaが20パターン、カラムbが2パターンのデータがあるとします。
× SELECT * FROM emp WHERE b = '男' AND a = 'A';
○ SELECT * FROM emp WHERE a = 'A' AND b = '男';
対象件数を一番少なくできる順番でWHERE条件を書いていく。
これは、結合の際にも言える。
結合する表をそれぞれ小さく絞ってから結合する事により、検索速度を速めることができる。
TOP
３．その他
（１）．サブクエリーを引数に取る場合、IN述語よりもEXISTS述語を使う
IN[NOT IN]とEXISTS[NOT EXISTS]は、たいていの場合、全く等しい結果集合を返します。
しかし、この両者でサブクエリーを作る場合は、EXISTSの方が圧倒的に速く動作します。
以下の2つのSQLは、同じ結果を返しますが、2番目の方が速いです。
× SELECT a FROM emp WHERE a IN (SELECT a FROM dept);
○ SELECT e.a FROM emp e WHERE EXISTS (SELECT * FROM dept d WHERE e.a = d.a);
なぜ、EXISTSの方が速いか？その理由は以下の2点です。
もし結合キー(この場合はa)にインデックスが張られていれば、deptテーブルの実表は見に行かず、インデックスのみを参照する。
もしdeptテーブルがインデックスを持っていなくても、優れたオプティマイザならば、 a列をソートした一時テーブルを作り、2分探索することで、全表走査よりも効率的に検索を行なう。
なお、EXISTS述語のサブクエリー内では、SELECT * を使うのが最速です。
（２）．IN述語の引数リストには、最もありそうなキーを左寄せする
なぜなら、INは、左から右へ引数を評価し、見つかった時点でtrueを返しそれより右の引数は見ないからです。
× SELECT * FROM emp WHERE add IN ('福島', '大阪', '東京', '宮城');
○ SELECT * FROM emp WHERE add IN ('東京', '大阪', '宮城', '福島');
--INの最大指定数について
ちなみに、INには1000個までしか値が指定できません。
それ以上指定するとエラーでSQLが落ちます。
（３）．行数を数えるときはCOUNT(*)よりもCOUNT(カラム名)を使う
このトリックは、インデックスを使います。
したがって、これがうまく働くためには、COUNT関数の引数となるカラムにインデックスが張られている必要があります。
× SELECT COUNT(*) FROM emp;
○ SELECT COUNT(a) FROM emp;
ここで、aがempテーブルの主キーだとすれば、当然、aにはユニークなインデックスが存在します。
それを利用します。
（４）．ROWIDによるアクセスが最速
ROWIDはどのテーブルでも必ず持っている擬似列であり、そこに格納されている値はレコードの物理アドレスです。
つまりROWIDはポインタの役割を果たします。
インデックスもROWIDを使用しています。
ROWIDは、セッションが終了すると変化するかもしれませんが、同一セッション中は不変であり、
Oracleでは常に最速のアクセスが保証されます。
SELECT rowid INTO :emp_ro FROM emp WHERE a = 0 FOR UPDATE OF a;
UPDATE emp SET a=1 WHERE rowid = :emp_ro;
（５）．SELECT列には使用するものだけ指定する
使用しない列まで取得すると余計なI/Oが発生する。
× SELECT * FROM emp;
○ SELECT a FROM emp;
（６）．列比較時には、列の型の一致に注意する
数値列、文字列を比較する場合は自動変換が動作し処理のオーバーヘッドがかかる。
a列はインデックス列で、NUMBER型の場合、
× SELECT * FROM emp WHERE a = '1';
○ SELECT * FROM emp WHERE a = 1;
前者はWHERE a = TO_NUMBER('1')と暗黙的に変換されている。
（７）．データ移動最速！？
表にあるデータを移動させる際に重宝するSQL文です。
・表を新しく作成する場合
CREATE TABLE emp2 UNRECOVERABLE AS SELECT * FROM emp;
とすることで、emp表のデータをemp2にコピーする事が可能です。
データ移動の方法としては最速です。（INSERTをぐるぐる回すより良い）
・表を新しく作成出来ない場合
INSERT /*+ APPEND */ INTO emp2 SELECT * FROM emp;
とすることで、emp表のデータをemp2にコピーする事が可能です。
データ移動の方法としては2番目に速いです。（ダイレクトパスインサートと言う）
TOP
４．インポート・エクスポート
SQLとは関係ないですが、簡単なエクスポート・インポート方法を書き残しておきます。
（１）エクスポート（データをdmpファイルに書き出す）
エクスポートを実行するコマンドはコマンドラインより
exp ユーザ/パス@リスナ パラメータ
です。
パラメータ一覧
パラメータ名 デフォルト値 説明
FILE=XXX.dmp EXPDAT.DMP 書き出すファイル名を指定。
複数ファイルに分けることも可能。
その際はFILESIZEオプションが必須。
拡張子を指定しないとデフォルトで.dmpが付けられる。
FILESIZE=600MB なし dmpファイルを分割する際に1ファイルの最大サイズを指定する。
LOG=XXX.log なし エクスポート中のログを書き出すファイル名指定
TABLES=('XXX','YYY') なし エクスポートする表名を指定。
FILESIZE=600MB なし dmpファイルを分割する際に1ファイルの最大サイズを指定する。
ROWS=y YES 行データをエクスポートするかをyes/noで指定する。
nを指定するとデータは保存されない。
CONSTRAINTS=y YES オブジェクトインポート時に宣言の完全性を再構築するために必要な
SQL文をエクスポートファイルに書き出す。
例：主キーと参照文
GRANTS=y YES SQL権限をエクスポートファイルに書き出す。
INDEXES=y YES インデックス（索引）をエクスポートファイルに書き出す。
COMPRESS=y YES データを圧縮して書き出す。
FULL=y NO Oracleに関するすべてのデータファイル，表領域，ロールバックセグメントなどを エクスポートファイルに書き出す。
PARFILE=XXX.dat なし この一覧にあるオプションをファイルにまとめて読み込ますことが出来ます。
そのファイルの名前を指定。
DIRECT=y NO ダイレクトパスでデータをエクスポートするか指定。
ダイレクトパスで指定した場合は処理速度が速くなる。
USERID=ユーザ/パス なし 実行するユーザの設定。
OWNER=XXX なし 書き出されるオブジェクトを含むOracleのアカウントリストを出力する。
BUFFER=10240 10240 データバッファサイズを指定。
RECORDLENGTH=XXXX OS依存 書き出されるレコード長で，バイト単位で表現される。
INCTYPE=XXXX なし 実行される増分エクスポートの種類。
RECORD=y YES Oracleに一部のデータディクショナリビュー内に書き出される増分エクスポートの種類を追随するように命令する。
この情報は増分エクスポートファイルからインポートを実行する際に使用される。
CONSISTENT=y NO Oracleに表相互間の一貫性を維持するように指定する。
これによって，エクスポートされる表がエクスポートの実行中に使用される場合でも表データのコピーがエクスポート開始時間付けで作成される。
STATISTICS=XXXX ESTIMETE SQL分析文をエクスポートファイルに書き出す。
IGNORE=y NO テーブル定義があっても無視してインポートする。
QUERY='WHERE XXX = YYYY' なし エクスポートするデータを選別する。
これを指定した場合はダイレクトパス（DIRECT）によるエクスポートは出来ない。
COMMIT=y NO Yにすると１行インポート毎にCOMMITが行われる。
そのため、RBSが大きくならない。
しかし、処理時間が肥大する。
デフォルトのNだとすべてのデータをインポートしてからCOMMITされる。
（２）インポート（データをdmpファイルから表に書き出す）
インポートを実行するコマンドはコマンドラインより
imp ユーザ/パス@リスナ パラメータ
です。
パラメータ一覧
パラメータ名 デフォルト値 説明
FILE=XXX.dmp EXPDAT.DMP インポートを行なう元ファイル名を指定。
複数ファイルに分けることも可能。
その際はFILESIZEオプションが必須。
拡張子を指定しないとデフォルトで.dmpが付けられる。
FILESIZE=600MB なし dmpファイルが分割されている場合に1ファイルの最大サイズを指定する。
LOG=XXX.log なし インポート中のログを書き出すファイル名指定
TABLES=('XXX','YYY') なし インポートする表名を指定。
CONSTRAINTS=y YES エクスポートファイルより宣言の完全性を再構築するために必要な
SQL文をインポートする。
例：主キーと参照文
GRANTS=y YES SQL権限をインポートする。
INDEXES=y YES インデックス（索引）をインポートする。
FULL=y NO Oracleに関するすべてのデータファイル，表領域，ロールバックセグメントなどを インポートする。
PARFILE=XXX.dat なし この一覧にあるオプションをファイルにまとめて読み込ますことが出来ます。
そのファイルの名前を指定。
USERID=ユーザ/パス なし 実行するユーザの設定。
OWNER=XXX なし 書き出されるオブジェクトを含むOracleのアカウントリストを出力する。
BUFFER=10240 10240 データバッファサイズを指定。
IGNORE=y NO インポート時にテーブルやインデックスなどの作成エラーを無視してインポートする。
COMMIT=y NO Yにすると１行インポート毎にCOMMITが行われる。
そのため、RBSが大きくならない。
しかし、処理時間が肥大する。
デフォルトのNだとすべてのデータをインポートしてからCOMMITされる。
TOP
５．ちょっとしたSQL達！！でも忘れちゃう。。。
◇ カラムの追加
◇ 既存のカラム名の変更
◇ 既存カラムの削除
◇ 既存のテーブル名の変更
□ カラムの長さ変更
□ 領域追加
□ テンポラリ再作成
□ 空きスペースの断片化を確認するには
□ ソートの見積もり
□ REDOログ見積もり
□ 権限付与
□ シャットダウン
□ PURGE(パージ)（10g限定 ゴミ箱機能）
□ SQL*Plusにおける各種設定
□ セッション切断について
□ MARGE（マージ）について（9i以降）
予定外に多かったのでこちらへ ⇒ ORACLEはっとした時に使えるSQL文
□ 新機能各種（10g以降）
10gについてはこちら ⇒ ORACLE10g新機能
TOP ６．パーティショニング（表領域のパーティション化）
表領域のパーティショニング（パーティション化）には以下の3種類が存在します。
(1)レンジ・パーティショニング
(2)ハッシュ・パーティショニング
(3)コンポジット・パーティショニング
それぞれを軽くですがご説明いたします。
前置きとして、「table_space_*」という表領域が4つ存在するものとします。
(1)レンジ・パーティショニング
あるキーを元に場合分けしていくのがレンジ・パーティショニングです。
下記のSQLをごらん下さい。
CREATE TABLE test
UNRECOVERABLE
COMPRESS
PARTITION BY RANGE(SNO)
( PARTITION ryoiki_1 VALUES LESS THAN (3500) TABLESPACE table_space_1,
PARTITION ryoiki_2 VALUES LESS THAN (7000) TABLESPACE table_space_2,
PARTITION ryoiki_3 VALUES LESS THAN (10500) TABLESPACE table_space_3,
PARTITION ryoiki_4 VALUES LESS THAN (14000) TABLESPACE table_space_4 )
AS SELECT * FROM moto_hyo;
これは、moto_hyo表のデータを元に、アーカイブを取らず（UNRECOVERABLE）領域を圧縮して（COMPRESS）
さらにパーティショニングしたtest表を作成しているところです。
レンジ・パーティショニングではキー列を指定して（ここではSNO）その数値がいくらかで
表領域を分けます。
この方法は時系列にデータが格納されるような場合に大きな効力を発揮するそうです。
今回は、
0 < SNO <= 3500 ⇒ table_space_1
3500 < SNO <= 7000 ⇒ table_space_2
7000 < SNO <= 10500 ⇒ table_space_3
10500 < SNO <= 14000 ⇒ table_space_4
という風に分けてあります。
レンジ・パーティショニングの利点は
・パーティショニングに使用したキー列をWHEREで指定してSELECTなりを行なうと
高速に検索が出来る（パーティション・プルーニング効果）
短所としては
・パーティション・プルーニング効果のためにWHEREに制限が出る。
（INやORを使用した文はパーティション・プルーニング効果が得られにくい）
・表領域を均等に使用するのがやや難しい。
と言う点です。
(2)ハッシュ・パーティショニング
あるキーをHASH関数にかけ、それで得られたHASH値を元にORACLEが適当に
表領域に割り振っていく方法がハッシュ・パーティショニングです。
下記のSQLをごらん下さい。
CREATE TABLE test
UNRECOVERABLE
COMPRESS
PARTITION BY HASH(SNO)
PARTITIONS 4
STORE IN (table_space_1,table_space_2,TABLESPACE table_space_3,TABLESPACE table_space_4)
AS SELECT * FROM moto_hyo;
先ほどと同じく、moto_hyo表のデータを元に、アーカイブを取らず（UNRECOVERABLE）領域を圧縮して（COMPRESS）さらにパーティショニングしたtest表を作成しているところです。
ハッシュ・パーティショニングではキー列を指定して（SNO）その後パーティション数を指定します（PARTITIONS 4）
指定された表領域（STORE以下）にキー列のHASH値を使いほぼ均等に分けます。
利点としては
・先ほどのレンジ・パーティショニングよりは作成する側にかかる負担は少なくなります。（適当に分けてくれるため）
・各表領域に均等にデータが配分されます。
・=、IN、IS NULLといった条件が指定された場合や、これらをORで連結した場合にパーティション・プルーニング効果を発揮できます。
欠点としては
・パーティション数が2 の累乗で無ければならない。
・値の範囲（BETWEEN等）でパーティション・プルーニングを行えない。
と言う事です。
(3)コンポジット・パーティショニング
コンポジット・パーティショニングは、レンジ・パーティショニングとハッシュ・パーティショニングを組み合わせたものです。
CREATE TABLE test
UNRECOVERABLE
COMPRESS
PARTITION BY RANGE(SNO)
SUBPARTITION BY HASH(SNO2)
SUBPARTITIONS 8
STORE IN (table_space_1,table_space_2,TABLESPACE table_space_3,TABLESPACE table_space_4)
( PARTITION ryoiki_1 VALUES LESS THAN (3500) ,
PARTITION ryoiki_2 VALUES LESS THAN (7000) ,
PARTITION ryoiki_3 VALUES LESS THAN (10500) ,
PARTITION ryoiki_4 VALUES LESS THAN (14000) )
AS SELECT * FROM moto_hyo;
まずレンジで4つにパーティショニングした後に、ハッシュで8つにサブパーティショニングします。
注意としてはコンポジット・パーティショニングでは、先にハッシュ・パーティショニングしてから
レンジ・パーティショニング することは出来ません。
コンポジット・パーティショニングではレンジ/ハッシュ両方でパーティション・プルーニングが可能な場合は、その相乗効果が発揮されます。
(4)パーティショニング後のテーブルのINDEX
パーティショニングしたテーブルにつけるインデックスの種類はローカルでなければいけません。
CREATE UNIQUE INDEX id_test ON test ( SNO )
LOCAL TABLESPACE table_space_idx_1 ;
また、パラレル・インデックス（parallel (degree パラレル数)句）を張ったり、
CREATE UNIQUE INDEX id_test ON test ( SNO )
LOCAL TABLESPACE table_space_idx_1 PARALLEL (DEGREE 8) ;
表に対しパラレルに検索することにより、大幅な検索処理時間短縮になるようです。
（実際にはまだ経験してませんが・・・）
select/*+ index (test,id_test) parallel_index (test,id_test,8) */ sno FROM test WHERE ...
TOP ７．マテリアライズド・ビュー
マテリアライズド・ビューには大きく2つの利用方法があます。
1つはリモート・データベース上に存在するデータを ローカル・データベース上に
定期的にコピーする目的で使用され、スナップショットとも呼ばれます。
もう1つは、ローカル・データベース上のデータの集計や結合処理を高速化するために使用されます。
ここでマテリアライズド・ビューではコピー元はディテール表、コピー先はコンテナ表と呼ばれます。
ディテール表 ⇒ 元表
コンテナ表 ⇒ 作成される表
マテリアライズド・ビューのディテール表のデータが更新された場合、
リフレッシュと呼ばれる処理によって、コンテナ表にその変更が反映されます。
リフレッシュには、毎回すべてのデータを入れ替える完全リフレッシュと、
ディテール表への 変更履歴を保持するマテリアライズド・ビュー・ログを利用し、
差分のみを反映する高速リフレッシュがあります。
①スナップショット
今回は高速リフレッシュを行なえるマテリアライズド・ビュー・ログを利用した方法を記述します。
(1) マテアライズド・ビュー・ログの作成 (コピー元インスタンス)
CREATE MATERIALIZED VIEW LOG ON ディテールtable;
(2) データベース・リンクの作成 (コピー先インスタンス)
CREATE DATABASE LINK リンク名
CONNECT TO SCOTT
IDENTIFIED BY TIGER USING 'schema';
(3) マテアライズド・ビューの作成 (コピー先インスタンス)
CREATE MATERIALIZED VIEW コンテナtable
REFRESH FAST
AS SELECT * FROM schema.ディテールtable@リンク名;
(4) リフレッシュ・グループの作成 (コピー先インスタンス)
EXECUTE DBMS_REFRESH.MAKE
(NAME=>'schema.GRP1',LIST=>'schema.コンテナtable',NEXT_DATE=>SYSDATE,INTERVAL=>'SYSDATE+1/24');
②結合を含むマテリアライズド・ビュー
結合を含むマテリアライズド・ビューを作成するには、マテリアライズド・ビューの
SELECTリストに各表の ROWIDを含める必要があります。
なお、今回の例ではON COMMITオプションを指定し、元表の更新がリアルタイムに
マテリアライズド・ビューへ反映されるように作成しています。
CREATE MATERIALIZED VIEW コンテナtable
REFRESH fast on commit
AS SELECT a.rowid,b.rowid,c.rowid, a.name,b.name,c.name
FROM ディテールtable1 a,ディテールtable2 b,ディテールtable3 c
WHERE a.key = b.key
AND b.key2 = c.key ;
これで、SQL内で結合して検索するよりもマテリアライズド・ビューを参照する事により
早く検索結果を取得する事が出来ます。
諸注意として、コンテナ表へのUPDATE・INSERT・DELETE等は行なえませんので・・・
TOP
８．各種確認コマンド
--空き領域の少ない（動的拡張が起こりそうな）セグメントの確認
select owner,table_name,blocks,empty_blocks
from dba_tables
where empty_blocks / (blocks + empty_blocks) < 0.1
and empty_blocks > 0;
このコマンドで表示されたテーブルは動的拡張が発生しそうです。
--エクステントの数の確認
SELECT
OWNER,SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, EXTENTS
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = '表領域名';
EXTENTSにてエクステントの数が確認できます。
--フラグメンテーション（分断化）の発生を確認
SELECT
TABLESPACE_NAME,
FILE_ID,
BLOCK_ID AS 開始ブロック,
BLOCK_ID + BLOCKS - 1 AS 終了ブロック,
BYTES AS サイズ,
BLOCKS AS ブロック数
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = '表領域名'
ORDER BY BLOCK_ID;
--行移行・行連鎖の発生を確認
select table_name,blocks,chain_cnt
from dba_tables
where owner='スキーマ名';
chain_cntにより行移行・行連鎖の行数を確認できます。
行移行・行連鎖の解消法は表はエクスポート・インポートでを行うことで解消されます。
--HWM空き領域が多いか確認
select table_name,num_rows,blocks,chain_cnt,avg_space
from dba_tables
where owner='スキーマ名';
num_rowsに対し、blocksが大きい場合にavg_spaceを確認することによりブロック内の空き領域が
多いかどうか確認できます。
SQL文のWHERE条件の表項目に対して、Oracle変換関数を使わないこと。
例えば：TO_CHAR、SUBSTR、TO_NUMBER、TO_DATE
●現在、TO_CHAR、TO_DATEを使う場合、修正するべき例
①より大きいの比較
WHERE TO_CHAR(T01.EXPIRY_DATE, 'YYYYMMDD') > ?
⇒ WHERE T01.EXPIRY_DATE > TO_DATE(? || ' 000000','YYYYMMDD HH24MISS')
②より小さいの比較
WHERE TO_CHAR(T01.EXPIRY_DATE, 'YYYYMMDD') < ?
⇒ WHERE T01.EXPIRY_DATE < TO_DATE(? || ' 235959','YYYYMMDD HH24MISS')
③ある日に限定の場合
AND ((TO_CHAR(T01.DATE_MODIF, 'YYYYMMDD') > ? AND TO_CHAR(T01.DATE_MODIF, 'YYYYMMDD') <= ?)
⇒ AND (T01.DATE_MODIF BETWEEN(TO_DATE(? || ' 000000', 'YYYYMMDD HH24MISS'), TO_DATE(? || ' 235959', 'YYYYMMDD HH24MISS'))
④悪い使い方
AND TO_CHAR(TO_DATE(T01.SMATERIALCREATEDATE,'YYYYMMDD'),'YYYYMM') = ? \
AND TO_CHAR(TO_DATE(T01.SPAYMENTDATE,'YYYYMMDD'),'YYYYMM') = ? \
⇒ AND T01.SMATERIALCREATEDATE like ? || '%'
AND T01.SPAYMENTDATE like ? || '%'
●現在、SUBSTRを使う場合、修正するべき例
①AND SUBSTR(A.CARD_NUMBER, 1, 1) = '4' \
⇒ AND A.CARD_NUMBER like '4%' \
AND SUBSTR(T01.CARD_NUMBER, 0, 6) = '521498' \
⇒ AND T01.CARD_NUMBER like '521498%' \
AND SUBSTR(T7.CRD_TIEUP_CON_NO, 1, 8) = '05150974' \
⇒ AND T7.CRD_TIEUP_CON_NO like '05150974%' \
②SUBSTR(TABLE6.CARD_UPDATE_SIGN_1, 4, 1) = '1'
⇒ TABLE6.CARD_UPDATE_SIGN_1 like '___1%'
③上記①②以外、複雑の使い方も存在します。
上記①②は全て修正し、複雑の使い方は検討し、出来るだけ使わないようにしましょう。
④SUBSTRの悪い／間違い使い方
悪い：SUBSTR(T01.CARD_NUMBER, 0, 6) -->いっぱいある、G050053、G050054、L17C206
間違い：SUBSTR(T.SALECONTOPOSSINFO, 20, 20) = '0' -->L17C217
④も全て修正してください。漏れないように、検査してください。
----------------------------------------------------------------------
０．ソート処理が発生するSQLをなるべく使わない
SQLを遅くする要因の一つに「ソート（並び替え）」の発生があります。
実際、「ソート（並び替え）」はいつ発生するのでしょうか。。。
（１）．ORDER BY句の使用
明らかなる「ソート（並び替え）」です。順番を並び替えようとしているので。。。
ただし、ORDER BY句に指定するカラムにINDEXが張られている時は発生しません。
（２）．UNION句の使用
結果を合体させ重複は１つとして出力するキーワードですが、この「重複は１つとして」が「ソート（並び替え）」です。
できる限りUNION ALL句（結果を合体させ重複はそのまま出力）を使いましょう。
× SELECT a FROM emp_a UNION SELECT b FROM emp_b;
○ SELECT a FROM emp_a UNION ALL SELECT b FROM emp_b;
（３）．DISTINCT句の使用
重複は１つとして出力するキーワードですが、２の説明の通り「重複は１つとして」が「ソート（並び替え）」です。
できる限り使わない方が良いでしょう。
× SELECT DISTINCT a FROM emp;
といった感じです。
ソートを使うななどとは言いませんが、不必要な部分での多用は避けてください。
TOP
１．INDEXを有効に使う
大抵の予想以上に遅いSQLの要因は「INDEX（索引）」がうまく働いていないという理由です。
では、「INDEX（索引）」はどのように張れば良いのかというと。。。
（１）．INDEX列に関数は使用しない
例えば、
× SELECT a FROM emp WHERE TO_CHAR(day,'YYYMMDD') = '19800111'
と、WHEREを指定すると、例えdayにインデックスが張ってあってもそれは利用されません。
この場合は
○ SELECT a FROM emp WHERE day = TO_DATE('19800111','YYYMMDD')
とすべきです。
この他にも多くの関数がORACLEには用意されていますが、使用するのはカラムではなく値の方にしましょう。
（２）．比較条件方法の改善
比較条件詞によってはインデックスを使用しないものがあります。
下記に変更一覧を記述しますので参考にして下さい。
・「IS」を使うとインデックスを使用しない
× SELECT a FROM emp WHERE a IS NOT NULL;
× SELECT a FROM emp WHERE a IS NULL;
○ SELECT a FROM emp WHERE a >= 0;
・「NOT」「<>」「!=」を使うとインデックスを使用しない
否定形(NOT EQUAL, NOT IN)はインデックスを使用できません。
× SELECT a FROM emp WHERE a != 0;
○ SELECT a FROM emp WHERE a > 0;
・複合インデックスはその順番どおりでないとインデックスを使用しない
CREATE INDEX id_emp ON emp (a,b) UNRECOVERABLE;
× SELECT a FROM emp WHERE b = 0 AND a = 12;
○ SELECT a FROM emp WHERE a = 12 AND b = 0;
× SELECT a FROM emp WHERE b = 0;
○ SELECT a FROM emp WHERE a = 12;
・INDEX列では「LIKE '%C%'」「LIKE '%C'」の使用は避ける
× SELECT a FROM emp WHERE c LIKE '%ACCOUNT%';
× SELECT a FROM emp WHERE c LIKE '%ACCOUNT';
○ SELECT a FROM emp WHERE c LIKE 'ACCOUNT%';
・条件の左側で式を用いている
× SELECT a FROM emp WHERE a * 1.1 > 100;
○ SELECT a FROM emp WHERE a > 100 / 1.1;
・条件でORを用いている
× SELECT a FROM emp WHERE a > 100 OR b = 'abc';
どうしても使いたいならビットマップ索引を張りましょう。
（３）．範囲検索時は上限・下限を指定する
上限・下限を指定できる場合は、双方を指定する事でインデックスの無駄な読み込みを回避できます。
× SELECT a FROM emp WHERE a <= 2800;
○ SELECT a FROM emp WHERE a BETWEEN 0 AND 2800;
（４）．無駄なインデックスをベタベタ張らない
管理の容易さ、領域の節減、負荷の減少の為にもテキトーにベタベタとインデックスを張らないようにしましょう。
（５）．インデックス作成時には可能ならUNRECOVERABLE句を
UNRECOVERABLE句を指定すると作成時・作成後にソート処理およびログ取得処理が割愛され作成時間が最速になります。
しかし、ログがないため、何かあったら復元できません。。。
頻繁にバッチなどでインデックスを張り替えてる処理にはお勧めです。
（６）．コストベース・オプティマイザで運用
インデックスの能力を最大限に引き出すにはコストベースでの運用を心がけましょう。
方法はインデックス作成後にはANALYZE文もしくはDBMS_STATSパッケージによりインデックスを張った表の統計情報を取ります。
ANALYZE文には以下の2パターンあります。
・完全な統計情報取得
ANALYZE TABLE emp COMPUTE STATISTICS;
・全データの10%だけ抽出して統計情報取得
ANALYZE TABLE emp STATISTICS SAMPLE 10 PERCENT;
これだけです。
もし、全ての表をコストベースで運用すると明示的に宣言する場合は、初期化パラメータで
optimizer_mode=ALL_ROWS
と、指定します。
ANALYZE文を実行した場合は実行された表のみコストベースになります。
（７）．定期的にインデックスの再作成を行なう
インデックスは，使いつづけると断片化をおこします。
よって，断片化が発生しているかを調べて，必要であればインデックススの再作成を行いましょう。
・インデックスを分析する
ANALYZE INDEX id_emp VALIDATE STRUCTURE;
SELECT lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len FROM index_status WHERE name = 'id_emp';
LF_ROWS LF_ROWS_LEN DEL_ROWS DEL_LF_ROWS_LEN
2589 92340 97 3432
☆ 現在のインデックスが2589行分ある。
☆ その物理消費量が92340バイトとなる。
☆ 削除された行に対する空のインデックスが97個。
☆ その空が3432バイトの領域を使っている。
☆ 97/2580で3.7%のが無駄に使われている。
☆ この値が10％を越えたら，インデックスの再作成を行う。
・インデックス再作成方法
ALTER INDEX id_emp REBUILD;
もしくはDROP・CREATEするだけです。
TOP
２．結合に注意！！
これも、よく聞く事です。
結合の仕方によっては検索スピードを上げることが可能です。
（１）．条件指定には件数の少ない表を指定する
ここで、empが2,000万件、dept表が20件とします。
× SELECT * FROM emp e,dept d WHERE e.DEPTNO = d.DEPTNO AND e.DEPTNO = 40;
○ SELECT * FROM emp e,dept d WHERE e.DEPTNO = d.DEPTNO AND d.DEPTNO = 40;
（２）．条件指定はリレーションシップに従う
× SELECT * FROM emp e,dept d,no n WHERE e.DEPTNO = d.DEPTNO AND e.DEPTNO = n.DEPTNO;
○ SELECT * FROM emp e,dept d,no n WHERE e.DEPTNO = d.DEPTNO AND d.DEPTNO = n.DEPTNO AND n.DEPTNO = e.DEPTNO;
（３）．WHERE句の抽出条件は件数を絞れるものから書く
ここで、カラムaが20パターン、カラムbが2パターンのデータがあるとします。
× SELECT * FROM emp WHERE b = '男' AND a = 'A';
○ SELECT * FROM emp WHERE a = 'A' AND b = '男';
対象件数を一番少なくできる順番でWHERE条件を書いていく。
これは、結合の際にも言える。
結合する表をそれぞれ小さく絞ってから結合する事により、検索速度を速めることができる。
TOP
３．その他
（１）．サブクエリーを引数に取る場合、IN述語よりもEXISTS述語を使う
IN[NOT IN]とEXISTS[NOT EXISTS]は、たいていの場合、全く等しい結果集合を返します。
しかし、この両者でサブクエリーを作る場合は、EXISTSの方が圧倒的に速く動作します。
以下の2つのSQLは、同じ結果を返しますが、2番目の方が速いです。
× SELECT a FROM emp WHERE a IN (SELECT a FROM dept);
○ SELECT e.a FROM emp e WHERE EXISTS (SELECT * FROM dept d WHERE e.a = d.a);
なぜ、EXISTSの方が速いか？その理由は以下の2点です。
もし結合キー(この場合はa)にインデックスが張られていれば、deptテーブルの実表は見に行かず、インデックスのみを参照する。
もしdeptテーブルがインデックスを持っていなくても、優れたオプティマイザならば、 a列をソートした一時テーブルを作り、2分探索することで、全表走査よりも効率的に検索を行なう。
なお、EXISTS述語のサブクエリー内では、SELECT * を使うのが最速です。
（２）．IN述語の引数リストには、最もありそうなキーを左寄せする
なぜなら、INは、左から右へ引数を評価し、見つかった時点でtrueを返しそれより右の引数は見ないからです。
× SELECT * FROM emp WHERE add IN ('福島', '大阪', '東京', '宮城');
○ SELECT * FROM emp WHERE add IN ('東京', '大阪', '宮城', '福島');
--INの最大指定数について
ちなみに、INには1000個までしか値が指定できません。
それ以上指定するとエラーでSQLが落ちます。
（３）．行数を数えるときはCOUNT(*)よりもCOUNT(カラム名)を使う
このトリックは、インデックスを使います。
したがって、これがうまく働くためには、COUNT関数の引数となるカラムにインデックスが張られている必要があります。
× SELECT COUNT(*) FROM emp;
○ SELECT COUNT(a) FROM emp;
ここで、aがempテーブルの主キーだとすれば、当然、aにはユニークなインデックスが存在します。
それを利用します。
（４）．ROWIDによるアクセスが最速
ROWIDはどのテーブルでも必ず持っている擬似列であり、そこに格納されている値はレコードの物理アドレスです。
つまりROWIDはポインタの役割を果たします。
インデックスもROWIDを使用しています。
ROWIDは、セッションが終了すると変化するかもしれませんが、同一セッション中は不変であり、
Oracleでは常に最速のアクセスが保証されます。
SELECT rowid INTO :emp_ro FROM emp WHERE a = 0 FOR UPDATE OF a;
UPDATE emp SET a=1 WHERE rowid = :emp_ro;
（５）．SELECT列には使用するものだけ指定する
使用しない列まで取得すると余計なI/Oが発生する。
× SELECT * FROM emp;
○ SELECT a FROM emp;
（６）．列比較時には、列の型の一致に注意する
数値列、文字列を比較する場合は自動変換が動作し処理のオーバーヘッドがかかる。
a列はインデックス列で、NUMBER型の場合、
× SELECT * FROM emp WHERE a = '1';
○ SELECT * FROM emp WHERE a = 1;
前者はWHERE a = TO_NUMBER('1')と暗黙的に変換されている。
（７）．データ移動最速！？
表にあるデータを移動させる際に重宝するSQL文です。
・表を新しく作成する場合
CREATE TABLE emp2 UNRECOVERABLE AS SELECT * FROM emp;
とすることで、emp表のデータをemp2にコピーする事が可能です。
データ移動の方法としては最速です。（INSERTをぐるぐる回すより良い）
・表を新しく作成出来ない場合
INSERT /*+ APPEND */ INTO emp2 SELECT * FROM emp;
とすることで、emp表のデータをemp2にコピーする事が可能です。
データ移動の方法としては2番目に速いです。（ダイレクトパスインサートと言う）
TOP
４．インポート・エクスポート
SQLとは関係ないですが、簡単なエクスポート・インポート方法を書き残しておきます。
（１）エクスポート（データをdmpファイルに書き出す）
エクスポートを実行するコマンドはコマンドラインより
exp ユーザ/パス@リスナ パラメータ
です。
パラメータ一覧
パラメータ名 デフォルト値 説明
FILE=XXX.dmp EXPDAT.DMP 書き出すファイル名を指定。
複数ファイルに分けることも可能。
その際はFILESIZEオプションが必須。
拡張子を指定しないとデフォルトで.dmpが付けられる。
FILESIZE=600MB なし dmpファイルを分割する際に1ファイルの最大サイズを指定する。
LOG=XXX.log なし エクスポート中のログを書き出すファイル名指定
TABLES=('XXX','YYY') なし エクスポートする表名を指定。
FILESIZE=600MB なし dmpファイルを分割する際に1ファイルの最大サイズを指定する。
ROWS=y YES 行データをエクスポートするかをyes/noで指定する。
nを指定するとデータは保存されない。
CONSTRAINTS=y YES オブジェクトインポート時に宣言の完全性を再構築するために必要な
SQL文をエクスポートファイルに書き出す。
例：主キーと参照文
GRANTS=y YES SQL権限をエクスポートファイルに書き出す。
INDEXES=y YES インデックス（索引）をエクスポートファイルに書き出す。
COMPRESS=y YES データを圧縮して書き出す。
FULL=y NO Oracleに関するすべてのデータファイル，表領域，ロールバックセグメントなどを エクスポートファイルに書き出す。
PARFILE=XXX.dat なし この一覧にあるオプションをファイルにまとめて読み込ますことが出来ます。
そのファイルの名前を指定。
DIRECT=y NO ダイレクトパスでデータをエクスポートするか指定。
ダイレクトパスで指定した場合は処理速度が速くなる。
USERID=ユーザ/パス なし 実行するユーザの設定。
OWNER=XXX なし 書き出されるオブジェクトを含むOracleのアカウントリストを出力する。
BUFFER=10240 10240 データバッファサイズを指定。
RECORDLENGTH=XXXX OS依存 書き出されるレコード長で，バイト単位で表現される。
INCTYPE=XXXX なし 実行される増分エクスポートの種類。
RECORD=y YES Oracleに一部のデータディクショナリビュー内に書き出される増分エクスポートの種類を追随するように命令する。
この情報は増分エクスポートファイルからインポートを実行する際に使用される。
CONSISTENT=y NO Oracleに表相互間の一貫性を維持するように指定する。
これによって，エクスポートされる表がエクスポートの実行中に使用される場合でも表データのコピーがエクスポート開始時間付けで作成される。
STATISTICS=XXXX ESTIMETE SQL分析文をエクスポートファイルに書き出す。
IGNORE=y NO テーブル定義があっても無視してインポートする。
QUERY='WHERE XXX = YYYY' なし エクスポートするデータを選別する。
これを指定した場合はダイレクトパス（DIRECT）によるエクスポートは出来ない。
COMMIT=y NO Yにすると１行インポート毎にCOMMITが行われる。
そのため、RBSが大きくならない。
しかし、処理時間が肥大する。
デフォルトのNだとすべてのデータをインポートしてからCOMMITされる。
（２）インポート（データをdmpファイルから表に書き出す）
インポートを実行するコマンドはコマンドラインより
imp ユーザ/パス@リスナ パラメータ
です。
パラメータ一覧
パラメータ名 デフォルト値 説明
FILE=XXX.dmp EXPDAT.DMP インポートを行なう元ファイル名を指定。
複数ファイルに分けることも可能。
その際はFILESIZEオプションが必須。
拡張子を指定しないとデフォルトで.dmpが付けられる。
FILESIZE=600MB なし dmpファイルが分割されている場合に1ファイルの最大サイズを指定する。
LOG=XXX.log なし インポート中のログを書き出すファイル名指定
TABLES=('XXX','YYY') なし インポートする表名を指定。
CONSTRAINTS=y YES エクスポートファイルより宣言の完全性を再構築するために必要な
SQL文をインポートする。
例：主キーと参照文
GRANTS=y YES SQL権限をインポートする。
INDEXES=y YES インデックス（索引）をインポートする。
FULL=y NO Oracleに関するすべてのデータファイル，表領域，ロールバックセグメントなどを インポートする。
PARFILE=XXX.dat なし この一覧にあるオプションをファイルにまとめて読み込ますことが出来ます。
そのファイルの名前を指定。
USERID=ユーザ/パス なし 実行するユーザの設定。
OWNER=XXX なし 書き出されるオブジェクトを含むOracleのアカウントリストを出力する。
BUFFER=10240 10240 データバッファサイズを指定。
IGNORE=y NO インポート時にテーブルやインデックスなどの作成エラーを無視してインポートする。
COMMIT=y NO Yにすると１行インポート毎にCOMMITが行われる。
そのため、RBSが大きくならない。
しかし、処理時間が肥大する。
デフォルトのNだとすべてのデータをインポートしてからCOMMITされる。
TOP
５．ちょっとしたSQL達！！でも忘れちゃう。。。
◇ カラムの追加
◇ 既存のカラム名の変更
◇ 既存カラムの削除
◇ 既存のテーブル名の変更
□ カラムの長さ変更
□ 領域追加
□ テンポラリ再作成
□ 空きスペースの断片化を確認するには
□ ソートの見積もり
□ REDOログ見積もり
□ 権限付与
□ シャットダウン
□ PURGE(パージ)（10g限定 ゴミ箱機能）
□ SQL*Plusにおける各種設定
□ セッション切断について
□ MARGE（マージ）について（9i以降）
予定外に多かったのでこちらへ ⇒ ORACLEはっとした時に使えるSQL文
□ 新機能各種（10g以降）
10gについてはこちら ⇒ ORACLE10g新機能
TOP ６．パーティショニング（表領域のパーティション化）
表領域のパーティショニング（パーティション化）には以下の3種類が存在します。
(1)レンジ・パーティショニング
(2)ハッシュ・パーティショニング
(3)コンポジット・パーティショニング
それぞれを軽くですがご説明いたします。
前置きとして、「table_space_*」という表領域が4つ存在するものとします。
(1)レンジ・パーティショニング
あるキーを元に場合分けしていくのがレンジ・パーティショニングです。
下記のSQLをごらん下さい。
CREATE TABLE test
UNRECOVERABLE
COMPRESS
PARTITION BY RANGE(SNO)
( PARTITION ryoiki_1 VALUES LESS THAN (3500) TABLESPACE table_space_1,
PARTITION ryoiki_2 VALUES LESS THAN (7000) TABLESPACE table_space_2,
PARTITION ryoiki_3 VALUES LESS THAN (10500) TABLESPACE table_space_3,
PARTITION ryoiki_4 VALUES LESS THAN (14000) TABLESPACE table_space_4 )
AS SELECT * FROM moto_hyo;
これは、moto_hyo表のデータを元に、アーカイブを取らず（UNRECOVERABLE）領域を圧縮して（COMPRESS）
さらにパーティショニングしたtest表を作成しているところです。
レンジ・パーティショニングではキー列を指定して（ここではSNO）その数値がいくらかで
表領域を分けます。
この方法は時系列にデータが格納されるような場合に大きな効力を発揮するそうです。
今回は、
0 < SNO <= 3500 ⇒ table_space_1
3500 < SNO <= 7000 ⇒ table_space_2
7000 < SNO <= 10500 ⇒ table_space_3
10500 < SNO <= 14000 ⇒ table_space_4
という風に分けてあります。
レンジ・パーティショニングの利点は
・パーティショニングに使用したキー列をWHEREで指定してSELECTなりを行なうと
高速に検索が出来る（パーティション・プルーニング効果）
短所としては
・パーティション・プルーニング効果のためにWHEREに制限が出る。
（INやORを使用した文はパーティション・プルーニング効果が得られにくい）
・表領域を均等に使用するのがやや難しい。
と言う点です。
(2)ハッシュ・パーティショニング
あるキーをHASH関数にかけ、それで得られたHASH値を元にORACLEが適当に
表領域に割り振っていく方法がハッシュ・パーティショニングです。
下記のSQLをごらん下さい。
CREATE TABLE test
UNRECOVERABLE
COMPRESS
PARTITION BY HASH(SNO)
PARTITIONS 4
STORE IN (table_space_1,table_space_2,TABLESPACE table_space_3,TABLESPACE table_space_4)
AS SELECT * FROM moto_hyo;
先ほどと同じく、moto_hyo表のデータを元に、アーカイブを取らず（UNRECOVERABLE）領域を圧縮して（COMPRESS）さらにパーティショニングしたtest表を作成しているところです。
ハッシュ・パーティショニングではキー列を指定して（SNO）その後パーティション数を指定します（PARTITIONS 4）
指定された表領域（STORE以下）にキー列のHASH値を使いほぼ均等に分けます。
利点としては
・先ほどのレンジ・パーティショニングよりは作成する側にかかる負担は少なくなります。（適当に分けてくれるため）
・各表領域に均等にデータが配分されます。
・=、IN、IS NULLといった条件が指定された場合や、これらをORで連結した場合にパーティション・プルーニング効果を発揮できます。
欠点としては
・パーティション数が2 の累乗で無ければならない。
・値の範囲（BETWEEN等）でパーティション・プルーニングを行えない。
と言う事です。
(3)コンポジット・パーティショニング
コンポジット・パーティショニングは、レンジ・パーティショニングとハッシュ・パーティショニングを組み合わせたものです。
CREATE TABLE test
UNRECOVERABLE
COMPRESS
PARTITION BY RANGE(SNO)
SUBPARTITION BY HASH(SNO2)
SUBPARTITIONS 8
STORE IN (table_space_1,table_space_2,TABLESPACE table_space_3,TABLESPACE table_space_4)
( PARTITION ryoiki_1 VALUES LESS THAN (3500) ,
PARTITION ryoiki_2 VALUES LESS THAN (7000) ,
PARTITION ryoiki_3 VALUES LESS THAN (10500) ,
PARTITION ryoiki_4 VALUES LESS THAN (14000) )
AS SELECT * FROM moto_hyo;
まずレンジで4つにパーティショニングした後に、ハッシュで8つにサブパーティショニングします。
注意としてはコンポジット・パーティショニングでは、先にハッシュ・パーティショニングしてから
レンジ・パーティショニング することは出来ません。
コンポジット・パーティショニングではレンジ/ハッシュ両方でパーティション・プルーニングが可能な場合は、その相乗効果が発揮されます。
(4)パーティショニング後のテーブルのINDEX
パーティショニングしたテーブルにつけるインデックスの種類はローカルでなければいけません。
CREATE UNIQUE INDEX id_test ON test ( SNO )
LOCAL TABLESPACE table_space_idx_1 ;
また、パラレル・インデックス（parallel (degree パラレル数)句）を張ったり、
CREATE UNIQUE INDEX id_test ON test ( SNO )
LOCAL TABLESPACE table_space_idx_1 PARALLEL (DEGREE 8) ;
表に対しパラレルに検索することにより、大幅な検索処理時間短縮になるようです。
（実際にはまだ経験してませんが・・・）
select/*+ index (test,id_test) parallel_index (test,id_test,8) */ sno FROM test WHERE ...
TOP ７．マテリアライズド・ビュー
マテリアライズド・ビューには大きく2つの利用方法があます。
1つはリモート・データベース上に存在するデータを ローカル・データベース上に
定期的にコピーする目的で使用され、スナップショットとも呼ばれます。
もう1つは、ローカル・データベース上のデータの集計や結合処理を高速化するために使用されます。
ここでマテリアライズド・ビューではコピー元はディテール表、コピー先はコンテナ表と呼ばれます。
ディテール表 ⇒ 元表
コンテナ表 ⇒ 作成される表
マテリアライズド・ビューのディテール表のデータが更新された場合、
リフレッシュと呼ばれる処理によって、コンテナ表にその変更が反映されます。
リフレッシュには、毎回すべてのデータを入れ替える完全リフレッシュと、
ディテール表への 変更履歴を保持するマテリアライズド・ビュー・ログを利用し、
差分のみを反映する高速リフレッシュがあります。
①スナップショット
今回は高速リフレッシュを行なえるマテリアライズド・ビュー・ログを利用した方法を記述します。
(1) マテアライズド・ビュー・ログの作成 (コピー元インスタンス)
CREATE MATERIALIZED VIEW LOG ON ディテールtable;
(2) データベース・リンクの作成 (コピー先インスタンス)
CREATE DATABASE LINK リンク名
CONNECT TO SCOTT
IDENTIFIED BY TIGER USING 'schema';
(3) マテアライズド・ビューの作成 (コピー先インスタンス)
CREATE MATERIALIZED VIEW コンテナtable
REFRESH FAST
AS SELECT * FROM schema.ディテールtable@リンク名;
(4) リフレッシュ・グループの作成 (コピー先インスタンス)
EXECUTE DBMS_REFRESH.MAKE
(NAME=>'schema.GRP1',LIST=>'schema.コンテナtable',NEXT_DATE=>SYSDATE,INTERVAL=>'SYSDATE+1/24');
②結合を含むマテリアライズド・ビュー
結合を含むマテリアライズド・ビューを作成するには、マテリアライズド・ビューの
SELECTリストに各表の ROWIDを含める必要があります。
なお、今回の例ではON COMMITオプションを指定し、元表の更新がリアルタイムに
マテリアライズド・ビューへ反映されるように作成しています。
CREATE MATERIALIZED VIEW コンテナtable
REFRESH fast on commit
AS SELECT a.rowid,b.rowid,c.rowid, a.name,b.name,c.name
FROM ディテールtable1 a,ディテールtable2 b,ディテールtable3 c
WHERE a.key = b.key
AND b.key2 = c.key ;
これで、SQL内で結合して検索するよりもマテリアライズド・ビューを参照する事により
早く検索結果を取得する事が出来ます。
諸注意として、コンテナ表へのUPDATE・INSERT・DELETE等は行なえませんので・・・
TOP
８．各種確認コマンド
--空き領域の少ない（動的拡張が起こりそうな）セグメントの確認
select owner,table_name,blocks,empty_blocks
from dba_tables
where empty_blocks / (blocks + empty_blocks) < 0.1
and empty_blocks > 0;
このコマンドで表示されたテーブルは動的拡張が発生しそうです。
--エクステントの数の確認
SELECT
OWNER,SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, EXTENTS
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = '表領域名';
EXTENTSにてエクステントの数が確認できます。
--フラグメンテーション（分断化）の発生を確認
SELECT
TABLESPACE_NAME,
FILE_ID,
BLOCK_ID AS 開始ブロック,
BLOCK_ID + BLOCKS - 1 AS 終了ブロック,
BYTES AS サイズ,
BLOCKS AS ブロック数
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = '表領域名'
ORDER BY BLOCK_ID;
--行移行・行連鎖の発生を確認
select table_name,blocks,chain_cnt
from dba_tables
where owner='スキーマ名';
chain_cntにより行移行・行連鎖の行数を確認できます。
行移行・行連鎖の解消法は表はエクスポート・インポートでを行うことで解消されます。
--HWM空き領域が多いか確認
select table_name,num_rows,blocks,chain_cnt,avg_space
from dba_tables
where owner='スキーマ名';
num_rowsに対し、blocksが大きい場合にavg_spaceを確認することによりブロック内の空き領域が
多いかどうか確認できます。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
