Mac De Oracle: 実行計画は, SQL文のレントゲン写真だ！ No.62 / ORDBMS機能であるコレクション型の列をアクセスする実行計画ってどうなるの？

DROP SHADOW CTRL

TEXT DROP SHADOW OFF

GaragebandとiMovieで

検索

検索用語を入力

Web Blog内

検索フォームを送信

プロフィール

Twitter

Tweets by @discus_hamburg

2026年5月
日	月	火	水	木	金	土
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30
31

最近のトラックバック

[memo]メモを取りつつ楽しめるようなスタイル (wmo6hash::blog)
ダーリンはGoogleで軽食なり。 (今日のゴハン)
�� プログラムは読む時間の方が長い - 遥か彼方の彼方から (�� プログラムは読む時間の方が長い - 遥か彼方の彼方から)
今流行のケブンリッジ関数・・・しってました！？ (あにずむ)
poken (drmarのメモ)
Photo exhibition "Natural Faces of AIDS Orphans"　写真展「エイズ孤児の素顔」@JICA地球ひろば　広尾 (メガ放談)
創る心とおもてなし―第1回 Webエンジニア料理対決に参加する (Kwappa開発室)
創る心とおもてなし～第一回webエンジニア料理対決に参加した (Mac De Oracle)
徹底比較!! PostgreSQL vs MySQLパート2 with 勝手に vs Firebird (14) (キムラデービーブログ)
Oracle de XMLDB #18 (discus)

Oracle

OTN US

Apple

その他

ブログ de ノベルティ
ノベルティグッズをただ、ただ、記録していくブログ。Mac De Oracleでは出来ないネタなので、別ブログを立てた次第。

Googleの広告

RSS

RSSを表示する

当サイトについて

プライバシーポリシー

■プライバシーポリシー

■個人情報の利用目的

　当ブログでは、メールでのお問い合わせ、ブログへのコメントの際、名前（ハンドルネーム）等の個人情報をご登録いただく場合がございます。これらの個人情報は質問に対する回答などでご連絡する場合や、スパムコメント抑止のために利用させていただくものであり、個人情報をご提供いただく際の目的以外では利用いたしません。

■個人情報の第三者への開示

　当サイトでは、個人情報は適切に管理し、以下に該当する場合を除いて第三者に開示することはありません。

本人のご了解がある場合
法令等への協力のため、開示が必要となる場合

個人情報の開示、訂正、追加、削除、利用停止については、ご本人からの個人データの開示、訂正、追加、削除、利用停止のご希望の場合には、ご本人であることを確認させていただいた上で速やかに対応させていただきます。

■アクセス解析ツールについて

　当サイトでは、Googleによるアクセス解析ツール「Googleアナリティクス」を利用しています。このGoogleアナリティクスはトラフィックデータの収集のためにCookieを使用しています。このトラフィックデータは匿名で収集されており、個人を特定するものではありません。この機能はCookieを無効にすることで収集を拒否することが出来ますので、お使いのブラウザの設定をご確認ください。

目的：アクセス数を把握するため
送信先：Google LCC
Googleは受け取った情報をどのように利用するか：Googleの規約に従います
送信される情報：以下４点(R), ユーザー数、セッションの統計情報、おおよその位置情報、ブラウザとデバイスの情報

■広告の配信について

当サイトはGoogleの広告を配信しています。

Googleについて

Google及びGoogleのパートナー（第三者配信事業者）の提供する広告を設置しております。その広告配信にはCookieを使用し、当サイトやその他のサイトへの過去のアクセス情報に基づいて広告を配信します。

Google が広告 Cookie を使用することにより、当サイトや他のサイトにアクセスした際の情報に基づいて、Google やそのパートナーが適切な広告を表示しています。

お客様はGoogleアカウントの広告設定ページ（https://adssettings.google.com/u/0/authenticated）で、パーソナライズ広告を無効にできます。また aboutads.info のページにアクセスして頂き、パーソナライズ広告掲載に使用される第三者配信事業者のCookieを無効にできます。

その他、Googleの広告におけるCookieの取り扱い詳細については、Googleのポリシーと規約ページ（https://policies.google.com/technologies/ads）をご覧ください。

■免責事項

　当サイトからリンクやバナーなどによって他のサイトに移動された場合、移動先サイトで提供される情報、サービス等について一切の責任を負いません。当サイトのコンテンツ・情報につきまして、可能な限り正確な情報を掲載するよう努めておりますが、誤情報が入り込んだり、情報が古くなっていることもございます。当サイトに掲載された内容によって生じた損害等の一切の責任を負いかねますのでご了承ください。

