Oracle最適化メモ1
Oracle最適化メモ 『Oracle 10 g SQL開発ガイド』より抜粋
1.HARVINGではなくWHEREを使う
2.ユニオンではなくユニオンALLを使用する
3.INではなくEXISTSを使う
4.DISTINCTではなくEXISTSを使用します.(DISTINCTは重複行を禁止する前に検索した行を並べ替えるため)
解析計画を表示:
1.explin plan for YOUR_SQL
2.select*fromテーブル(dbmsplan.display);
1.DISTINCTをEXISTSで置換して提出する
複数表の情報(例えば、部門表と従業員表)を含むクエリーのペアは、SELECT文でDISTINCTを使用しないようにします.EXISTで置換することも考えられます.
たとえば:
効果がない:
SELECT
DISTINCT DEPT_NO,DEPT_NAME
FROM DEPT D,EMP E
WHERE D.DEPT_NO=E.DEPT_NO.
効率:
SELECT DEPT_NO,DEPT_NAME
FROM DEPT D
WHERE EXISTS(SELECT) 1
FROM EMP E
WHERE E E.DEPT＿NO=D.DEPT＿NO;
EXISTSはクエリをより迅速にします.RDBMSコアモジュールはサブクエリの条件を満たしたら、すぐに結果に戻ります.
2.最も効率的な削除重複記録方法(ROWIDを使用しているため)
DELETE FROM EMP a
WHERE a.ROWID>(SELECT MIN(b.ROWID)
FROM EMP b WHERE a.EMP惭NO=b.EMPNO);
－－select文で重複記録をフィルタリングする場合は、類似したものを使用できます.
SELECT*FROM EMP aWHERE a.ROWID = (SELECT MIN(b.ROWID)FROM EMP b WHERE a.EMP惭NO=b.EMPNO);
3.できるだけ多くCOMMITを使うことができれば、プログラムの中でできるだけ多くCOMMITを使うようにしてください.このようにプログラムの性能が向上して、需要もCOMMITから放出された資源によって減少します.
COMMITから放出されたリソース:
a. ロールバックでデータを復元するための情報です.
b. プログラム文で取得されたロック
c. redo log bufferの中の空間
d. ORACLEは上記3つのリソースのうち、内部費用を管理するために
·表の索引の注意点:
1.WHERE文はインデックスの使用を決定します.
*注意が必要なWHERE子文
いくつかのSELECT文の中のWHERE子文はインデックスを使用しません.ここにいくつかの例があります.
次の例では、‘!=’インデックスを使用しません.覚えてください.インデックスは表の中に何があるかだけ教えます.表の中に何があるか教えられません.
インデックスを使用しない:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMONT!=0;
インデックスを使う:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMONT>0
以下の例では、「𞓜𞓜」は文字接続関数であり、他の関数のようにインデックスを停止している.
インデックスを使用しない:
SELECT ACCOUNT_NAME,AMONT
FROM TRANSACTION
WHERE ACCOUNT_NAME 124; ACCOUNT_TYPE='AMEXA'
インデックスを使う:
SELECT ACCOUNT_NAME,AMONT
FROM TRANSACTION
WHERE ACCOUNT_NAME=‘AMEX’
AND ACCOUNT_TYPE='A'
下の例では、「+」は数学関数です.他の数学関数のようにインデックスを停止しました.
インデックスを使用しない:
SELECT ACCOUNT_NAME,AMONT
FROM TRANSACTION
WHERE AMONT+3000>5000;
インデックスを使う:
SELECT ACCOUNT_NAME,AMONT
FROM TRANSACTION
WHERE AMONT>2000;
以下の例では、同じインデックス列は互いに比較できません.これは全表スキャンを有効にします.
インデックスを使用しない:
SELECT ACCOUNT_NAME,AMONT
FROM TRANSACTION
WHERE ACCOUNT_NAME=NVL(:ACCuNAME,ACCOUNTNAME);
インデックスを使う:
SELECT ACCOUNT_NAME,AMONT
FROM TRANSACTION
WHERE ACCOUNT_NAME LIKE NVL(:ACCuNAME、');
訳者は:
関数を使用する列にインデックスを有効にするには、ORACLEの新しい機能が必要です.関数ベースのインデックスがいいかもしれません.
CREATE INDEX EMP_I ON EMP(UPER(ename))/*関数ベースの索引を作成します.
SELECT*FROM emp WHERE UPER=「BLACKSNAIL」索引*/を使用します
2.数値タイプの変換はインデックスを使用しない可能性があります.したがって、できるだけインデックスの列をNUMBERタイプと定義します.
現在、EMP_を想定していますTYPEは文字タイプの索引列です.
SELECT…FROM EMP
WHERE EMP_TYPE=123
この文はORACLEで変換されます.
SELECT…
FROM EMP
WHERE TO_NUMBER(EMPYPE)=123
内部で発生するタイプ変換のため、このインデックスは使用されません.
3.索引の列での計算を避ける.
WHERE子文では、索引列が関数の一部である場合、最適化器はインデックスを使用せずに全表スキャンを使用します.
例:
効果がない:
SELECT…
FROM DEPT
WHERE SAL*12>25000;
効率:
SELECT…
FROM DEPT
WHERE SAL > 25000/12
訳者は:
これは非常に実用的な規則です.必ず覚えてください.
4.DECODEの使用.
DECODEコパス
DECODE関数を使用して、同じレコードをスキャンしたり、同じテーブルに接続したりしないようにすることができます.
たとえば:
SELECT COUNT(*)、SUM(SAL)
FROM EMP
WHERE DEPT_NO=0020
AND ENAME LIKE‘SMITH’;
SELECT COUNT(*)、SUM(SAL)
FROM EMP
WHERE DEPT_NO=0030
AND ENAME LIKE‘SMITH’;
DECODE関数で効率的に同じ結果が得られます.
SELECT COUNT(DEPT＿NO,0020,'X',NULL)D 0020_COUNT
COUNT(DECODE)D 0030_COUNT
SUM(DECODE(DEPT＿NO,0020,SAL,NULL)D 0020_SAL
SUM(DECODE)D 0030_SAL
FROM EMP WHERE ENAME LIKE‘SMITH’;
同様に、DECODE関数は、GROUT BYとORDER BYのサブフレーズにも適用されます.
5.row_numberとover partition byの使用.
次の文の役割はmsisdnグループによって、create(u)を検索します.タイムの最新記録
select msisdn,imsi,user_タイプ reg_.タイプid,prov_id,create_時間
from(
select msisdn、imsi、useruutype、city uuid、provuuid、createmutime、
rowmunumber()over(partition by msisdn order by createm desc)r
from UDPAIRL UHIS
where Dupair＿between id and MIDDLE ID
)
where r=1
1.HARVINGではなくWHEREを使う
2.ユニオンではなくユニオンALLを使用する
3.INではなくEXISTSを使う
4.DISTINCTではなくEXISTSを使用します.(DISTINCTは重複行を禁止する前に検索した行を並べ替えるため)
解析計画を表示:
1.explin plan for YOUR_SQL
2.select*fromテーブル(dbmsplan.display);
1.DISTINCTをEXISTSで置換して提出する
複数表の情報(例えば、部門表と従業員表)を含むクエリーのペアは、SELECT文でDISTINCTを使用しないようにします.EXISTで置換することも考えられます.
たとえば:
効果がない:
SELECT
DISTINCT DEPT_NO,DEPT_NAME
FROM DEPT D,EMP E
WHERE D.DEPT_NO=E.DEPT_NO.
効率:
SELECT DEPT_NO,DEPT_NAME
FROM DEPT D
WHERE EXISTS(SELECT) 1
FROM EMP E
WHERE E E.DEPT＿NO=D.DEPT＿NO;
EXISTSはクエリをより迅速にします.RDBMSコアモジュールはサブクエリの条件を満たしたら、すぐに結果に戻ります.
2.最も効率的な削除重複記録方法(ROWIDを使用しているため)
DELETE FROM EMP a
WHERE a.ROWID>(SELECT MIN(b.ROWID)
FROM EMP b WHERE a.EMP惭NO=b.EMPNO);
－－select文で重複記録をフィルタリングする場合は、類似したものを使用できます.
SELECT*FROM EMP aWHERE a.ROWID = (SELECT MIN(b.ROWID)FROM EMP b WHERE a.EMP惭NO=b.EMPNO);
3.できるだけ多くCOMMITを使うことができれば、プログラムの中でできるだけ多くCOMMITを使うようにしてください.このようにプログラムの性能が向上して、需要もCOMMITから放出された資源によって減少します.
COMMITから放出されたリソース:
a. ロールバックでデータを復元するための情報です.
b. プログラム文で取得されたロック
c. redo log bufferの中の空間
d. ORACLEは上記3つのリソースのうち、内部費用を管理するために
·表の索引の注意点:
1.WHERE文はインデックスの使用を決定します.
*注意が必要なWHERE子文
いくつかのSELECT文の中のWHERE子文はインデックスを使用しません.ここにいくつかの例があります.
次の例では、‘!=’インデックスを使用しません.覚えてください.インデックスは表の中に何があるかだけ教えます.表の中に何があるか教えられません.
インデックスを使用しない:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMONT!=0;
インデックスを使う:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMONT>0
以下の例では、「𞓜𞓜」は文字接続関数であり、他の関数のようにインデックスを停止している.
インデックスを使用しない:
SELECT ACCOUNT_NAME,AMONT
FROM TRANSACTION
WHERE ACCOUNT_NAME 124; ACCOUNT_TYPE='AMEXA'
インデックスを使う:
SELECT ACCOUNT_NAME,AMONT
FROM TRANSACTION
WHERE ACCOUNT_NAME=‘AMEX’
AND ACCOUNT_TYPE='A'
下の例では、「+」は数学関数です.他の数学関数のようにインデックスを停止しました.
インデックスを使用しない:
SELECT ACCOUNT_NAME,AMONT
FROM TRANSACTION
WHERE AMONT+3000>5000;
インデックスを使う:
SELECT ACCOUNT_NAME,AMONT
FROM TRANSACTION
WHERE AMONT>2000;
以下の例では、同じインデックス列は互いに比較できません.これは全表スキャンを有効にします.
インデックスを使用しない:
SELECT ACCOUNT_NAME,AMONT
FROM TRANSACTION
WHERE ACCOUNT_NAME=NVL(:ACCuNAME,ACCOUNTNAME);
インデックスを使う:
SELECT ACCOUNT_NAME,AMONT
FROM TRANSACTION
WHERE ACCOUNT_NAME LIKE NVL(:ACCuNAME、');
訳者は:
関数を使用する列にインデックスを有効にするには、ORACLEの新しい機能が必要です.関数ベースのインデックスがいいかもしれません.
CREATE INDEX EMP_I ON EMP(UPER(ename))/*関数ベースの索引を作成します.
SELECT*FROM emp WHERE UPER=「BLACKSNAIL」索引*/を使用します
2.数値タイプの変換はインデックスを使用しない可能性があります.したがって、できるだけインデックスの列をNUMBERタイプと定義します.
現在、EMP_を想定していますTYPEは文字タイプの索引列です.
SELECT…FROM EMP
WHERE EMP_TYPE=123
この文はORACLEで変換されます.
SELECT…
FROM EMP
WHERE TO_NUMBER(EMPYPE)=123
内部で発生するタイプ変換のため、このインデックスは使用されません.
3.索引の列での計算を避ける.
WHERE子文では、索引列が関数の一部である場合、最適化器はインデックスを使用せずに全表スキャンを使用します.
例:
効果がない:
SELECT…
FROM DEPT
WHERE SAL*12>25000;
効率:
SELECT…
FROM DEPT
WHERE SAL > 25000/12
訳者は:
これは非常に実用的な規則です.必ず覚えてください.
4.DECODEの使用.
DECODEコパス
expr to eachsearch value one byone.Ifexpr is equal to asearch,then Oracle Database returns the corerectingresult.Ifのmatch is found,then Oracle returnsdefault.Ifdefault is omitted,then Oracle returns null.DECODE関数を使用して、同じレコードをスキャンしたり、同じテーブルに接続したりしないようにすることができます.
たとえば:
SELECT COUNT(*)、SUM(SAL)
FROM EMP
WHERE DEPT_NO=0020
AND ENAME LIKE‘SMITH’;
SELECT COUNT(*)、SUM(SAL)
FROM EMP
WHERE DEPT_NO=0030
AND ENAME LIKE‘SMITH’;
DECODE関数で効率的に同じ結果が得られます.
SELECT COUNT(DEPT＿NO,0020,'X',NULL)D 0020_COUNT
COUNT(DECODE)D 0030_COUNT
SUM(DECODE(DEPT＿NO,0020,SAL,NULL)D 0020_SAL
SUM(DECODE)D 0030_SAL
FROM EMP WHERE ENAME LIKE‘SMITH’;
同様に、DECODE関数は、GROUT BYとORDER BYのサブフレーズにも適用されます.
5.row_numberとover partition byの使用.
次の文の役割はmsisdnグループによって、create(u)を検索します.タイムの最新記録
select msisdn,imsi,user_タイプ reg_.タイプid,prov_id,create_時間
from(
select msisdn、imsi、useruutype、city uuid、provuuid、createmutime、
rowmunumber()over(partition by msisdn order by createm desc)r
from UDPAIRL UHIS
where Dupair＿between id and MIDDLE ID
)
where r=1