Oracle にはどのようなインデックスがありますか?

Oracle のインデックス タイプには、非一意インデックス、一意インデックス、ビットマップ インデックス、ローカル プレフィックス パーティション インデックス、ローカル非プレフィックス パーティション インデックス、グローバル プレフィックス パーティション インデックス、ハッシュ パーティション インデックス、関数ベースのインデックスが含まれます。テーブルにデータを挿入した後、インデックスを作成する必要があります。一意のインデックスは、「create unique index」ステートメントで作成できます。

このチュートリアルの動作環境: Windows 7 システム、Oracle 11g バージョン、Dell G3 コンピューター。

インデックスとは何ですか?

#インデックスは、テーブル内のデータへのアクセスを高速化することを目的として、テーブルの 1 つ以上の列に構築される補助オブジェクトです。
• Oracle ストレージ インデックス データ構造は B* ツリー (バランス ツリー) であり、リーフ ノードに異なる B* 番号インデックスがあることを除いて、ビットマップ インデックスにも同じことが当てはまります。
• インデックスは次のように構成されています。上位のインデックス ブロックには下位のインデックス ブロックのインデックス データが含まれ、リーフ ノードにはインデックス データと行の実際の位置を決定する ROWID が含まれます。

インデックスの説明

1) インデックスはデータベース オブジェクトの 1 つで、本のインデックスと同様に、データの検索を高速化するために使用されます。データベースにインデックスを作成すると、データベース プログラムが結果をクエリするときに読み取る必要のあるデータの量を減らすことができます。これは、書籍でインデックスを使用すると、本全体を読まなくても必要な情報を見つけることができるのと同様です。

#2) インデックスはテーブル上に構築されるオプションのオブジェクトです。インデックスの鍵は、デフォルトのフル テーブル スキャンの取得方法をソートされたインデックス キーのセットに置き換えることで、取得効率を向上させます

3) インデックスは、関連するテーブルやデータから論理的および物理的に独立しています。インデックスを作成または削除しても、基本テーブルには影響しません。

#4) インデックスが作成されると、それが実行されます。 DML 操作中 (たとえば、関連操作の挿入、変更、または削除を実行するとき)、Oracle は自動的にインデックスを管理し、インデックスの削除はテーブルには影響しません

5) インデックスは透過的ですテーブルに関係なく、ユーザーに対してインデックスが存在するかどうかにかかわらず、SQL ステートメントの使用法は変わりません

6) Oracle は主キーの作成時に列にインデックスを自動的に作成します

インデックスを使用する目的:

クエリ速度の高速化

I/O 操作の削減
• ディスクの並べ替えを不要にする (インデックスを使用すると並べ替えを高速化できます)

インデックスを使用する場合:

クエリによって返されるレコード数は、並べ替えられたテーブルの場合は 40% 未満、並べ替えられていないテーブルの場合は 7% 未満です。

テーブルには多くのフラグメントがあります (頻繁に追加と削除が行われます)

インデックスの種類

非一意インデックス (最も一般的に使用される)

一意インデックス
• ビットマップ インデックス
• ローカル接頭辞付きパーティション インデックス
• ローカル非接頭辞付きパーティション インデックス
• グローバル接頭辞付きパーティション インデックス
• ハッシュ パーティション インデックス
• 関数ベースのインデックス
• #インデックス管理のガイドライン

データをテーブルに挿入した後にインデックスを作成する

After SQL*Loader または

import
• ツールを使用してデータを挿入またはロードする場合は、インデックスを作成する方が効率的です。
正しいテーブルと列にインデックスを付けます

ソートされた大きなテーブルの 40%、またはソートされていないテーブルの 7% が頻繁に取得されます 行については、インデックスを構築することをお勧めします。テーブルの関連付け、インデックス列は結合に使用されます;

列の値は比較的一意です;

• 値の範囲 (大: B* ツリー インデックス、小: ビットマップ インデックス);
• 日付型列は通常、関数ベースのインデックスに適しています。
• #列には多くの null 値があり、インデックス付けには適していません
• パフォーマンスを考慮してインデックス列を配置する

多くの場合、レコードを取得するために複数のフィールドが一緒に使用され、結合されたインデックスは単一のインデックスよりも効率的です。最もよく使用される列を先頭に置きます。例: dx_groupid_serv_id(groupid,serv_id)

、

where
• 条件で
groupid
• または # を使用します ##groupid ,serv_id、クエリはインデックスを使用します。serv_id フィールドのみが使用される場合、インデックスは無効になります。不要なインデックスを結合/分割してください。 。
テーブルごとのインデックスの数を制限する

テーブルには数百のインデックスを含めることができます (そうしますか?)。テーブルの挿入と更新を頻繁に行う場合、システム CPU のインデックスが増えるほど、I/O の負担が重くなります。

各テーブルのインデックスは 5 つ以下にすることをお勧めします。

不要になったインデックスを削除します

無効なインデックスは、主に関数ベースのインデックスの使用時に発生します。またはビットマップ グラフ インデックス、代わりに B* ツリー インデックスを使用します;

アプリケーション内のクエリはインデックスを使用しません;