■プライバシーポリシーの変更について

　当サイトは、個人情報に関して適用される日本の法令を遵守するとともに、本ポリシーの内容を適宜見直しその改善に努めます。
修正された最新のプライバシーポリシーは常に本ページにて開示されます。

2024年7月12日 (金) / Author : Hiroshi Sekiguchi.

実行計画は, SQL文のレントゲン写真だ！ No.62 / ORDBMS機能であるコレクション型の列をアクセスする実行計画ってどうなるの？ Tweet

前回の実行計画は, SQL文のレントゲン写真だ！は2023/1だったので、Long time no seeな感じではありますが、このシリーズもネタストックが多いのでまだまだ続けていく予定です:)

前回の実行計画は, SQL文のレントゲン写真だ！のエントリーは以下
実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 / No.61 / ANSI JOINのおまけのおまけ

さて、今日のレントゲンからはどのような状況が見えるのでしょうか。。。

一から準備するのは大変なのでサンプルスキーマである、OEの customers表を利用します。
Oracle Database Release 18 / Database Sample Schemas / 4.5 OEサンプル・スキーマの表の説明

オブジェクト型の列が複数ありますね。ニコニコ（よいサンプルだw）
（今回のエントリーでは、CUST_ADDRESS、CUST_ADDRESS列を利用します）

OE@orclpdb1> desc customers
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 CUSTOMER_ID                               NOT NULL NUMBER(6)
 CUST_FIRST_NAME                           NOT NULL VARCHAR2(20)
 CUST_LAST_NAME                            NOT NULL VARCHAR2(20)
 CUST_ADDRESS                                       CUST_ADDRESS_TYP
 PHONE_NUMBERS                                      PHONE_LIST_TYP
 NLS_LANGUAGE                                       VARCHAR2(3)
 NLS_TERRITORY                                      VARCHAR2(30)
 CREDIT_LIMIT                                       NUMBER(9,2)
 CUST_EMAIL                                         VARCHAR2(40)
 ACCOUNT_MGR_ID                                     NUMBER(6)
 CUST_GEO_LOCATION                                  MDSYS.SDO_GEOMETRY
 DATE_OF_BIRTH                                      DATE
 MARITAL_STATUS                                     VARCHAR2(20)
 GENDER                                             VARCHAR2(1)
 INCOME_LEVEL                                       VARCHAR2(20)

利用する列に絞ってデータを覗いてみます。
（オブジェクト型を利用している2列を含む５列に絞ってあります）

OE@orclpdb1> r
  1  SELECT
  2    c.customer_id
  3    , c.cust_first_name
  4    , c.cust_last_name
  5    , c.cust_address AS address
  6    , c.phone_numbers AS phones
  7  FROM
  8    customers c
  9  WHERE
 10*   customer_id = 348

