Oracleラーニングノートの8(SQLの最適化方法のいくつか)

1.一般的なSQL文の最適化
1.1すべてのカラム名の代わりに「*」を使用しないことを推奨
SELECT文では「*」を使用してテーブルのすべてのカラム名をリストできますが、このような書き方はOracleシステムにとって解析的な動的な問題があります.Oracleシステムでは、データ辞書をクエリーして「*」をテーブルのすべてのカラム名に変換するため、システム時間がかかります.
1.2 DELETEの代わりにTRUNCATEを使用
DELETEを使用してテーブルのデータ行を削除する場合、OracleではUNDO tablespaceを使用してリカバリ情報を保存します.TRUNCATEでは使用しません.構文:

truncate [table|cluster] schema.[tale_name][cluster_name][drop|reuse storage]

truncateコマンドを使用してデータテーブルを動的に削除するストレージ・プロシージャを作成します.

create or replace procedure trun_table(table_deleted in varchar2) as --             
    cur_name integer;  --       
begin
    cur_name := dbms_sql.open_cursor;  --    
    dbms_sql.parse(cur_name,'truncate table '||table_deleted||' drop storage',
        dbms_sql.native);  --  truncate table table_deleted  
    dbms_sql.close_cursor(cur_name); --    
exception
    when others then
        dbms_sql.close_cursor(cur_name); --   ...
    raise;
end trun_table;
/

1.3整合性を確保するためにCOMMIT文を多用する
PL/SQLブロックでは、いくつかの相互接続DML文を1つのBEGIN...ENDブロックに書くことが多く、各ブロックのENDの前にCOMMIT文を使用することをお勧めします.これにより、オブジェクトDML文のタイムリーな提出を実現し、同時に事務所が占有するリソースを解放することができます.
1.4テーブルのクエリ回数を最小限に抑える
サブクエリを含むSQL文では、テーブルに対するクエリを減らすことに特に注意してください. 
1.5[NOT]INの代わりに[NOT]EXISTS
サブクエリでは、[NOT]IN句は内部のソートとマージを実行します.どの買番号でも、[NOT]INはサブクエリのテーブルに対して全テーブル遍歴を実行するため、最も非効率です.[NOT]INを使用しないように、次の2つのコードのように、外部ジョイン(outer joins)、[NOT]EXISTS句に変更できます.
1つ目は、非効率なNOT IN句:

select empno,ename from emp
    where empno not in(select deptno from dept where loc='BEIJING');

2つ目は、効率的なEXIST句です.

select empno,ename from emp
    where exists(select deptno from dept where loc='BEIJING');

[NOT]INの後ろにサブクエリがあり、クエリの結果セットが多い場合は[NOT]INは使用しないでください.ただし、[NOT]INの後ろの括弧内がリスト(列挙可能な数)またはサブクエリが満たす結果セットが少ない場合にも使用できます. 
2.テーブル接続の最適化
2.1ドライバテーブルの選択
ドライバテーブル(Driving Table)とは、最初にアクセスされたテーブル(通常はフルテーブルスキャンでアクセスされる)のことです.例:

select s.name,d.dept_name from department d,students s
    where d.dept_no=s.dept_no;

STUDENTSテーブルのDEPT_NO列にインデックスが作成され、DEPARTOMENTテーブルのDEPT_NO列にインデックスが作成されていないとします.DEPARTOMENTが最初にアクセスされる(FROMに続く)ため、DEPARTOMENTテーブルはクエリーのドライバテーブルとして使用されます.両方のテーブルにインデックスを作成する必要があります.そうしないと、音響効率が向上します.
2.2 WHERE句の接続順序
Oracleでは、WHERE句を下から順に解析しています.この原理により、表間の接続は他のWHERE条件の前に書かなければなりません.最大データ記録をフィルタリングする条件で書かなければならないWHERE句の末尾、つまり、表が接続操作を行う前に、フィルタリングされた記録データが多ければ多いほど良いです.
3.インデックスの合理的な使用
3.1索引列と式の選択

値の少ないキーワードや式については、標準的なBツリーインデックスを使用しないで、ビットマップインデックスの作成を検討してください.

頻繁に変更されるカラムをインデックスカラムとして使用しないでください.

インデックス列を選択する際には、そのインデックスによるINSERT、UPDATE、DELETE操作が価値があるかどうかも考慮する.

3.2複合インデックスの使用

create index complex_index on tb_test(column1,column2,column3);

 ここでのインデックスは複合インデックスで、クエリ文にWHERE...AND従属文を付けてから複合インデックスを使用できる3つの列が含まれています.
3.3大きいテーブルのスキャンを避ける
次の場合、Oracleではフル・テーブル・スキャンが使用されます.

クエリーされたテーブルにはインデックスがありません.

はすべてのローを返す必要があります.

like付きで「%」という文が全表スキャンです.

インデックス・プライマリ・カラムには条件付き制限がありますが、関数を使用すると、Oracleは全テーブル・スキャンを使用します.

にis null、is not null、または!=などの文が付いていると、テーブル全体がスキャンされます.たとえば、次のようになります.

select * from emp where job != 'MANAGER';