• インデックスは必要がありますインデックスを再構築する前に削除する必要がありますが、 alter Index...rebuild を使用してインデックスを再構築する場合は、インデックスを削除する必要はありません。
インデックス データ ブロック領域の使用量

• • インデックスを作成するとき、特に主キーを作成するときは、テーブルスペースを明確に指定する必要があります。
  • pctfress を適切に設定します。注意: pctused はインデックスに指定できません。
  • インデックスのサイズを見積もり、ストレージパラメータを適切に設定します。デフォルトはテーブルスペースのサイズであるか、initial と next が同じサイズに設定されます。

  インデックスの並列作成を検討してください

  • インデックスの並列作成は、大きなテーブルに使用できます。インデックスを並列作成すると、ストレージ パラメーターが変更されます。プロセスは個別に使用されます。例: initial は 1M、並列処理は 8 であり、少なくとも 8M インデックスの作成中にスペースが消費されます;

  nologging を使用してインデックスを作成することを検討してください

  • インデックスを作成するには大規模なテーブルでは、nologging を使用して REDO ログを減らすことができます。
  • REDO ログ ファイル領域を節約します。
  • インデックスの作成時間を短縮します。
  • インデックス作成時のパフォーマンスを向上させます。大きなインデックスを並行して作成します。

  最適なインデックスを作成するにはどうすればよいですか?

  インデックスを明示的に作成する

  create index index_name on table_name(field_name)
  　　tablespace tablespace_name
  　　pctfree 5
  　　initrans 2
  　　maxtrans 255
  　　storage
  　　(
  　　minextents 1
  　　maxextents 16382
  　　pctincrease 0
  　　);

  関数ベースのインデックスを作成するインデックス:

  UPPER、LOWER、TO_CHAR(date) および他の関数分類でよく使用されます。例:

  create index idx_func on emp (UPPER(ename)) tablespace tablespace_name;

  ビットマップ インデックスの作成:

  ベースを比較する 小さくて比較的安定したベースを持つ列のインデックスを作成する場合は、最初にビットマップ インデックスを考慮する必要があります。例:

  create bitmap index idx_bitm on class (classno) tablespace tablespace_name;

  一意のインデックスを明示的に作成する

  create unique index ステートメントを使用して、一意のインデックスを作成できます。例:

  create unique index dept_unique_idx on dept(dept_no) tablespace idx_1;

  制約に関連するインデックスの作成

  using インデックスを使用できます。 ステートメント (unique および primary key) 制約に関連するフィールドのインデックスを作成します。例:

  alter table table_name
  　　add constraint PK_primary_keyname primary key (field_name)
  　　using index tablespace tablespace_name；

  ローカル パーティション インデックスの作成方法

  • ベース テーブルはパーティション テーブルである必要があります;
  • パーティションの数はベース テーブルと同じです;
  • 各インデックス パーティションのサブパーティションの数対応するベース テーブル パーティションと同じです。
  • ベース テーブルのサブパーティション内の行のインデックス エントリは、インデックスの対応するサブパーティションに格納されます。例:

  　　Create Index TG_CDR04_SERV_ID_IDX On TG_CDR04(SERV_ID)
  　　Pctfree 5　　Tablespace TBS_AK01_IDX
  　　Storage (
  　　MaxExtents 32768　　PctIncrease 0　　FreeLists 1　　FreeList Groups 1　　)
  　　local　　/

  レンジ パーティションのグローバル インデックスを作成する方法

  基本テーブルには、グローバル テーブルとパーティション テーブルを使用できます。

  create index idx_start_date on tg_cdr01(start_date)
  　　global partition by range(start_date)
  　　(partition p01_idx vlaues less than (‘0106’)
  　　partition p01_idx vlaues less than (‘0111’)
  　　…
  　　partition p01_idx vlaues less than (‘0401’ ))
  　　/

  既存のインデックスの再構築
  既存のインデックスを再構築する現時点では、クエリには影響しません。

  インデックスを再構築すると、追加のデータ ブロックが削除される可能性があります。
  クエリの効率が向上します。インデックス クエリ;

  alter index idx_name rebuild nologging;

  パーティション インデックスの場合:

  alter index idx_name rebuild partition partiton_name nologging;

  インデックスを削除する理由

  • インデックスは不要になりました;
  • インデックス関連テーブルをターゲットにしていません。公開されたクエリにより、期待されるパフォーマンスの向上が得られます。
  • アプリケーションは、データのクエリにインデックスを使用していません。
  • インデックスは無効なので、再構築する前に削除する必要があります。
  • インデックスが断片化されすぎているため、再構築する前に削除する必要があります;
  • ステートメント: <br>dropindex idx_name; <br>dropindexidx_namedroppartitionpartition_name; <br>

  インデックス確立のコスト

  基本テーブルを維持する場合、システムはインデックスを同じ状態に維持する必要があります。不合理なインデックスは、主に CPU と I/O に現れるシステム リソースに深刻な影響を及ぼします。

  データの挿入、更新、削除により、大量の db ファイル シーケンシャル読み取りロック待機が生成されます。

  データが数百万個あり、特定のフィールドにインデックスを追加しましたが、クエリのパフォーマンスが向上しません。主に oracle## のインデックス制限が原因である可能性があります。 ＃。

  oracle のインデックスにはいくつかのインデックス制限があります。これらのインデックス制限が発生すると、インデックスが追加されている場合でも、oracle はフル テーブル スキャンを実行します。クエリのパフォーマンスは、インデックスを作成しない場合と比べて改善されず、データベース内のインデックスを維持するためのシステム オーバーヘッドにより、パフォーマンスが低下する可能性があります。

  拡張知識: 一般的なインデックス制限の問題

  1. 不等号演算子 (, != )# を使用します。 ##次の状況では、列

  dept_id

  にインデックスがある場合でも、クエリ ステートメントは依然としてテーブル全体のスキャンを実行します<pre class='brush:php;toolbar:false;'>select * from dept where staff_num &lt;&gt; 1000;</pre>ただし、これは実際に必要です開発クエリでは、問題の解決策はありませんか? ＃＃＃＃＃＃持っている！

  不等号をクエリ構文の または に置き換えることで、インデックスを使用してテーブル全体のスキャンを回避できます。上記のステートメントを次のように変更すると、インデックスを使用できるようになります。

  select * from dept shere staff_num < 1000 or dept_id > 1000;

  2. is null または is not null

  is null または

  is nuo null

  の使用も制限されますデータベースでは null 値が定義されていないため、インデックスを使用します。インデックス付きの列に多くの null がある場合、インデックスは使用されません (インデックスがビットマップ インデックスでない限り、これについては今後のブログ記事で詳しく説明します)。 SQL ステートメントで null を使用すると、多くの問題が発生します。 この問題を解決する方法は、テーブルの作成時に、インデックスを付ける必要がある列を非 null (not null)

  として定義することです。 3. 関数を使用します

  関数ベースのインデックスが使用されていない場合、where 句のインデックス付き列で関数を使用すると、オプティマイザはこれらのインデックスを無視します。次のクエリではインデックスは使用されません:

  select * from staff where trunc(birthdate) = '01-MAY-82';

  但是把函数应用在条件上，索引是可以生效的，把上面的语句改成下面的语句，就可以通过索引进行查找。

  select * from staff where birthdate < (to_date(&#39;01-MAY-82&#39;) + 0.9999);

  4、比较不匹配的数据类型

  比较不匹配的数据类型也是难于发现的性能问题之一。下面的例子中，dept_id是一个varchar2型的字段，在这个字段上有索引，但是下面的语句会执行全表扫描。

  select * from dept where dept_id = 900198;

  这是因为oracle会自动把where子句转换成to_number(dept_id)=900198，就是3所说的情况，这样就限制了索引的使用。把SQL语句改为如下形式就可以使用索引

  select * from dept where dept_id = '900198';

  5、使用like子句

  使用like子句查询时，数据需要把所有的记录都遍历来进行判断，索引不能发挥作用，这种情况也要尽量避免。

  Like 的字符串中第一个字符如果是‘%’则用不到索引

  Column1 like ‘aaa%’ 是可以的
  Column1 like ‘%aaa%’用不到

  6、使用IN

  尽管In写法要比exists简单一些，exists一般来说性能要比In要高的多 

  用In还是用Exists的时机 

  当in的集合比较小的时候,或者用Exists无法用到选择性高的索引的时候，用In要好，否则就要用Exists
  例：

  select count(*) from person_info where xb in (select xb_id from dic_sex);
  
  Select count(*) from n_acntbasic a where shbxdjm =:a and exists(select 1 from person_info where pid=a.pid and …);
  
  Select * from person_info where zjhm=3101….;--将会对person_info全表扫描
  
  Select * from person_info where zjhm =‘3101…’;--才能用到索引

  假定TEST表的dt字段是date类型的并且对dt建了索引。
  如果要查‘20041010’一天的数据.下面的方法用不到索引

  Select * from test where to_char(dt,’yyyymmdd’) =‘20041010’;

  而以下将会用到索引。

  select * from test where dt >=to_date(‘20041010’,’yyyymmdd’) and dt < to_date(‘20041010’,’yyyymmdd’) + 1

  7、如果能不用到排序，则尽量避免排序。

  用到排序的情况有
  集合操作。Union ,minus ,intersect等,注：union all 是不排序的。

  Order byGroup byDistinctIn

  有时候也会用到排序
  确实要排序的时候也尽量要排序小数据量,尽量让排序在内存中执行，有文章说，内存排序的速度是硬盘排序的1万倍。

  在排序的字段上创建索引，让排序在内存中执行，加快排序速度。
  

  8、在基于CBO的优化器（花费）下，表的统计数据过期。也可能导致不使用索引。

  解决：执行表分析。获取表的最新信息。

  9、获取的数据量过大，全部扫描效率更高

  10、索引字段的值分散率太低，值太集中，如类型字段都是1,2， 状态类型Y-有效/N-无效。这类型的字段最好别建索引。

  尽管在这些字段上建立了索引，但对全表数据区分度不大。最后还是会全表扫描。

  推荐教程：《Oracle教程》

以上がOracle にはどのようなインデックスがありますか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。