ORACLE実行計画の基本的な概念

ORACLE実行計画の基本概念(1)

 
一.関連概念
Rowidの概念:rowidは偽の列であり、偽の列である以上、この列はユーザー定義ではなく、システム自身が加えたものである.各テーブルにはrowidのダミー列がありますが、テーブルにはROWID列の値は物理的に格納されません.ただし、他のカラムのように使用できますが、変更されたカラムを削除したり、そのカラムの値を変更したり、挿入したりすることはできません.ローのデータがデータベースに挿入されると、rowidはローのライフサイクル内で一意です.つまり、ローがロー移行を生成してもローのrowidは変わりません.
Recursive SQLコンセプト:ユーザーが発行したsql文を実行するために、Oracleは追加の文を実行する必要があります.これらの追加の文を'rcursive calls'または'rcursive SQL statements'と呼びます.DDL文が発行されると、ORACLEは常にrecursive SQL文を暗黙的に発行し、データ辞書情報を変更して、ユーザーがDDL文を正常に実行できるようにします.必要なデータディクショナリ情報が共有メモリにない場合、データディクショナリ情報がハードディスク(HDD)からメモリに読み込まれるRecursive callsが頻繁に発生します.ユーザーはこれらのrecursive SQL文の実行状況に関心を持っていません.必要に応じて、ORACLEは自動的に内部でこれらの文を実行します.もちろんDML文とSELECTはrecursive SQLを引き起こす可能性があります.簡単に言えば、トリガをrecursive SQLと見なすことができます.
Row Source(行ソース):クエリーで使用され、前の操作で返された条件に合致する行の集合、すなわちテーブルのすべての行データの集合であってもよい.テーブルの一部のローデータの集合であってもよい.前の2つのrow sourceを接続操作(join接続など)した行データの集合であってもよい.
Predicate(述語):クエリのWHERE制限条件
Driving Table(ドライバテーブル):外層テーブル(OUTER TABLE)とも呼ばれます.この概念はHASHとの接続をネストするために用いられる.row sourceがより多くのローデータを返すと、後続のすべての操作に悪影響を及ぼします.なお、ここではドライバテーブルと訳すが、実際にはドライバラインソース(driving row source)と訳すのがより正確である.一般的には、クエリーの制約条件を適用した後、少ないローソースのテーブルをドライバテーブルとして返すので、大きなテーブルがWHERE条件に制約条件(等値制限など)がある場合、その大きなテーブルがドライバテーブルとして適切であるため、小さなテーブルだけがドライバテーブルとして使用できるわけではないが、正確にはクエリーの制約条件を適用した後、少ないローソースのテーブルをドライバテーブルとして返すべきである.実行計画では、上のrow sourceのために、後で具体的な説明をします.後述する説明では、このテーブルを接続動作のrow source 1と呼ぶのが一般的である.
Probed Table(プローブテーブル):内層テーブル(INNER TABLE)とも呼ばれるテーブル.ドライバ・テーブルから特定のローのデータを取得した後、接続条件に合致するローを探します.したがって、テーブルは大きなテーブル(実際には大きなrow sourceを返すテーブル)であり、対応するカラムにインデックスがある必要があります.後述する説明では、このテーブルを接続動作のrow source 2と呼ぶのが一般的である.
コンポジットインデックス(concatenated index):create index idx_のような複数の列からなるインデックスemp on emp(col 1,col 2,col 3,......)ではidx_Empインデックスは、コンビネーションインデックスです.コンビネーションインデックスには、上記の例ではcol 1列がブート列であるブート列(leading column)という重要な概念があります.クエリーを行うときに「where col 1=?」を使用できます.「where col 1=?and col2 = ?”,このような制限条件ではインデックスが使用されますが、「where col 2=?」クエリはインデックスを使用しません.したがって、制約条件にパイロット列が含まれている場合、この制約条件は組合せインデックスを使用します.
選択可能(selectivity):次の一意のキーの数とテーブル内のロー数を比較すると、カラムの選択性を判断できます.カラムの「ユニーク・キーの数/テーブル内のロー数」の比が1に近いほど、カラムの選択性が高くなり、インデックスの作成に適しているほど、同じインデックスの選択性も高くなります.選択性の高いカラムでクエリーを行う場合、返されるデータは少なく、インデックス・クエリーを使用するのに適しています.二.oracleアクセスデータのアクセス方法
1)フルテーブルスキャン(Full Table Scans,FTS)
フルテーブルスキャンを実現するために、Oracleはテーブル内のすべてのローを読み取り、各ローが文のWHERE制約条件を満たしているかどうかを確認します.1つのマルチブロックを読み取るのではなく、1回のI/Oでマルチブロックデータブロック(db_block_multiblock_read_countパラメータ設定)を読み取ることができます.これにより、I/Oの合計回数が大幅に減少し、システムのスループットが向上します.したがって、マルチブロック読み取りによりフルテーブルスキャンを非常に効率的に実現できます.また、マルチブロックリード操作は、全テーブルスキャンの場合にのみ使用できます.このアクセスモードでは、各データブロックは1回のみ読み出される.FTSを使用する前提条件:比較的大きなテーブルでは、データを取り出すことが多く、総量の5%から10%を超える場合、またはパラレルクエリー機能を使用したい場合を除き、全テーブルスキャンは推奨されません.全表スキャンを使用した例:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL> explain plan for select * from dual;
Query Plan
-----------------------------------------
SELECT STATEMENT[CHOOSE] Cost=
TABLE ACCESS FULL DUAL
 

