見出し画像

Oracleのインデックス効率に影響する要素

はじめに

こんにちは。SHIFTの小澤です。「Oracleのインデックス効率に影響する要素」について記事にまとめていきます。

この記事を読むとできるようになること

  • カーディナリティ度(選択度)を考慮したインデックス設計ができるようになる

  • クラスタリングファクタが何かを説明できるようになる

  • B-Treeインデックスの高さが何か、B-Treeインデックスの高さはどのように増えていくのかを説明できるようになる

  • Insert文やDelete文によりインデックス・ブロックがどのように更新されるのかを説明できるようになる

  • ヒストグラムが何かを説明できるようになる

はじめに

OracleがSQLを受け取ると、最初に「SQLの解析」(「構文チェック」「セマンティクス・チェック」「共有プール・チェック」)を行います。

例えばSQLの"FROM"を"FORM"("O"と"R"が逆)とスペルミスしていると「構文チェック」でエラーになります。
また、例えばEMP_NONEXISTENT表というのは存在しないとして、この表を問合せるSQL文は意味がないので"SELECT ... FROM EMP_NONEXISTENT"のようなSQLは「セマンティクス・チェック」でエラーになります。
「共有プール・チェック」は、前に実行した時の実行計画を再利用できるかどうかのチェックです(Oracleが一度作成した実行計画は「共有プール」と言うメモリ領域に残され、再利用できるようになっています)。

「共有プール・チェック」の結果、再利用できる実行計画があればそのまま実行へ移ります(ソフト・パース)。
再利用できるSQLがなければオプティマイザがSQLを最適化し、実行計画を作成します(ハード・パース)。

オプティマイザがSQLを最適化し、実行計画を作成する時、インデックスを利用した方が良いのか、利用する場合はどのインデックスをどの順番にどのアクセス・パス(テーブル・フルスキャン、インデックス・レンジ・スキャンなど)で利用してテーブル結合すると性能が良いのか、など判断するための要素は複数あります。

本記事では、インデックスの効率に影響する要素をいくつか見ていきます。

カーディナリティ度(選択度)はインデックス作成する際は必ず考慮が必要ですので是非押さえておいていただきたい内容です。 その他は、大きなテーブル(数千万行以上)のテーブルに対するSQL性能が思ったほど出ない場合にチェックしてみると良いのではないかと思います。

カーディナリティ度(選択度)

カーディナリティ度は、テーブルをある列で検索した時に、テーブルの行数に対してどの程度返される行数を絞り込めるかを表す指標です。

EMP_TEST表の例を見てみます(EMP_TEST表はEMPテーブルをコピーして作りました。
EMPテーブルというのはOracle製品で提供されているデモ用のテーブルです。開発環境ではOracleをインストールする時に一緒にインストールすることが多いと思いますが、詳しくは別の記事で触れようと思います)。
EMP_TEST表の行数は14件あります。
EMP_TEST表のEMP_NUMBER列にINDEX(EMP_TEST_N7)が張ってあります。 EMP_NUMBERの個別値の数は14個、EMP_TEST表の行数も14個あります。
例えばEMP_TEST表をEMP_NUMBER列でWHERE EMP_NUMBER = '07369'のように検索すると1行に絞り込むことができます。
この時、INDEX(EMP_TEST_N7)のカーディナリティ度が高い、と言います。

SQL> select * from emp_test;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO EMP_N
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- -----
      7369 SMITH      CLERK	      7902 17-DEC-80	    800 		   20 07369
      7499 ALLEN      SALESMAN	      7698 20-FEB-81	   1600        300	   30 07499
      7521 WARD       SALESMAN	      7698 22-FEB-81	   1250        500	   30 07521
      7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20 07566
      7654 MARTIN     SALESMAN	      7698 28-SEP-81	   1250       1400	   30 07654
      7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		   30 07698
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10 07782
      7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 		   20 07788
      7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10 07839
      7844 TURNER     SALESMAN	      7698 08-SEP-81	   1500 	 0	   30 07844
      7876 ADAMS      CLERK	      7788 23-MAY-87	   1100 		   20 07876

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO EMP_N
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- -----
      7900 JAMES      CLERK	      7698 03-DEC-81	    950 		   30 07900
      7902 FORD       ANALYST	      7566 03-DEC-81	   3000 		   20 07902
      7934 MILLER     CLERK	      7782 23-JAN-82	   1300 		   10 07934

