Oracle University 講師が ORACLE MASTER 取得を目指している方に向けて試験トピックを解説するブログ連載講座。「ORACLE MASTER Silver SQL 2019」資格取得に向けた試験トピックについて解説します。

————————————————-

今回はORACLE MASTER Silver SQLの試験トピック「索引、シノニムとシーケンスの管理 – 索引の管理 -」に関連する問題をご紹介いたします。ORACLE MASTER Silver SQL(Exam Number: 1Z0-071-JPN)の詳細につきましては以下URLをご参考ください。

Oracle Database SQL Exam Number: 1Z0-071

URL: https://education.oracle.com/ja/oracle-database-sql/pexam_1Z0-071

————————————————-

今回の問題はデータベースにとって非常に重要なオブジェクトである索引に関する内容をやっていきましょう。適切な索引を作成することはデータベースのパフォーマンスを向上することにつながります。索引の管理やOracleデータベースの機能に関する内容もありますので確実に押さえておきましょう。

(解説)

それでは索引についての解説を行いましょう。索引はデータベース内のデータに素早くアクセスするための非常に重要なオブジェクトになります。索引があると何故データアクセスが早くなるか？については、身近なもので例えて考えると理解しやすいと思います。皆さんは書籍などで自分の探したい用語が記載されているページを素早く開くためにどのようなことを実施しているでしょうか？先頭ページから1ページずつ探す・・ということは行っていないですよね。書籍内にある索引を使って自分が確認したい内容が記載されているページをピンポイントで開いているかと思います。イメージとしてはデータベースの索引もそれと同じと考えるとよいでしょう。

以下にデータベースの索引の図を載せておきますので、これを使用して索引構造についてもう少し詳しく確認していきましょう。

まず左上の「従業員ID索引」の図をご確認ください。索引の構造は「ルート・ブロック」、「ブランチ・ブロック」、「リーフ・ブロック」の3つに分けることができます。「従業員ID索引」を見ていただくと、ルート(根)の部分から、ブランチ(枝)に分かれて、リーフ(葉)に広がっていくというツリー構造の形になっているのがわかると思います。

末端のリーフ・ブロック内には索引を構成している列値(上図では従業員ID)と、その列値が表のどこに格納されているかを指し示す物理アドレス情報「行アドレス」が格納されています。この行アドレスはROW IDといいますので合わせて覚えておきましょう。

上図では従業員表の従業員IDに索引を作成している例ですが、右上に記載されているSELECT文を実行するとどうなるでしょうか。WHERE句に「従業員ID = 1003」としており、索引を使用することで該当データの物理アドレスROW IDを取得してピンポイントで表のデータにアクセスすることが可能となっていますね。

索引を使用する際には「ルート」⇒ 「ブランチ」⇒「リーフ」の方向でリーフ・ブロック内にあるROW IDを取得しますが、ルートやブランチには、末端のリーフ・ブロックに効率よくアクセスするための管理データが格納されていると考えるとよいでしょう。「ルート」から「リーフ」までアクセスする索引内の動作について難しく考える必要はありません。先ほど身近なものの例で、書籍の索引を見るというお話をしたと思いますが、書籍の索引も「A」から「Z」、「あ」から「ん」のようにちゃんとソートされて記載されていますよね。それにより索引内でも目的のキーワードを探すのが簡単になっていると思いますが、データベースの索引についてもデータがソートされており目的のデータに効率よくアクセスすることができると考えていただくと理解しやすいと思います。

ちなみに、索引を使用するかどうかの判断はOracleデータベースが内部的に判断してくれます。索引を使用することで効率よくデータの絞り込みができ、該当データへのアクセスが早くなると判断した場合は索引を使用して動作してくれますので基本的にユーザー側で意識する必要はありません。

そうなると、「Oracle データベースが索引の使用有無を自動で判断してくれるなら表の列に対して全部索引を作っておけばいいのでは？」と思うかもしれませんが、そのような操作はしてはいけません。索引の作成は、列のデータの種類が多く存在していることや、実行されているSQL文が該当列を絞り込み条件で指定しているなど、しっかりと確認したうえで、適切な列に対して作成することを必ず心がけてください。

その理由について、索引を構成している列値に対して更新処理が入った場合について考えてみます。以下の図を確認してください。

まずは右上のSQL文を確認してください。従業員表の従業員ID 1003の人のIDを901に変更している内容です。従業員ID列は索引が構築されていました。表に対しての更新処理は該当データをUPDATEして終了になりますが、索引側のデータはどうでしょう？

