【実践】パフォーマンス・チューニング索引

Transcript

1. 【実践】 パフォーマンス・チューニング索引 森⽥ 暖⼰ ⽇本オラクル株式会社 2024年5⽉
2. 1. Oracle Databaseの索引概説 2. 索引を使⽤した検索 3. Bツリー索引の理解とその他の主要な索引 4. コンポジット索引の考え⽅ 5. まとめ Agenda Copyright © 2024, Oracle and/or its affiliates 2
3. Copyright © 2024, Oracle and/or its affiliates 3 Oracle Databaseの索引概説
4. 索引は表データの格納場所を格納し、問合せを⾼速化するためのオブジェクト n 索引がない場合: 表のすべてのデータにアクセスしてデータを検索する n 索引がある場合: 昇順に並べられた索引から検索対象⾏の場所(ROWID)を特定して、対象⾏にアクセス 索引 CUST_ID CUST_NAME CUST_ADDRESS 100 ⽥中 東京都… 101 鈴⽊ ⼤阪府… 102 ⼭⽥ 福岡県… … 498 佐藤 宮城県… 499 吉⽥ 北海道… 300~ 200~ 400~ 100 ROWID 101 ROWID 102 ROWID … 198 ROWID 199 ROWID 200 ROWID 201 ROWID 202 ROWID … 298 ROWID 299 ROWID 300 ROWID 301 ROWID 302 ROWID … 398 ROWID 399 ROWID 400 ROWID 401 ROWID 402 ROWID … 498 ROWID 499 ROWID 例︓索引のCUST_ID_IDX=498を使って検索する場合 CUST_ID_IDX（顧客番号索引） CUSTOMERS（顧客表） CUST_ID_IDX=498は300~ CUST_ID_IDX=498は400~ CUST_ID_IDX=498のデータ の格納場所を特定 Copyright © 2024, Oracle and/or its affiliates 4
5. 索引の利点 • 表に格納されているデータにアクセスする際に、 ディスクI/Oを削減するための様々な⼿段の⼀つ • 索引の有無によって、SQL⽂の表現を変更する必要なし • SQL⽂の性能が悪いときに索引によるチューニングを検討できる 主要な索引 • Bツリー索引(標準的な索引) • ツリー構造で特定のデータにアクセスできるようにした索引 • その他の索引 索引の概要(1) Copyright © 2024, Oracle and/or its affiliates 5 Bツリー索引の サブタイプ 説明 降順索引 特定の列のデータが降順で格納 逆キー索引 列の順序は保ちながら、各索引キーのバイトを物理的に逆にする 索引構成表 ヒープ構成表とは異なり、索引内にデータすべてを含む 索引タイプ 説明 ビットマップ索引 索引エントリにおいてビットマップを使⽤することで複数の⾏を指すよ うにしたもの。主にデータ・ウェアハウス⽤途や⾮定型問合せで使⽤ ファンクション索引 1つ以上の列を含むファンクションや式の値が計算され、索引に格 納されるもの データベース概要: 3 索引と索引構成表 / 管理者ガイド: 21 索引の管理 ※上記以外の索引(クラスター索引など)については以下のマニュアルを参照 Row1 Row2 Row3 Row4 ・ ・ ・ 表 Row 1 Row 2 Row 3 Row 4 ・・ Row n ・・ 索引 表 索引を利⽤しない場合 索引を利⽤する場合 ディスクI/O︓少 ディスクI/O︓多
6. ⼀意索引と⾮⼀意索引 • ⼀意索引(UNIQUE) • 表の複数⾏のキー列に重複した値が⼊らないことを保証 • ⾮⼀意索引 • 索引付けされた1つ以上の列に重複値が許可される NULL値 • すべてのキー列がNULLの表の⾏には索引は作成されない (ビットマップ索引は例外) 単⼀列索引とコンポジット索引 • 単⼀列索引 • 1つの列に対してのみ索引が作成される • コンポジット索引(連結索引: concatenated index) • 表の中の複数の列に対して索引が作成される • 列をデータを取得する問合せに最も有⽤な順序に配置 索引の概要(2) Copyright © 2024, Oracle and/or its affiliates 6 ⼀意索引 ⾮⼀意索引 emp_id emp_name dept_id 1 Trott 100 2 Smith 101 3 Yakub 102 4 Irwin 100 5 Lopez 101 … … … 単⼀列 索引 コンポジット 索引 emp_id emp_name dept_id 1 Trott 100 2 Smith 101 3 Yakub 102 4 Irwin 100 5 Lopez 101 … … … emp表 emp表 重複値 重複値
7. Copyright © 2024, Oracle and/or its affiliates 7 索引を利⽤した表へのアクセス emp_id emp_name dept_id 1 Trott 100 2 Smith 101 3 Yakub 102 4 Adams 103 5 Irwin 100 SELECT * FROM emp WHERE emp_name = 'Smith' AND dept_id = 101; 索引emp_name_idx 索引emp_dept_idx 索引emp_name_idxを 利⽤する場合 索引emp_dept_idxを 利⽤する場合 emp表 108以降 どの索引を使⽤するかは問合せオプティマイザが決定 Mason Frank Hills Frank Smith Mason Nalty Smith Trott Adams Baker Mより前 M以降 Fより前 F以降 Sより前 105 103 104 103 108 105 106 108 109 100 101 105より前 105以降 103より前 103以降 108より前 SELECT * FROM emp WHERE emp_name = 'Smith'; SELECT * FROM emp WHERE dept_id = 101;
8. Copyright © 2024, Oracle and/or its affiliates 8 索引を使⽤した検索
9. 全表スキャンと索引スキャン 全表スキャン(フルスキャン) 全てのデータを検索、⽐較する 索引スキャン(インデックススキャン) ROWIDを使⽤して直接該当データブロックにアクセス 索引スキャン(インデックススキャン) 効率が悪いケース データの検索⽅法 Copyright © 2024, Oracle and/or its affiliates 9 同じ表と索引でも、データの状況(返すべきデータが表に存在する割合など)によって最適なSQL実⾏計画が異なる。 Row1 Row2 Row3 Row4 ・ ・ ・ 表 Row 1 Row 2 Row 3 Row 4 ・・ Row n ・・ 索引 表 Row 1 Row 2 Row 3 Row 4 ・・ Row n ・・ 索引 表
10. 表領域 領域の管理単位 【参考】論理記憶域構造の概要 Copyright © 2024, Oracle and/or its affiliates 10 索引A 表A セグメント セグメント 1対1 1対1 データファイル データファイル 物理構成 論理構成 索引A 表A データ・ブロック データ格納におけるI/Oの単位。 デフォルト8KB。 表領域ごとに異なるサイズを指定可能 エクステント 複数の連続したデータ・ブロッ クで構成される、データベース 記憶域の割当ての論理単位 セグメント 1つのオブジェクトに割り当 てられた領域
11. • セグメントに含まれる未使⽤領域を除いたすべてのブロックを 読み込む。 • I/Oを減らすためブロックを⼀度にまとめて読み込む (マルチブロックREAD) • 上記の例では、8 Block単位で読み込むとI/Oは6回発⽣ • 索引にアクセスし、索引ブロックから⾏アドレス(ROWID)を 取得し、直接該当データの⼊った表ブロックにアクセス • 必要となるデータが格納されたブロックのみ読み込む (シングルブロックREAD) • 上記の例では、I/Oは4回発⽣ • (索引の階層や、索引による表データの絞り込み度合いにより I/O回数は異なる) 全表スキャンと索引スキャンの違い Copyright © 2024, Oracle and/or its affiliates 11 全表スキャン 索引スキャン 表A 表Aの セグメント ↑ 最⾼⽔位標(High Water Mark: HWM) 48 Block （4 Blockは未使⽤） ※HWM: 使⽤済みセグメント領域と未使⽤のセグメント領域を分割するライン 表A 表Aの セグメント ↑ HWM 索引A ↑ HWM 索引Aの セグメント
12. • 読み込むブロックは隣接しているため、ブロックより⼤き い I/O コールを使⽤可能 →マルチブロックREAD • マルチブロックREADを利⽤することにより、ディスクへの I/O 回数を減らすことができる 【マルチブロックREADの効果例】 • マルチブロックREADの設定を変えて、SQL⽂を実⾏し、 経過時間を⽐較 (対象テーブルには約19万⾏格納) マルチブロックREAD Copyright © 2024, Oracle and/or its affiliates 12 初期化パラメータ db_file_multiblock_read_count =8 8ブロック単位で データを読み込む SQL> ALTER SYSTEM SET db_file_multiblock_read_count=1; SQL> SELECT * FROM emp; 経過: 00:00:23.83 SQL> ALTER SYSTEM SET db_file_multiblock_read_count=128; SQL> SELECT * FROM emp; 経過: 00:00:15.22 <補⾜> db_file_multiblock_read_countの最⼤値は︖ db_file_multiblock_read_count = <最⼤I/Oサイズ / db_block_size> ※最⼤I/Oサイズは、オペレーティング・システムの制限を受けます。 ※ Oracle Base Database Service EE RU19.22におけるデフォルト値
13. 基本的な考え⽅ 充分に⼩さな表 • データベース・ブロック数が5未満の表 ※ • v$sysstat の「table scans (short tables)」 統計情報の対象となる表 Bツリー索引の得意な範囲検索 • プライマリー・キーを指定したアクセス • 表全体の10%-20%以内の範囲検索 ※ ⼤きな表 • パラレル処理の検討 • パーティショニングによる表の検討 • Oracle ExadataのSmart ScanやStorage Indexの検討 Bツリー検索で表のデータを10%-20%に絞 り込めない場合 • 新規で充分に表データを絞り込める 索引を検討 • コンポジット索引を⼯夫する • Bツリー以外の索引を検討 • ビットマップ索引 • ファンクション索引 • 複数表の結合条件によっては、索引だけ のアクセスを検討 全表スキャンと索引スキャンではどちらが効率的か Copyright © 2024, Oracle and/or its affiliates 13 全表スキャンが効率的 索引スキャンが効率的 要検討(ケースバイケース) ※ My Oracle Support: Systemwide Tuning Using STATSPACK Reports (Doc ID 228913.1) 表A 表Aの セグメント 索引A 索引Aの セグメント 表A 表Aの セグメント 索引A 索引Aの セグメント
14. ⼀般的な指針 • IS NULL や IS NOT NULL 条件を使⽤した検 索例 (COMM列に索引がある場合) • NOT 条件を使⽤した検索例 (DEPTNO 列に索引がある場合) • 列に計算式を含む検索例 (SAL列に索引がある場合) • 列に関数を使⽤する検索例 (HIRE_DATE列に索引がある場合) • LIKE 条件を使った検索例 (NAME列に索引がある場合) 索引を利⽤するためのSQL⽂の書き⽅ Copyright © 2024, Oracle and/or its affiliates 14 NULL⽐較やNOT(!=)を使⽤しない 列を演算しない 後⽅⼀致(中間⼀致)を使⽤しない 参考:津島博⼠のパフォーマンス講座 第9回 良いSQLについて SELECT name, deptno FROM emp WHERE comm = 0.1; SELECT name, deptno FROM emp WHERE comm IS NULL; SELECT name, deptno FROM EMP WHERE comm IS NOT NULL; SELECT name, deptno FROM emp WHERE deptno = 30; SELECT name, deptno FROM emp WHERE deptno != 30; SELECT name, deptno FROM emp WHERE sal > 10000/1.1; SELECT name, deptno FROM emp WHERE sal*1.1 > 10000; SELECT name, deptno FROM emp WHERE hire_date = TO_DATE('20240528','YYYYMMDD'); SELECT name, deptno FROM emp WHERE TO_CHAR(hire_date,'YYYYMMDD') = '20240528'; SELECT name, deptno FROM emp WHERE name LIKE 'Smi%'; SELECT name, deptno FROM emp WHERE name LIKE '%mit%'; SELECT name, deptno FROM emp WHERE name LIKE '%mith'; 中間⼀致 後⽅⼀致 ※ IS NULL、IS NOT NULLは使わないようにする DWH系で参照⽤途の表ならば ビットマップ索引の利⽤を検討可能 ※ INやORに置き換えることを検討する ※ ファンクション索引の利⽤を検討可能 ※ ファンクション索引の利⽤を検討可能 ※ どうしても⽂字列の中間⼀致、後⽅⼀致の検索が 必須ならば、Oracle Textを利⽤するように アプリケーション変更を検討。 SQL⽂もOracle Textを利⽤するように変更する ことになる
15. Copyright © 2024, Oracle and/or its affiliates 15 Bツリー索引の理解とその他の主要な索引 Bツリー索引の サブタイプ 説明 降順索引 特定の列のデータが降順で格納 逆キー索引 列の順序は保ちながら、各索引キーのバイトを物理的に逆にする 索引構成表 ヒープ構成表とは異なり、索引内にデータすべてを含む 索引タイプ 説明 ビットマップ索引 索引エントリにおいてビットマップを使⽤することで複数の⾏を指すようにし たもの。主にデータ・ウェアハウス⽤途や⾮定型問合せで使⽤ ファンクション索引 1つ以上の列を含むファンクションや式の値が計算され、索引に格納される もの
16. Bツリー索引の構造 Bツリー索引の構造 • 複数の範囲に分割された順序付きの値リスト • 2タイプのブロック 1. 検索⽤のブランチ・ブロック 2. キー値のソート⽤のリーフ・ブロック • すべてのリーフ・ブロックは⾃動的に同じ深さになるため どの位置からでも検索にかかる時間はほぼ同じになる ブランチ・ブロック • 下位レベルの索引ブロックを指す索引データが含まれる • キーを含む下位ブロックへのポインタが含まれる リーフ・ブロック • すべての索引付きデータ値と ⾏を検索するための対応をするROWIDに含まれる • 各エントリは(キー、ROWID)によってソートされる • リーフ・ブロックも互いにリンクしている Bツリー索引の理解 Copyright © 2024, Oracle and/or its affiliates 16 41~ 81~ ... 200~ 11~ 20~ ... 32~ 210~ 221~ ... 246~ リーフ・ブロック ブランチ・ブロック 49~ 54~ ... 78~ 246,rowid 248,rowid 248,rowid ... 250,rowid 220,rowid 222,rowid 223,rowid ... 228,rowid 0,rowid 0, rowid ... 10,rowid 11,rowid 11,rowid 12, rowid ... 19, rowid ... ... データベース概要: 3 索引と索引構成表
17. 表の索引対象の列が更新されるたびに、索引は⾃動的にメンテナンス • 該当リーフ・ブロックに索引エントリと ROWIDを追加 • 必要に応じて分割(後述) • 該当リーフ・ブロックから索引エントリと ROWIDを削除 • 領域が空いても⾃動的にマージされない ⇒断⽚化しやすい • 該当リーフ・ブロックに対して削除処理と 追加処理を⾏う Bツリー索引のメンテナンス Copyright © 2024, Oracle and/or its affiliates 17 表への⾏挿⼊(INSERT)時 表からの⾏削除(DELETE)時 表からの⾏更新(UPDATE)時 ROWID emp_id emp_name ROWID1 1 Trott ROWID2 2 Smith ROWID3 3 Yakub … … … ROWID8 8 Irwin ROWID9 6 Lopez emp_id emp_name 6 Lopez ①INSERT EMP表 emp_id ROWID 1 ROWID1 2 ROWID2 3 ROWID3 6 ROWID9 8 ROWID8 リーフ・ ブロック ②索引エントリの追加 10~ 30~ 30~ ROWID emp_id emp_name ROWID1 1 Trott ROWID2 2 Smith ROWID3 3 Yakub … … … ROWID8 8 Irwin ROWID9 6 Lopez ①DELETE EMP表 emp_id ROWID 1 ROWID1 2 ROWID2 3 ROWID3 6 ROWID9 8 ROWID8 リーフ・ ブロック ②索引エントリの削除 10~ 30~ 30~ ROWID emp_id emp_name ROWID1 1 Trott ROWID2 2 Smith ROWID3 3 Yakub … … … ROWID8 8 Irwin ROWID9 6 ⇒ 9 Lopez emp_id 6 ⇒ 9 ①UPDATE EMP表 emp_id ROWID 1 ROWID1 2 ROWID2 3 ROWID3 6 ROWID9 8 ROWID8 9 ROWID9 リーフ・ ブロック ②索引エントリの 削除と追加 10~ 30~ 30~ 索引 索引 索引 参考:津島博⼠のパフォーマンス講座 第8回 断⽚化について
18. 該当ブロックにそれ以上データが挿⼊できなくなると分割 リーフ・ブロックの分割 • 追加キーが最⼤値でない場合 • 追加キーが最⼤値の場合 ブランチ・ブロックの分割 • 常に50:50に分割 • ルートが⼀杯になると、索引の⾼さが⼀つ増える • これまでのルートが分割し、新しいルートができる Bツリー索引の分割(split) Copyright © 2024, Oracle and/or its affiliates 18 INSERT 分割(50:50) INSERT 分割(90:10) INSERT ブランチ・ブロックも分割(50:50) ブランチ リーフ INSERT ルート ルート ブランチ リーフ ブランチ リーフ ブランチ リーフ 索引の ⾼さ: 4 索引の ⾼さ: 3 参考:津島博⼠のパフォーマンス講座 第8回 断⽚化について
19. 索引構造の分析 メンテナンスの⽬安 • 索引の⾼さ: 4階層以上 • 削除済みエントリ数の占める割合: 20%以上 • DEL_LF_ROW/LF_ROWS > 0.2 • 同⼀ブランチ下のリーフ・ブロックのマージをおこなう • 追加のストレージ領域は不要 • 索引の⾼さは変わらない 【参考】Bツリー索引の断⽚化解消 Copyright © 2024, Oracle and/or its affiliates 19 参考:津島博⼠のパフォーマンス講座 第6回 パフォーマンスの基礎である索引について 索引の結合(coalesce) 索引の再構築(rebuild) ANALYZE INDEX <索引名> coalesce︔ データベース管理者ガイド: 21.2.13 索引の結合と再作成に関するコストと利点の検討 • 新しい索引を作成し、古い索引を削除 • ⼀時的に2倍のストレージ領域が必要 • 可能であれば索引の⾼さを縮⼩ • オンライン再構築(索引の再構築中にDML操作が可能)も可能 • オンライン再構築はEnterprise Editionの機能 ANALYZE INDEX <索引名> rebuild (online)︔ SQL> ANALYZE INDEX IX_TEST VALIDATE STRUCTURE ︔ SQL> SELECT name, height, lf_rows, del_lf_rows 2 FROM index_stats; NAME HEIGHT LF_ROWS DEL_LF_ROW ---------- ----------- ---------- ---------- IX_TEST 2 70000 30000
20. SELECT⽂で降順指定をした場合の索引の利⽤ Oracle DatabaseのBツリー索引のデータ格納を降順にする 問合せ処理において降順ソートをおこなう場合に考慮 • デフォルト(昇順) • ⽂字データ: 各バイトに含まれるバイナリ値の順序 • 数値データ: 最⼩の数から最⼤の数へ • ⽇付データ: 古い値から新しい値へ • DESCキーワードを指定することで上記デフォルトの逆順に索 引データを格納 降順索引の作成例 ユースケース • order by句の指定に昇順(ASC)、降順(DESC)指定が 混在している場合に、コンポジット索引を活⽤することで ソート処理を減らしたい • デフォルトの昇順索引においても、Bツリー 索引のリーフ・ブロックは双⽅向のリンクを 持っているため、ソート対象となる全ての 列が降順指定ならば、そのまま降順ソート で利⽤ 降順索引 Copyright © 2024, Oracle and/or its affiliates 20 CREATE INDEX sales_idx ON sales(sale_date ASC, product_id DESC); SELECT sale_date, product_id, quantity FROM sales WHERE sales_date > TO_DATE('20240101','YYYYMMDD') ORDER BY sales_date DESC, product_id DESC; 2024/01/01, 15, rowid 2024/01/01, 21, rowid 2024/01/01, 33, rowid 2024/01/02, 16, rowid 2024/01/02, 21, rowid SELECT sale_date, product_id, quantity FROM sales WHERE sales_date > TO_DATE('20240101','YYYYMMDD') ORDER BY sales_date ASC, product_id DESC; 2024/01/01, 33, rowid 2024/01/01, 21, rowid 2024/01/01, 15, rowid 2024/01/02, 21, rowid 2024/01/02, 16, rowid • 問合せのソート指定に含まれる列の昇順、降順が混在していると、 デフォルトの索引では条件絞り込みのみで使⽤され、別途ソート処 理が必要となる • 左の降順索引の作成例の索引があれば、索引をソート処理で使⽤ リーフ・ブロック (デフォルト)のイメージ リーフ・ブロック (⼀部降順)のイメージ
21. 索引列のデータをビット単位で反転させ、反転させたデータをソートして索引に格納する索引 逆キー索引 Copyright © 2024, Oracle and/or its affiliates 21 27~ 76~ 1 3 5 ・・・ 23 25 27 29 31 ・・・ ・・・ 49 51 53 55 ・・・ ・・・ 75 76 79 81 ・・・ 98 99 51~ 64~ 190~ ・・・ 6 ・・・ 58 ・・・ ・・・ 70 ・・・ 122 ・・・ ・・・ 134 ・・・ 186 ・・・ ・・・ 198 ・・・ 218 ・・・ 250 127~ <通常のBツリー索引> • 連続した値がINSERTされる環境では、特定のブロックに アクセスが集中し、性能問題が発⽣することがある <逆キー索引> • 値をビット単位で左右逆にすることによりアクセスを分散 INSERT 値 ビット単位で反転 91 (01011011) 218 (11011010) 92 (01011100) 58 (00111010) 93 (01011101) 186 (10111010) 94 (01011110) 122 (01111010) 95 (01011111) 250 (11111010) 96 (01100000) 6 (00000110) 97 (01100001) 134 (10000110) 98 (01100010) 70 (01000110) 99 (01100011) 198 (11000110) CREATE INDEX 索引名 ON 表名(列名) REVERSE; データベース概要: 逆キー索引
22. 逆キー索引の特徴 逆キー索引の利⽤ Copyright © 2024, Oracle and/or its affiliates 22 ユースケース • 索引のキー値を逆にすることにより、挿⼊値を索引のリーフ・キー全体に分散させることができるため 特定の索引ブロックにアクセスが集中することを防ぐことが可能 気をつけるべき点 • 範囲検索(<、>、between など)に索引を使うことができない • Oracle RAC構成において、索引ブロックのアクセスを分散させるために利⽤を検討することも INSERT 12 INSERT 13 INSERT 14 INSERT 11 INSERT 15 INSERT 16 12 (00001100) → 48 (00110000) 13 (00001101) → 176 (10110000) 14 (00001110) → 112 (01110000) 11 (00001011) → 208 (11010000) 15 (00001111) → 240 (11110000) 16 (00010000) → 8 (00001000) 参考: 実はシンプル︕RACチューニングの考え⽅
23. データ全体をBツリー索引に格納している索引 データを特定の順序で物理的に格納すべき場合に利⽤ • 情報取得(参照中⼼)、空間データ、 OLAPアプリケーションなど 索引構成表の作成例 • CREATE TABLE で「ORGANIZATION INDEX」を指定 • しきい値(PCTTHRESHOLD)を設定し、⼀部のデータを 別の領域に格納することもできる ユースケース • 主キーを使ったデータへのアクセスを 索引アクセスのみで完結 • データ全体がBツリー索引に 含まれているため 気をつけるべき点 • 仮想列を格納できない • Autonomous Database では利⽤できない (2024年5⽉時点) • ⾏オーバーフロー領域を利⽤すると、追加I/Oが発⽣ • 2次索引を作成できるが、論理ROWIDとして主キーの内容 (base64エンコード)を利⽤するため⾮効率 索引構成表 Copyright © 2024, Oracle and/or its affiliates 23 CREATE TABLE emp ( emp_id NUMBER(2) PRIMARY KEY, name VARCHAR2(40), ・・・・ address VARCHAR2(200), ・・・・ ) ORGANIZATION INDEX TABLESPACE idx PCTTHRESHOLD 20 OVERFLOW TABLESPACE data; データベース概要: 索引構成表の概要 SELECT * FROM emp WHERE emp_name = 'Smith'; 1,Trott,100 2,Smith,101 索引構成表: emp 索引: emp_name_idx Smith,2 Trott,1 主キー 主キー 【2次索引の例】 2 Smith 101 ... 20 W34th St., New York ...
24. 列値と各レコードがその値に該当するか否かをビットで表した索引 データ・ウェアハウス(DWH)⽤または問合せが多数の列を ⾮定型⽅式で参照する環境で利⽤ • 対象となる列はカーディナリティが低いこと • 索引付けの対象となる表は読み取り専⽤か、DML⽂による 重要な変更の対象外であること ビットマップ索引の作成例 ユースケース • DWH⽤途のOR条件などのビット演算による⾼速な検索 • DWH⽤途で索引サイズを⼩さくしたい 気をつけるべき点 • 複数⾏の情報をまとめて保持するため、表データの更新時 に他のユーザーからのアクセスを制限すべき ビットマップ索引 Copyright © 2024, Oracle and/or its affiliates 24 CREATE BITMAP INDEX emp_gender_idx ON emp(gender); empno name location gender ROWID1 1 Trott Tokyo M ROWID2 2 Smith Tokyo F ROWID3 3 Yakub Sapporo M ROWID4 4 Adams Osaka F ROWID5 5 Irwin Tokyo M ROWID Tokyo Osaka Sapporo ROWID1 1 0 0 ROWID2 1 0 0 ROWID3 0 0 1 ROWID4 0 1 0 ROWID5 1 0 0 ROWID M F ROWID1 1 0 ROWID2 0 1 ROWID3 1 0 ROWID4 0 1 ROWID5 1 0 emp表 まとめて情報をビットとして保持: Tokyo: (11001) Osaka: (00010) Sapporo: (00100) ビット情報に変換 まとめて情報をビットとして保持: M: (10101) F: (01010) データベース概要: ビットマップ索引の概要
25. 1つ以上の列を含むファンクションや式の値を計算し、索引に格納したもの SQL⽂のWHERE句としてファンクションや式があったとしても、 効果的にデータ抽出を⾏うために利⽤ • 算術式、あるいはSQLファンクション、PL/SQLファンクション、 パッケージ・ファンクションまたはCコールアウトを含む式のいず れかを使⽤できる ファンクション索引の作成例 ユースケース • ファンクション索引は事前計算を⾏い、式の値を格納 問合せでは式の計算のかわりに索引から得ることができる ⇒パフォーマンス向上 • より強⼒なソート機能の実現 • ⼤⽂字、⼩⽂字を区別しないソートのために(UPPER、LOWER) • ⾔語別のソートのためのSQL関数NLSSORTの呼び出し 気をつけるべき点 • INSERTやUPDATEの際はファンクション索引のメンテナンスの ためにファンクションが実⾏される • 頻繁に変更される列へのファンクション索引適⽤は注意 • ファンクション索引にはNULL値を含めることができない • 列にNULLを含む可能性のある索引式が含まれていないことを 確認する、もしくは索引式にNVL関数を使⽤してNULLを 他の値に置き換える ファンクション索引 Copyright © 2024, Oracle and/or its affiliates 25 CREATE INDEX emp_hire_date_idx ON employees (TO_CHAR(HIRE_DATE, 'YYYYMMDD'), HIRE_DATE); SQL> select first_name, department_id 2 from employees 3 where TO_CHAR(HIRE_DATE, 'YYYYMMDD') = '20051221'; ----------------------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | |* 2 | INDEX RANGE SCAN | EMP_HIRE_DATE_IDX | ----------------------------------------------------------------- 実⾏計画(⼀部抜粋) データベース概要: 3 索引と索引構成表 データベース開発ガイド: 11.4 ファンクション索引を使⽤する場合
26. Copyright © 2024, Oracle and/or its affiliates 26 コンポジット索引の考え⽅
27. 表の中の複数の列に対して作成される索引 • 列をコンポジット索引で、データを取得する問合せに最も有⽤な順序に配置 • 列は、表内で隣り合っている必要はない コンポジット索引とは Copyright © 2024, Oracle and/or its affiliates 27 emp_id emp_name dept_id ROWID1 1 Trott 100 ROWID2 2 Smith 101 ROWID3 3 Yakub 102 ROWID4 4 Adams 103 ROWID5 5 Irwin 100 Mason Frank,102,ROWID7 Hills,101,ROWID6 Frank Smith Mason,100,ROWID10 Nalty,103,ROWID8 Smith,101,ROWID2 Trott,100,ROWID1 Adams,103,ROWID4 Baker,100,ROWID9 CREATE INDEX emp_idx01 ON emp(emp_name, dept_id); SELECT * FROM emp WHERE emp_name = 'Smith' AND dept_id = 101; emp表 emp_idx01索引 1つ⽬のキー列を元に Bツリーのツリー構造が構成
28. 「データを取得する問合せに最も有⽤な順序に配置」とは 例) 商品表: 100,000 レコード 検索例 ① 「10,000円」以下の商品⼀覧 ② 「1年以内」に発売された商品⼀覧 ③ カテゴリ「冷蔵庫」の商品⼀覧 ④ 「A社」の商品⼀覧 ⑤ 「冷蔵庫」かつ「A社」の商品⼀覧 ⑥ 「冷蔵庫」かつ「A社」かつ 「10,000円」以下の商品⼀覧 「カテゴリ」、「製造元」を指定することによる絞り込み • 全商品数(100,000)÷カテゴリ数(20) = 5,000 • 全商品数(100,000)÷製造元数(500) = 200 • (データのバリエーションが均等であると仮定するならば、) 製造元で先に絞り込むべき ⇒ コンポジット索引の先頭カラムに「製造元」を指定する コンポジット索引におけるカラムの順序 Copyright © 2024, Oracle and/or its affiliates 28 列名 説明 特徴 商品ID 商品の⼀意の識 別⼦ 商品名 商品名 カテゴリ 商品カテゴリ (テレビ、冷蔵庫、 洗濯機等) カテゴリ数: 20 製造元 家電製品製造元 (A社、B社...) 製造元数: 500社 型番 家電製品の特定 モデルを識別 特徴 製品仕様 ⽂字列 価格 在庫数量 発売⽇ 製品の発売⽇ サプライヤーID 商品供給業者 単⼀列索引 候補 コンポジット索引 候補 ⑤ ⑥ プライマリー・キー ① ② ③ ④ CREATE INDEX 商品idx06 ON 商品表(製造元,カテゴリ,価格);
29. コンポジット索引を効果的に利⽤できるSQL⽂の条件指定とパフォーマンス • SQL⽂ • 指定カラムとコンポジット索引の利⽤可否とパフォーマンス コンポジット索引の効果的な利⽤ Copyright © 2024, Oracle and/or its affiliates 29 CREATE INDEX 商品idx06 ON 商品表(製造元,カテゴリ,価格); SELECT * FROM 商品表 WHERE 製造元 = ? AND カテゴリ = ? AND 価格 < ?; No. 製造元 カテゴリ 価格 コンポジット索引の利⽤可否とパフォーマンス 1 〇 〇 〇 ◎ コンポジット索引の使⽤が最適かつパフォーマンスも最適 2 〇 〇 × 〇 コンポジット索引を使⽤可能 No.2, No.3のケースは単⼀列索引よりコンポジット索引の⽅が速いケースが多い 3 〇 × 〇 4 〇 × × 5 × 〇 〇 △ コンポジット索引を使うこともある 詳細は、オプティマイザのアクセス・パス「索引スキップ・スキャン」を確認 6 × 〇 × 7 × × 〇 8 × × × × コンポジット索引は使⽤不可 • コンポジット索引の定義 ※前ページにおいて、コンポジット索引の候補として、⑤(製造元、カテゴリ)、⑥(製造元、カテゴリ、価格) のカラムから構成する案がありましたが、⑥があれば⑤は作成しなくてもよいことになります ※製造元のバリエーションが2,3社とバリエーションが極端に少ない場合、索引スキップ・スキャンを利⽤す る可能性があります。前ページの前提のように製造元が500社であれば使われないものと推測されます
30. 問合せにより⾏ソースから⾏を取得するための⼿法 表のすべての⾏が読み取られ、選択基準を満たしてい ない⾏がフィルタ処理される 主キーなどの⼀意索引が作成されている列に対し、 等価条件で検索 ORDER BYでソート操作を排除するなど、 さまざまな操作で利⽤ 代表的なオプティマイザのアクセス・パス Copyright © 2024, Oracle and/or its affiliates 30 SQLチューニング・ガイド: 8 オプティマイザのアクセス・パス 全表スキャン 索引⼀意スキャン 全索引スキャン ROWIDによる表アクセス 索引レンジ・スキャン ⾼速全索引スキャン 特定されたROWIDによるダイレクト・アクセス ほとんどの場合、索引スキャン後にROWIDによるアクセス が⾏なわれる 選択性が⾼く、スキャンの範囲が絞られる検索 問合せのすべての列が索引に存在する時に考慮 索引を表のようにマルチブロックREAD Row 1 Row 2 Row 3 ・・ Row n Row 1 Row 2 Row 3 ・・ Row n Row 1 Row 2 Row 3 ・・ Row n ROWID Row 1 Row 2 Row 3 ・・ Row n Row 1 Row 2 Row 3 ・・ Row n 表A 表Aの セグメント (アクセスなし) ↑ HWM 索引A 索引Aの セグメント 表アクセス 索引アクセス ※問合せのすべての列が索引に存在する時は表へアクセスしない ※問合せのすべての列が索引に存在する時は表へアクセスしない
31. コンポジット索引とアクセス・パスの例 Copyright © 2024, Oracle and/or its affiliates 31 参考:津島博⼠のパフォーマンス講座 第21回 索引について（２） | Id | Operation | Name | -------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| TAB01 | |* 2 | INDEX FULL SCAN | IX_TAB01_3 | | Id | Operation | Name | -------------------------------------------------- | 0 | SELECT STATEMENT | | |* 1 | INDEX RANGE SCAN | IX_TAB01_2 | | Id | Operation | Name | -------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| TAB01 | |* 2 | INDEX RANGE SCAN | IX_TAB01_1 | CREATE INDEX ix_tab01_1 ON tab01 (c1,c2); SELECT c1,c2,c3 FROM tab01 WHERE c1 = 10 ORDER BY c2; CREATE INDEX ix_tab01_3 ON tab01 (c2,c1); SELECT c1,c2,c3 FROM tab01 WHERE c1 = 10 ORDER BY c2; CREATE INDEX ix_tab01_2 ON tab01 (c1,c2,c3); SELECT c1,c2,c3 FROM tab01 WHERE c1 = 10 ORDER BY c2; WHERE句の⽐較条件に指定している列c1が、 コンポジット索引の先頭列としても設定されている そのため、INDEX RANGE SCANをおこなうことで、充分にデータが絞り 込まれると判断されている ORDER BY句として列c2が指定されており、 コンポジット索引の先頭列としても設定されている WHERE句の⽐較条件として指定している列c1が コンポジット索引の2番⽬の列として設定されている ソート操作を排除するためINDEX FULL SCANを選択 WHERE句の⽐較条件に指定している列c1が、 コンポジット索引の先頭列としても設定されている 問合せの全ての列がコンポジット索引に含まれているため、 表へのアクセスは⾏なわない ① ③ ② ※ SELECT⽂はすべて同じ
32. Copyright © 2024, Oracle and/or its affiliates 32 まとめ
33. Copyright © 2024, Oracle and/or its affiliates 33 1. Oracle Databaseの索引概説 • 索引は表データの問合せを⾼速化するための仕組み 2. 索引を使⽤した検索 • 全表スキャン: 全てのデータ・ブロックを読み込み、全てのデータを検索、⽐較する • 索引スキャン: 索引にアクセスし、ROWIDを使⽤して直接該当データ・ブロックにアクセスする • どちらが効率的かは条件によって異なり、Bツリー索引以外の索引や他の Oracle Database の機能の利⽤の検討が必要な場合もある 3. Bツリー索引の理解とその他の主要な索引 • Oracle Database ではBツリー索引の利⽤が基本となるが、Bツリー索引以外の索引もそれぞれに適したユースケースで利⽤を検討する 4. コンポジット索引の考え⽅ • コンポジット索引: 表の複数の列に対して作成され、カラムの先頭にカーディナリティの⾼いカラムを配置することで、パフォーマンスが向上 • オプティマイザからのアクセスパスは何種類もあり、その時に最適な実⾏計画をオプティマイザが決定する。 まとめ