14 rows selected.

SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'EMP_TEST';

TABLE_NAME	     INDEX_NAME 	  INDEX_TYPE		UNIQUENES STATUS   VISIBILIT
-------------------- -------------------- --------------------- --------- -------- ---------
EMP_TEST	     EMP_TEST_N7	  NORMAL		NONUNIQUE VALID    VISIBLE

SQL> select table_name, index_name, column_position, column_name
  2  from user_ind_columns
  3  where table_name = 'EMP_TEST'
  4  order by index_name, column_position;

TABLE_NAME	     INDEX_NAME 	  COLUMN_POSITION COLUMN_NAME
-------------------- -------------------- --------------- --------------------
EMP_TEST	     EMP_TEST_N7			1 EMP_NUMBER

SQL> select distinct_keys, num_rows
  2  from user_indexes
  3  where index_name = 'EMP_TEST_N7';

DISTINCT_KEYS	NUM_ROWS
------------- ----------
	   14	      14

例の中で、USER_INDEXSビューの統計情報を検索していますが、テーブルやインデックスの統計情報を収集することで情報が更新されます。

個別キー(DISTINCT_KEYS)の数をテーブル内の行数(NUM_ROWS)と比較するとインデックスのカーディナリティ度が分かります(DISTINCT_KEYS / NUM_ROWS が1に近ければカーディナリティ度が高い)。
カーディナリティ度が高い時は、インデックスが少数の行を返す、つまり行数を絞り込めるということになり、インデックス利用で効率的に行数を絞り込める、ということです。

インデックスのカーディナリティ度は、オプティマイザが実行パスを決定する時に影響します。
インデックスのカーディナリティ度が高いほど、各個別値について返される行数が少なくなります。
Oracleは、カーディナリティ度が高いインデックス・アクセスに対してコスト(COST)を低く見積もるので、Oracleのオプティマイザは、カーディナリティ度が大きいインデックスを使った実行計画を選ぶ可能性が高くなります。

複合インデックスの場合もカーディナリティ度が高いインデックスでないとインデック・スアクセスに対するコストが低くならないので、もし、インデックスの2列目以降の列がカーディナリティ度をあまり向上させない場合は、2列目以降の列を追加するデメリット(テーブルが更新された時にインデックスの更新に時間がかかる)がメリット(インデックス利用によりSQL実行のコストが低くなる)を上回る可能性があるので、複合インデックスの場合もカーディナリティ度が高い列に対してインデックスを張った方が良いです。

クラスタリングファクタ

クラスタリングファクタを説明する前に、ROWID、Block番号などのイメージを持っていただきたいので先にこちらを見てみます。
EMP_TESTテーブルの中身を確認します。 ROWIDは行のアドレスが入っています。

SQL> select rowid, empno, ename, emp_number
  2  from emp_test;

ROWID			EMPNO ENAME	 EMP_N
------------------ ---------- ---------- -----
AAASABAAMAAAJqbAAA	 7369 SMITH	 07369
AAASABAAMAAAJqbAAB	 7499 ALLEN	 07499
AAASABAAMAAAJqbAAC	 7521 WARD	 07521
AAASABAAMAAAJqbAAD	 7566 JONES	 07566
AAASABAAMAAAJqbAAE	 7654 MARTIN	 07654
AAASABAAMAAAJqbAAF	 7698 BLAKE	 07698
AAASABAAMAAAJqbAAG	 7782 CLARK	 07782
AAASABAAMAAAJqbAAH	 7788 SCOTT	 07788
AAASABAAMAAAJqbAAI	 7839 KING	 07839
AAASABAAMAAAJqbAAJ	 7844 TURNER	 07844
AAASABAAMAAAJqbAAK	 7876 ADAMS	 07876

