索引の作成と管理

索引のガイドライン

索引の作成ガイドライン
  • 大きな表で頻繁に検索する行数が総行数の15%未満の場合に索引を作成する。
  • 複数の表を結合するパフォーマンスを改善するには、結合に使用する列に索引を付ける。
  • 小さい表に索引は不要である。
  • 一意の値が比較的多い。
  • 値の範囲が広い(通常の索引が適している)。
  • 値の範囲が狭い(ビットマップ索引が適している)。
  • 列に多くのNULLが含まれているが、通常の問合せでは必ず値を持つ列を選択する。
  • LONG列およびLONG RAW列には索引を作成できない。
  • 表からデータを検索する速度とその表を更新する速度は二律背反的である。表が主に読取り専用である場合、索引を増やすと有効ですが、表が頻繁に更新される場合は、索引を少なくすることを薦める。
CREATE INDEX … NOLOGGING
  • REDOログ・ファイルの領域を節約できる。
  • 索引の作成に要する時間が削減できる。
  • 大規模な索引のパラレル作成のパフォーマンスが向上する。
  • NOLOGGINGを使用して作成された索引はアーカイブされないため、索引作成後にバックアップを実行する必要がある。
索引の断片化の解消

索引の再作成

  • 索引を別の表領域に迅速に移動できる。
  • 多くのディスク領域を必要とし、コストが高い。
  • 新しいツリーを作成して、可能であればその高さを縮小する。
  • オリジナルの索引を削除せずに、記憶域パラメータと表領域パラメータを迅速に変更できる。

索引の結合

  • 索引を別の表領域に移動することはできない。
  • 必要なディスク領域が少ないため、コストが低い。
  • ツリーの同じブランチ内のリーフ・ブロックを結合する。
  • 索引のリーフ・ブロックを迅速に解放できる。

索引の作成

Bツリー索引(B-Tree)
  • 主キー索引および選択的な索引に最も適する。
  • Bツリー索引を連結索引として使用すると、索引列順にソートされたデータを取り出すことができる。

Bツリー一意索引の作成

CREATE UNIQUE INDEX TOM.IDX_TAB_HASH_PART_1 ON TAB_HASH_PART(ORDER_ID) TABLESPACE INDX STORAGE(INITIAL 512K);

Bツリー複合索引の作成
CREATE INDEX TOM.IDX_TAB_HASH_PART_2 ON TAB_HASH_PART(BOOK_NO,ORDER_DATE) TABLESPACE INDX;
ビットマップ索引
  • カーディナリティが低いデータに適する。
  • 圧縮技法によって最小限のI/Oで多数のROWIDを生成する。
  • ビットマップ索引は、索引内で問合せを満たすことができるため、COUNT()を使用した問合せで特に効果的である。
  • パーティション表に使用できるのはローカル・ビットマップ索引のみである。
CREATE BITMAP INDEX TOM.IDX_TAB_HASH_PART_3 ON TAB_HASH_PART(BOOK_TYPE) TABLESPACE INDX LOCAL;
ファンクション索引
  • ベース・データ上の関数から導出された値に対するBツリー経由でアクセスできる。
  • ファンクション索引ではNULLの使用に制限があり、問合せオプティマイザを使用可能にしておく必要がある。
  • 複合列への問合せで導出される結果を生成する場合や、データベースへのデータの格納方法における制限を克服する場合に、特に役立つ。
CREATE INDEX TOM.IDX_TAB_OBJECTS ON TAB_OBJECTS(UPPER(OBJECT_NAME)) TABLESPACE INDX;

上記のファンクション索引は下記のSQLに有効になる。
SELECT * FROM TAB_OBJECTS WHERE UPPER(OBJECT_NAME) = 'TAB_HASH_PART';
ファンクション索引の使用と不使用の検証
まずは普通Bツリー索引で検索してみる。
PROD(TOM)>CREATE INDEX TOM.IDX_TAB_OBJECTS ON TAB_OBJECTS(OBJECT_NAME) TABLESPACE INDX;
 