索引のリーフ・ブロック内はデータがソートされて格納されていると説明しました。データがソートされていることによりルートやブランチ・ブロック内の管理データを効率よくたどることによって該当のリーフ・ブロックにアクセスができていたわけですが、リーフ・ブロック内のデータを単純にUPDATEしてしまうとデータのソートが崩れてしまい、該当データにアクセスできなくなってしまいます。そのため索引への更新処理は、該当データをDELETEして、データのソートが保たれるように適切なリーフ・ブロック内へINSERTする処理が実行されます。この動作の際に該当リーフ・ブロックが一杯だった場合はページ分割が発生したり、必要に応じてブランチ・ブロックやルート・ブロックへの更新が入る場合もあります。こういった索引へのメンテナンス処理についてはユーザー側が意識する必要はなく、Oracleデータベースが自動で行ってくれますが、もし無意味にすべての列に対して索引が作成されていたら・・・どうでしょうか。領域の無駄という点もありますが、この索引へのメンテナンス動作がオーバーヘッドになり、SQL処理が遅くなることが想像できるのではないかと思います。このような理由から索引の構築は、列データの種類やSQL文の内容も考慮して適切なものを作成する必要があるというわけですね。

それでは索引の構造について確認ができたと思いますので、後は問題の選択肢の内容を見ながら説明していきましょう。

選択肢1は正しい内容ですね。索引に対してはユーザーが意識することなく自動でメンテナンス処理が実行されています。

選択肢2は誤りの内容になるのですが追加で説明が必要ですね。先に解説したとおり索引というのは適切なものを作成し、不要なものは領域やオーバーヘッドを考慮して削除すべきなのですが、索引をいきなりDROPしてしまうというのは非常にリスクがありますよね。「多分使ってないだろうなぁ・・」的な感じで削除できるようなオブジェクトではありません。そのためOracleデータベースでは索引の使用有無の影響を確認するための機能の一つとして「不可視索引」という機能を提供しています。この不可視索引の機能を使用すると、Oracleデータベースから一時的に索引を見えなくさせる(不可視)ことができます。

設定方法を以下に記載しておきますので参考にしてください。

※OPTIMIZER_USE_INVISIBLE_INDEXES初期化パラメータがデフォルトのFALSEの場合

※オプティマイザとはサーバー・プロセスに実装されているSQLの実行計画を生成する機能

不可視索引機能のよいところは、INVISIBLEと設定しても索引のメンテナンス作業は実行されるという点です。

INVISIBLE設定後も索引自体は存在し、索引列に対して更新があってもメンテナンスが行われるため、VISIBLE設定を行えば簡単に元に戻せます。

さて、選択肢2の内容に戻りますが、「INVISIBLE設定を行った索引に対して手動でメンテナンスが必要」と書いてありますので誤りということになりますね。

選択肢3の内容は正しい内容になります。索引を使用するメリットとしてはデータを効率よく絞り込み、データに素早くアクセスするという点になります。PRIMARY KEY制約や、UNIQUE制約を設定するということは一意の値を保証するということになり、これらの列に索引があると非常に効率よく絞り込みができます。そのためPRIMARY KEY制約、UNIQUE制約を設定した列に対しては自動的に索引が作成されるようになっています。

選択肢4は誤りです。索引は1つの列だけでなく、複数の列を指定して作成することが可能です。このような索引のことをコンポジット索引(連結索引)といいます。実行しているSQL文のWHERE句の条件なども考慮して複数の列を指定した索引を作成することで、単一列を指定した索引よりも効率があがる場合があります。

選択肢5も誤りですね。文章の前半部分に「常にその索引を使用する動作」とありますが、索引があるからといって必ず索引を使うということではありません。索引を使用するかどうかについては、Oracleデータベースが、索引によりデータを効率よく絞り込むことができると判断した場合です。尚、この選択肢は後半部分の文章も間違ってますね。索引を作成する列については、データの種類やSQL文のWHERE句の条件などを考慮して適切な列に対して作成することが重要です。

選択肢6は正しい内容になります。索引を構成する元データは表のデータになりますが、表と索引はそれぞれ独立したオブジェクトになりますので、索引の削除中であっても表データ側にロックがかかるなどの影響はありません。

以上の結果から、正解の選択肢は１、３、６になります。

今回は、 データベース内のデータアクセスの際に非常に重要なオブジェクトとなる索引について学習しました。索引の構造や、索引に関するOracleデータベースの機能などをしっかりと学習することで、SQLのパフォーマンス・チューニングといった高度な内容にもつながっていきますのでしっかりと内容を押さえておきましょう！それでは今回の講義これで終了とします。お疲れ様でした。

————————————————-

– ORACLE MASTER Silver SQL 2019 のご紹介

– ORACLE MASTER Silver SQL 2019 試験記事トピック一覧

————————————————-

その他の ORACLE MASTER 試験トピック解説講座シリーズ：

【Oracle University講師によるORACLE MASTER Gold DBA 2019 試験トピック解説講座】トピック一覧

————————————————-