ROWID			EMPNO ENAME	 EMP_N
------------------ ---------- ---------- -----
AAASABAAMAAAJqbAAL	 7900 JAMES	 07900
AAASABAAMAAAJqbAAM	 7902 FORD	 07902
AAASABAAMAAAJqbAAN	 7934 MILLER	 07934

14 rows selected.

"AAASABAAMAAAJqbAAA"が実際に何を表しているのかを、DBMS_ROWIDパッケージを使って確認してみます。

SQL> select
  2  dbms_rowid.rowid_object('AAASABAAMAAAJqbAAA') "Data Object ID"
  3 ,dbms_rowid.rowid_relative_fno('AAASABAAMAAAJqbAAA') "Relative File No"
  4 ,dbms_rowid.rowid_block_number('AAASABAAMAAAJqbAAA') "Block Number"
  5 ,dbms_rowid.rowid_row_number('AAASABAAMAAAJqbAAA') "Row Number"
  6 from dual;

Data Object ID Relative File No Block Number Row Number
-------------- ---------------- ------------ ----------
	 73729		     12        39579	      0

行が物理的にどの表領域のどのブロックの何行目に入っているのかを示しています。
つまり、ROWIDが分かると物理的にアクセスする行の位置が一意に分かるので必要最小限のコストでアクセスできます。

  • Data Object ID:オブジェクトのデータ・オブジェクト番号。

  • Relative File No:行が存在するデータファイル番号(最初のファイルは1)。ファイル番号は表領域に対して相対的です。

  • Block Number:行が存在するデータファイルのデータ・ブロック番号。

  • Row Number:データ・ブロック内での行の位置(最初の行は0)。

EMP_TEST表のインデックスを見てみます。
EMP_TEST表のEMP_NUMBER列に対してEMP_TEST_N7インデックスを張っています。

SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'EMP_TEST';

TABLE_NAME	     INDEX_NAME 	  INDEX_TYPE		UNIQUENES STATUS   VISIBILIT
-------------------- -------------------- --------------------- --------- -------- ---------
EMP_TEST	     EMP_TEST_N7	  NORMAL		NONUNIQUE VALID    VISIBLE

SQL> select table_name, index_name, column_position, column_name
  2  from user_ind_columns
  3  where table_name = 'EMP_TEST'
  4  order by index_name, column_position;

TABLE_NAME	     INDEX_NAME 	  COLUMN_POSITION COLUMN_NAME
-------------------- -------------------- --------------- --------------------
EMP_TEST	     EMP_TEST_N7			1 EMP_NUMBER

EMP_TEST表とEMP_TEST_N7インデックスのOBJECT_IDを確認します。

SQL> col object_name for a20
SQL> select object_name, object_id, object_type
  2  from dba_objects
  3  where object_name in ('EMP_TEST', 'EMP_TEST_N7');

OBJECT_NAME	      OBJECT_ID OBJECT_TYPE
-------------------- ---------- -----------------------
EMP_TEST		  73729 TABLE
EMP_TEST_N7		  73762 INDEX

インデックスの中身を確認するためにインデックス・ツリー・ダンプを取得してみます。
"alter session"の最後に指定した数字はインデックスのOJBECT_IDです。

SQL> conn system/xxxxxxxx@orclpdb
Connected.
SQL> alter session set events 'immediate trace name treedump level 73762';

Session altered.

インデックス・ツリー・ダンプを見てみます。

cd /u01/app/oracle/diag/rdbms/orcl19/orcl19/trace
less orcl19_ora_32739.trc

----- begin tree dump
leaf: 0x3009aa3 50371235 (0: row:14.14 avs:7758)
----- end tree dump

Data Block Addressが50371235です。 DBMS_UTILITYを使って、ファイル番号とブロック番号を取得してみます。パラメータはData Block Addressです。

SQL> select dbms_utility.data_block_address_file(50371235) "FILE_ID",
  2         dbms_utility.data_block_address_block(50371235) "BLOCK_ID"
  3  from dual;

