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_NUMBEREMP_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 dumpData 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
→07369col 1にはROWIDが入っています。Block番号39579の行0を示しています。
03 00 9a 9b 00 00
→768 39579 0さて本題のクラスタリングファクタですが、これはテーブルに対してインデックスがどの程度整列して並んでいるかを表しています。
インデックスをスキャンしたあと、テーブルをスキャンする際にどの程度コストがかかるのかをチェックするために使用します。
(クラスタリングファクタにインデックスのカーディナリティ度を掛けるとコストが得られます。
オプティマイザは実行計画を決定する要素としてこのコストを使います。
ただし、ユーザが参照できる実行計画上は、カーディナリティ度やクラスタリングファクタの数値自体を確認することはできません。)。
クラスタリングファクタはインデックス・スキャン時に読み取られるブロック数を記録します。
使用するインデックスのクラスタリングファクタが大きい場合は、隣接する行が異なるブロックに散らばっているため、各インデックス・ブロックに対応するテーブルの行を取得するために多くのデータブロックを参照する必要があります。
クラスタリングファクタがテーブルのブロック数に近い場合、インデックスは順序良く並んでいます。
クラスタリングファクタがテーブルの行数に近い場合、インデックスは順序良く並んでいません。
クラスタリングファクタは次のように計算されます。
インデックスを順番にスキャンする。
現在読んでいるインデックス値に対応するROWIDのBlock Number部を、直前のインデックス値のものと比較する(インデックス内の隣接する行を比較する)
現在の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 1B-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-22B-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:24USER_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/