2018-07-20 oracle最適化:全テーブルスキャンを回避
6279 ワード
1.返されるローには、where句がないという制限はありません.
2.データ・テーブルがインデックス・プライマリ・カラムに対応するローに対して条件を定義していない
たとえば、City-State-Zipカラムに3つの複合インデックスが作成されている場合、Stateカラムはインデックスのプライマリカラムではないため、Stateカラムに限定された条件でのみこのインデックスを使用できません.
3.インデックスのプライマリ・カラムには制限がありますが、条件式で次の式を使用すると、インデックスが無効になり、テーブル全体がスキャンされます.
(1)where句でフィールドを関数、式で操作すると、エンジンがインデックスの使用を放棄して全テーブルスキャンを行うことになります.
Demo:
(2)クエリーフィールドis nullでインデックスが失効し、全テーブルスキャンを引き起こす.
Where City is nullまたは、where City is not null、
解決方法:
SQLの文法の中でNULLを使うのは多くの面倒があって、最も良いインデックスの列はすべてNOT NULLです;is nullの場合、結合インデックス、nvl(フィールド、0)を確立することができ、テーブルとインデックスanalyseの後、is nullクエリ時にインデックス検索を再有効にすることができますが、効率はまだ肯定的ではありません.is not nullではインデックスは使用されません.一般的なデータ量の大きいテーブルはis nullでクエリーしないでください.
numにデフォルト値0を設定して、テーブルのnum列にnull値がないことを確認し、クエリーを実行できます.
(3)クエリ条件に等しくないオペレータ(<>、!=)を使用すると、インデックスが制限され、テーブル全体がスキャンされます.
解決方法:オペレータに等しくないものをorに変更することで、インデックスを使用して全テーブルスキャンを回避できます.たとえば、
(4)インデックスのプライマリ・カラムには制限がありますが、like操作および値を使用して「%」で開始するか、値が付与変数です.
例:
解決方法:まず、ファジイクエリーをできるだけ避け、ビジネスで必ずファジイクエリーを使用する必要がある場合は、少なくとも全ファジイクエリーを使用しないことを保証します.右ファジイクエリー、すなわちlike'...%では、インデックスが使用されます.左ファジイlike'%...'インデックスを直接使用することはできませんが、reverse+function indexの形式でlike'...%に変更できます.フルブラーは最適化できないので、必要なら検索エンジンを使うことを考えます.データベース・サーバの負荷を低減するために、データベース・ファジイ・クエリーを最小限に抑えることができます.
4.or文の不適切な使用は全表スキャンを引き起こす
理由:where句で比較される2つの条件.1つはインデックスがあり、1つはインデックスがなく、orを使用すると全テーブルスキャンが発生します.例えば、where A=:1 or B=:2、Aにインデックスがあり、Bにインデックスがない場合、B=:2を比較すると全テーブルスキャンが再開されます
5.ファジイクエリの効率が低い:
理由:like自体は効率が低く、クエリー条件がlikeを使用することをできるだけ避けるべきである.like'%...%'について(フルブラー)このような条件では、インデックスを使用することができず、フルテーブルスキャンの自然効率が低くなります.また、マッチングアルゴリズムの関係で、ブラークエリのフィールド長が大きいほど、ブラークエリの効率が低くなります.
解決方法:まず、ファジイクエリーをできるだけ避け、ビジネスで必ずファジイクエリーを使用する必要がある場合は、少なくとも全ファジイクエリーを使用しないことを保証します.右ファジイクエリー、すなわちlike'...%では、インデックスが使用されます.左ぼかしlike
‘%...’インデックスを直接使用することはできませんが、reverse+function indexの形式でlike'...%に変更できます.フルブラーは最適化できないので、必要なら検索エンジンを使うことを考えます.データベース・サーバの負荷を低減するために、データベース・ファジイ・クエリーを最小限に抑えることができます.
6.クエリ条件にis nullを含むselect文の実行が遅い
理由:Oracleでは、クエリーフィールドis nullで単一インデックスが無効になり、全テーブルスキャンが発生します.
解决方法:SQLの文法の中でNULLを使うのは多くの面倒があって、最も良いインデックスの列はすべてNOT NULLです;is nullの場合、結合インデックス、nvl(フィールド、0)を確立することができ、テーブルとインデックスanalyseの後、is nullクエリ時にインデックス検索を再有効にすることができますが、効率はまだ肯定的ではありません.is not nullではインデックスは使用されません.一般的なデータ量の大きいテーブルはis nullでクエリーしないでください.
7.クエリー条件でオペレータ(<>、!=)に等しくないselect文を使用した実行が遅い
理由:SQLでは、オペレータに等しくないとインデックスが制限され、比較フィールドにインデックスがある場合でも、テーブル全体がスキャンされます.
解決方法:オペレータに等しくないものをorに変更することで、インデックスを使用して全テーブルスキャンを回避できます.たとえばcolumn<>’aaa’をcolumn’aaa’に変更するとインデックスが使用できます.
8.結合インデックスを使用して、クエリー条件に先頭列がない場合、インデックスが機能せず、テーブル全体のスキャンを引き起こします.
ただし、Oracle 9 iからは、インデックスのプリアンブルがWHERE句に表示されない場合でも、オプティマイザが結合インデックスを使用できるように、インデックスジャンプスキャンの機能が導入されています.例えば、create index skip 1 on emp 5(job,empno);全インデックススキャンselect count()from emp 5 where empno=7900;インデックスジャンプスキャンselect/+index(emp 5 skip 1)/count()from emp 5 where empno=7900;前者はフルテーブルスキャンで、後者はコンビネーションインデックスを使用します.
9.or文の不適切な使用は、全テーブルスキャンを引き起こす
理由:where句で比較される2つの条件.1つはインデックスがあり、1つはインデックスがなく、orを使用すると全テーブルスキャンが発生します.たとえば、where A=:1 or B=:2、Aにインデックスがあり、Bにインデックスがない場合、B=:2を比較すると全テーブルスキャンが再開されます.
10.インデックスを結合します.ソートするときは、インデックス内の各カラムの順序でソートする必要があります.インデックス内のカラムが1つしかソートされていない場合でも、ソートのパフォーマンスが低下します.例えば、create index skip 1 on emp 5(job,empno,date);select job,empno from emp5 where job=’manager’and empno=’10’order by job,empno,date desc;実際にはjob=’manager’and empno=’10’の条件に合致するレコードをクエリーしてdate降順に並べただけですが、order by date descと書くと性能が悪いです.
11.Update文では、1、2つのフィールドのみを変更し、Updateのすべてのフィールドを使用しないでください.そうしないと、頻繁に呼び出されると、パフォーマンスが大幅に消費され、ログが大量に発生します.
12.複数のビッグデータ量のテーブルJOINについては、先にページを分けてからJOINしなければならない.そうしないと、論理読みが高く、性能が悪い.
13.select count(*) from table;このように条件のないcountは全表スキャンを引き起こし、ビジネス上の意味がなく、必ず根絶しなければならない.
14.sqlのwhere条件は、where column=:1などの変数をバインドし、where column=‘aaa’と書かないでください.これにより、実行するたびに再分析し、CPUとメモリリソースを浪費します.
15.inオペレータを使用しないでください.これにより、データベースは全テーブルスキャンを行います.
推薦案:業務密集のSQLの中でできるだけINオペレータを採用しない
16.not in not inを使用してもインデックスは作成されません
推奨案:not existsまたは(外部連結+判断が空)で代用
17>>および<オペレータ(オペレータより大きいか小さいか)
オペレータより大きいか小さいかは、インデックスがあるため調整する必要はありませんが、テーブルに100万レコード、数値フィールドA、30万レコードのA=0、30万レコードのA=1、39万レコードのA=2、1万レコードのA=3など、最適化できる場合があります.A>2を実行するとA>=3と大きく異なります.A>2の場合、ORACLEは2のレコードインデックスを見つけて比較し、A>=3の場合、ORACLEは=3のレコードインデックスを直接見つけます.
18.UNIONオペレータ
UNIONは、テーブルリンクを行った後に重複するレコードをフィルタリングするので、テーブルリンク後に発生した結果セットをソート演算し、重複するレコードを削除して結果を返す.実際のほとんどのアプリケーションでは重複するレコードは生成されず、最も一般的なのはプロセステーブルと履歴テーブルUNIONである.次のようになります.
このSQLは、実行時に2つのテーブルの結果を取り出し、並べ替えスペースで並べ替えて重複したレコードを削除し、最後に結果セットを返します.テーブルのデータ量が大きいとディスクで並べ替えられる可能性があります.推奨方法:UNIOALLオペレータをUNIOALLの代わりに使用します.UNIOALL操作は2つの結果を簡単にマージして返すだけです.
19.WHERE後の条件順の影響
WHERE句の後の条件順序は、ビッグデータ量テーブルのクエリに直接影響します.
上の2つのSQLのdy_djおよびxh_bzはどちらのフィールドもインデックスが行われていないので、実行時は全テーブルスキャンで、最初のSQLのdy_dj='1 KV以下の'条件は、記録セット内で99%の割合であり、xh_bz=1の比率は0.5%にとどまり、1番目のSQLを行うときは99%のレコードをdy_djおよびxh_bzの比較は、2番目のSQLでは0.5%のレコードがdy_djおよびxh_bzの比較から,2番目のSQLのCPU占有率は1番目より有意に低かった.
20.クエリー・テーブルの順序の影響
FROMの後のテーブルのリスト順はSQLの実行性能に影響し、インデックスやORACLEがテーブルを統計分析していない場合はORACLEがテーブルの出現順にリンクするため、テーブルの順序が間違っているとサーバリソースを十分に消費するデータ交差が生じる.(注:テーブルを統計的に分析すると、ORACLEは自動的に小さなテーブルのリンクを先進化し、大きなテーブルのリンクを行う)
2.データ・テーブルがインデックス・プライマリ・カラムに対応するローに対して条件を定義していない
たとえば、City-State-Zipカラムに3つの複合インデックスが作成されている場合、Stateカラムはインデックスのプライマリカラムではないため、Stateカラムに限定された条件でのみこのインデックスを使用できません.
3.インデックスのプライマリ・カラムには制限がありますが、条件式で次の式を使用すると、インデックスが無効になり、テーブル全体がスキャンされます.
(1)where句でフィールドを関数、式で操作すると、エンジンがインデックスの使用を放棄して全テーブルスキャンを行うことになります.
Demo:
where upper(city)='TokYo' City || 'X' like 'TOKYO%',
select id from t where num/2=100 : select id from t where num=100*2
select * from emp where to_char(hire_date,'yyyymmdd')='20080411' ( )
select * from emp where hire_date = to_char('20080411','yyyymmdd') ( )
(2)クエリーフィールドis nullでインデックスが失効し、全テーブルスキャンを引き起こす.
Where City is nullまたは、where City is not null、
解決方法:
SQLの文法の中でNULLを使うのは多くの面倒があって、最も良いインデックスの列はすべてNOT NULLです;is nullの場合、結合インデックス、nvl(フィールド、0)を確立することができ、テーブルとインデックスanalyseの後、is nullクエリ時にインデックス検索を再有効にすることができますが、効率はまだ肯定的ではありません.is not nullではインデックスは使用されません.一般的なデータ量の大きいテーブルはis nullでクエリーしないでください.
select id from t where num is null numにデフォルト値0を設定して、テーブルのnum列にnull値がないことを確認し、クエリーを実行できます.
select id from t where num=0 (3)クエリ条件に等しくないオペレータ(<>、!=)を使用すると、インデックスが制限され、テーブル全体がスキャンされます.
Where city!='TOKYO'. 解決方法:オペレータに等しくないものをorに変更することで、インデックスを使用して全テーブルスキャンを回避できます.たとえば、
column<>’aaa’をcolumn’aaa’に変更すると、インデックスを使用できます.(4)インデックスのプライマリ・カラムには制限がありますが、like操作および値を使用して「%」で開始するか、値が付与変数です.
例:
where City like '%YOK%'
where City like: City_bind_Variable xl_rao
select * from emp where name like '%A' ( )
select * from emp where name like 'A%' ( )
解決方法:まず、ファジイクエリーをできるだけ避け、ビジネスで必ずファジイクエリーを使用する必要がある場合は、少なくとも全ファジイクエリーを使用しないことを保証します.右ファジイクエリー、すなわちlike'...%では、インデックスが使用されます.左ファジイlike'%...'インデックスを直接使用することはできませんが、reverse+function indexの形式でlike'...%に変更できます.フルブラーは最適化できないので、必要なら検索エンジンを使うことを考えます.データベース・サーバの負荷を低減するために、データベース・ファジイ・クエリーを最小限に抑えることができます.
4.or文の不適切な使用は全表スキャンを引き起こす
理由:where句で比較される2つの条件.1つはインデックスがあり、1つはインデックスがなく、orを使用すると全テーブルスキャンが発生します.例えば、where A=:1 or B=:2、Aにインデックスがあり、Bにインデックスがない場合、B=:2を比較すると全テーブルスキャンが再開されます
5.ファジイクエリの効率が低い:
理由:like自体は効率が低く、クエリー条件がlikeを使用することをできるだけ避けるべきである.like'%...%'について(フルブラー)このような条件では、インデックスを使用することができず、フルテーブルスキャンの自然効率が低くなります.また、マッチングアルゴリズムの関係で、ブラークエリのフィールド長が大きいほど、ブラークエリの効率が低くなります.
解決方法:まず、ファジイクエリーをできるだけ避け、ビジネスで必ずファジイクエリーを使用する必要がある場合は、少なくとも全ファジイクエリーを使用しないことを保証します.右ファジイクエリー、すなわちlike'...%では、インデックスが使用されます.左ぼかしlike
‘%...’インデックスを直接使用することはできませんが、reverse+function indexの形式でlike'...%に変更できます.フルブラーは最適化できないので、必要なら検索エンジンを使うことを考えます.データベース・サーバの負荷を低減するために、データベース・ファジイ・クエリーを最小限に抑えることができます.
6.クエリ条件にis nullを含むselect文の実行が遅い
理由:Oracleでは、クエリーフィールドis nullで単一インデックスが無効になり、全テーブルスキャンが発生します.
解决方法:SQLの文法の中でNULLを使うのは多くの面倒があって、最も良いインデックスの列はすべてNOT NULLです;is nullの場合、結合インデックス、nvl(フィールド、0)を確立することができ、テーブルとインデックスanalyseの後、is nullクエリ時にインデックス検索を再有効にすることができますが、効率はまだ肯定的ではありません.is not nullではインデックスは使用されません.一般的なデータ量の大きいテーブルはis nullでクエリーしないでください.
7.クエリー条件でオペレータ(<>、!=)に等しくないselect文を使用した実行が遅い
理由:SQLでは、オペレータに等しくないとインデックスが制限され、比較フィールドにインデックスがある場合でも、テーブル全体がスキャンされます.
解決方法:オペレータに等しくないものをorに変更することで、インデックスを使用して全テーブルスキャンを回避できます.たとえばcolumn<>’aaa’をcolumn’aaa’に変更するとインデックスが使用できます.
8.結合インデックスを使用して、クエリー条件に先頭列がない場合、インデックスが機能せず、テーブル全体のスキャンを引き起こします.
ただし、Oracle 9 iからは、インデックスのプリアンブルがWHERE句に表示されない場合でも、オプティマイザが結合インデックスを使用できるように、インデックスジャンプスキャンの機能が導入されています.例えば、create index skip 1 on emp 5(job,empno);全インデックススキャンselect count()from emp 5 where empno=7900;インデックスジャンプスキャンselect/+index(emp 5 skip 1)/count()from emp 5 where empno=7900;前者はフルテーブルスキャンで、後者はコンビネーションインデックスを使用します.
9.or文の不適切な使用は、全テーブルスキャンを引き起こす
理由:where句で比較される2つの条件.1つはインデックスがあり、1つはインデックスがなく、orを使用すると全テーブルスキャンが発生します.たとえば、where A=:1 or B=:2、Aにインデックスがあり、Bにインデックスがない場合、B=:2を比較すると全テーブルスキャンが再開されます.
10.インデックスを結合します.ソートするときは、インデックス内の各カラムの順序でソートする必要があります.インデックス内のカラムが1つしかソートされていない場合でも、ソートのパフォーマンスが低下します.例えば、create index skip 1 on emp 5(job,empno,date);select job,empno from emp5 where job=’manager’and empno=’10’order by job,empno,date desc;実際にはjob=’manager’and empno=’10’の条件に合致するレコードをクエリーしてdate降順に並べただけですが、order by date descと書くと性能が悪いです.
11.Update文では、1、2つのフィールドのみを変更し、Updateのすべてのフィールドを使用しないでください.そうしないと、頻繁に呼び出されると、パフォーマンスが大幅に消費され、ログが大量に発生します.
12.複数のビッグデータ量のテーブルJOINについては、先にページを分けてからJOINしなければならない.そうしないと、論理読みが高く、性能が悪い.
13.select count(*) from table;このように条件のないcountは全表スキャンを引き起こし、ビジネス上の意味がなく、必ず根絶しなければならない.
14.sqlのwhere条件は、where column=:1などの変数をバインドし、where column=‘aaa’と書かないでください.これにより、実行するたびに再分析し、CPUとメモリリソースを浪費します.
15.inオペレータを使用しないでください.これにより、データベースは全テーブルスキャンを行います.
推薦案:業務密集のSQLの中でできるだけINオペレータを採用しない
16.not in not inを使用してもインデックスは作成されません
推奨案:not existsまたは(外部連結+判断が空)で代用
17>>および<オペレータ(オペレータより大きいか小さいか)
オペレータより大きいか小さいかは、インデックスがあるため調整する必要はありませんが、テーブルに100万レコード、数値フィールドA、30万レコードのA=0、30万レコードのA=1、39万レコードのA=2、1万レコードのA=3など、最適化できる場合があります.A>2を実行するとA>=3と大きく異なります.A>2の場合、ORACLEは2のレコードインデックスを見つけて比較し、A>=3の場合、ORACLEは=3のレコードインデックスを直接見つけます.
18.UNIONオペレータ
UNIONは、テーブルリンクを行った後に重複するレコードをフィルタリングするので、テーブルリンク後に発生した結果セットをソート演算し、重複するレコードを削除して結果を返す.実際のほとんどのアプリケーションでは重複するレコードは生成されず、最も一般的なのはプロセステーブルと履歴テーブルUNIONである.次のようになります.
select * from gc_dfys
union
select * from ls_jg_dfys
このSQLは、実行時に2つのテーブルの結果を取り出し、並べ替えスペースで並べ替えて重複したレコードを削除し、最後に結果セットを返します.テーブルのデータ量が大きいとディスクで並べ替えられる可能性があります.推奨方法:UNIOALLオペレータをUNIOALLの代わりに使用します.UNIOALL操作は2つの結果を簡単にマージして返すだけです.
19.WHERE後の条件順の影響
WHERE句の後の条件順序は、ビッグデータ量テーブルのクエリに直接影響します.
Select * from zl_yhjbqk where dy_dj = '1K ' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1K '
上の2つのSQLのdy_djおよびxh_bzはどちらのフィールドもインデックスが行われていないので、実行時は全テーブルスキャンで、最初のSQLのdy_dj='1 KV以下の'条件は、記録セット内で99%の割合であり、xh_bz=1の比率は0.5%にとどまり、1番目のSQLを行うときは99%のレコードをdy_djおよびxh_bzの比較は、2番目のSQLでは0.5%のレコードがdy_djおよびxh_bzの比較から,2番目のSQLのCPU占有率は1番目より有意に低かった.
20.クエリー・テーブルの順序の影響
FROMの後のテーブルのリスト順はSQLの実行性能に影響し、インデックスやORACLEがテーブルを統計分析していない場合はORACLEがテーブルの出現順にリンクするため、テーブルの順序が間違っているとサーバリソースを十分に消費するデータ交差が生じる.(注:テーブルを統計的に分析すると、ORACLEは自動的に小さなテーブルのリンクを先進化し、大きなテーブルのリンクを行う)