2)ROWIDによるテーブルアクセス(Table Access by ROWIDまたはrowid lookup)
ローのROWIDは、ローが存在するデータ・ファイル、データ・ブロック、およびローがそのブロック内にある場所を示します.したがって、ROWIDによるデータへのアクセスは、ターゲット・データに迅速にナビゲートでき、Oracleが単行データにアクセスする最も速い方法です.このアクセス方法では、複数の読み出し動作は使用されず、1回のI/Oで1つのデータブロックしか読み込めません.インデックスを使用してデータをクエリーするなど、実行計画でアクセス方法をよく見ます.
ROWIDアクセスを使用する方法:

SQL> explain plan for select * from dept where rowid = 'AAAAyGAADAAAAATAAF';
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID DEPT [ANALYZED]

3)インデックススキャン(Index Scanまたはindex lookup)
まず、indexによってデータに対応するrowid値(一意でないインデックスに対して複数のrowid値を返す可能性がある)を検索し、rowidに基づいてテーブルから直接具体的なデータを取得します.この検索方法は、インデックススキャンまたはインデックス検索(index lookup)と呼ばれます.1つのrowidは、対応するデータブロックが1回のi/oで得られる行データを一意に表し、この場合、このi/oは1つのデータベースブロックのみを読み出す.
インデックスには、各インデックスの値に加えて、この値を持つローに対応するROWID値も格納されます.インデックススキャンは、(1)インデックスをスキャンして対応するrowid値を得る2ステップから構成することができる.(2)見つかったrowidによりテーブルから具体的なデータを読み出す.ステップごとに1回のI/Oですが、インデックスについては、常に使用されているため、ほとんどがメモリにCACHEされているため、ステップ1のI/Oは常に論理I/Oであり、つまりデータはメモリから得ることができます.しかし、第2ステップでは、テーブルが比較的大きいと、そのデータがメモリ全体に存在することは不可能であるため、そのI/Oは物理I/Oである可能性が高い.これは機械的な操作であり、論理I/Oに比べて、極めて時間がかかる.したがって、複数のテーブルでインデックス・スキャンを行う場合、取得されたデータが総量の5%～10%を超えると、インデックス・スキャンを使用すると効率が大幅に低下します.次のようになります.

SQL> explain plan for select empno, ename from emp where empno=10;
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED] 
INDEX UNIQUE SCAN EMP_I1

ただし、クエリのデータがすべてインデックス内にある場合は、第2ステップの操作を回避し、不要なI/Oを回避できます.この場合、インデックススキャンで取り出したデータが多くても効率的です