索引が作成されました。
 
PROD(TOM)>ANALYZE TABLE TAB_OBJECTS COMPUTE STATISTICS;
 
表が分析されました。
 
PROD(TOM)>SET AUTOTRACE ON
PROD(TOM)>SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM TAB_OBJECTS WHERE UPPER(OBJECT_NAME) = 'TAB_HASH_PART';
 
OWNER      OBJECT_NAME                              OBJECT_TYPE
---------- ---------------------------------------- ----------------------------------------
TOM        TAB_HASH_PART                            TABLE PARTITION
TOM        TAB_HASH_PART                            TABLE PARTITION
TOM        TAB_HASH_PART                            TABLE PARTITION
TOM        TAB_HASH_PART                            TABLE PARTITION
TOM        TAB_HASH_PART                            TABLE
TOM        TAB_HASH_PART                            TABLE PARTITION
 
6行が選択されました。
 
実行計画
----------------------------------------------------------
Plan hash value: 93652071
 
---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |    95 |  2565 |    55   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TAB_OBJECTS |    95 |  2565 |    55   (0)| 00:00:01 | -- 全表走査なので索引が無効になる。
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(UPPER("OBJECT_NAME")='TAB_HASH_PART')
 
統計
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        249  consistent gets
          0  physical reads
          0  redo size
        650  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed
 
PROD(TOM)>

ファンクション索引を張り付けて検索してみる。
PROD(TOM)>CREATE INDEX TOM.IDX_TAB_OBJECTS ON TAB_OBJECTS(UPPER(OBJECT_NAME)) TABLESPACE INDX;
 
索引が作成されました。
 
PROD(TOM)>ANALYZE TABLE TAB_OBJECTS COMPUTE STATISTICS;
 
表が分析されました。
 
PROD(TOM)>SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM TAB_OBJECTS WHERE UPPER(OBJECT_NAME) = 'TAB_HASH_PART';
 
OWNER      OBJECT_NAME                              OBJECT_TYPE
---------- ---------------------------------------- ----------------------------------------
TOM        TAB_HASH_PART                            TABLE PARTITION
TOM        TAB_HASH_PART                            TABLE PARTITION
TOM        TAB_HASH_PART                            TABLE PARTITION
TOM        TAB_HASH_PART                            TABLE PARTITION
TOM        TAB_HASH_PART                            TABLE
TOM        TAB_HASH_PART                            TABLE PARTITION
 
6行が選択されました。
 
実行計画
----------------------------------------------------------
Plan hash value: 4259064920
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    27 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB_OBJECTS     |     1 |    27 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TAB_OBJECTS |     1 |       |     1   (0)| 00:00:01 | -- 索引が検索に有効
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(UPPER("OBJECT_NAME")='TAB_HASH_PART')
 
統計
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        650  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed
 
PROD(TOM)>

上記の例では、ファンクション索引によって問い合わせの効率を改善されることを得る。
逆キー索引
  • 挿入アプリケーションでの索引のホット・スポットを除去するように設計される。
  • 挿入パフォーマンスに優れていますが、索引レンジ・スキャンには使用できない。
CREATE INDEX TOM.IDX_TAB_OBJECTS_OBJECT_ID ON TAB_OBJECTS(OBJECT_ID) REVERSE;
制約に対応付けられた索引の作成

{CREATE TABLE | ALTER TABLE } … USING INDEX
ALTER TABLE … USING INDEXの例

PROD(TOM)>CREATE INDEX PK_TAB_OBJECTS ON TAB_OBJECTS(OBJECT_ID) TABLESPACE INDX;
 
索引が作成されました。
 
PROD(TOM)>ALTER TABLE TAB_OBJECTS ADD CONSTRAINT PK_TAB_OBJECTS PRIMARY KEY (OBJECT_ID) USING INDEX PK_TAB_OBJECTS;
 
表が変更されました。
 
PROD(TOM)>