FILE_ID   BLOCK_ID
---------- ----------
	12	39587

"alter system dump"でデータファイルをダンプしてみます。
datafileにFILE_IDの12、blockにBLOCK_IDの39587を指定して実行します。 (注:本記事ではOracleの理解を深めるために実行しています。本番環境では特にサポートの指示がある場合を除き、実行しないようにしてください。)

SQL> alter system dump datafile 12 block 39587;

System altered.

less orcl19_ora_32739.trc
...
row#0[8017] flag: -------, lock: 0, len=15
col 0; len 5; (5):  30 37 33 36 39
col 1; len 6; (6):  03 00 9a 9b 00 00
row#1[8002] flag: -------, lock: 0, len=15
col 0; len 5; (5):  30 37 34 39 39
col 1; len 6; (6):  03 00 9a 9b 00 01
row#2[7987] flag: -------, lock: 0, len=15
col 0; len 5; (5):  30 37 35 32 31
col 1; len 6; (6):  03 00 9a 9b 00 02
row#3[7972] flag: -------, lock: 0, len=15
col 0; len 5; (5):  30 37 35 36 36
col 1; len 6; (6):  03 00 9a 9b 00 03
...

col 0には値が入っています。07369はSMITHのEMP_NUMBERです。

30 37 33 36 39
→07369

col 1にはROWIDが入っています。Block番号39579の行0を示しています。

03 00 9a 9b 00 00
→768 39579 0

さて本題のクラスタリングファクタですが、これはテーブルに対してインデックスがどの程度整列して並んでいるかを表しています。
インデックスをスキャンしたあと、テーブルをスキャンする際にどの程度コストがかかるのかをチェックするために使用します。
(クラスタリングファクタにインデックスのカーディナリティ度を掛けるとコストが得られます。
オプティマイザは実行計画を決定する要素としてこのコストを使います。
ただし、ユーザが参照できる実行計画上は、カーディナリティ度やクラスタリングファクタの数値自体を確認することはできません。)。

クラスタリングファクタはインデックス・スキャン時に読み取られるブロック数を記録します。
使用するインデックスのクラスタリングファクタが大きい場合は、隣接する行が異なるブロックに散らばっているため、各インデックス・ブロックに対応するテーブルの行を取得するために多くのデータブロックを参照する必要があります。
クラスタリングファクタがテーブルのブロック数に近い場合、インデックスは順序良く並んでいます。
クラスタリングファクタがテーブルの行数に近い場合、インデックスは順序良く並んでいません。

クラスタリングファクタは次のように計算されます。

  1. インデックスを順番にスキャンする。

  2. 現在読んでいるインデックス値に対応するROWIDのBlock Number部を、直前のインデックス値のものと比較する(インデックス内の隣接する行を比較する)

  3. 現在のROWIDが直前のものと異なるBlock Numberを指している場合、クラスタリングファクタを加算する。(これをインデックス全体に対して繰り返す)

以下の例でUSER_INDEXESビューのCLUSTERING_FACTOR列は、インデックスされた列に対してデータがどの程度順序良く並んでいるのかを示しています。
CLUSTERING_FACTOR列の値がインデックスのリーフブロック数に近い場合、テーブルのデータはインデックスに対して順序良く並んでいます。
インデックスのリーフブロック数に近くない場合はテーブルのデータはインデックスに対して順序良く並んでいません。
インデックスのリーフブロックには、インデックスした列の値と、対応するROWIDが格納されています。

SQL> conn scott/xxxxxxxx@orclpdb
Connected.

SQL> select rowid, empno, ename, emp_number
  2  from emp_test;

