OracleのSQL最適化-インデックスの基本原理(二)

ガイド:1、なぜインデックスを使うのですか.2、インデックスの作成に適した状況は?3、索引を作成する策略.4、インデックスの操作方法.5、よく使われるインデックス操作.
1、なぜインデックスを使うのですか.
(1)、原因
インデックスには1列しかありません.ioは小さいので、速いです.
インデックス内のこの列はソートされ、二叉検索され、クエリーの速度が向上します.
(2)、原因分析
インデックスは、データベース・テーブルの1つまたは複数のカラムの値をソートする構造です.
インデックスの役割は図書のディレクトリに相当し、ディレクトリのページ番号に基づいて必要な内容をすばやく見つけることができます.
テーブルに大量のレコードがある場合、テーブルをクエリーするには2つの検索方法があります.
1つ目の検索情報方式は、全テーブル検索であり、すべてのレコードを1つずつ取り出し、クエリー条件と1つ比較し、条件を満たすレコードを返します.
これにより、データベースシステムの時間が大幅に消費され、ディスクI/O操作が大幅に発生します.
2つ目は、テーブルにインデックスを作成し、インデックスにクエリー条件を満たすインデックス値を見つけ、最後にインデックスに保存されたROWID(ページ番号に相当)を使用することです.
テーブル内の対応するレコードをすばやく見つけます.
注意:テーブル内のデータを追加、削除、変更する場合、インデックスも動的にメンテナンスされ、データのメンテナンス速度が低下します.
2、インデックスの作成に適した状況:
(1)テーブルのプライマリ・キー、外部キーにはインデックスが必要です.
(2)他のテーブルと頻繁に接続されるテーブルは、接続フィールドにインデックスを作成する必要があります.
(3)WHERE句によく登場するフィールド,特に大きなテーブルのフィールドには,インデックスを作成すべきである.
(4)インデックスは選択性の高いフィールドに作成すべきである.
(5)インデックスは小さなフィールドに作成すべきであり,大きなテキストフィールドや超長いフィールドではインデックスの作成には適していない.
(6)複合インデックスの作成には注意深く分析する必要がある.
(7)複合インデックス内の主列フィールドを正しく選択し、一般的に選択性の良いフィールドである.
(8)単一フィールドクエリが少なくない場合、複合インデックスを確立することができる.そうでない場合は、単一フィールドインデックスを考慮します.
(9)複合インデックスに含まれるフィールドがWHERE句に単独でしばしば現れる場合,複数の単一フィールドインデックスに分解する.
(10)複合インデックスに含まれるフィールドが3つを超える場合、その必要性をよく考慮し、複合のフィールドを減らすことを考慮する.
(11)単一フィールドインデックスと、これらのフィールド上の複合インデックスがある場合、複合インデックスを削除することが一般的である.
(12)DML操作(insert,update,delete)を頻繁に行うテーブルは,あまりインデックスを作成しない.
(13)不要なインデックスを削除し、実行計画に悪影響を及ぼさないようにする.
3、索引作成の策略
(1)データをインポートしてからインデックスを作成する.
(2)小さなテーブルにインデックスを作成する必要はありません.
(3)性別フィールドなどの値範囲の小さいフィールドに対してビットマップインデックスを確立する.
(4)テーブル内のインデックスの数を制限する.
(5)インデックスに適切なPCTFREE値を設定する.
(6)インデックスを格納する表領域は,個別に設定することが望ましい.
4、索引の操作方法:
(1)、インデックス格納場所——データベースファイル:
データベース・ファイルには、ユーザー・データ(テーブル、インデックスなど)、データ・ディクショナリ、ストアド・プロシージャ、関数、パケットのコード、
並べ替えに使用される一時データやロールバックセグメントデータなど.
(2)、索引の表示:
ORACLEデータ辞書ビューの種類は,USER,ALL,DBAである.
　　USER_*:ユーザが所有するオブジェクト情報、すなわちユーザ自身が作成したオブジェクト情報について.
　　ALL_*:ユーザーがアクセスできるオブジェクトに関する情報、すなわちユーザーが自分で作成したオブジェクトに関する情報に加えて
他のユーザーが作成したオブジェクトですが、そのユーザーがアクセスできる情報です.
　　DBA_*:データベース全体のオブジェクトに関する情報
注意:データベース内のインデックス・データが多い場合は、ALL_のクエリは避けてください.INDEXES、DBA_INDEXES.
システムビューには、インデックス名、対応するテーブル、カラムなどが格納されます.
    all_ind_columns/dba_ind_columns/user_ind_columns   

  select * from user_indexes where table='  ' ;
  select * from user_ind_columns where index_name=upper('&index_name');

(3)インデックスフラグメントのクリーンアップ
a.インデックスのマージ(単純にB木の葉ノードのストレージフラグメントをマージするだけで、インデックスの物理的組織構造は変更されません)

    alter index emp_pk coalesce;

b.インデックスの再構築(ストレージフラグメントを除去するだけでなく、インデックスのすべてのストレージパラメータの設定を変更し、インデックスを
他の表領域では、インデックスの再構築は、実際には指定した表領域で新しいインデックスを再構築し、元のインデックスを削除することです.

    alter index emp_pk rebuild;

(4)索引の削除

   drop index emp_ename; 

インデックスに破損したデータ・ブロックが含まれているか、ストレージ・フラグメントが多すぎる場合は、まずインデックスを削除してから再構築する必要があります. 
インデックスがコンストレイントの作成時にoracleによって自動的に生成される場合は、コンストレイントを無効にするか、コンストレイントを削除する方法で対応するインデックスを削除できます.
テーブルを削除すると、oracleはテーブルに関連するすべてのインデックスを自動的に削除します.
5、よく使う索引操作
(1)テーブル内のインデックスを1枚検索する

 select * from user_indexes where table_name=upper('tableName'); 

(2)クエリインデックスフィールド

 select * from user_ind_columns where index_name=('indexName');

(3)あるフィールドにインデックスを作成する

 create index index_name on table_name(col_name);

(4)ユーザの下のインデックスの表示

  select  * from user_indexes-          -----            
  select  * from user_indexes where table_name='A';      -----        A   
  drop index index_name     
  select index_name,index_type,status,blevel from user_indexes where table_name = '？';  
           -----           
  select table_name, index_name, column_name, column_position from user_ind_columns 
  where  table_name='？';    ----       

(5)インデックス作成

 Create unique clustered index    on   (  1)  --   
 Create index     on   (  1,  2)  -------