CUSTOMER_ID CUST_FIRST_NAME      CUST_LAST_NAME       ADDRESS(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_P PHONES
----------- -------------------- -------------------- -------------------------------------------------- ----------------------------------------------------
        348 Kelly                Lange                CUST_ADDRESS_TYP('Piazza Del Congresso 22', '36121 PHONE_LIST_TYP('+39 49 012 4373', '+39 49 083 4373')
                                                      9', 'San Giminiano', NULL, 'IT')

ひとつめは、CUST_ADDRESS_TYP型、住所情報ですね。複数ある属性から、STREET_ADDRESS　だけをアクセスすることにします

OE@orclpdb1> set linesize 80
OE@orclpdb1> desc CUST_ADDRESS_TYP
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 STREET_ADDRESS                                     VARCHAR2(40)
 POSTAL_CODE                                        VARCHAR2(10)
 CITY                                               VARCHAR2(30)
 STATE_PROVINCE                                     VARCHAR2(10)
 COUNTRY_ID                                         CHAR(2)

ふたつめは、PHONE_LIST_TYP型、複数の電話を持つことを前提としたモデルですが、最大で5個までですね。それ以上の人はどうするのでしょう？（という余計なことはここでは気にしないでw
電話番号は２つは不要なので、最初の電話番号だけを利用することします。 1顧客1電話番号（主番号）という問い合わせにすると面白そうですね。すこし難易度を上げたSELECT文のほうが面白いですし:)

OE@orclpdb1> desc PHONE_LIST_TYP
 PHONE_LIST_TYP VARRAY(5) OF VARCHAR2(25)

ところで、
オブジェクト型に出くわすと、Oracle Databaseって色々飲み込んだというか取り込んだORDBMSでもあることを思い出させてくれますw
それと同時に、ああああ〜〜っ。構文どうだっけーーーーと。（サクッと出てこないw
(ちなみに、Oracle Database 8の頃にORDBMSの機能が取り込まれた。と言う、ちょっと曖昧な記憶がありますが、おそらくその頃なので、1997年ぐらいですよねw)

ということで、オブジェクト型にアクセスしつつ、今日の実行計画というレントゲン写真を診ながら動きを確認してみましょう。
CUST_ADDRESS_TYP型の属性は単純なので修飾してあげればよいですよね。

 OE@orclpdb1> r
  1  SELECT
  2    customer_id
  3    , cust_first_name
  4    , cust_last_name
  5    , cust_address.street_address
  6  FROM
  7    customers 
  8  WHERE
  9*   customer_id = 348
  , cust_address.street_address
    *
行5でエラーが発生しました。:
ORA-00904: "CUST_ADDRESS"."STREET_ADDRESS": 無効な識別子です。

あ”〜やっちまった。オブジェクト型を扱うときは、表エイリアスが必要だったはず！

OE@orclpdb1> r
  1  SELECT
  2    c.customer_id
  3    , c.cust_first_name
  4    , c.cust_last_name
  5    , c.cust_address.street_address
  6  FROM
  7    customers c
  8  WHERE
  9*   customer_id = 348

CUSTOMER_ID CUST_FIRST_NAME      CUST_LAST_NAME       CUST_ADDRESS.STREET_ADDRESS
----------- -------------------- -------------------- ------------------------------
        348 Kelly                Lange                Piazza Del Congresso 22

では、cusomters表に含まれる、CUST_ADDRESS_TYP型を含むクエリーの実行計画はどうなるか...

よく見る index unique scan + table access by index rowid、ユニークキーまたは主キーによる1行だけのアクセスですね。ふむふむ。

OE@orclpdb1> r
  1  explain plan for SELECT
  2    c.customer_id
  3    , c.cust_first_name
  4    , c.cust_last_name
  5    , c.cust_address.street_address
  6  FROM
  7    customers c
  8  WHERE
  9*   customer_id = 348

解析されました。

経過: 00:00:00.00
OE@orclpdb1> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 4238351645

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    37 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | CUSTOMERS_PK |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUSTOMER_ID"=348)

次に、customers表に含まれるコレクションオブジェクPHONE_LIST_TYP型をアクセスしてみます。
少しずつオブジェクト型を思い出してきましたw

コレクション型は、表として扱う必要があるので、TABLEファンクションを使う必要がありますよね（思い出してきましたw　パイプラインファンクションと同じ考え方）
PHONE_LIST_TYP型はコレクション型で属性名のないVARCHAR2のVARRAY型です。TABLEファンクションを利用した場合、単一列の仮想表として返されるので、列名には、COLUMN_VALUE疑似列を使います。XML/JSONでも応用できる知識なので覚えておくと便利です。
Oracle Database Release 19 / SQL言語リファレンス / COLUMN_VALUE疑似列
c.cust_address.street_addresとして、CUST_ADDRESS_TYP型のstreet_addres属性まで指定することで通常の列のように扱えます。それほどトリッキーな構文ではないですよね。

コレクション型のアクセス方法は独特なので、いざという時に慌てないよう、日頃からSQLパズルなどで遊んでいると良いかもしれません。
ある程度使えるようになっていないと、道に迷って時間を溶かすことになるので。。。

customers表のPHONE_LIST_TYP型コレクションには複数の電話番号が含まれています（よくありますよね。固定電話番号、携帯とか複数登録させるユーザー登録画面など）
このSELECT文では、PHONE_LIST_TYP型コレクションから最初の電話番号を主電話番号として取り出し(ROW_NUMBERウィンドウ関数を利用している箇所)、1顧客N電話番号ではなく、1顧客1電話番号（主番号のみ）でリストしています。

表エイリアスは必須（前述の通り）になりますが、もう一つ、コレクション型を仮想表にするTABLEファンクションを利用しています。ここがポイント。
結合しているイメージでOK。中に抱えているコレクションを仮想表として取り出し結合していると思えばイメージしやすいはず:)　
（実行計画では、それをそのままおこなっている部分があります。実行計画の赤字部分の操作を、よーく確認しておいてください）