ROWID			EMPNO ENAME	 EMP_N
------------------ ---------- ---------- -----
AAASABAAMAAAJqbAAA	 7369 SMITH	 07369
AAASABAAMAAAJqbAAB	 7499 ALLEN	 07499
AAASABAAMAAAJqbAAC	 7521 WARD	 07521
AAASABAAMAAAJqbAAD	 7566 JONES	 07566
AAASABAAMAAAJqbAAE	 7654 MARTIN	 07654
AAASABAAMAAAJqbAAF	 7698 BLAKE	 07698
AAASABAAMAAAJqbAAG	 7782 CLARK	 07782
AAASABAAMAAAJqbAAH	 7788 SCOTT	 07788
AAASABAAMAAAJqbAAI	 7839 KING	 07839
AAASABAAMAAAJqbAAJ	 7844 TURNER	 07844
AAASABAAMAAAJqbAAK	 7876 ADAMS	 07876

ROWID			EMPNO ENAME	 EMP_N
------------------ ---------- ---------- -----
AAASABAAMAAAJqbAAL	 7900 JAMES	 07900
AAASABAAMAAAJqbAAM	 7902 FORD	 07902
AAASABAAMAAAJqbAAN	 7934 MILLER	 07934

14 rows selected.

SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'EMP_TEST';

TABLE_NAME	     INDEX_NAME 	  INDEX_TYPE		UNIQUENES STATUS   VISIBILIT
-------------------- -------------------- --------------------- --------- -------- ---------
EMP_TEST	     EMP_TEST_N7	  NORMAL		NONUNIQUE VALID    VISIBLE

SQL> select table_name, index_name, column_position, column_name
  2  from user_ind_columns
  3  where table_name = 'EMP_TEST'
  4  order by index_name, column_position;

TABLE_NAME	     INDEX_NAME 	  COLUMN_POSITION COLUMN_NAME
-------------------- -------------------- --------------- --------------------
EMP_TEST	     EMP_TEST_N7			1 EMP_NUMBER

SQL> select index_name, table_name, clustering_factor
  2  from user_indexes
  3  where table_name = 'EMP_TEST';

INDEX_NAME	     TABLE_NAME 	  CLUSTERING_FACTOR
-------------------- -------------------- -----------------
EMP_TEST_N7	     EMP_TEST				  1

B-Treeインデックスの高さ

B-Treeインデックスの高さは、ROWIDを返すために読み取るI/Oの量に影響します。 B-Treeが高くなれば、それだけ読み取るブロックが増えます。
インデックスのブロックは"sinble block read"されるため、それぞれにI/Oが発生することになります。
例えば、高さが2でユーザーに1行を返すインデックスでは、インデックスから3つ、テーブルから1つ、合計で4つのブロックを読み込む必要があります。
つまり、B-Treeインデックスが高くなればなるほど、データを取得するために必要なI/Oの量も増えるということです。

インデックスの統計情報を収集した後、USER_INDEXESでB-Treeの高さを確認できます。

SQL> select blevel, index_name, last_analyzed
  2  from user_indexes
  3  where index_name = 'EMP_TEST_N7';

    BLEVEL INDEX_NAME		LAST_ANAL
---------- -------------------- ---------
	 0 EMP_TEST_N7		30-JAN-22

B-Treeは、テーブル内のインデックスを張った列のNULL以外の値の数が狭い範囲で多くなると高くなっていきます。
インデックス内の行が多数削除されている場合も高くなっていきます。 高くなりすぎた場合は、インデックスを再構築すると高さを下げられます。
インデックスを再構築して高さを下げると、インデックスに対するI/Oは減ります。
目安としては、インデックス内の削除行の数が20-25%になったら、インデックスを再構築してB-Treeインデックスの高さを下げると、空き領域への無駄なI/Oを減らすことができます。

一般的には、データベースブロックサイズが大きいほど、インデックスのB-Treeの高さを低くできます。B-Treeインデックスの高さ(BLEVEL)のレベルが増えると、DML操作中の性能が劣化します。

ハードウェア性能や処理するSQLによって実際の効果はそれぞれだと思いますが、目安としては、数千万行以上あるテーブルに対するB-Treeインデックスの高さが4以上かインデックス内の削除行の数が20%以上の場合はインデックス再構築を検討すると良いのではないかと思われます。

OracleがB-Tree INDEXの高さを調整する方法