SQL> explain plan for select empno from emp where empno=10;--    empno  
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
INDEX UNIQUE SCAN EMP_I1
 

さらに、sql文でインデックス列をソートする場合、インデックスは事前にソートされているため、実行計画でインデックス列をソートする必要はありません.

SQL> explain plan for select empno, ename from emp
where empno > 7876 order by empno;
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT[CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED] 
INDEX RANGE SCAN EMP_I1 [ANALYZED] 

この例では、インデックスがソートされているため、インデックスの順序で条件に合致するローがクエリーされるため、さらなるソート操作が回避されることがわかります.
インデックスのタイプとwhere制限条件によって、4種類のインデックススキャンがあります.インデックス一意スキャン(index unique scan)インデックス範囲スキャン(index range scan)インデックス全スキャン(index full scan)インデックス高速スキャン(index fast full scan)(1)インデックス一意スキャン(index unique scan)
一意のインデックスで値を検索すると、常に単一のROWIDが返されます.UNIQUEまたはPRIMARY KEY制約が存在する場合(文が単一のローにのみアクセスすることを保証する)、Oracleは常に一意性スキャンを実行します.一意性制約を使用する例:
SQL> explain plan forselect empno,ename from emp where empno=10;Query Plan------------------------------------SELECT STATEMENT [CHOOSE] Cost=1TABLE ACCESS BY ROWID EMP [ANALYZED] INDEX UNIQUE SCAN EMP_I 1(2)インデックス範囲スキャン(index range scan)
1つのインデックスを使用して複数行のデータにアクセスし、一意のインデックスでインデックス範囲スキャンを使用する典型的な例は、述語(where制限条件)で範囲オペレータ(たとえば>、<、<>、>=、<=、between)を使用してインデックス範囲スキャンを使用する例です.
 

SQL> explain plan for select empno,ename from emp
where empno > 7876 order by empno;
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT[CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED] 
INDEX RANGE SCAN EMP_I1 [ANALYZED] 

非一意インデックスでは、述語col=5が複数行のデータを返す可能性があるため、非一意インデックスではインデックス範囲スキャンが使用されます.
index rang scanを使用する3つのケース:
(a)一意インデックス列にrangeオペレータ(><>>=<=between)を使用した(b)組合せインデックスでは、一部の列のみを使用してクエリーを行い、非一意インデックス列に対する複数行(c)のクエリーをクエリーします.(3)インデックスフルスキャン(index full scan)
フルテーブルスキャンに対応し、対応するフルインデックススキャンもあります.また、クエリされたデータは、インデックスから直接取得できる必要があります.フルインデックススキャンの例:

An Index full scan will not perform single block i/o's and so it may prove to be inefficient. 
e.g.
Index BE_IX is a concatenated index on big_emp (empno, ename)
SQL> explain plan for select empno, ename from big_emp order by empno,ename;
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT[CHOOSE] Cost=26
INDEX FULL SCAN BE_IX [ANALYZED]

(4)インデックスクイックスキャン(index fast full scan)
インデックス内のすべてのデータブロックをスキャンすることはindex full scanと似ていますが、クエリされたデータをソートしないこと、すなわちデータがソート順で返されないことが顕著な違いです.このアクセス方法では、最大スループットを達成し、実行時間を短縮するために、複数の読み取り機能を使用してもよいし、並列読み込みを使用してもよい.
インデックスのクイックスキャンの例:
BE_IXインデックスは、複数のカラムインデックスです.

big_emp (empno,ename)
 
SQL> explain plan for select empno,ename from big_emp;
Query Plan
------------------------------------------
SELECT STATEMENT[CHOOSE] Cost=1
INDEX FAST FULL SCAN BE_IX [ANALYZED]

複数のカラムインデックスの2番目のカラムのみを選択します.

SQL> explain plan for select ename from big_emp;
Query Plan
------------------------------------------
SELECT STATEMENT[CHOOSE] Cost=1
INDEX FAST FULL SCAN BE_IX [ANALYZED]