OE@orclpdb1> r
  1   SELECT
  2     cust.customer_id
  3     , cust.phone# AS primary_phone#
  4   FROM
  5   (
  6     SELECT
  7       c.customer_id
  8       , cr.COLUMN_VALUE AS phone#
  9       , ROW_NUMBER()
 10           OVER (
 11             PARTITION BY c.customer_id
 12             ORDER BY c.customer_id
 13       ) AS phone_count
 14     FROM
 15       customers c
 16       , TABLE(c.phone_numbers) cr
 17     ORDER BY
 18       c.customer_id
 19       , phone_count
 20   ) cust
 21   WHERE
 22*    cust.phone_count = 1

CUSTOMER_ID PRIMARY_PHONE#
----------- ---------------------------------------------------------------------------
        101 +1 317 123 4104
        102 +1 317 123 4111

....中略....

        980 +91 80 012 3837
        981 +86 10 012 3839

このコレクション型から配列の属性を取り出すSELECT文の実行計画は以下の通り。コレクション自体は表の列として保持されているのでcustomers表以外へのオブジェクトにはアクセスしませんが、TABLEファンクションで作り出した仮想表から属性を取り出す際の操作として、COLLECTION ITERATOR PICKLER FETCH が行われています。
この　COLLECTION ITERATOR PICKLER FETCH　って以前、実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 9 / No.44 / COLLECTION ITERATOR PICKLER FETCHで紹介したことがあるので覚えている方も多いと思います。テーブルファンクション特有の操作です。

また、ウィンドウ関数で必要となるソートをCUSTOMERS表の主キーをINDEX FULL SCANすることで回避していのも面白い最適化です。
ソートするよりソート済みの主キーを使ってアクセスしたほうが効率的と判断した結果、INDEX FULL SCAN -> WINDOW NOSORT という操作が行われています。
データ量が多くなるケースだとソートを避ける最適化が多く見られるものOracle Databaseのオプティマイザの特徴ですね。わかりやすくて好きです:)　
悪くない実行計画ではないでしょうか。

OE@orclpdb1> r
  1   EXPLAIN PLAN FOR SELECT
  2     cust.customer_id
  3     , cust.phone# AS primary_phone#
  4   FROM
  5   (
  6     SELECT
  7       c.customer_id
  8       , cr.COLUMN_VALUE AS phone#
  9       , ROW_NUMBER()
 10           OVER (
 11             PARTITION BY c.customer_id
 12             ORDER BY c.customer_id
 13       ) AS phone_count
 14     FROM
 15       customers c
 16       , TABLE(c.phone_numbers) cr
 17     ORDER BY
 18       c.customer_id
 19       , phone_count
 20   ) cust
 21   WHERE
 22*    cust.phone_count = 1

解析されました。

経過: 00:00:00.17
OE@orclpdb1> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2349769165

-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |   319 | 12760 | 22981   (2)| 00:00:01 |
|*  1 |  VIEW                                |              |   319 | 12760 | 22981   (2)| 00:00:01 |
|*  2 |   WINDOW NOSORT                      |              |  2605K|   201M| 22981   (2)| 00:00:01 |
|   3 |    NESTED LOOPS                      |              |  2605K|   201M| 22981   (2)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID      | CUSTOMERS    |   319 | 25201 |    10   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN                 | CUSTOMERS_PK |   319 |       |     1   (0)| 00:00:01 |
|   6 |     COLLECTION ITERATOR PICKLER FETCH|              |  8168 | 16336 |    72   (2)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CUST"."PHONE_COUNT"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "C"."CUSTOMER_ID" ORDER BY  NULL )<=1)

ということで、実行計画は読めてなんぼ、という感じなので、読む練習は怠らないようにしておきたいものですね。いろいろな機能が追加されてくるので。。。RDBMSっぽくないやつがRDBMSっぽい世界でどのように最適化され、実行されていくのか。。。最近はAI というかVector？もあるし

雨ばかりの東京より。雨が降らなきゃ猛暑だし。

ではまた。

投稿日時 2024年7月12日 (金) 12時00分 Oracle, チューニング, FAQ, SQL, パフォーマンス | 固定リンク | 0

Mac De Oracle

MacOS X で動作する Oracle　が正式にリリースされたのだ！ 1996年にリリースされた、Personal Oracle7 for MacOS以来、封印されていたMacOS版が、（2005年）９年ぶりに正式リリースされたのでいろいろあそんでみたくなった！

DROP SHADOW CTRL

GaragebandとiMovieで

検索

Twitter

気まぐれ写真館

最近の記事

最近のコメント

最近のトラックバック

バックナンバー

カテゴリー