B-Tree LEVEL(BLEVEL)は、ルートブロックからリーフブロックまでのインデックスの高さです。
高さが0の場合は、ルートブロックとリーフブロックが同じです。 全てのインデックスは、1つのリーフノード(ブロック)から開始します(B-Tree Levelが0)。
行がインデックスに追加されると、Oracleはデータをリーフノードブロックに追加します。
行が挿入されていき、1つ目のブロックが埋まると、新しいブロックを2つ作成します。
このインデックス分割には2つのタイプがあります。
(90-10インデックス分割と50-50インデックス分割) どちらの方法で分割するのかは、行に挿入される値によります。

  • 新しく挿入される値が、インデックスに既に存在するどの値よりも大きい場合、ブロックの値を新しいブロックの1つにコピーし、もう一方の新しいブロックに新しく挿入する値を挿入する。(90-10インデックス分割)

  • 新しく挿入される値が、インデックスに既に存在するどの値よりも大きくない場合、50-50インデックス分割で分割する。新しいブロックの1つにインデックス値の小さい値の半分を、もう一方にインデックス値の大きい値の半分を挿入する。

いっぱいになった既存のブロックは、新しいリーフノードへのポインタのみになり、ブランチになります(今回の例では最初の1つのブロックが分割され、インデックスのルートブランチになります)。 ここまでの結果でインデックスのブランチレベルは1になります。

行がインデックスに挿入され続けてリーフノードがいっぱいになると、Oracleは新しいリーフブロックを作成します。
挿入される値が、現在のリーフノードブロック内のどの値よりも大きい場合、Oracleはその値を新しいブロックに挿入します。
値が現在のリーフノードブロック内のMAX値ではない場合、Oracleはブロックの値を半分(50-50)に分割し、現在のブロック内で低い方の値を保持し、新しいブロックに上位半分の値を挿入します。
次に、これらのリーフノードのブランチブロックが、新しいブロックと既存のブロックのポインターを持つように更新されます。
これが、ブランチノードブロックがいっぱいになるまで続きます。

ブランチノードがいっぱいになると、同じブロック分割操作が実行されます。 新しいブランチブロックが追加され、そのブロックの上位半分が新しいブロックにコピーされ、下位半分は既存のブランチブロックに残ります。
これによりインデックスの高さ(BLEVEL)は増えません。
単純にインデックスを検索するときにスキャンするブランチが増えます。
ルートブランチブロックがいっぱいになって分割された場合にだけB-Treeインデックスが高くなります。

1つのインデックスに対して2つの新しいブロックが作成されるのは、ルートブランチブロックが分割されたときだけです。
現在のルートブロックの内容は2つのリーフブロックに分割され、ルートブロックはインデックスTreeの最上位レベル(ルート)になります。
ルートブロックのブロックアドレスは変わらず、ルートが分割されたときに2つの新規ブロックを追加することで、インデックス・ツリーのバランスを調整します。

Updateがインデックスに与える影響

テーブルのUpdate操作による影響は、Updateでインデックスを張った列が更新された時だけ発生します。
そのため、多くの場合、Update操作はインデックスに全く影響を与えません。
Update文によりインデックスを張った列が更新されると、インデックス内の操作としては"Delete & Insert"になります。
古い値は削除済みとしてマークされ、新しい値が挿入されます。
インデックス・エントリは、Oracleの遅延ブロック・クリーンアウトという機能(データブロックのステータス更新を後で行う(遅延させる)ことによって、テータス更新のためのブロック読み取りを省略し、実行中のトランザクションを速く完了させる。ただし、その後、更新済みブロックが読み取られると、そのトランザクション内でステータス更新が行われる(つまり、そのための時間はかかる)。)によって削除されます。
インデックス・エントリが削除され、ブロックがクリーンアウトされた後に初めて、その領域を新しいエントリで再利用できるようになります。

Deleteがインデックスに与える影響