CREATE TABLE … USING INDEXの例
CREATE TABLE TOM.USER_INFO
(
    USER_ID                        NUMBER(5,0) NOT NULL,
    USER_NAME                      VARCHAR2(30) NOT NULL,
    AGE                            NUMBER(3,0),
    BIRTH                          DATE,
    ADDRESS                        VARCHAR2(100),
    REMARKS                        VARCHAR2(1000),
    CONSTRAINT PK_USER_INFO PRIMARY KEY (USER_ID) USING INDEX TABLESPACE INDX
)
TABLESPACE EXAMPLE;
索引作成時の統計情報収集

索引の作成または再作成の際、リソース・コストをほとんど使用せずに統計を収集できる。
CREATE INDEX … COMPUTE STATISTICS

CREATE UNIQUE INDEX TOM.IDX_TAB_HASH_PART_1 ON TAB_HASH_PART(ORDER_ID) TABLESPACE INDX COMPUTE STATISTICS;
索引のオンラインでの作成

CREATE INDEX … ONLINE

  • 実表に索引を作成または再作成しながら、同じ実表を更新できる。
  • 索引の作成中でもデータ操作言語(DML)操作が実行できる。
  • データ定義言語(DDL)操作は実行できない。
  • 索引の作成中または再作成中のパラレル実行はサポートされていない。
CREATE UNIQUE INDEX TOM.IDX_TAB_HASH_PART_1 ON TAB_HASH_PART(ORDER_ID) TABLESPACE INDX COMPUTE STATISTICS ONLINE;

索引の管理

既存の索引の再作成

索引を再作成すると同時に統計情報を取得

ALTER INDEX IDX_TAB_HASH_PART_1 REBUILD COMPUTE STATISTICS;

索引をオンラインで再作成
ALTER INDEX IDX_TAB_HASH_PART_1 REBUILD ONLINE TABLESPACE USERS COMPUTE STATISTICS;
索引の使用状況の監視

ALTER INDEX … {MONITORING | NOMONITORING} USAGE

ALTER INDEX PK_TAB_OBJECTS MONITORING USAGE;

V$OBJECT_USAGEを問い合わせして監視情報を確認できる。
PROD(TOM)>select count(*) from tab_objects;
 
  COUNT(*)
----------
      9525
 
PROD(TOM)>select index_name,monitoring,used from V$OBJECT_USAGE;
 
INDEX_NAME                                MONITORIN USED
----------------------------------------- --------- ---------
PK_TAB_OBJECTS                            YES       YES
 
PROD(TOM)>
索引の領域使用の監視

ANALYZE INDEX…VALIDATE STRUCTURE

ANALYZE INDEX PK_TAB_OBJECTS VALIDATE STRUCTURE;

INDEX_STATSを問い合わせて情報を確認できる。
PROD(TOM)>SELECT NAME,HEIGHT,USED_SPACE,LF_ROWS,BR_ROWS,PCT_USED FROM INDEX_STATS;
 
NAME                               HEIGHT USED_SPACE    LF_ROWS    BR_ROWS   PCT_USED
------------------------------ ---------- ---------- ---------- ---------- ----------
PK_TAB_OBJECTS                          2     143250       9525         40         88
 
PROD(TOM)>
索引の削除
DROP INDEX IDX_TAB_OBJECTS;

索引の情報

DBA_INDEXES

データベース内にあるすべての表の索引が表示される。

DBA_IND_COLUMNS

表の索引の列が表示される。

DBA_IND_EXPRESSIONS

表のファンクション索引の式が表示される。

DBA_IND_STATISTICS

索引のオプティマイザ統計が含まれる。

INDEX_STATS

最後に発行されたANALYZE INDEX…VALIDATE STRUCTURE文の情報が格納されている。

INDEX_HISTOGRAM

最後に発行されたANALYZE INDEX…VALIDATE STRUCTURE文の情報が格納されている。

V$OBJECT_USAGE

ALTER INDEX…MONITORING USAGE機能で生成された索引使用状況の情報が含まれる。

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License