テーブルのDelete操作により、インデックスからエントリが実際に削除されて空の領域ができるわけではありません。
テーブルの行に対してDelete操作が発生すると、対応するインデックス・エントリは削除済みとしてマークされますが、実際にクリーンアウトされるまではインデックスに残っています。
インデックス・エントリのクリーンアウトが行われるケースとしては、そのブロックに対するInsert操作があります。
インデックス・エントリは、Oracleの遅延ブロック・クリーンアウト機能によっても消去されます。
インデックス・エントリが削除され、ブロックがクリーンアウトされた後、インデックス・ブロック内の領域を新しいエントリで再利用できるようになります。

ヒストグラム

ヒストグラムには、テーブルやインデックスの統計情報を収集する時にデータの分布を記録します。
この情報を使い、コストベースオプティマイザは、インデックスを使って行数を絞り込める時にインデックスを使い、行数を絞り込めない時にインデックスを使わない、という判断をします。
ヒストグラムは、インデックスだけでなく、テーブルの列に作成することもできます。

ヒストグラムを作成する主な理由は、テーブル内のデータが非常に偏っている場合にオプティマイザが適切に実行計画を立てられるようにするためです。
例えば、1つか2つの値がテーブルの大部分を占めるような場合は、インデックスを利用してもSQLを実行する際に必要なI/Oが減らないことがあります。
ヒストグラムを作成すると、コストベースオプティマイザは、いつインデックスを利用すると行を絞り込めるのか、または値が偏っているためテーブルの80%の行が返ってきてしまうのかを認識できるようになります。

ヒストグラムを作成する場合は、サイズを指定します。
サイズは、ヒストグラムのバケット数です。
各バケットには、列の値と行数に関する情報が含まれます。

実際に見てみます。以下のようにしてEMPLOYEES表のSALARY列のヒストグラムを10バケットで収集します。

SQL> execute dbms_stats.gather_table_stats('hr','employees',method_opt => 'for columns size 10 salary');

PL/SQL procedure successfully completed.

USER_TABLESのLAST_ANALYZEDで統計情報の収集日時が最新化されたことを確認できます。

SQL> col table_name for a20
SQL> select table_name, to_char(last_analyzed, 'YYYY/MM/DD HH24:MI:SS')
  2  from user_tables
  3  where table_name = 'EMPLOYEES';

TABLE_NAME	     TO_CHAR(LAST_ANALYZ
-------------------- -------------------
EMPLOYEES	     2022/02/06 14:09:24

USER_TAB_HISTOGRAMSで各バケットの値の範囲と行数を確認できます。 (SALARYが0〜2100までが1件、2101(2100+1)〜2600までが14(15-1)件、など)

SQL> select endpoint_number, endpoint_value
  2  from user_tab_histograms
  3  where table_name = 'EMPLOYEES'
  4    and column_name = 'SALARY';

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
	      1 	  2100
	     15 	  2600
	     30 	  3100
	     42 	  3900
	     53 	  6100
	     65 	  7300
	     77 	  8300
	     92 	 10000
	    104 	 14000
	    107 	 24000

10 rows selected.

Oracleのヒストグラムは幅をバランスするのではなく高さをバランスするように作られます。
従って、各バケットの値の幅には開きがあるが、行数は同じような数になります。
バケットの開始点と終了点は、それらの値を含む行数によって決まります。

データに偏りがない列にヒストグラムを利用しても、性能向上には繋がりません。

デフォルトでは、Oracleはヒストグラムに254個のバケットを作成します。 SIZEは1から254の間で指定できます。

【このSHIFT公式ブロガーの最新記事】


執筆者プロフィール:小澤 雅弘
DBMSベンダー、ISPなどでDB関連のプロジェクトを20数年経験。得意領域はDBのパフォーマンスチューニング。

お問合せはお気軽に
https://service.shiftinc.jp/contact/

SHIFTについて(コーポレートサイト)
https://www.shiftinc.jp/

SHIFTのサービスについて(サービスサイト)
https://service.shiftinc.jp/

SHIFTの導入事例
https://service.shiftinc.jp/case/

お役立ち資料はこちら
https://service.shiftinc.jp/resources/

SHIFTの採用情報はこちら
https://recruit.shiftinc.jp/career/