Mac De Oracle: AI

DROP SHADOW CTRL

TEXT DROP SHADOW OFF

GaragebandとiMovieで

検索

検索用語を入力

Web Blog内

検索フォームを送信

プロフィール

Twitter

Tweets by @discus_hamburg

2025年12月
日	月	火	水	木	金	土
	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とAmazonの広告を配信しています。

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）をご覧ください。

Amazonについて

AmazonはアソシエイトリンクのURLに含まれるパラメータを通じて利用者が直前に閲覧したアソシエイトサイトに関する情報などを収集し、これをアソシエイトパートナーに対する紹介料の算定、報告及び支払いに使用したり、アソシエイトパートナーによる複数のウェブサイトや商品戦略毎のパフォーマンスの分析に供したりしています。

アソシエイトパートナーの利用目的：閲覧の傾向や履歴の分析のため広告効果の分析のため
外部送信プログラム名：Amazonアソシエイトプログラム
送信先の会社名：アマゾンジャパン合同会社
送信する情報の内容：閲覧した内容についての情報送信先の利用目的：自社の広告宣伝を目的とした広告情報の掲載のため、ウェブサイトやアプリの監視のため

Amazonによる個人に関する情報の取得及び取扱いに関する方針につきましてはAmazon.co.jpプライバシー規約をご覧ください。

■免責事項

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

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

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

2025年7月 4日 (金)

実行計画は, SQL文のレントゲン写真だ！ No.67 / AI Vector Search - VECTOR INDEX HNSW SCAN のバリエーション

Previously on Mac De Oracle
前回は、VECTOR INDEX はどこ？、見積もりサイズだとそれなりのサイズだったのに... の謎を探るべく、我々は洞窟の奥へ向かった！(完結編)でした。
今日は、再びレントゲン写真に戻り、VECTOR INDEX HNSW SCAN のバリエーションをいくつか確認しておこうと思います。
（なかなか興味深いので、一度診ておけば、いざというときに慌てなくて済むと思います）

いきなりってのもあれなので、先に以下のマニュアルを一読しておくと良いと思います。マニュアルでもポイントが解説されているネタなので:)　解説しないとちょっと分かりずらい点が多いからだと思いますがw

Oracle Database 23ai / Oracle AI Vector Search ユーザーズ・ガイド / HNSW ベクトルインデックスのオプティマイザプランのバリエーションを解説している章があります。なかなか興味深い。
おそらく、表には積極的に登場してこない補助表が、突然実行計画に現れることへの戸惑いと実行計画の読み方にちょっとした癖がある点の緩和と実行計画のバリエーションごとのメリデメを理解してもらうためにも解説が必要だったのだろうなぁ。と想像。
Oracle Database / Release 23 / Oracle AI Vector Search User's Guide / Optimizer Plans for HNSW Vector Indexes

INMEMORYな索引なのに、OperationにINMEMORYというキーワードが無くて、おや？　と違和感があったり、ちょっとめんどくさい癖があるなぁと。。。w

まずは、
vector_index_neighbor_graph_reloadパラメータはCDBレベルで restart に設定した ( Oracle Database 23ai 23.6以降はデフォルトが restart になっています ) ので再起動しても vector index (HNSW) はポピュレートされメモリー上に復活しているはず。。。という確認から。

[oracle@localhost ~]$ sudo service oracle stop
[sudo] oracle のパスワード:
Stopping oracle (via systemctl):  
                                                           [  OK  ]
[oracle@localhost ~]$ 
[oracle@localhost ~]$ sudo service oracle start
Starting oracle (via systemctl):  
                                                           [  OK  ]
[oracle@localhost ~]$ 
[oracle@localhost ~]$ sqlplus scott@localhost:1521/freepdb1

...略...

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
に接続されました。
SCOTT@localhost:1521/freepdb1> @show_vector_mem_pool    

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL                     369098752  236978176 DONE
64KB POOL                    150994944    2686976 DONE
IM POOL METADATA              16777216   16777216 DONE

経過: 00:00:00.05
SCOTT@localhost:1521/freepdb1> @show_vector_segments

       OBJ   MEMBYTES
---------- ----------
         0     131072
     80126  239534080

経過: 00:00:00.02

それぞれの実行計画のpros/consはマニュアルで読んでもらうとして、
VECTOR INDEX HNSW SCAN PRE-FILTER WITH JOIN BACKから診てみましょう。

JOIN BACKするかしないかの違いは最後にもう一度ベース表をアクセスするかどうか。（次の実行計画で言うと、Id=5でSEARCH_DATAをtable access by index rowidでアクセスしている箇所がJOIN BACK)
これらのバリエーションはデータ量とフィルタリング量との兼ね合いになるのでベクトル索引で近傍検索の実行計画をヒントで固定化するのは比較的難易度が高そう（どちらの傾向に固定した方が良いかの判断は難しい）だろうな、と思っているところ。固定できるか、したほうが良いかの見極めというか、割り切りなのかもしれないが決め打ちするだけの情報は揃えた上で決める必要はりそう。最初は経過観察なのが良いだろうと思っているところだが。。。とはいえ、覚えていて損ないかなぁ。

また、これらのバリエーションでは、VECTOR INDEX (HNSW)以外に、補助表の主役であるMAP表が登場します（Id=9の部分)。
VECTOR INDEX (HNSW)本体だけでなく補助表の存在も把握しておくことが大切な理由はここにもあります。（ベース表に比べるとサイズは小さいわけですが）

後半で別途まとめますが、この実行では内部ビューが新たに作られています。
Id=7の VW_HPJ_91CF1FF7 がそれです。内部的に作成されるビューにはそれぞれのトランスフォームに関連する短縮名が付与されるのが、これまでのOracle Databaseのオプティマイザのお約束ですね。
VW_HPJ_、 Hnsw scan Pre-filter with Join back -> HPJ になりそうですよね。 VW_HPJ_という内部ビューをみたらVECTOR_INDEX_TRANSFORM VECTOR INDEX HNSW SCAN PRE_FILTER WITH JOIN BACKが行われていると考えてよいでしょうね。

SELECT
  /*+
    GATHER_PLAN_STATISTICS
  */
  id
  , description
  , community
  , location_desc
  , district
  , TO_NUMBER( v_distance ) AS v_distance
FROM
(
  SELECT
    /*+ 
      VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix pre_filter_with_join_back)
    */
    id
    , description
    , community
    , location_desc
    , district
    , VECTOR_DISTANCE
      (
        vector_desc
        , VECTOR_EMBEDDING
         (
           all_minilm_l6 USING 'Incident in which someone may have been murdered' AS data
         )
      , COSINE
    ) v_distance
  FROM
    search_data
  WHERE
    community = 'AUSTIN'
  ORDER BY
    v_distance
  FETCH APPROX FIRST 20 ROWS ONLY
)
/


Plan hash value: 3994424349

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                                                        | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                                                             |      1 |        |       |       |  6649 (100)|          |     20 |00:00:00.93 |     412 |    410 |       |       |          |
|   1 |  VIEW                                  |                                                             |      1 |      1 |   157 |       |  6649   (1)| 00:00:01 |     20 |00:00:00.93 |     412 |    410 |       |       |          |
|*  2 |   COUNT STOPKEY                        |                                                             |      1 |        |       |       |            |          |     20 |00:00:00.93 |     412 |    410 |       |       |          |
|   3 |    VIEW                                |                                                             |      1 |      1 |   157 |       |  6649   (1)| 00:00:01 |     20 |00:00:00.93 |     412 |    410 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY              |                                                             |      1 |      1 |  1665 |       |  6649   (1)| 00:00:01 |     20 |00:00:00.93 |     412 |    410 |  4096 |  4096 | 4096  (0)|
|*  5 |      TABLE ACCESS BY INDEX ROWID       | SEARCH_DATA                                                 |      1 |      1 |  1665 |       |  6648   (1)| 00:00:01 |     20 |00:00:00.94 |     412 |    410 |       |       |          |
|   6 |       VECTOR INDEX HNSW SCAN PRE-FILTER| SEARCH_DATA_HNSW_IX                                         |      1 |      1 |  1665 |       |  6648   (1)| 00:00:01 |     20 |00:00:00.93 |     392 |    390 |       |       |          |
|   7 |        VIEW                            | VW_HPJ_91CF1FF7                                             |      1 |   6235 |   152K|       |  6647   (1)| 00:00:01 |   6235 |00:00:00.06 |     392 |    390 |       |       |          |
|*  8 |         HASH JOIN RIGHT OUTER          |                                                             |      1 |   6235 |     9M|  3296K|  6647   (1)| 00:00:01 |   6235 |00:00:00.06 |     392 |    390 |  8506K|  2096K| 9004K (0)|
|   9 |          TABLE ACCESS FULL             | VECTOR$SEARCH_DATA_HNSW_IX$78074_80224_0$HNSW_ROWID_VID_MAP |      1 |    125K|  1831K|       |   102   (0)| 00:00:01 |    125K|00:00:00.01 |     373 |    371 |       |       |          |
|* 10 |          INDEX RANGE SCAN              | SEARCH_DATA_COMMNITY_IX                                     |      1 |   6235 |       |       |    23   (0)| 00:00:01 |   6235 |00:00:00.01 |      19 |     19 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$3        / "from$_subquery$_001"@"SEL$1"
   2 - SEL$3
   3 - SEL$7185E227 / "from$_subquery$_003"@"SEL$3"
   4 - SEL$7185E227
   5 - SEL$7185E227 / "SEARCH_DATA"@"SEL$2"
   6 - SEL$7185E227 / "SEARCH_DATA"@"SEL$2"
   7 - SEL$BF33016E / "VW_HPJ_91CF1FF7"@"SEL$2"
   8 - SEL$BF33016E
   9 - SEL$BF33016E / "VTIX_RIDVID"@"SEL$2"
  10 - SEL$BF33016E / "SEARCH_DATA"@"SEL$2"

...略...

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

   2 - filter(ROWNUM<=20)
   4 - filter(ROWNUM<=20)
   5 - filter("SEARCH_DATA"."COMMUNITY"='AUSTIN')
   8 - access("SEARCH_DATA".ROWID="VTIX_RIDVID"."BASE_TABLE_ROWID")
  10 - access("SEARCH_DATA"."COMMUNITY"='AUSTIN')

...略...

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

  10 -  SEL$BF33016E / "SEARCH_DATA"@"SEL$2"
           -  VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix pre_filter_with_join_back)

次に、VECTOR INDEX HNSW SCAN PRE-FILTER WITHOUT JOIN BACKを診てみましょう。
違いは、JOIN BACKがないところですよね、ヒントの通りw。
実行計画のId=5でVECTOR INDEX HNSW SCAN PRE_FILTERが行われていますが、その後でベース表を再度アクセスすることはありません。
なお、今回の検索パターンだと join back したほうが多少軽めですよね。んーーー難しい。普段はオプティマイザにお任せのほうが良いかもなと思うわけです。よほどのことがない限り。はい。

ここでも、新たな内部ビュー、 VW_HPF_475999B9 が作成されています。 VW_HPF_、 Hnsw scan Pre-Filter without join back -> HPF でしょうか。ちょっとムズイw VW_HPF_内部ビューを見つけたら、VECTOR INDEX HNSW SCAN PRE-FILTER WITHOUT JOIN BACKが行われてると理解して良さそうです。

このケースでも補助表であるMAP表が登場します。なにこれ？　俺は作ってないぞ！　と驚かないようにしてくださいねw。（当ブログを読んだ方は驚くことはないはずですがw）
また、マニュアルにも記載されていますが、Hash join だけでなく Nested Loops Joinになることもあります。データ量と索引有無次第ではありますが、覚えておくと良いでしょう。（この例では Apaptive Planが選択されているため、どちらの結合方式になるかは、Join Cardinarity次第です）

SELECT
  /*+
    GATHER_PLAN_STATISTICS
  */
  id
  , description
  , community
  , location_desc
  , district
  , TO_NUMBER( v_distance ) AS v_distance
FROM
(
  SELECT
    /*+ 
      VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix pre_filter_without_join_back) 
    */
    id
    , description
    , community
    , location_desc
    , district
    , VECTOR_DISTANCE
      (
        vector_desc
        , VECTOR_EMBEDDING
         (
           all_minilm_l6 USING 'Incident in which someone may have been murdered' AS data
         )
      , COSINE
    ) v_distance
  FROM
    search_data
  WHERE
    community = 'AUSTIN'
  ORDER BY
    v_distance
  FETCH APPROX FIRST 20 ROWS ONLY
)
/


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                                    | Name                                                        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                             |                                                             |      1 |        |       |  5998 (100)|          |     20 |00:00:02.77 |    5929 |   5904 |       |       |          |
|     1 |  VIEW                                        |                                                             |      1 |     20 |  3140 |  5998   (1)| 00:00:01 |     20 |00:00:02.77 |    5929 |   5904 |       |       |          |
|  *  2 |   COUNT STOPKEY                              |                                                             |      1 |        |       |            |          |     20 |00:00:02.77 |    5929 |   5904 |       |       |          |
|     3 |    VIEW                                      |                                                             |      1 |     20 |  3140 |  5998   (1)| 00:00:01 |     20 |00:00:02.77 |    5929 |   5904 |       |       |          |
|  *  4 |     SORT ORDER BY STOPKEY                    |                                                             |      1 |     20 | 35220 |  5998   (1)| 00:00:01 |     20 |00:00:02.77 |    5929 |   5904 |  4096 |  4096 | 4096  (0)|
|     5 |      VECTOR INDEX HNSW SCAN PRE-FILTER       | SEARCH_DATA_HNSW_IX                                         |      1 |     20 | 35220 |  5997   (1)| 00:00:01 |     20 |00:00:02.77 |    5929 |   5904 |  1278K|  1076K| 1175K (0)|
|     6 |       VIEW                                   | VW_HPF_475999B9                                             |      1 |   6235 |  1071K|  5996   (1)| 00:00:01 |   6235 |00:00:02.13 |    5914 |   5904 |       |       |          |
|  *  7 |        HASH JOIN OUTER                       |                                                             |      1 |   6235 |   499K|  5996   (1)| 00:00:01 |   6235 |00:00:02.13 |    5914 |   5904 |  1448K|  1287K| 1856K (0)|
|-    8 |         NESTED LOOPS OUTER                   |                                                             |      1 |   6235 |   499K|  5996   (1)| 00:00:01 |   6235 |00:00:10.37 |    5540 |   5533 |       |       |          |
|-    9 |          STATISTICS COLLECTOR                |                                                             |      1 |        |       |            |          |   6235 |00:00:10.36 |    5540 |   5533 |       |       |          |
|    10 |           TABLE ACCESS BY INDEX ROWID BATCHED| SEARCH_DATA                                                 |      1 |   6235 |   407K|  5893   (1)| 00:00:01 |   6235 |00:00:10.36 |    5540 |   5533 |       |       |          |
|  * 11 |            INDEX RANGE SCAN                  | SEARCH_DATA_COMMNITY_IX                                     |      1 |   6235 |       |    23   (0)| 00:00:01 |   6235 |00:00:00.01 |      19 |     19 |       |       |          |
|-   12 |          TABLE ACCESS BY INDEX ROWID         | VECTOR$SEARCH_DATA_HNSW_IX$78074_80224_0$HNSW_ROWID_VID_MAP |      0 |      1 |    15 |   102   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|- * 13 |           INDEX UNIQUE SCAN                  | SYS_C0013920                                                |      0 |        |       |            |          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|    14 |         TABLE ACCESS FULL                    | VECTOR$SEARCH_DATA_HNSW_IX$78074_80224_0$HNSW_ROWID_VID_MAP |      1 |    125K|  1831K|   102   (0)| 00:00:01 |    125K|00:00:00.03 |     374 |    371 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$3        / "from$_subquery$_001"@"SEL$1"
   2 - SEL$3
   3 - SEL$2D1A9934 / "from$_subquery$_003"@"SEL$3"
   4 - SEL$2D1A9934
   5 - SEL$2D1A9934 / "SEARCH_DATA"@"SEL$2"
   6 - SEL$6D23FDEA / "VW_HPF_475999B9"@"SEL$475999B9"
   7 - SEL$6D23FDEA
   8 - SEL$6D23FDEA
  10 - SEL$6D23FDEA / "SEARCH_DATA"@"SEL$2"
  11 - SEL$6D23FDEA / "SEARCH_DATA"@"SEL$2"
  12 - SEL$6D23FDEA / "VTIX_RIDVID"@"SEL$EBB9871C"
  13 - SEL$6D23FDEA / "VTIX_RIDVID"@"SEL$EBB9871C"
  14 - SEL$6D23FDEA / "VTIX_RIDVID"@"SEL$EBB9871C"

...略...

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

   2 - filter(ROWNUM<=20)
   4 - filter(ROWNUM<=20)
   7 - access("SEARCH_DATA".ROWID="VTIX_RIDVID"."BASE_TABLE_ROWID")
  11 - access("SEARCH_DATA"."COMMUNITY"='AUSTIN')
  13 - access("SEARCH_DATA".ROWID="VTIX_RIDVID"."BASE_TABLE_ROWID")

...略...

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

  10 -  SEL$6D23FDEA / "SEARCH_DATA"@"SEL$2"
           -  VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix pre_filter_without_join_back)

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

次は、VECTOR INDEX TRANSFORM IN-FILTER WITH JOIN BACK これまでの PRE-FILTERではなく、IN-FILTER + JOIN BACKをヒントで強制しています

なお、マニュアルにも記載がありますが、In-filterの実行計画の読み方は少々癖があります！

Id=8の元表のアクセスを見てください！。いきなり、TABLE ACCESS BY USER ROWID で SEARCH_DATA 表をアクセスしています！！！
この実行計画の開始ポイントは、Id=8ではなく、Id=6の VECTOR INDEX HNSW SCAN IN-FILTER で、VECTOR INDEX (HNSW)である SEARCH_DATA_HNSW_IX 索引をトラバースしている部分です:)
Id=6で識別されたベクトルごとに、Id=8の元表に対応するrowidのフィルタが適用され、関連する列が抽出されます!!!!!!!

VECTOR INDEX TRANSFORMの時の実行計画の読み方は正しく覚えないと軽くハマりそうですねw 少々癖があるので覚えるしかないですよ！w（ここも試験にでるよ！！w しらんけど）

最後に、Id=5でJOIN BACKして、COUNT STOPKEYの操作へ遷移していきます！！！！

また、ここでも内部生成の新たなビューが登場しています。
VW_HIJ_ ですね。 HIJ -> Hnsw scan In-filter with Join back ということでしょうね。想像するに。　

SELECT
  /*+
    GATHER_PLAN_STATISTICS
  */
  id
  , description
  , community
  , location_desc
  , district
  , TO_NUMBER( v_distance ) AS v_distance
FROM
(
  SELECT
    /*+ 
      VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix in_filter_with_join_back) 
    */
    id
    , description
    , community
    , location_desc
    , district
    , VECTOR_DISTANCE
      (
        vector_desc
        , VECTOR_EMBEDDING
         (
           all_minilm_l6 USING 'Incident in which someone may have been murdered' AS data
         )
      , COSINE
    ) v_distance
  FROM
    search_data
  WHERE
    community = 'AUSTIN'
  ORDER BY
    v_distance
  FETCH APPROX FIRST 20 ROWS ONLY
)
/


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                     |      1 |        |       |     3 (100)|          |     20 |00:00:02.28 |    4725 |   4431 |       |       |          |
|   1 |  VIEW                                 |                     |      1 |      1 |   157 |     3  (34)| 00:00:01 |     20 |00:00:02.28 |    4725 |   4431 |       |       |          |
|*  2 |   COUNT STOPKEY                       |                     |      1 |        |       |            |          |     20 |00:00:02.28 |    4725 |   4431 |       |       |          |
|   3 |    VIEW                               |                     |      1 |      1 |   157 |     3  (34)| 00:00:01 |     20 |00:00:02.28 |    4725 |   4431 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY             |                     |      1 |      1 |  1652 |     3  (34)| 00:00:01 |     20 |00:00:02.28 |    4725 |   4431 |  4096 |  4096 | 4096  (0)|
|*  5 |      TABLE ACCESS BY INDEX ROWID      | SEARCH_DATA         |      1 |      1 |  1652 |     2   (0)| 00:00:01 |     20 |00:00:02.28 |    4725 |   4431 |       |       |          |
|   6 |       VECTOR INDEX HNSW SCAN IN-FILTER| SEARCH_DATA_HNSW_IX |      1 |      1 |  1652 |     2   (0)| 00:00:01 |     20 |00:00:02.28 |    4705 |   4423 |       |       |          |
|   7 |        VIEW                           | VW_HIJ_475999B9     |   4773 |      1 |       |     1   (0)| 00:00:01 |    265 |00:00:01.59 |    4705 |   4423 |       |       |          |
|*  8 |         TABLE ACCESS BY USER ROWID    | SEARCH_DATA         |   4773 |      1 |  1652 |     1   (0)| 00:00:01 |    265 |00:00:01.59 |    4705 |   4423 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$3        / "from$_subquery$_001"@"SEL$1"
   2 - SEL$3
   3 - SEL$79710E8E / "from$_subquery$_003"@"SEL$3"
   4 - SEL$79710E8E
   5 - SEL$79710E8E / "SEARCH_DATA"@"SEL$2"
   6 - SEL$79710E8E / "SEARCH_DATA"@"SEL$2"
   7 - SEL$860F096D / "VW_HIJ_475999B9"@"SEL$2"
   8 - SEL$860F096D / "SEARCH_DATA"@"SEL$2"

...略...

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

   2 - filter(ROWNUM<=20)
   4 - filter(ROWNUM<=20)
   5 - filter("SEARCH_DATA"."COMMUNITY"='AUSTIN')
   8 - filter("SEARCH_DATA"."COMMUNITY"='AUSTIN')

...略...

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

   8 -  SEL$860F096D / "SEARCH_DATA"@"SEL$2"
           -  VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix in_filter_with_join_back)

最後に、VECTOR INDEX TRANSFORM IN-FILTER WITHOUT JOIN BACK In-filtewrでJoin Backなしというタイプです。

このタイプがもっとも無駄がなさそうですね。今回の例で使っている検索パターンだと。。。:)

これもマニュアルの記述されているとおり、実行計画の開始位置にクセがあります。
Id=5の VECTOR INDEX HNSW SCAN IN-FILTER で、VECTOR INDEX (HNSW) をトラバースするところがスタートです。
次に、Id=7のベース表をVECTOR INDEXから取得したrowidでアクセス。
その後、COUNT STOPKEYの操作へ入ります。

そして、ここでも新顔の内部ビュー、 VW_HIF_ -> Hnsw scan In-Filter with join back ということで、 HIFになっていると思われます:)

SELECT
  /*+
    GATHER_PLAN_STATISTICS
  */
  id
  , description
  , community
  , location_desc
  , district
  , TO_NUMBER( v_distance ) AS v_distance
FROM
(
  SELECT
    /*+ 
      VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix in_filter_without_join_back) 
    */
    id
    , description
    , community
    , location_desc
    , district
    , VECTOR_DISTANCE
      (
        vector_desc
        , VECTOR_EMBEDDING
         (
           all_minilm_l6 USING 'Incident in which someone may have been murdered' AS data
         )
      , COSINE
    ) v_distance
  FROM
    search_data
  WHERE
    community = 'AUSTIN'
  ORDER BY
    v_distance
  FETCH APPROX FIRST 20 ROWS ONLY
 )
/


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |      1 |        |       |     3 (100)|          |     20 |00:00:00.52 |    4705 |    733 |       |       |          |
|   1 |  VIEW                                |                     |      1 |     20 |  3140 |     3  (34)| 00:00:01 |     20 |00:00:00.52 |    4705 |    733 |       |       |          |
|*  2 |   COUNT STOPKEY                      |                     |      1 |        |       |            |          |     20 |00:00:00.52 |    4705 |    733 |       |       |          |
|   3 |    VIEW                              |                     |      1 |     20 |  3140 |     3  (34)| 00:00:01 |     20 |00:00:00.52 |    4705 |    733 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY            |                     |      1 |     20 | 34960 |     3  (34)| 00:00:01 |     20 |00:00:00.52 |    4705 |    733 |  4096 |  4096 | 4096  (0)|
|   5 |      VECTOR INDEX HNSW SCAN IN-FILTER| SEARCH_DATA_HNSW_IX |      1 |     20 | 34960 |     2   (0)| 00:00:01 |     20 |00:00:00.52 |    4705 |    733 |   835K|   835K|  543K (0)|
|   6 |       VIEW                           | VW_HIF_475999B9     |   4773 |      1 |   151 |     1   (0)| 00:00:01 |    265 |00:00:00.02 |    4705 |    733 |       |       |          |
|*  7 |        TABLE ACCESS BY USER ROWID    | SEARCH_DATA         |   4773 |      1 |    67 |     1   (0)| 00:00:01 |    265 |00:00:00.02 |    4705 |    733 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$3        / "from$_subquery$_001"@"SEL$1"
   2 - SEL$3
   3 - SEL$81BAFB36 / "from$_subquery$_003"@"SEL$3"
   4 - SEL$81BAFB36
   5 - SEL$81BAFB36 / "SEARCH_DATA"@"SEL$2"
   6 - SEL$066A4CD4 / "VW_HIF_475999B9"@"SEL$475999B9"
   7 - SEL$066A4CD4 / "SEARCH_DATA"@"SEL$2"

...略...

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

   2 - filter(ROWNUM<=20)
   4 - filter(ROWNUM<=20)
   7 - filter("SEARCH_DATA"."COMMUNITY"='AUSTIN')

...略...

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

   7 -  SEL$066A4CD4 / "SEARCH_DATA"@"SEL$2"
           -  VECTOR_INDEX_TRANSFORM(search_data search_data_hnsw_ix in_filter_without_join_back)

まとめ VECTOR INDEX TRANSFORM の特徴

・VECTOR INDEX (HNSW)以外に、補助表であるMAP表が使われる（場合によってはその索引も）
・In-filter時の実行計画開始の開始ポイントに癖があるので要注意
・内部で生成されるビューがバリエーション分増加した

最後に、今回新たに登場した内部ビュー名と、これまでに把握されている内部ビューのまとめ。

VECTOR INDEX TRANSFORM

VW_HPJ_ / Hnsw scan Pre-filter with Join back
VW_HPF_ / Hnsw scan Pre-Filter with join back
VW_HIJ_ / Hnsw scan In-filter with Join back
VW_HIF_ / Hnsw scan In-Filter with join back

上記に加え以前からいくつかメジャーな内部生成ビューがまとめられていいます。覚えておくとなにが行われているか分かり易いと思いますよ。
Internal Views / Oracle Scratchpad / Jonathan Lewis

では、また！

Enjoin Execution Plans, SQLs, and AI Vector Search!

Related article on Mac De Oracle
実行計画は, SQL文のレントゲン写真だ！

投稿日時 2025年7月 4日 (金) 12時48分 Oracle, チューニング, FAQ, SQL, パフォーマンス, in-memory, VECTOR, AI, Oracle Database 23ai | 固定リンク | 0 | コメント (0)

2025年6月25日 (水)

VECTOR INDEX はどこ？、見積もりサイズだとそれなりのサイズだったのに... の謎を探るべく、我々は洞窟の奥へ向かった！(完結編)

Previously on Mac De Oracle
前回はVECTOR INDEX はどこ？、見積もりサイズだとそれなりのサイズだったのに... の謎を探るべく、我々は洞窟の奥へ向かった！(後編) でした。
謎は、ほぼ解決したかな？と、思ったのですが、モヤモヤは晴れずwww
ということで、完結編的な何か。という位置付けのまとめを書いておくことにしました。（なお、23.5以降VECTOR INDEX関連も機能追加があったり。。。まあ、追いかけるの大変です）

注）ちょい古いリリースなので最新リリースとは挙動など異なる点がある可能性がある点、ご了承ください

SCOTT@localhost:1521/freepdb1> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

インメモリー近傍グラフ・ベクトル索引の特徴まとめ (的なもの)

インメモリー近傍グラフ・ベクトル索引は、ディクショナリービューからは、OBJECTでもSEGMENTも無い扱いになっているよう見えてしまう。
しかし、vecsys.vector$indexにリストされるOBJECT_IDは、*_OBJECTSと共通で、vecsys.vector$indexにはあるが、*_OBJECTS上はIDが欠番のように見える（後述）。無いけどあるみたいな不思議な扱いになっている。（今のところ）
なぜそうしたのだろう？....

Oraclerならお馴染みの *_OBJECTS　や　*_SEGMENTS　には現れないが、in-memory onlyのオブジェクトとして専用のプール上にポピューレートされた場合に存在している in-memory indexである。
（ここテストにでますよ。嘘w）

また、in-memory vector indexの補助表及び関連する索引はリストされる（セグメントサイズは、deferred segment creationであるものも含むため状況次第）
メモリー上とは言っても他と同じ扱いで、属性としてin-memory onlyのような列を追加すればよかったのでは？と、個人的には思っていたりする。。。

参考）
(上記の時事検証 23ai 23.4で行ったため、最新のリリースとは差異がある可能性があり、気づいた差分について随時補足追記してあります)
作成したin-memory vector index ( SEARCH_DATA_HNSW_IX ) の IDX_OBJN(列名の短縮系も*_OBJECTSで利用されているOBJECT_IDとな異なり、IDX_OBJN ( N は Numberの略と思われる) という、いわゆるシノニムになってしまっている（この点も状況をわかりにくくしている原因でしょうね。Oraclerじゃない方が作ったような違和感を感じる）

SCOTT@localhost:1521/freepdb1> select idx_objn, idx_name from vecsys.vector$index;

  IDX_OBJN IDX_NAME
---------- -------------------------------
     80113 SEARCH_DATA_HNSW_IX

で、この IDX_OBJN = OBJECT_ID ( *_OBJECTS で使われている　）、いきなりシノニムになってる！　と仮説をたて UESR_OBJECTSを検索してみると、存在しない！　（何ぃ〜〜〜っ！）
ちがうのか？（*_OBJECTSにはリストされないだけで、採番元は同じなので、はやり仮説は正しかったw。後述）

SCOTT@localhost:1521/freepdb1> select object_name,object_type from user_objects where object_id = 80113;

レコードが選択されませんでした。

念の為、data_object_idでも検索してみる（こちらにもないですよねーw）

SCOTT@localhost:1521/freepdb1> select object_name,object_type from user_objects where data_object_id = 80113;

レコードが選択されませんでした。

USER_SEGMENTSをVECTOR INDEX NAMEで検索してみるがやはり無い。ストレージ上のデータをそのままインメモリー化しているわけではないいうことだろうと想像するが。ではメモリー上の情報は。。。

SCOTT@localhost:1521/freepdb1> select segment_name from user_segments where segment_name = 'SEARCH_DATA_HNSW_IX';

レコードが選択されませんでした。

VECTOR INDEXとしては、 *_INDEXES から確認は可能であるが”！。。。

SCOTT@localhost:1521/freepdb1> select index_name,index_type,table_name from user_indexes where table_name='SEARCH_DATA';

 INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
------------------------------ --------------------------- ------------------------------
SYS_IL0000078074C00009$$       LOB                         SEARCH_DATA
SEARCH_DATA_HNSW_IX            VECTOR                      SEARCH_DATA

in-memory vector indexはSGA上の専用メモリープール上に造られる。
では、v$vector_memory_poolからポピュレートされていることを確認。

SCOTT@localhost:1521/freepdb1> l
  1  SELECT
  2    pool
  3    , alloc_bytes
  4    , used_bytes
  5    , populate_status
  6  FROM
  7*   v$vector_memory_pool
SCOTT@localhost:1521/freepdb1> /

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL                     369098752  236978176 DONE
64KB POOL                    150994944    2686976 DONE
IM POOL METADATA              16777216   16777216 DONE

ちなみに、vector indexがポピュレートされる前の状態は以下。1MB/64KB Poolが消費されたことがわかります。つまり、236,978,176 + 2,686,976 = 239,665,152 = 228.6MB

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL                     369098752          0 DONE
64KB POOL                    150994944          0 DONE
IM POOL METADATA              16777216   16777216 DONE

in-memory vector indexのメモリセグメントの詳細を確認するには　v$vector_mem_segments_detail　を参照する。vecsys.vector$indexのidx_objnにリストされていた 80113 が 239MB　程度使用されていることがわかる。
（このサイズ、覚えておいてくださいね。後で使います。 239,534,080 = 228.4MB）

SCOTT@localhost:1521/freepdb1> l
  1  SELECT
  2    obj
  3    , membytes
  4  FROM
  5*   v$vector_mem_segments_detail
SCOTT@localhost:1521/freepdb1> /

       OBJ   MEMBYTES
---------- ----------
         0     131072
     80113  239534080

in-memory vector index (HNSW)の補助表と関連索引の object_id を見てみましょう。
vecsys.vector$indexでは、idx_objn となっていた列の値と連続していること点が確認できます。つまり、object_id　を採番しているシーケンスを利用している。。。in-memory vector index (HNSW) も *_OBJECTSに含まれている扱い。。。だが、*_objectsには含まれていません！！！！

CREATE VECTOR INDEXで作成された、in-memory vector index（ SEARCH_DATA_HNSW_IX ）の idx_objn = 80113 , その後に作成される補助表及び索引の *_objects.object_id　は、80114 以降が採番されています。
かつ、80113 という object_id は、*_objects にはリストされず。
妙ですよね。内部的には *_objectsに含まれていてもおかしくないと思われる扱いにであるかのように見えますよね。うーーむ。

SCOTT@localhost:1521/freepdb1> l
  1  WITH
  2    vector_idx_auxiliary_tables
  3    AS (
  4      SELECT
  5       idx_name AS vector_index_name
  6       , REPLACE(aux_table_name,'"','') AS aux_table_name
  7      FROM
  8      (
  9          SELECT
 10            vvi.idx_name AS idx_name
 11            ,vvi.idx_auxiliary_tables.rowid_vid_map_name
 12            ,vvi.idx_auxiliary_tables.shared_journal_transaction_commits_name
 13            ,vvi.idx_auxiliary_tables.shared_journal_change_log_name
 14          FROM
 15            vecsys.vector$index vvi
 16      ) objs
 17      UNPIVOT (
 18        aux_table_name FOR related_obj_name IN
 19        (
 20          rowid_vid_map_name
 21          , shared_journal_transaction_commits_name
 22          , shared_journal_change_log_name
 23        )
 24      )
 25    )
 26  SELECT
 27    object_id
 28  ,object_name
 29  ,object_type
 30  FROM
 31    user_objects
 32  WHERE
 33    object_name in (
 34  SELECT
 35    aux_table_name AS segment_name
 36  FROM
 37    vector_idx_auxiliary_tables
 38  WHERE
 39    vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
 40  UNION ALL
 41  SELECT
 42    index_name AS segment_name
 43  FROM
 44    user_indexes
 45  WHERE
 46    EXISTS
 47    (
 48       SELECT
 49         1
 50       FROM
 51         vector_idx_auxiliary_tables
 52       WHERE
 53         vector_idx_auxiliary_tables.aux_table_name = user_indexes.table_name
 54         AND vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
 55    )
 56* )
SCOTT@localhost:1521/freepdb1> /

 OBJECT_ID OBJECT_NAME                                                                                                                      OBJECT_TYPE
---------- -------------------------------------------------------------------------------------------------------------------------------- -----------------------
     80114 VECTOR$SEARCH_DATA_HNSW_IX$78074_80113_0$HNSW_ROWID_VID_MAP                                                                      TABLE
     80115 SYS_C0013840                                                                                                                     INDEX
     80117 VECTOR$SEARCH_DATA_HNSW_IX$78074_80113_0$HNSW_SHARED_JOURNAL_TRANSACTION_COMMITS                                                 TABLE PARTITION
     80116 VECTOR$SEARCH_DATA_HNSW_IX$78074_80113_0$HNSW_SHARED_JOURNAL_TRANSACTION_COMMITS                                                 TABLE
     80118 PK_XID_80113                                                                                                                     INDEX
     80120 VECTOR$SEARCH_DATA_HNSW_IX$78074_80113_0$HNSW_SHARED_JOURNAL_CHANGE_LOG                                                          TABLE PARTITION
     80119 VECTOR$SEARCH_DATA_HNSW_IX$78074_80113_0$HNSW_SHARED_JOURNAL_CHANGE_LOG                                                          TABLE
     80124 SYS_IL0000080119C00007$$                                                                                                         INDEX PARTITION
     80123 SYS_IL0000080119C00007$$                                                                                                         INDEX

9行が選択されました。

SCOTT@localhost:1521/freepdb1> l
  1 select object_id, data_object_id from user_objects where object_name = 'SEARCH_DATA'
SCOTT@localhost:1521/freepdb1> /

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     78074          78796

ここまでで、in-memory vector indexはどこ？
って話の場所的なところは見切れたかなと思いますが、
もう一つ、explain plan for create vector index及び、dbms_space.create_index_costプロシージャでの見積もりサイズは、信じて良いのか？、よくないのか？

エラーにはなってないが、信用してよいのだろうか。。。
ちなみに、23.6以降だが、INDEX_VECTOR_MEMORY_ADVISORプシージャによりインメモリーサイズを見積もることができるようになった。とか。。。すぐには用意できないのでw 23.4で一旦確認してみる

CREATE VECTOR INDEXで作成されるINMEMORYの索引オブジェクトに加え、補助表とよばれる表が複数存在する。未検証だが変更をトラックするタイプの補助表は更新量によってはセグメントサイズが一時的に増加する可能性あり

変更をトラッキングするのための2つのパーティション表と関連索引に関しては変更が無い限りセグメントは作成されない。はず。（deferred segment creationになっているようなので）
つまり新規作成時にはオブジェクトとしては存在するものの、セグメントは存在しない。
ということは、残る一つの補助表（仮でMAP表と呼ぶことにする）とその索引はある程度のセグメントを保持することになるだろうと予想。
また、インメモリー索引自体はベース表のVECTOR列のサイズに依存するのではないだろうか。。。と。

とりあえず、補助表のセグメントサイズを改めて確認してみる（in-memory vector index を何度かdrop/createしていためセグメント名に含まれるobject_idが異なりますが気になさらず。。）
今回のケース 125,000行のデータでは合計で 9MB 程度です。誤差の範囲程度のサイズではあります。ではメモリ上のサイズを占めるデータのベースとなる表のセグメントサイズも今一度確認しておきましょう。

SEGMENT_NAME                                                                     SEGMENT_TYPE               MB
-------------------------------------------------------------------------------- ------------------ ----------
VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_ROWID_VID_MAP                      TABLE                       3
SYS_C0013800                                                                     INDEX                       6

VECTORデータ型の列以外もありますが、ベース表のセグメントサイズは、 249MB あります。。。おやおや、サイズ、近似してますよね。。

SCOTT@localhost:1521/freepdb1> r
  1* select segment_name,segment_type,bytes / 1024 /1024 "MB" from user_segments where segment_name = 'SEARCH_DATA'

SEGMENT_NAME                   SEGMENT_TYPE               MB
------------------------------ ------------------ ----------
SEARCH_DATA                    TABLE                     248

もう少しベース表のセグメントサイズを調べてみましょう。

SCOTT@localhost:1521/freepdb1> l
  1  create table search_data_without_vector_desc
  2  as
  3  select
  4    id
  5    ,primary_description
  6    ,description
  7    ,location_desc
  8    ,district
  9    ,ward
 10    ,community
 11    ,c_year
 12* from search_data
SCOTT@localhost:1521/freepdb1> /

表が作成されました。

SCOTT@localhost:1521/freepdb1> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>upper('search_data_without_vector_desc'),cascade=>true,no_invalidate=>false);

PL/SQLプロシージャが正常に完了しました。

おお、10MBなので、VECTORデータ型の列を含むSEARCH_DATA表のVECTORデータ型部分のサイズは、238MB　程度みたいですね。むむむ。。。

SCOTT@localhost:1521/freepdb1> r
  1  select
  2    segment_name
  3    ,segment_type
  4    ,bytes / 1024 /1024 "MB"
  5  from
  6    user_segments
  7  where
  8*   segment_name = upper('search_data_without_vector_desc')

SEGMENT_NAME                    SEGMENT_TYPE               MB
------------------------------- ------------------ ----------
SEARCH_DATA_WITHOUT_VECTOR_DESC TABLE                      10

in-memory vector index (HNSW)のメモリー消費サイズは、　228.4MB
（これに、補助表のMAP表と索引のセグメントサイズ 9MB を加算すると、237.4MB　おおおおおおおおーっ）

ベース表のVECTORデータ型だけのセグメントサイズは、238MB　

で、ですねぇ〜、EXPLAIN PLAN FOR CREATE VECTOR INDEX ...の見積もりサイズと、DBMS_SPACE.CREATE_INDEX_COSTプロシージャによる見積もり。。。VECTOR INDEXに対応できているという情報は未確認のままですが。。。

EXPLAIN PLAN FOR CREATE VECTOR INDEX ...
- estimated index size: 293M bytes

DBMS_SPACE.CREATE_INDEX_COSTプロシージャによるセグメントサイズ見積もり...
Segment Size (MB) :280

という結果になりました。偶然近い値だったの？？。。ということでもなさそうな気はします。。

ただし、ストレージのセグメントサイズではなくて、VECTOR MEMORY AREAで消費される索引サイズの見積もりとして....

みなさんは、どう思います？？？？

おまけの疑問というか違和感（これまでも少しだけ触れたが、妙な違和感がある）

これまでのIN-MEMORYとはことなり、VECTOR INDEX (HNSW)の場合、実行計画のオペレーション ( VECTOR INDEX HNSW SCAN ) には、INMEMORYというワードが含まれず勘違いしやすい気がするのに、
なぜ？ INMEMORYを含めなかったのだろう。また、*_OBJECTS/*_SEGMENTSを使わず、OBJECT_IDをIDX_OBJNというシノニムまで作り、vecsys.vector$index　で別管理かつ、補助表をJSONに書き込んでいる。
SQL文面倒なんすけど。。というのとこれまでの*_OBJECTSの存在と、vecsys.vector$indexのidx_objnの分離（*_objectsのobject_idの採番と同じなのに。。）といい、これまでと違う空気感が強い。

少し調べたのですが、
以下メッセージからも読み取れるように　INMEMORY というワードは、Oracle Database上、In-Memory Column Store Architectureをイメージさせるものではないことは確かようだ、
では、なぜ、INMEMORYというキーワードを実行計画のオペレーション名に含めなかった理由は少々理解しにくい。INMEMORY VECTOR INDEX HNSW SCANのほうがイメージしやすいと思うのは私だけだろうか。。。
このフワフワしてるところが違和感の原因でもあるな。。。

ORA-51815
INMEMORY NEIGHBOR GRAPH HNSW vector index snapshot is too old.
Oracle Database 23ai / Error Messages Oracle Database / Database Error Messages / ORA-51815

最後に、vector_index_neighbor_graph_reload を restartにすると再ロードされるよ（scope=bothでspfileにも同時に反映できるよ！）

23.4までは初期化パラメータ vector_index_neighbor_graph_reload のデフォルトが OFF であった。
インスタンス再起動で VECTOR INDEX (HNSW) がインメモリー上に再作成されない！！！w（なんだとーーー！）
23.6以降では、デフォルトが変更になり、 restart がデフォルトになっています。ここ試験にでるよ（知らんけどw

おまけに、ADB-Sだと、V$VECTOR_INDEXなんてビューが提供されていたり。。。なぜ全てで提供しないのだろう。。。23.4だからって話でもなさそうで。。

関連するディクショナリービューなどが整備されてないように見える（今後整備されるような気はする）。対応しきれていない部分はJSON化して回避しているようにも見える（やめて〜〜w）

ということで、最終的な私の理解のビジュアル化w 2025/6/25時点、かつ、Oracle Database 23ai 23.4 を元にした理解は以下の通り（まだ理解不足な箇所はあるかもしれない）

なお、
DBMS_VECTOR.GET_INDEX_STATUSプロシージャが返すステータスをみると興味深い内容が載っている
Oracle Database 23 / Oracle AI Vector Search User's Guide / Vector Index Status, Checkpoint, and Advisor Procedures / GET_INDEX_STATUS

CREATE VECTOR INDEX (HNSW)に関する状態を取得するプロシージャが返すステータスには以下のように記載されている。In-memory vector index (HNSW)のDDL発行からvector memory poolにロードされ、multi-layered HNSW graphができあがるまでのステータスがわかります！！！

そしてここでも、なんでプロシージャ必要だったのだろう。。。。と、言う素朴な疑問が！！！

Oracle Databaseには昔から、V$SESSION_LONGOPSってビューがあって。。。。
長時間操作のステータスがわかるようになっているのだが.....

ますます、なぜ、これまでの機能やビューを有効に再利用していないのだろう。。。という点が気になる...。が、現状は見切れたかな、と。
（もう一つのVector Indexのタイプと更新トラッキングと反映、いくつかの実行計画パターンの確認を除く）

HNSW Index Initialization
Initialization phase for the HNSW vector index creation

HNSW Index Auxiliary Tables Creation
Creation of the internal auxiliary tables for the HNSW Neighbor Graph vector index

HNSW Index Graph Allocation
Allocation of memory from the vector memory pool for the HNSW graph

HNSW Index Loading Vectors
Loading of the base table vectors into the vector pool memory

HNSW Index Graph Construction
Creation of the multi-layered HNSW graph with the previously loaded vectors

HNSW Index Creation Completed
HNSW vector index creation finished

では、また。

Enjoy SQL! and AI vector search!

・Oracle Database 23ai freeで試すVector Search - ONNXモデル準備編
・Oracle Database 23ai freeで試すVector Search - データ準備編
・実行計画は, SQL文のレントゲン写真だ！ No.66 / AI Vector Search - VECTOR INDEX HNSW SCAN
・VECTOR INDEX はどこ？、見積もりサイズだとそれなりのサイズだったのに... の謎を探るべく、我々は洞窟の奥へ向かった！(前編)
・VECTOR INDEX はどこ？、見積もりサイズだとそれなりのサイズだったのに... の謎を探るべく、我々は洞窟の奥へ向かった！(後編)

投稿日時 2025年6月25日 (水) 00時00分 Oracle, FAQ, SQL, in-memory, INDEX, Oracle Database 23c Free Developer Release, JSON, VECTOR, AI | 固定リンク | 0 | コメント (0)

2025年6月18日 (水)

VECTOR INDEX はどこ？、見積もりサイズだとそれなりのサイズだったのに... の謎を探るべく、我々は洞窟の奥へ向かった！(後編)

Previously on Mac De Oracle
前回は、VECTOR INDEX はどこ？、見積もりサイズだとそれなりのサイズだったのに... の謎を探るべく、我々は洞窟の奥へ向かった！(前編) でした。
今日はその後編です。

10046トレースよりVECTOR INDEX（HNSW）作成では前回の通り、補助表3つ（うち2つは変更をトラックするためなので作成直後は空）、それらの表に加え、各1つの索引が作成されるということがハッキリしました。
ただ、VECTOR INDEXの補助表はvecsys.vector$indexという表にJSONとして保持されている点と、USER_INDEXESから確認できるVECTOR INDEX自体は実態を持たない、さらに、Oraclerにはお馴染みの*_INDEXESなどから単純に取得できないことも見えてきました（今後もっと便利になることを期待したいですね）

(2025/6/18追記)
これ、HNSWってインメモリーと言っても、元ネタは永続化されているわけで、それをメモリープールにポピュレートしてるって理解（間違ってないとは思うけど、違ってたらコメントもらえるとありがたいです）なので、後編ではそのあたりを見ておこうかと。。。
インメモリー近傍グラフ・ベクトル索引

(作成される補助表の名称にVECTOR INDEXのベース表の名称が入っているようなので中間一致で検索していますが、これだとノイズも多くなるので検索キツイですよね。索引名はシステム生成名称なのでベース表の名称で中間一致検索はできません！。このケースに限ればなんとか拾えてるけどという感じではありますね。。。ということで後半ではJSONから引っこ抜いてなんとかするパズルもwやってますw)

SCOTT@localhost:1521/freepdb1> select index_name , table_name, index_type from user_indexes where table_name like '%SEARCH_DATA%';

INDEX_NAME                     TABLE_NAME                                                                       INDEX_TYPE
------------------------------ -------------------------------------------------------------------------------- ---------------------------
SYS_IL0000079525C00007$$       VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_SHARED_JOURNAL_CHANGE_LOG          LOB
SYS_IL0000078074C00009$$       SEARCH_DATA                                                                      LOB
SEARCH_DATA_HNSW_IX            SEARCH_DATA                                                                      VECTOR
SYS_C0013800                   VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_ROWID_VID_MAP                      NORMAL
PK_XID_79519                   VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_SHARED_JOURNAL_TRANSACTION_COMMITS NORMAL

SCOTT@localhost:1521/freepdb1> r
  1  SELECT
  2    idx_name
  3    ,(
  4       SELECT
  5         table_name
  6       FROM
  7         user_tables
  8       WHERE
  9         user_tables.table_name = (
 10           SELECT
 11             object_name
 12           FROM
 13             user_objects
 14           WHERE
 15             user_objects.object_id = vecsys.vector$index.idx_base_table_objn
 16        )
 17     ) AS tab_name
 18    ,JSON_SERIALIZE(
 19      idx_auxiliary_tables
 20      RETURNING VARCHAR2 PRETTY
 21    ) AS idx_auxiliary_tables
 22  FROM
 23    vecsys.vector$index
 24  WHERE
 25*   idx_name = 'SEARCH_DATA_HNSW_IX'

IDX_NAME                       TAB_NAME                       IDX_AUXILIARY_TABLES
------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------
SEARCH_DATA_HNSW_IX            SEARCH_DATA                    {
                                                                "rowid_vid_map_objn" : 79520,
                                                                "shared_journal_transaction_commits_objn" : 79522,
                                                                "shared_journal_change_log_objn" : 79525,
                                                                "rowid_vid_map_name" : "VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_ROWID_VID_MAP",
                                                                "shared_journal_transaction_commits_name" : "VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_SHARED_JOURNAL_TRANSACTION_COMMITS",
                                                                "shared_journal_change_log_name" : "VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_SHARED_JOURNAL_CHANGE_LOG"
                                                              }

これまでの調査の結果、VECTOR INEDEX (HNSW) であるSEARCH_DATA_HNSW_IXはオブジェクトでもなく、セグメントもない（当然、シノニムにもなれない。仮にも索引ですから）
VECTOR INDEX (HNSW)の索引セグメントのサイズは、作成直後のIDX_AUXILIARY_TABLESと、索引があればそれら索引のセグメントサイズの合計ということになりますよね！！！
（やっと見えてきたw、なんなんだこれ）

こんな感じでJSONからIDX_AUXILIARY_TABLESにある関連表のオブジェクトID、または、テーブル名を取り出しUSER_INDEXESから索引も合わせて取得したうえで、それぞれのセグメントサイズの合計を取得すれば物理的なサイズは見えますよね。。。。（めんどくさいw）

SCOTT@localhost:1521/freepdb1> l
  1  WITH
  2    vector_idx_auxiliary_tables
  3    AS (
  4      SELECT
  5       idx_name AS vector_index_name
  6       , REPLACE(aux_table_name,'"','') AS aux_table_name
  7      FROM
  8      (
  9          SELECT
 10            vvi.idx_name AS idx_name
 11            ,vvi.idx_auxiliary_tables.rowid_vid_map_name
 12            ,vvi.idx_auxiliary_tables.shared_journal_transaction_commits_name
 13            ,vvi.idx_auxiliary_tables.shared_journal_change_log_name
 14          FROM
 15            vecsys.vector$index vvi
 16      ) objs
 17      UNPIVOT (
 18        aux_table_name FOR related_obj_name IN
 19        (
 20          rowid_vid_map_name
 21          , shared_journal_transaction_commits_name
 22          , shared_journal_change_log_name
 23        )
 24      )
 25    )
 26  SELECT
 27    aux_table_name AS segment_name
 28  FROM
 29    vector_idx_auxiliary_tables
 30  WHERE
 31    vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
 32  UNION ALL
 33  SELECT
 34    index_name AS segment_name
 35  FROM
 36    user_indexes
 37  WHERE
 38    EXISTS
 39    (
 40       SELECT
 41         1
 42       FROM
 43         vector_idx_auxiliary_tables
 44       WHERE
 45         vector_idx_auxiliary_tables.aux_table_name = user_indexes.table_name
 46         AND vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
 47    )
 48*
SCOTT@localhost:1521/freepdb1> /

SEGMENT_NAME
---------------------------------------------------------------------------------------
VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_ROWID_VID_MAP
VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_SHARED_JOURNAL_TRANSACTION_COMMITS
VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_SHARED_JOURNAL_CHANGE_LOG
SYS_C0013800
PK_XID_79519
SYS_IL0000079525C00007$$

6行が選択されました。

経過: 00:00:00.16

では、あらためて、EXPLAIN PLAN FOR CREATE VECTOR INDEX...の見積もりサイズと、実際のサイズを比較してみましょう!

SCOTT@localhost:1521/freepdb1> l
  1  explain plan for
  2  CREATE VECTOR INDEX search_data_hnsw_ix ON search_data ( vector_desc )
  3    ORGANIZATION
  4      INMEMORY NEIGHBOR GRAPH
  5      DISTANCE COSINE
  6*     WITH TARGET ACCURACY 90
SCOTT@localhost:1521/freepdb1> /

解析されました。

経過: 00:00:00.02
SCOTT@localhost:1521/freepdb1> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 2727344110

----------------------------------------------------------------------------------------------
| Id  | Operation              | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |                     |   125K|   188M| 14689   (1)| 00:00:01 |
|   1 |  VECTOR INDEX BUILD    | SEARCH_DATA_HNSW_IX |       |       |            |          |
----------------------------------------------------------------------------------------------

Note
-----
   - estimated index size: 293M bytes

なんどかコメントしてますが、SEARCH_DATA_HNSW_IXという索引セグメントは無く！、複数の補助表と索引群から構成されている。それらの作成直後のサイズは....

SCOTT@localhost:1521/freepdb1> r
  1  WITH
  2    vector_idx_auxiliary_tables
  3    AS (
  4      SELECT
  5       idx_name AS vector_index_name
  6       , REPLACE(aux_table_name,'"','') AS aux_table_name
  7      FROM
  8      (
  9          SELECT
 10            vvi.idx_name AS idx_name
 11            ,vvi.idx_auxiliary_tables.rowid_vid_map_name
 12            ,vvi.idx_auxiliary_tables.shared_journal_transaction_commits_name
 13            ,vvi.idx_auxiliary_tables.shared_journal_change_log_name
 14          FROM
 15            vecsys.vector$index vvi
 16      ) objs
 17      UNPIVOT (
 18        aux_table_name FOR related_obj_name IN
 19        (
 20          rowid_vid_map_name
 21          , shared_journal_transaction_commits_name
 22          , shared_journal_change_log_name
 23        )
 24      )
 25    )
 26  SELECT
 27    user_segments.segment_name
 28    , user_segments.segment_type
 29    , user_segments.bytes / 1024 / 1024 AS "MB"
 30  FROM
 31    user_segments
 32    INNER JOIN
 33    (
 34      SELECT
 35        aux_table_name AS segment_name
 36      FROM
 37        vector_idx_auxiliary_tables
 38      WHERE
 39        vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
 40      UNION ALL
 41      SELECT
 42        index_name AS segment_name
 43      FROM
 44        user_indexes
 45      WHERE
 46        EXISTS
 47        (
 48           SELECT
 49             1
 50           FROM
 51             vector_idx_auxiliary_tables
 52           WHERE
 53             vector_idx_auxiliary_tables.aux_table_name = user_indexes.table_name
 54             AND vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
 55        )
 56     ) vector_idx_aux_objects
 57     ON
 58       vector_idx_aux_objects.segment_name = user_segments.segment_name
 59*

SEGMENT_NAME                                                                     SEGMENT_TYPE               MB
-------------------------------------------------------------------------------- ------------------ ----------
VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_ROWID_VID_MAP                      TABLE                       3
SYS_C0013800                                                                     INDEX                       6

誤差が大きいですよね。正しく見積もれてない可能性が高いように思いますが、少なく出るよりマシ程度かもしれません。今後のチューニングに期待。。。。。というところでしょうか。。。

SCOTT@localhost:1521/freepdb1> l
  1  explain plan for
  2  CREATE VECTOR INDEX search_data_hnsw_ix ON search_data ( vector_desc )
  3    ORGANIZATION
  4      INMEMORY NEIGHBOR GRAPH
  5      DISTANCE COSINE
  6*     WITH TARGET ACCURACY 90
SCOTT@localhost:1521/freepdb1> /

だと、

- estimated index size: 293M bytes
ですが、
実際には以下の通り、　9M Bytes程度でした。
（もしかして、VECTOR INDEX　(HNSW)ってINMEMORYだからメモリーサイズ？　んなことないか、いや、あったりしてw　と思ったりし始めているw　 23ai 23.6では、DBMS_VECTOR.INDEX_VECTOR_MEMORY_ADVISORプロシージャによりメモリーサイズを見積もれるようになっていたりする。。怪しい。2025/6/19追記）

SEGMENT_NAME                                                                     SEGMENT_TYPE               MB
-------------------------------------------------------------------------------- ------------------ ----------
VECTOR$SEARCH_DATA_HNSW_IX$78074_79519_0$HNSW_ROWID_VID_MAP                      TABLE                       3
SYS_C0013800                                                                     INDEX                       6

Bツリー索引の見積り（19cで確かめたときは、これほど大きな差にはなっていませんでした）に比べると精度は低そうですよね。と言うより、VECTOR INDEXには対応できてないのかもしれませんね....

おまけで、
DBMS_SPACEパッケージの索引サイズ見積もりも試しておきましょう。。。。。やはり、explain plan for文同様にVECTOR INDEXのセグメントサイズ見積もりには対応してなさそう。（小さくでるわけではないのですがw）

SCOTT@localhost:1521/freepdb1> set serveroutput on
SCOTT@localhost:1521/freepdb1> l
  1  DECLARE
  2    used_bytes NUMBER;
  3    segment_bytes NUMBER;
  4  BEGIN
  5    DBMS_SPACE.CREATE_INDEX_COST (
  6      ddl=> 'CREATE VECTOR INDEX search_data_hnsw_ix ON search_data ( vector_desc )'
  7            || ' ORGANIZATION'
  8            || ' INMEMORY NEIGHBOR GRAPH'
  9            || ' DISTANCE COSINE'
 10            || ' WITH TARGET ACCURACY 90'
 11      , used_bytes => used_bytes
 12      , alloc_bytes =>  segment_bytes
 13    );
 14    DBMS_OUTPUT.PUT_LINE('Segment Size (MB) :'||segment_bytes/1024/1024);
 15    DBMS_OUTPUT.PUT_LINE('Index data Size (MB) :'||used_bytes/1024/1024);
 16* END;
SCOTT@localhost:1521/freepdb1> /
Segment Size (MB) :280
Index data Size (MB) :188.94672393798828125

PL/SQLプロシージャが正常に完了しました。

Enjoy SQL, PL/SQL and VECTOR SEARCH!

VECTOR INDEXの謎はもう少し探る必要があるようだ...w (後編のおまけにつづくw）

めちゃ快適な気温の場所なので、
東京は猛暑と聞いて恐怖している

では、また。

参考）
今回利用したコードの一部

WITH
  vector_idx_auxiliary_tables
  AS (
    SELECT
     idx_name AS vector_index_name
     , REPLACE(aux_table_name,'"','') AS aux_table_name
    FROM
    (
        SELECT
          vvi.idx_name AS idx_name
          ,vvi.idx_auxiliary_tables.rowid_vid_map_name
          ,vvi.idx_auxiliary_tables.shared_journal_transaction_commits_name
          ,vvi.idx_auxiliary_tables.shared_journal_change_log_name
        FROM
          vecsys.vector$index vvi
    ) objs
    UNPIVOT (
      aux_table_name FOR related_obj_name IN
      (
        rowid_vid_map_name
        , shared_journal_transaction_commits_name
        , shared_journal_change_log_name
      )
    )
  )
SELECT
  aux_table_name AS segment_name
FROM
  vector_idx_auxiliary_tables
WHERE
  vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
UNION ALL
SELECT
  index_name AS segment_name
FROM
  user_indexes
WHERE
  EXISTS
  (
     SELECT
       1
     FROM
       vector_idx_auxiliary_tables
     WHERE
       vector_idx_auxiliary_tables.aux_table_name = user_indexes.table_name
       AND vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
  )
;

WITH
  vector_idx_auxiliary_tables
  AS (
    SELECT
     idx_name AS vector_index_name
     , REPLACE(aux_table_name,'"','') AS aux_table_name
    FROM
    (
        SELECT
          vvi.idx_name AS idx_name
          ,vvi.idx_auxiliary_tables.rowid_vid_map_name
          ,vvi.idx_auxiliary_tables.shared_journal_transaction_commits_name
          ,vvi.idx_auxiliary_tables.shared_journal_change_log_name
        FROM
          vecsys.vector$index vvi
    ) objs
    UNPIVOT (
      aux_table_name FOR related_obj_name IN
      (
        rowid_vid_map_name
        , shared_journal_transaction_commits_name
        , shared_journal_change_log_name
      )
    )
  )
SELECT
  user_segments.segment_name
  , user_segments.segment_type
  , user_segments.bytes / 1024 / 1024 AS "MB"
FROM
  user_segments
  INNER JOIN
  (
    SELECT
      aux_table_name AS segment_name
    FROM
      vector_idx_auxiliary_tables
    WHERE
      vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
    UNION ALL
    SELECT
      index_name AS segment_name
    FROM
      user_indexes
    WHERE
      EXISTS
      (
         SELECT
           1
         FROM
           vector_idx_auxiliary_tables
         WHERE
           vector_idx_auxiliary_tables.aux_table_name = user_indexes.table_name
           AND vector_idx_auxiliary_tables.vector_index_name = 'SEARCH_DATA_HNSW_IX'
      )
   ) vector_idx_aux_objects
   ON
     vector_idx_aux_objects.segment_name = user_segments.segment_name
;

set serveroutput on
DECLARE
  used_bytes NUMBER;
  segment_bytes NUMBER;
BEGIN
  DBMS_SPACE.CREATE_INDEX_COST (
    ddl=> 'CREATE VECTOR INDEX search_data_hnsw_ix ON search_data ( vector_desc )'
          || ' ORGANIZATION'
          || ' INMEMORY NEIGHBOR GRAPH'
          || ' DISTANCE COSINE'
          || ' WITH TARGET ACCURACY 90'
    , used_bytes => used_bytes
    , alloc_bytes =>  segment_bytes
  );
  DBMS_OUTPUT.PUT_LINE('Segment Size (MB) :'||segment_bytes/1024/1024);
  DBMS_OUTPUT.PUT_LINE('Index data Size (MB) :'||used_bytes/1024/1024);
END;
/

投稿日時 2025年6月18日 (水) 00時00分 Oracle, PL/SQL, チューニング, FAQ, SQL, パフォーマンス, in-memory, INDEX, Oracle Database 23c Free Developer Release, JSON, VECTOR, AI | 固定リンク | 0 | コメント (0)

2025年6月17日 (火)

VECTOR INDEX はどこ？、見積もりサイズだとそれなりのサイズだったのに... の謎を探るべく、我々は洞窟の奥へ向かった！(前編)

Previously on Mac De Oracle
前回は、実行計画は, SQL文のレントゲン写真だ！ No.66 / AI Vector Search - VECTOR INDEX HNSW SCANでした。
今日はそれに絡んで気づいたVECTOR INDEXの謎を追いかけてみることにします。

前回の最後の宿題というか謎の一つ目、覚えてますか？　　そう、VECTOR INDEXを作成後、まあよくある、USER_INDEXESを検索して作成されたVECTOR INDEXの名称を確認して、EXPLAIN PLAN FORで見積もった索引サイズとどの程度乖離しているのだろうと、手癖でUSER_SEGMENTSをアクセスしてみると、なな、なーーーーーんと、ない、無い！、なーーーい！。　（INMEMORYとはいっても元ネタがあってINMEMORY化されるわけで仕組み的に理解しにくいのと、これまでのディクショナリービューの使い方と異なっていてものすごく追いにくい）
なんで？　という謎。

SCOTT@localhost:1521/freepdb1> select index_name,index_type,table_name from user_indexes where table_name='SEARCH_DATA';

 INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
------------------------------ --------------------------- ------------------------------
SYS_IL0000078074C00009$$       LOB                         SEARCH_DATA
SEARCH_DATA_HNSW_IX            VECTOR                      SEARCH_DATA

経過: 00:00:00.05
SCOTT@localhost:1521/freepdb1> select segment_name from user_segments where segment_name = 'SEARCH_DATA_HNSW_IX';

レコードが選択されませんでした。

その後、アッ！　
いつもと違うビューがあったことを思い出し、覗いてみると、
作成したVECTOR INDEX (HNSW)の名称とともに、IDX_AUXILIARY_TABLES列（JSONデータ型）に、なにやらそれらしい値があるじゃありませんか！！！
そーなんだーーーーっ。VECTOR INDEXの名称は、VECTOR INDEXの補助表の集まりをまとめる為だけの存在？！！！
というところまででした。

SCOTT@localhost:1521/freepdb1> r
  1* SELECT * FROM vecsys.vector$index

  IDX_OBJN   IDX_OBJD IDX_OWNER# IDX_NAME                       IDX_BASE_TABLE_OBJN IDX_BASE_TABLE_OWNER# IDX_PARAMS                     IDX_AUXILIARY_TABLES           
---------- ---------- ---------- ------------------------------ ------------------- --------------------- ------------------------------ ------------------------------
     78797                   153 SEARCH_DATA_HNSW_IX                          78074                   153 {"type":"HNSW","num_neighbors" {"rowid_vid_map_objn":78798,"s
                                                                                                          :32,"efConstruction":200,"dist hared_journal_transaction_comm
                                                                                                          ance":"COSINE","accuracy":90," its_objn":78800,"shared_journa
                                                                                                          vector_type":"FLOAT32","vector l_change_log_objn":78803,"rowi
                                                                                                          _dimension":384,"degree_of_par d_vid_map_name":"VECTOR$SEARCH
                                                                                                          allelism":1,"pdb_id":3,"indexe _DATA_HNSW_IX$78074_78797_0$HN
                                                                                                          d_col":"VECTOR_DESC"}          SW_ROWID_VID_MAP","shared_jour
                                                                                                                                         nal_transaction_commits_name":
                                                                                                                                         "VECTOR$SEARCH_DATA_HNSW_IX$78
                                                                                                                                         074_78797_0$HNSW_SHARED_JOURNA
                                                                                                                                         L_TRANSACTION_COMMITS","shared
                                                                                                                                         _journal_change_log_name":"VEC
                                                                                                                                         TOR$SEARCH_DATA_HNSW_IX$78074_
                                                                                                                                         78797_0$HNSW_SHARED_JOURNAL_CH
                                                                                                                                         ANGE_LOG"}
                                                                                                                                         
SCOTT@localhost:1521/freepdb1> r
  1  SELECT
  2    JSON_SERIALIZE(idx_params RETURNING VARCHAR2 PRETTY) AS "INDEX PARAM"
  3    ,JSON_SERIALIZE(idx_auxiliary_tables RETURNING VARCHAR2 PRETTY) AS "INDEX AUX"
  4* FROM vecsys.vector$index

INDEX PARAM                                        INDEX AUX
-------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------
{                                                  {
  "type" : "HNSW",                                   rowid_vid_map_objn" : 78798,
  "num_neighbors" : 32,                              "shared_journal_transaction_commits_objn" : 78800,
  "efConstruction" : 200,                            "shared_journal_change_log_objn" : 78803,
  "distance" : "COSINE",                             "rowid_vid_map_name" : "VECTOR$SEARCH_DATA_HNSW_IX$78074_78797_0$HNSW_ROWID_VID_MAP",
  "accuracy" : 90,                                   "shared_journal_transaction_commits_name" : "VECTOR$SEARCH_DATA_HNSW_IX$78074_78797_0$HNSW_SHARED_JOURNAL_TRANSACTION_COMMITS",
  "vector_type" : "FLOAT32",                         "shared_journal_change_log_name" : "VECTOR$SEARCH_DATA_HNSW_IX$78074_78797_0$HNSW_SHARED_JOURNAL_CHANGE_LOG"
  "vector_dimension" : 384,                        }
  "degree_of_parallelism" : 1,
  "pdb_id" : 3,
  "indexed_col" : "VECTOR_DESC"
}

一旦ここまでの情報を整理すると

VECTOR INDEX（この例ではHNSW)の実態は、複数の補助表から構成されており、それらをまとめているのが作成時に指定したVECTOR INDEXの名称。
オブジェクトとしては存在しているが、それ自体は、セグメントを持たない!!!（IN MEMORY展開される索引だからとはいっても。。なにかしらあるのでは。。？）

現時点の23aiには、使い勝手の良いディクショナリービューは提供されていない。。。。まじか！

その代わりに、vecsys.vector$indexディクショナリー表（ビューではない）から詳細を追うことができる。（vecsysスキーマ）

さらに、補助表を見つけるためには、該当ディクショナリー表のIDX_AUXILIARY_TABLES列に格納されているJSONの*_objnや、補助表のオブジェクトID、*_nameから探れ！、と。
ようするに、このJSONに対象となるVECTOR INDEXとその実態の依存関係が入っている！！！！！
（ちなみに、表しかないが、おそらく、関連索引もありそうだ、というかあるだろうな。補助表の索引は補助表をキーにしてUSER_INDEXESから探すしかないだろうけども。）

ここまで見てきたところで、
めんどくせーーーーーーーーーーーーーっ！　なぜ、依存関係をJSONに突っ込んだの？。。。。という顔をしているところwwwww

そしてもう一つの謎、EXPLAIN PLAN FOR CREATE VECTOR INDEX ...foo bar ..として見積もられたVECTOR INDEXの見積もりサイズって、こいつら補助表含めた合計値？　
なのか？　　。。。だよなw 精度的に微妙な気がしなくも無い

全部かき集めて、合計して、見積もりサイズとの差分を見てみよう！
B*Treeの索引見積もりって、結構いい感じのサイズを弾き出してくれるわけだが。。。果たして。。。こいつは、どうなんだ？　SEARCH_DATA_HNSW_IXって実態持ってないし。。。

では、もっと探ってみなければw

そのまえに、CREATE VECTOR INDEXの謎を探るべくw
10046トレースで洞窟の奥に潜入することにします！　ww (わからなくなったら、これしかない！)

SCOTT@localhost:1521/freepdb1> desc search_data
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 PRIMARY_DESCRIPTION                                VARCHAR2(40)
 DESCRIPTION                                        VARCHAR2(100)
 LOCATION_DESC                                      VARCHAR2(100)
 DISTRICT                                           VARCHAR2(30)
 WARD                                               NUMBER
 COMMUNITY                                          VARCHAR2(30)
 C_YEAR                                             NUMBER
 VECTOR_DESC                                        VECTOR(*, *)

SCOTT@localhost:1521/freepdb1> select count(*) from search_data

  COUNT(*)
----------
    125000

作成済みVECTOR INDEX (HNSW)を削除しておきます。

SCOTT@localhost:1521/freepdb1> drop index search_data_hnsw_ix;

索引が削除されました。

経過: 00:00:01.77

SCOTT@localhost:1521/freepdb1> select index_name , table_name, index_type from user_indexes where table_name like '%SEARCH_DATA%';

INDEX_NAME                     TABLE_NAME                     INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
SYS_IL0000078074C00009$$       SEARCH_DATA                    LOB

SCOTT@localhost:1521/freepdb1> select table_name,column_name,segment_name,index_name from user_lobs where table_name like '%SEARCH_DATA%';

TABLE_NAME                     COLUMN_NAME                    SEGMENT_NAME                   INDEX_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
SEARCH_DATA                    VECTOR_DESC                    SYS_LOB0000078074C00009$$      SYS_IL0000078074C00009$$

経過: 00:00:00.07

10046トレースの準備ができたので、トレースを設定しCREATE VECTOR INDEX文を実行して洞窟の奥へw

SCOTT@localhost:1521/freepdb1> show parameter timed_statistics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
timed_statistics                     boolean     TRUE

SCOTT@localhost:1521/freepdb1> show parameter max_dump_file_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size                   string      32M
SCOTT@localhost:1521/freepdb1> alter session set max_dump_file_size = unlimited;

セッションが変更されました。

経過: 00:00:00.00
SCOTT@localhost:1521/freepdb1> show parameter tracefile_identifier

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
tracefile_identifier                 string
SCOTT@localhost:1521/freepdb1> alter session set tracefile_identifier = 'create_vector_index';

セッションが変更されました。

経過: 00:00:00.00
SCOTT@localhost:1521/freepdb1> alter session set events '10046 trace name context forever, level 12';

セッションが変更されました。

経過: 00:00:00.02
SCOTT@localhost:1521/freepdb1> l
  1  CREATE VECTOR INDEX search_data_hnsw_ix ON search_data ( vector_desc )
  2    ORGANIZATION
  3    INMEMORY NEIGHBOR GRAPH
  4    DISTANCE COSINE
  5*   WITH TARGET ACCURACY 90
SCOTT@localhost:1521/freepdb1> /

索引が作成されました。

経過: 00:00:39.24
SCOTT@localhost:1521/freepdb1> alter session set events '10046 trace name context off';

セッションが変更されました。

経過: 00:00:00.01

...略...

[oracle@localhost ~]$ echo $ORACLE_BASE
/opt/oracle
[oracle@localhost ~]$ cd $ORACLE_BASE/diag/rdbms/free/FREE/trace
[oracle@localhost trace]$ pwd
/opt/oracle/diag/rdbms/free/FREE/trace
[oracle@localhost trace]$ 
[oracle@localhost trace]$ ll *create_vector_index*
-rw-r-----. 1 oracle oinstall 2836064  6月 12 00:24 FREE_ora_4133_create_vector_index.trc
-rw-r-----. 1 oracle oinstall  602896  6月 12 00:24 FREE_ora_4133_create_vector_index.trm
[oracle@localhost trace]$ 
[oracle@localhost trace]$ tkprof FREE_ora_4133_create_vector_index.trc FREE_ora_4133_create_vector_index.txt waits=yes explain=scott@localhost:1521/freepdb1 sys=yes 

...略...

[oracle@localhost trace]$ ll *create_vector_index*
-rw-r-----. 1 oracle oinstall 2836064  6月 12 00:24 FREE_ora_4133_create_vector_index.trc
-rw-r-----. 1 oracle oinstall  602896  6月 12 00:24 FREE_ora_4133_create_vector_index.trm
-rw-rw-r--. 1 oracle oracle    500986  6月 12 00:30 FREE_ora_4133_create_vector_index.txt
[oracle@localhost trace]$ 
[oracle@localhost trace]$ view FREE_ora_4133_create_vector_index.txt

ということで、整形したトレースファイルからCREATE文を抜き出してみた。
これは私が実行したDDL文なので参考程度に...

CREATE VECTOR INDEX search_data_hnsw_ix ON search_data ( vector_desc )
  ORGANIZATION
  INMEMORY NEIGHBOR GRAPH
  DISTANCE COSINE
  WITH TARGET ACCURACY 90

以降、トレースから抜き出した補助表及関連する索引を作成するDDL
VECTOR$SEARCH_DATA_HNSW_IX$78074_79461_0$HNSW_ROWID_VID_MAP表、および、主キー索引の作成（基本的にこの補助表が主役)

CREATE TABLE SCOTT.VECTOR$SEARCH_DATA_HNSW_IX$78074_79461_0$HNSW_ROWID_VID_MAP 
(base_table_rowid ROWID PRIMARY KEY, vertex_id NUMBER)
  
CREATE UNIQUE INDEX "SCOTT"."SYS_C0013760" on
  "SCOTT"."VECTOR$SEARCH_DATA_HNSW_IX$78074_79461_0$HNSW_ROWID_VID_MAP"("BASE_TABLE_ROWID")
   NOPARALLEL

VECTOR$SEARCH_DATA_HNSW_IX$78074_79461_0$HNSW_SHARED_JOURNAL_TRANSACTION_COMMITS表、および、主キー索引の作成

CREATE TABLE SCOTT.VECTOR$SEARCH_DATA_HNSW_IX$78074_79461_0$HNSW_SHARED_JOURNAL_TRANSACTION_COMMITS 
(usn NUMBER NOT NULL, slot NUMBER NOT NULL, seq
  NUMBER NOT NULL, commit_scn NUMBER NOT NULL, 
  CONSTRAINT pk_xid_79461 PRIMARY KEY(usn, slot, seq)) 
  PARTITION BY RANGE(commit_scn) 
  INTERVAL(100)
  (PARTITION pdefault VALUES LESS THAN (0))

CREATE UNIQUE INDEX "SCOTT"."PK_XID_79461" on
  "SCOTT"."VECTOR$SEARCH_DATA_HNSW_IX$78074_79461_0$HNSW_SHARED_JOURNAL_TRANSACTION_COMMITS"("USN","SLOT","SEQ") 
  NOPARALLEL

VECTOR$SEARCH_DATA_HNSW_IX$78074_79461_0$HNSW_SHARED_JOURNAL_CHANGE_LOG表の作成（HNSW_SHARED_JOURNAL_TRANSACTION_COMMITS表の参照パーティションになっている）

CREATE TABLE SCOTT.VECTOR$SEARCH_DATA_HNSW_IX$78074_79461_0$HNSW_SHARED_JOURNAL_CHANGE_LOG 
(usn NUMBER NOT NULL, slot NUMBER NOT NULL, seq NUMBER NOT
  NULL, xcn NUMBER, base_table_rowid ROWID, dml_op VARCHAR2(10), 
  data_vector vector(384, FLOAT32), 
  CONSTRAINT fk_xid_79461 FOREIGN KEY(usn, slot, seq)
  REFERENCES 
  SCOTT.VECTOR$SEARCH_DATA_HNSW_IX$78074_79461_0$HNSW_SHARED_JOURNAL_TRANSACTION_COMMITS(usn, slot, seq)) 
  PARTITION BY REFERENCE(fk_xid_79461)

各表をdescribeしてみると...

VECTOR列を持つ表のROWIDとVERTEX_IDをマップしていますね、表を見てなんとなく想像できる列名で素敵w。RDBMSのモデリングで列からなにやってるか想像もできねー設計しているのを見たりしてると心が清くなった気がしますね。
これがVECTOR INDEXの主役といってもよいでしょうね。

SCOTT@localhost:1521/freepdb1> desc VECTOR$SEARCH_DATA_HNSW_IX$78074_79461_0$HNSW_ROWID_VID_MAP
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 BASE_TABLE_ROWID                          NOT NULL ROWID
 VERTEX_ID                                          NUMBER

次の２つの表は、元の表のVECTORに影響のある更新がトラックされているみたい。詳しい資料読んではいないのですが、変更をトラックしていく表っぽうので、変更のない状況では空なんじゃないだろうか。。。あとで確認します。

SCOTT@localhost:1521/freepdb1> desc VECTOR$SEARCH_DATA_HNSW_IX$78074_79461_0$HNSW_SHARED_JOURNAL_TRANSACTION_COMMITS
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 USN                                       NOT NULL NUMBER
 SLOT                                      NOT NULL NUMBER
 SEQ                                       NOT NULL NUMBER
 COMMIT_SCN                                NOT NULL NUMBER

SCOTT@localhost:1521/freepdb1> desc VECTOR$SEARCH_DATA_HNSW_IX$78074_79461_0$HNSW_SHARED_JOURNAL_CHANGE_LOG
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 USN                                       NOT NULL NUMBER
 SLOT                                      NOT NULL NUMBER
 SEQ                                       NOT NULL NUMBER
 XCN                                                NUMBER
 BASE_TABLE_ROWID                                   ROWID
 DML_OP                                             VARCHAR2(10)
 DATA_VECTOR                                        VECTOR(384, FLOAT32)

一応user_indexesビューから全体を見てみます。USER_INDEXESビューを通して見ると新たに7つのオブジェクトが作成されたことがわかります！！！！（赤字部分）
vecsys.vector$index表からは、補助表の存在しか見えませんでしたが、索引も作成されてます。実際にはこの表と索引のセグメントサイズの合計が、VECTOR INDEX作成時のセグメントサイズってことですよね？！（INMEMORYだからメモリーサイズって可能性もなくはないけど、どっちかわからんw）　とはいえ、CREATE VECTOR INDEX ほげほげ索引で、指定したほげほげ索引自体はセグメントは持たない！(INMEMORYテーブルとは異なる持ち方をしているだけなのかもしれないが、わかりにくいw）


SCOTT@localhost:1521/freepdb1> select index_name , table_name from user_indexes where table_name like '%SEARCH_DATA%';

INDEX_NAME                     TABLE_NAME
------------------------------ ----------------------------------------------------------------------------------------------------
SYS_IL0000078074C00009$$       SEARCH_DATA
SEARCH_DATA_HNSW_IX            SEARCH_DATA
SYS_C0013720                   VECTOR$SEARCH_DATA_HNSW_IX$78074_79421_0$HNSW_ROWID_VID_MAP
PK_XID_79421                   VECTOR$SEARCH_DATA_HNSW_IX$78074_79421_0$HNSW_SHARED_JOURNAL_TRANSACTION_COMMITS
SYS_IL0000079427C00007$$       VECTOR$SEARCH_DATA_HNSW_IX$78074_79421_0$HNSW_SHARED_JOURNAL_CHANGE_LOG

経過: 00:00:00.29

表名を中間一致検索すると関連する索引と表をリストすることができますが、これだとなんかイケてない感じが強いのでvecsys.vector$index表より扱いやすいビューを提供してもらいたいですね！　まじで (苦笑

長くなってしまったので、一旦、謎の１つをまとめておきたいと思います。
VECTOR INDEX (HNSW)のセグメントサイズとEXPLAIN PLAN FOR CREATE VECTOR INDEX...の見積もりサイズの差分確認は次回のおたのしみということで。

CREATE VECTOR INDEX search_data_hnsw_ix ON search_data ( vector_desc )
  ORGANIZATION
  INMEMORY NEIGHBOR GRAPH
  DISTANCE COSINE
  WITH TARGET ACCURACY 90

作成されるベクター索引(今回の例では、search_data_hnsw_ix)は *_INDEXESにはVECTOR索引としてリストされるが実体は持たない（INMEMORYとはいっても何がどうINMEMORY化されるかディクショナリーから追いやすかったテーブルのINMEMORY化とは傾向が違いそう）。
補助表とその索引を含めたオブジェクト群をアクセスするためのキーのような存在。ベクター索引（今回のHNSW索引の例では、マップ表とベクターの更新をトラックするパーティション表と参照パーティション表からなる３表と付随する３つの索引から構成されている。ベクター索引の主役は、ベクター列をもつベース表のROWIDをVERTEX_IDをマップするマップ表とその索引。変更がない状態であれば、おそらくこの２つのオブジェクトがセベクター索引のセグメントサイズの総量になりそう。

また、
これらオブジェクト（索引も含め）を簡単に一覧するビューは無く、中間一致検索で無理やり検索するか、vecsys.vector$index表のJSONデータを使って他のビューと結合して取得する必要がありそう（めんどくせぇw）

ということまでは見えた。（以下は、洞窟の奥へ潜入したときの手書きメモww）

もうひとつ、前述の通り、search_data_hnsw_ixというベクター索引の便宜上のオブジェクトは同一名のセグメントは持っていません。しかし、以下のようなSELECT文では、該当ベクター索引が利用されていることを示すオブジェクトとして現れてくるというのも特徴的ですね。
SQL文と実行計画によってはMAP表が現われてくることもあるようなので、その例についてもいずれ書こうと思っています:)　（それはそれで謎いですよね。突然裏の主役が実行計画に顔を出してくるわけなのでw）

SCOTT@localhost:1521/freepdb1> r
  1  SELECT
  2    id
  3    , description
  4    , community
  5    , location_desc
  6    , district
  7    , TO_NUMBER( v_distance ) AS v_distance
  8  FROM
  9  (
 10    SELECT
 11      id
 12      , description
 13      , community
 14      , location_desc
 15      , district
 16      , VECTOR_DISTANCE
 17        (
 18          vector_desc
 19          , VECTOR_EMBEDDING
 20           (
 21             all_minilm_l6 USING 'Incident in which someone may have been murdered' AS data
 22           )
 23        , COSINE
 24      ) v_distance
 25    FROM
 26      search_data
 27    ORDER BY
 28      v_distance
 29    FETCH APPROX FIRST 10 ROWS ONLY
 30* )


10行が選択されました。

経過: 00:00:00.32

実行計画
----------------------------------------------------------
Plan hash value: 2333665681

-------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                     |    10 |  1570 |     2  (50)| 00:00:01 |
|   1 |  VIEW                           |                     |    10 |  1570 |     2  (50)| 00:00:01 |
|*  2 |   COUNT STOPKEY                 |                     |       |       |            |          |
|   3 |    VIEW                         |                     |    10 |  1570 |     2  (50)| 00:00:01 |
|*  4 |     SORT ORDER BY STOPKEY       |                     |    10 | 16400 |     2  (50)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| SEARCH_DATA         |    10 | 16400 |     1   (0)| 00:00:01 |
|   6 |       VECTOR INDEX HNSW SCAN    | SEARCH_DATA_HNSW_IX |    10 | 16400 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

なお、今回利用している Oracle Database 23.4は最近の更新ペースからするとすでに古いリリースになっていますw　
VECTOR関連追加など含め結構な差分がりそうですね。例えば、DBMS_VECTOR.INDEX_VECTOR_MEMORY_ADVISOR()プロシージャが無いなど。（マニュアルには記載されているんですが、リリースいくつから有効というような表記は見当たらず）
また、VirtualBox Applienceだと、現時点では、23.7 が最新みたいなので入れ替えないとな。。。

23って、機能的にいつ頃落ち着くんだろうなぁ〜っ。

次回へ、つづく！

Enjoy SQL! and JSON?!

投稿日時 2025年6月17日 (火) 00時00分 Oracle, PL/SQL, チューニング, FAQ, SQL, VirtualBox, パフォーマンス, INDEX, Oracle Database 23c Free Developer Release, JSON, VECTOR, AI | 固定リンク | 0 | コメント (0)

2025年6月 5日 (木)

実行計画は, SQL文のレントゲン写真だ！ No.66 / AI Vector Search - VECTOR INDEX HNSW SCAN

Previously on Mac De Oracle
前回は、Oracle Database 23ai freeで試すVector Search - データ準備編でした。
今回は、AI Vector SearchでVECTOR INDEX HNSW SCANとそれが利用できない場合の実行計画という名のSQL文のレントゲンを診ていきたいと思います。:)

その前に、VECTOR INDEX HNSW を利用する前に必要な準備がまだ残っていました。
vector_memory_sizeの設定です。デフォルトでは 0 になっています。

Oracle Dataabase 23ai / Oracle AI Vector Search User's Guide/ Oracle AI Vector Search Parameters

https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/oracle-ai-vector-search-parameters.html

SYS@FREE> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------

...略...

inmemory_xmem_size                   big integer 0
memory_max_size                      big integer 0
memory_max_target                    big integer 0
memory_size                          big integer 0
memory_target                        big integer 0
optimizer_inmemory_aware             boolean     TRUE
shard_apply_max_memory_size          integer     0
shared_memory_address                integer     0
vector_memory_size                   big integer 0

SYS@FREE> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 1536M
sga_min_size                         big integer 0
sga_target                           big integer 1536M

Oracle Database 23ai / Oracle AI Vector Searchユーザーズ・ガイド / ベクトル・プールのサイズ設定

https://docs.oracle.com/cd/G11854_01/vecse/size-vector-pool.html

今回はギリギリですがw 512MBに設定して試します。

SYS@FREE> create pfile from spfile;

ファイルが作成されました。

経過: 00:00:00.01
SYS@FREE> ALTER SYSTEM SET vector_memory_size=512m SCOPE=spfile
SYS@FREE> shutdown immediate
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
SYS@FREE> startup
ORACLEインスタンスが起動しました。

Total System Global Area 1603726344 bytes
Fixed Size                  5360648 bytes
Variable Size             654311424 bytes
Database Buffers          402653184 bytes
Redo Buffers                4530176 bytes
Vector Memory Area        536870912 bytes
データベースがマウントされました。
データベースがオープンされました。
SYS@FREE> 
SYS@FREE> show parameter vector_memory_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
vector_memory_size                   big integer 512M

vector_memory_sizeサイズの準備ができたので、Vector Index (HNSW) を作成します。

まずは、HNSWタイプののVector Indexを作成します。

SCOTT@localhost:1521/freepdb1> l
  1  CREATE VECTOR INDEX search_data_hnsw_ix ON search_data ( vector_desc )
  2    ORGANIZATION
  3      INMEMORY NEIGHBOR GRAPH
  4      IDISTANCE COSINE
  5*     WITH TARGET ACCURACY 90
SCOTT@localhost:1521/freepdb1> /

索引が作成されました。

経過: 00:00:36.13
SCOTT@localhost:1521/freepdb1> r
  1  select index_name,table_name,index_type,index_subtype,uniqueness,ityp_name
  2* from user_indexes where table_name = 'SEARCH_DATA'

INDEX_NAME                     TABLE_NAME                     INDEX_TYPE                  INDEX_SUBTYPE                UNIQUENES ITYP_NAME
------------------------------ ------------------------------ --------------------------- ---------------------------- --------- --------------------
SYS_IL0000078074C00009$$       SEARCH_DATA                    LOB                                                      UNIQUE
SEARCH_DATA_HNSW_IX            SEARCH_DATA                    VECTOR                      INMEMORY_NEIGHBOR_GRAPH_HNSW NONUNIQUE

経過: 00:00:00.00
SCOTT@localhost:1521/freepdb1> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'SEARCH_DATA',no_invalidate=>false, cascade=>true);

PL/SQLプロシージャが正常に完了しました。

ちなみに、vector_memory_sizeに指定したメモリサイズでは小さすぎる場合、以下のエラーが発生します。vector_memory_sizeを増加するか、データ量を削減するかどちらかなんですよね。Oracle Database 23ai Freeってメモリサイズの制限も厳しいので。

CREATE VECTOR INDEX search_data_hnsw_ix ON search_data ( vector_desc )
                                           *
行1でエラーが発生しました。:
ORA-51961: ベクトル・メモリー領域が不足しています。 ヘルプ:
https://docs.oracle.com/error-help/db/ora-51961/

VECTOR INDEX HNSW SCAN + TABLE ACCESS BY INDEX ROWIDになるか確認してみましょう.

索引（Hierarchical Navigable Small World (HNSW)またはInverted File Flat (IVF)ベクトル索引）を利用させるためには、以下のガイドラインに従うことが重要なので一読しておくことをおすすめしておきます。（知らないと簡単にハマってしまうので）

Oracle Database 23ai / Oracle AI Vector Searchユーザーズ・ガイド / 索引使用のガイドライン

https://docs.oracle.com/cd/G11854_01/vecse/guidelines-using-vector-indexes.html

シカゴの犯罪データから、"Incident in which someone may have been murdered" という条件で殺人事件に絡んでそうな事件を10件検索してみます。APPROXというキーワードも重要なので忘れずに。

SELECT
  id
  , description
  , community
  , location_desc
  , district
  , TO_NUMBER( v_distance ) AS v_distance
FROM
(
  SELECT 
    id
    , description
    , community
    , location_desc
    , district
    , VECTOR_DISTANCE 
      (
        vector_desc
        , VECTOR_EMBEDDING
         (
           all_minilm_l6 USING 'Incident in which someone may have been murdered' AS data
         )
      , COSINE
    ) v_distance
  FROM 
    search_data
  ORDER BY 
    v_distance 
  FETCH APPROX FIRST 10 ROWS ONLY
)
/

おおおおおお、索引使ってる:) ほっとした

ただ、これまでの INMEMORYとはことなり、INMEMORYにあるはずので索引アクセスのはずなのに、実行計画のOPERATIONには、INMEMORYというキーワードがないこと。ディクショナリービューの使い方もこれまでとは異なる傾向がり、なかなか分かりづらい部分も多い。今回気づいた違和感というか謎は要調査（2025/6/20追記）

SCOTT@localhost:1521/freepdb1> l
  1  SELECT
  2    id
  3    , description
  4    , community
  5    , location_desc
  6    , district
  7    , TO_NUMBER( v_distance ) AS v_distance
  8  FROM
  9  (
 10    SELECT
 11      id
 12      , description
 13      , community
 14      , location_desc
 15      , district
 16      , VECTOR_DISTANCE
 17        (
 18          vector_desc
 19          , VECTOR_EMBEDDING
 20           (
 21             all_minilm_l6 USING 'Incident in which someone may have been murdered' AS data
 22           )
 23        , COSINE
 24      ) v_distance
 25    FROM
 26      search_data
 27    ORDER BY
 28      v_distance
 29    FETCH APPROX FIRST 10 ROWS ONLY
 30* )
SCOTT@localhost:1521/freepdb1> /

        ID DESCRIPTION                              COMMUNITY            LOCATION_DESC                            DISTRICT   V_DISTANCE
---------- ---------------------------------------- -------------------- ---------------------------------------- ---------- ----------
  13405240 AGGRAVATED - OTHER DANGEROUS WEAPON      AUSTIN               APARTMENT                                15TH       .631286621
  13405155 AGGRAVATED - OTHER DANGEROUS WEAPON      AUSTIN               HOSPITAL BUILDING / GROUNDS              15TH       .631286621
  13395521 AGGRAVATED - OTHER DANGEROUS WEAPON      MORGAN PARK          RESIDENCE                                22ND       .631286621
  13404912 AGGRAVATED - OTHER DANGEROUS WEAPON      ASHBURN              SCHOOL - PUBLIC BUILDING                 8TH        .631286621
  13405606 AGGRAVATED - OTHER DANGEROUS WEAPON      AVALON PARK          STREET                                   4TH        .631286621
  13509969 AGGRAVATED - OTHER DANGEROUS WEAPON      AUBURN GRESHAM       APARTMENT                                6TH        .631286621
  13396440 AGGRAVATED - OTHER DANGEROUS WEAPON      DUNNING              STREET                                   16TH       .631286621
  13396497 AGGRAVATED - OTHER DANGEROUS WEAPON      WOODLAWN             APARTMENT                                3RD        .631286621
  13405235 AGGRAVATED - OTHER DANGEROUS WEAPON      NEAR WEST SIDE       SIDEWALK                                 12TH       .631286621
  13396404 AGGRAVATED - OTHER DANGEROUS WEAPON      NEAR NORTH SIDE      CONVENIENCE STORE                        18TH       .631286621

10行が選択されました。

経過: 00:00:00.37
SCOTT@localhost:1521/freepdb1> set autot trace exp stat
SCOTT@localhost:1521/freepdb1> r
  1  SELECT
  2    id
  3    , description
  4    , community
  5    , location_desc
  6    , district
  7    , TO_NUMBER( v_distance ) AS v_distance
  8  FROM
  9  (
 10    SELECT
 11      id
 12      , description
 13      , community
 14      , location_desc
 15      , district
 16      , VECTOR_DISTANCE
 17        (
 18          vector_desc
 19          , VECTOR_EMBEDDING
 20           (
 21             all_minilm_l6 USING 'Incident in which someone may have been murdered' AS data
 22           )
 23        , COSINE
 24      ) v_distance
 25    FROM
 26      search_data
 27    ORDER BY
 28      v_distance
 29    FETCH APPROX FIRST 10 ROWS ONLY
 30* )


10行が選択されました。

経過: 00:00:00.32

実行計画
----------------------------------------------------------
Plan hash value: 2333665681

-------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                     |    10 |  1570 |     2  (50)| 00:00:01 |
|   1 |  VIEW                           |                     |    10 |  1570 |     2  (50)| 00:00:01 |
|*  2 |   COUNT STOPKEY                 |                     |       |       |            |          |
|   3 |    VIEW                         |                     |    10 |  1570 |     2  (50)| 00:00:01 |
|*  4 |     SORT ORDER BY STOPKEY       |                     |    10 | 16400 |     2  (50)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| SEARCH_DATA         |    10 | 16400 |     1   (0)| 00:00:01 |
|   6 |       VECTOR INDEX HNSW SCAN    | SEARCH_DATA_HNSW_IX |    10 | 16400 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

   2 - filter(ROWNUM<=10)
   4 - filter(ROWNUM<=10)


統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
       1621  bytes sent via SQL*Net to client
        108  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

ヒントで索引を利用できなくしてみます。新しいヒントが結構追加されていますが、それらについては、また、別の機会にでも。

Oracle Database 23ai / Oracle AI Vector Search User's Guide / Vector Index Hints

https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/vector-index-hints.html

SELECT
  id
  , description
  , community
  , location_desc
  , district
  , TO_NUMBER( v_distance ) AS v_distance
FROM
(
  SELECT 
    /*+
      NO_VECTOR_INDEX_SCAN(search_data)
    */
    id
    , description
    , community
    , location_desc
    , district
    , VECTOR_DISTANCE 
      (
        vector_desc
        , VECTOR_EMBEDDING
         (
           all_minilm_l6 USING 'Incident in which someone may have been murdered' AS data
         )
      , COSINE
    ) v_distance
  FROM 
    search_data
  ORDER BY 
    v_distance 
  FETCH APPROX FIRST 10 ROWS ONLY
)
/

 
10行が選択されました。

経過: 00:00:03.84

実行計画
----------------------------------------------------------
Plan hash value: 2441119222

------------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |    10 |  1570 |       | 51357   (1)| 00:00:03 |
|   1 |  VIEW                    |             |    10 |  1570 |       | 51357   (1)| 00:00:03 |
|*  2 |   COUNT STOPKEY          |             |       |       |       |            |          |
|   3 |    VIEW                  |             |   125K|    18M|       | 51357   (1)| 00:00:03 |
|*  4 |     SORT ORDER BY STOPKEY|             |   125K|   195M|   244M| 51357   (1)| 00:00:03 |
|   5 |      TABLE ACCESS FULL   | SEARCH_DATA |   125K|   195M|       |  8543   (1)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - filter(ROWNUM<=10)
   4 - filter(ROWNUM<=10)


統計
----------------------------------------------------------
       5635  recursive calls
          0  db block gets
      48201  consistent gets
      46497  physical reads
          0  redo size
       1589  bytes sent via SQL*Net to client
        108  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
        119  sorts (memory)
          0  sorts (disk)
         10  rows processed

APPROXキーワードをEXACTに変えました。これもVECTOR INDEXを利用できなくなる条件の一つです。診てみましょう

SELECT
  id
  , description
  , community
  , location_desc
  , district
  , TO_NUMBER( v_distance ) AS v_distance
FROM
(
  SELECT 
    id
    , description
    , community
    , location_desc
    , district
    , VECTOR_DISTANCE 
      (
        vector_desc
        , VECTOR_EMBEDDING
         (
           all_minilm_l6 USING 'Incident in which someone may have been murdered' AS data
         )
      , COSINE
    ) v_distance
  FROM 
    search_data
  ORDER BY 
    v_distance 
  FETCH EXACT FIRST 10 ROWS ONLY
)
/

実行計画とは関係ないですが、APPROXとEXACTの問い合わせ結果って結構違うのね。。。

SCOTT@localhost:1521/freepdb1> /

        ID DESCRIPTION                              COMMUNITY            LOCATION_DESC                            DISTRICT   V_DISTANCE
---------- ---------------------------------------- -------------------- ---------------------------------------- ---------- ----------
  13325717 OTHER CRIME AGAINST PERSON               BRIDGEPORT           APARTMENT                                9TH        .382904768
  13331403 OTHER CRIME AGAINST PERSON               HEGEWISCH            APARTMENT                                4TH        .382904768
  13329763 OTHER CRIME AGAINST PERSON               NEAR NORTH SIDE      SMALL RETAIL STORE                       18TH       .382904768
  13330138 OTHER CRIME AGAINST PERSON               ROSELAND             STREET                                   5TH        .382904768
  13329559 OTHER CRIME AGAINST PERSON               CHICAGO LAWN         APARTMENT                                8TH        .382904768
  13329389 OTHER CRIME AGAINST PERSON               BELMONT CRAGIN       ALLEY                                    25TH       .382904768
  13328588 OTHER CRIME AGAINST PERSON               LOOP                 APARTMENT                                1ST        .382904768
  13328610 OTHER CRIME AGAINST PERSON               SOUTH CHICAGO        APARTMENT                                4TH        .382904768
  13325825 OTHER CRIME AGAINST PERSON               WEST RIDGE           PARKING LOT / GARAGE (NON RESIDENTIAL)   24TH       .382904768
  13326308 OTHER CRIME AGAINST PERSON               PULLMAN              STREET                                   5TH        .382904768
  
  
  
実行計画
----------------------------------------------------------
Plan hash value: 2441119222

------------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |    10 |  1570 |       | 51357   (1)| 00:00:03 |
|   1 |  VIEW                    |             |    10 |  1570 |       | 51357   (1)| 00:00:03 |
|*  2 |   COUNT STOPKEY          |             |       |       |       |            |          |
|   3 |    VIEW                  |             |   125K|    18M|       | 51357   (1)| 00:00:03 |
|*  4 |     SORT ORDER BY STOPKEY|             |   125K|   195M|   244M| 51357   (1)| 00:00:03 |
|   5 |      TABLE ACCESS FULL   | SEARCH_DATA |   125K|   195M|       |  8543   (1)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - filter(ROWNUM<=10)
   4 - filter(ROWNUM<=10)

ということで、
実行計画は, SQL文のレントゲン写真だ！ No.66 / AI Vector Search - VECTOR INDEX HNSW SCAN
はここまで。

今回は一部だけ試してますが更新関連なども含めいろいろな仕組みがあるようで。。。ちょっと追っかけてみないと見えてこないところも多いです。マニュアルも多いですし。。。斜め読みしていて、まじか。。。。となっているところ。

ところで、前回explain plan 文でVector Indexサイズの見積もりを取得していたのですが、覚えてますでしょうか？実はちょっとクセがあるみたいなんですよね。*_SEGMENTSビューからは索引名では見つからないので。。。

あれ、 SEARCH_DATA_HNSW_IX　というVECTOR索引って、索引名で検索しても、*_SEGMENTSビューにでないのか。別の名前になってるのか。。。　セグメントサイズ見たかったのに。。。という謎だけ投げつけて、宿題としておきますw（わざとらしいw） うううーーーーむ、いろいろと知っておく必要なる事項は多そうですねぇ。。。。。

SCOTT@localhost:1521/freepdb1> select index_name,index_type,table_name from user_indexes where table_name='SEARCH_DATA';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
------------------------------ --------------------------- ------------------------------
SYS_IL0000078074C00009$$       LOB                         SEARCH_DATA
SEARCH_DATA_HNSW_IX            VECTOR                      SEARCH_DATA

経過: 00:00:00.05
SCOTT@localhost:1521/freepdb1> select segment_name from user_segments where segment_name = 'SEARCH_DATA_HNSW_IX';

レコードが選択されませんでした。

SCOTT@localhost:1521/freepdb1> r
  1* SELECT * FROM vecsys.vector$index

  IDX_OBJN   IDX_OBJD IDX_OWNER# IDX_NAME                       IDX_BASE_TABLE_OBJN IDX_BASE_TABLE_OWNER# IDX_PARAMS                     IDX_AUXILIARY_TABLES           
---------- ---------- ---------- ------------------------------ ------------------- --------------------- ------------------------------ ------------------------------
     78797                   153 SEARCH_DATA_HNSW_IX                          78074                   153 {"type":"HNSW","num_neighbors" {"rowid_vid_map_objn":78798,"s
                                                                                                          :32,"efConstruction":200,"dist hared_journal_transaction_comm
                                                                                                          ance":"COSINE","accuracy":90," its_objn":78800,"shared_journa
                                                                                                          vector_type":"FLOAT32","vector l_change_log_objn":78803,"rowi
                                                                                                          _dimension":384,"degree_of_par d_vid_map_name":"VECTOR$SEARCH
                                                                                                          allelism":1,"pdb_id":3,"indexe _DATA_HNSW_IX$78074_78797_0$HN
                                                                                                          d_col":"VECTOR_DESC"}          SW_ROWID_VID_MAP","shared_jour
                                                                                                                                         nal_transaction_commits_name":
                                                                                                                                         "VECTOR$SEARCH_DATA_HNSW_IX$78
                                                                                                                                         074_78797_0$HNSW_SHARED_JOURNA
                                                                                                                                         L_TRANSACTION_COMMITS","shared
                                                                                                                                         _journal_change_log_name":"VEC
                                                                                                                                         TOR$SEARCH_DATA_HNSW_IX$78074_
                                                                                                                                         78797_0$HNSW_SHARED_JOURNAL_CH
                                                                                                                                         ANGE_LOG"}
                                                                                                                                         
SCOTT@localhost:1521/freepdb1> r
  1  SELECT
  2    JSON_SERIALIZE(idx_params RETURNING VARCHAR2 PRETTY) AS "INDEX PARAM"
  3    ,JSON_SERIALIZE(idx_auxiliary_tables RETURNING VARCHAR2 PRETTY) AS "INDEX AUX"
  4* FROM vecsys.vector$index

INDEX PARAM                                        INDEX AUX
-------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------
{                                                  {
  "type" : "HNSW",                                   "rowid_vid_map_objn" : 78798,
  "num_neighbors" : 32,                              "shared_journal_transaction_commits_objn" : 78800,
  "efConstruction" : 200,                            "shared_journal_change_log_objn" : 78803,
  "distance" : "COSINE",                             "rowid_vid_map_name" : "VECTOR$SEARCH_DATA_HNSW_IX$78074_78797_0$HNSW_ROWID_VID_MAP",
  "accuracy" : 90,                                   "shared_journal_transaction_commits_name" : "VECTOR$SEARCH_DATA_HNSW_IX$78074_78797_0$HNSW_SHARED_JOURNAL_TRANSACTION_COMMITS",
  "vector_type" : "FLOAT32",                         "shared_journal_change_log_name" : "VECTOR$SEARCH_DATA_HNSW_IX$78074_78797_0$HNSW_SHARED_JOURNAL_CHANGE_LOG"
  "vector_dimension" : 384,                        }
  "degree_of_parallelism" : 1,
  "pdb_id" : 3,
  "indexed_col" : "VECTOR_DESC"
}

では、また！

Enjoin Execution Plans, SQLs, and AI Vector Search!

Related article on Mac De Oracle
実行計画は, SQL文のレントゲン写真だ！

AI Vector Search

・Oracle Database 23ai freeで試すVector Search - ONNXモデル準備編
・Oracle Database 23ai freeで試すVector Search - データ準備編

投稿日時 2025年6月 5日 (木) 00時00分チューニング, FAQ, SQL, パフォーマンス, INDEX, Oracle Database 23c Free Developer Release, JSON, VECTOR, AI | 固定リンク | 0 | コメント (0)

2025年6月 4日 (水)

Oracle Database 23ai freeで試すVector Search - データ準備編

Previously on Mac De Oracle
前回は、 ONNXモデル準備編でした。意外にすんなりできちゃいましたね。
で、今回は、Vector Searchのレントゲンという名の実行計画を取得するデータの準備編です。

ただし、Oracle Database 23ai Freeを利用するため以下のリソース制限があります。
その制限の中で行える量に調整しつつ準備します。

Oracle Database 23ai Free limitations

https://www.oracle.com/database/free/

Resource limit up to
・2 CPUs (for foreground processes)
・2 GB of RAM
・12 GB of user data on disk (included SYSAUX)

VirtualBOXの対象VMストレージサイズは十分なサイズにしてあります。(Oracle Database 23ai Freeの制限以上は確保してあります。Linux上での作業領域含む)

% VBoxManage showvminfo 25951093-0df5-47e8-823c-ac7fdf2902bf | grep Memory
Memory size:                 4096MB

VM上からみたストレージ空き容量とCPU数

[oracle@localhost work4vector]$ sudo df -aH | grep '/sd.'
[sudo] oracle のパスワード:
/dev/sda3         58G   20G   38G   35% /
/dev/sda1        1.1G  346M  719M   33% /boot

[oracle@localhost work4vector]$ grep cpu.cores /proc/cpuinfo | sort -u
cpu cores  : 2

Oracle Database 23aiのデータサイズは以下の通り。制限まで十分に余裕はあります

SYS@localhost:1521/freepdb1> r
  1* select file_name,tablespace_name,bytes/1024/1024/1024 "GB",autoextensible from dba_data_files

FILE_NAME                                              TABLESPACE_NAME                        GB AUT
------------------------------------------------------ ------------------------------ ---------- ---
/opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf         SYSAUX                          .76171875 YES
/opt/oracle/oradata/FREE/FREEPDB1/users01.dbf          USERS                          .463867188 YES
/opt/oracle/oradata/FREE/FREEPDB1/system01.dbf         SYSTEM                         .419921875 YES
/opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf        UNDOTBS1                         .1953125 YES

Vector Search向けサンプルデータはGetting Started with Oracle Database 23ai AI Vector Searchでも紹介されているChicago Data Portalのオープンデータを利用します。結構いいサイズのデータが公開されています。
とは言ってもVector Indexも含めたサイズを考えると、Oracle Database 23ai Freeに全データを取り込むのは現実的なく一部を取り込んで使うことにします。

一部を使うとはいっても結構なサイズです。データ自体欠損していたりするのもあるようなのでそれなりに工夫して使いまっすw
ETL屋さんの腕の見せ所ですねww

スクリーンショット1

スクリーンショット2

Chcago Data Portalから取得したオープンデータのCSV形式ファイルは以下のとおり。

シカゴの犯罪データで2001年から直近のデータまで公開されています。 データセットには座標を含むGEOデータも含まれていますが今回は利用しません。このデータセットだけで1.9GB程度ですが、最終的に取り込むのは1年分より少ない程度にします。
chicago_crimes_2001_20250508.csv

属性やコード値の意味および、マスタデータとなるデータセットは何かという情報は、Chicago Data Portalで該当するデータセットを開くと表示される列ヘッダーのヘルプポップアップダイアログに記載されています。
（前述、スクリーンショット２参照のこと）
以下のマスターデータセットは、chicago_crimes_2001_20250508.csv　と結合してコード値の読み替えなどに利用します。

Community Areaとして定義されているエリア名称などが含まれます（最新のもの） Boundaries_-_Community_Areas_20250508.csv

Districtの地区名称などが定義されています（最新のもの） PoliceDistrictDec2012_20250508.csv

シカゴ警察が利用しているイリノイ州の標準犯罪レポートIUCRコードマスター（最新のもの） Chicago_Police_Department_-_Illinois_Uniform_Crime_Reporting__IUCR__Codes_20250512.csv

使うかわからないですが、犯罪発生データに含まれているWARDの情報のマスターデータセットです（最新のものですが、結局使わなかったw） WARDS_2015_20250508.csv

上記データは、以下に作成したディレクトリオブジェクトに対応するディレクトリに配置して外部表のソースとなります。

SYS@localhost:1521/freepdb1> create directory chicago_crimes_data_dir as '/home/oracle/work4vector';

ディレクトリが作成されました。

経過: 00:00:00.46
SYS@localhost:1521/freepdb1> r
  1* select directory_name, directory_path from dba_directories where directory_name = upper('chicago_crimes_data_dir')

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------
CHICAGO_CRIMES_DATA_DIR        /home/oracle/work4vector

経過: 00:00:00.01

SYS@localhost:1521/freepdb1> grant read on directory chicago_crimes_data_dir to scott;
権限付与が成功しました。

経過: 00:00:00.04

SCOTT@localhost:1521/freepdb1> !ls -l
合計 1917360
-rw-r--r--. 1 oracle oracle         3566  5月  9 09:46 Boundaries_-_Community_Areas_20250508.csv
-rw-r--r--. 1 oracle oracle        22307  5月 12 23:33 Chicago_Police_Department_-_Illinois_Uniform_Crime_Reporting__IUCR__Codes_20250512.csv
-rw-r--r--. 1 oracle oracle          202  5月  9 09:47 PoliceDistrictDec2012_20250508.csv
-rw-r--r--. 1 oracle oracle         1558  5月  9 09:48 WARDS_2015_20250508.csv
-rw-r--r--. 1 oracle oracle   1947211979  5月  9 03:40 chicago_crimes_2001_20250508.csv

Vector Searchで利用する表は以下、それ以外はこの表のデータを作りだすための元となるデータで外部票として取り込みVector Searchで利用する表データに変換します。
(DROP TABLE IF EXISTS構文は、Oracle Database 23ai 以降で使えます)

 DROP TABLE IF EXISTS search_data PURGE;
 CREATE TABLE search_data
 (
   id NUMBER
   , primary_description VARCHAR2(40)
   , description VARCHAR2(100)
   , location_desc VARCHAR2(100)
   , district VARCHAR2(30)
   , ward NUMBER
   , community VARCHAR2(30)
   , c_year NUMBER
   , vector_desc VECTOR
 )
 /

上記表のインプットとなる各外部票は以下の通り。

シカゴの犯罪データのオリジナルです。search_data_org（外部表）

列コメントにある数字と列名は、search_dataの列順と列名に対応しています。（ただし、そのままセットするものもあれば適宜型変換やマスターデータと結合して読み替えたりします。元になる列を示しています。
chicago_crimes_2001_20250508_2.csvというファイルがありますが、chicago_crimes_2001_20250508.csvのうちBad dataとして弾かれたデータが出力される.badファイルを元にデータが通るように整形したものを再度インプットとして利用したものです。

CREATE TABLE search_data_org
(
  id                VARCHAR2(100)    -- 1 id
 ,case_number       VARCHAR2(100) 
 ,case_Date         VARCHAR2(100) 
 ,block             VARCHAR2(100) 
 ,iucr              VARCHAR2(100)    -- 2 primary_description
 ,primary_type      VARCHAR2(100) 
 ,description       VARCHAR2(100)    -- 3 description
 ,Location_desc     VARCHAR2(100)    -- 4 location_desc
 ,arrest            VARCHAR2(100) 
 ,domestic          VARCHAR2(100) 
 ,beat              VARCHAR2(100) 
 ,district          VARCHAR2(100)    -- 5 district
 ,ward              VARCHAR2(100)    -- 6 ward
 ,community         VARCHAR2(100)    -- 7 community
 ,fbi_code          VARCHAR2(100) 
 ,x_coordinate      VARCHAR2(100) 
 ,y_coordinate      VARCHAR2(100) 
 ,c_year            VARCHAR2(100)    -- 8 c_year
 ,updated_on        VARCHAR2(100) 
 ,latitude          VARCHAR2(100) 
 ,longitude         VARCHAR2(100) 
 ,location          VARCHAR2(100) 
) 
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY chicago_crimes_data_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE 
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
    MISSING FIELD VALUES ARE NULL
    (
       id                     
      ,case_number
      ,case_Date
      ,block
      ,iucr
      ,primary_type
      ,description           
      ,Location_desc         
      ,arrest
      ,domestic
      ,beat
      ,district              
      ,ward                  
      ,community             
      ,fbi_code
      ,x_coordinate
      ,y_coordinate
      ,c_year                 
      ,updated_on
      ,latitude
      ,longitude
      ,location1
      ,location2
    )
    COLUMN TRANSFORMS
    (
      location FROM CONCAT 
      ( 
        location1
        ,location2 
      )
    )
  )
  LOCATION (
    'chicago_crimes_2001_20250508.csv'
    ,'chicago_crimes_2001_20250508_2.csv'
  )
)
REJECT LIMIT UNLIMITED
/

以降、マスターデータセットの外部表定義です。

communitiesマスタ（外部表）
AREA_NUMBER列がキーです。search_data_org外部表（シカゴの犯罪データ）のcommunity列と結合することで（列名違いすぎるwww、実案件だったらキレてるな、俺w）、COMMUNITY_NAMEに読み替えることができる、と。。。
とは言え、ちょいと怪しいので外部結合するようにしとくかw

CREATE TABLE communities 
(
  AREA_NUMBER NUMBER
  , COMMUNITY_NAME VARCHAR2(100)
  , AREA_NUM_1 NUMBER
  , SHAPE_AREA NUMBER
  , SHAPE_LEN NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY chicago_crimes_data_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE 
    SKIP 1
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
    MISSING FIELD VALUES ARE NULL
    (
      AREA_NUMBER
      , COMMUNITY_NAME
      , AREA_NUM_1
      , SHAPE_AREA
      , SHAPE_LEN
    )
  )
  LOCATION (
    'Boundaries_-_Community_Areas_20250508.csv'
  )
)REJECT LIMIT UNLIMITED/

districtsマスタ（外部表）
DIST_NUMBER列がキーで、search_data_org外部表（シカゴの犯罪データ）のdistrict列と結合することで、DIST_LABELに読み替えることができます。(これも列名が異なっていて非常にわかりにくいwwww なんでこんな状態なのみたいなw)
これも外部結合の方が良さそうな雰囲気はありますね。実際詳しく追ったわけではないですがw

CREATE TABLE districts
(
   DIST_LABEL VARCHAR2(100)
   , DIST_NUMBER NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY chicago_crimes_data_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE 
    SKIP 1
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
    MISSING FIELD VALUES ARE NULL
    (
       DIST_LABEL
       , DIST_NUMBER
    )
  )
  LOCATION (
    'PoliceDistrictDec2012_20250508.csv'
  )
)
REJECT LIMIT UNLIMITED
/

illinoi_uniform_crime_reporting_codesマスタ（外部表）
iucr列とsearch_data_org外部表（シカゴの犯罪データ）のiucr列を結合することでprimary_descriptionに読み替えることができます。結合キー列名が同じなのはよろしいのですが、ここまでバラバラなのにここだけ同じなのかww （これもなんとなく怪しいので、外部結合にしようと思います）

CREATE TABLE illinoi_uniform_crime_reporting_codes
(
  iucr                     VARCHAR2(5)
  , primary_description    VARCHAR2(100)
  , secondary_description  VARCHAR2(100)
  , index_code             CHAR(1)
  , active                 BOOLEAN NOT NULL
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY chicago_crimes_data_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE 
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
    MISSING FIELD VALUES ARE NULL
    (
      iucr
      ,primary_description
      ,secondary_description
      ,index_code
      ,active
    )
  )
  LOCATION (
    'Chicago_Police_Department_-_Illinois_Uniform_Crime_Reporting__IUCR__Codes_20250512.csv'
  )
)
REJECT LIMIT UNLIMITED
/

最後にsearch_dataにVECTORを埋め込んだデータを登録するためのIASです。VECTOR_EMBEDDING関数でsearch_data_org.descriptionを元にVECTORデータを生成しています。
全データ（830万件）のうち、2024年に発生したデータのID順で、125,000行だけ登録します。（理由は、Oracle Database 23ia Freeのデータ量というより2GBまでしか使えないというメモリサイズの制限が重しになっているためです。後述）

TRUNCATE TABLE search_data;
INSERT INTO search_data
(
  id
  , primary_description
  , description
  , location_desc
  , district
  , ward
  , community
  , c_year
  , vector_desc
)
SELECT
  sdo.id                       AS id
 , iucrc.primary_description   AS primary_description
 , sdo.description             AS description
 , sdo.Location_desc           AS Location_desc
 , d.dist_label                AS district
 , TO_NUMBER(sdo.ward)         AS ward
 , c.community_name            AS community
 , TO_NUMBER(sdo.c_year)       AS c_year
 , VECTOR_EMBEDDING(all_minilm_l6 USING sdo.description AS data) AS vector_desc
FROM 
  search_data_org sdo
  LEFT OUTER JOIN districts d
  ON
    TO_NUMBER(sdo.district) = d.dist_number
  LEFT OUTER JOIN communities c
  ON 
    TO_NUMBER(sdo.community) = c.area_number
  LEFT OUTER JOIN illinoi_uniform_crime_reporting_codes iucrc
  ON
    sdo.iucr = iucrc.iucr
WHERE
  TO_NUMBER(sdo.c_year) IN 2024
ORDER BY
  id
  , c_year
FETCH FIRST 125000 ROWS ONLY
/

では最初に、Vector Searchで利用する表だけ作成しておきます。データは前述のIASでガツンと登録しちゃいます。

SCOTT@localhost:1521/freepdb1> l
  1   CREATE TABLE search_data
  2   (
  3     id NUMBER
  4     , primary_description VARCHAR2(40)
  5     , description VARCHAR2(100)
  6     , location_desc VARCHAR2(100)
  7     , district VARCHAR2(30)
  8     , ward NUMBER
  9     , community VARCHAR2(30)
 10     , c_year NUMBER
 11     , vector_desc VECTOR
 12*  )
SCOTT@localhost:1521/freepdb1> /

表が作成されました。

経過: 00:00:00.03

もろもろエラーデータを補正しつつw なんとか search_data_org 外部表の作成に成功！　（エラーになっていたログはバッサリ削除しましたが。。。）

SCOTT@localhost:1521/freepdb1> l
  1  CREATE TABLE search_data_org
  2  (
  3    id                VARCHAR2(100)    -- 1
  4   ,case_number       VARCHAR2(100)
  5   ,case_Date         VARCHAR2(100)
  6   ,block             VARCHAR2(100)
  7   ,iucr              VARCHAR2(100)    -- 2 
  8   ,primary_type      VARCHAR2(100)
  9   ,description       VARCHAR2(100)    -- 3
 10   ,Location_desc     VARCHAR2(100)    -- 4
 11   ,arrest            VARCHAR2(100)
 12   ,domestic          VARCHAR2(100)
 13   ,beat              VARCHAR2(100)
 14   ,district          VARCHAR2(100)    -- 5 nullあり
 15   ,ward              VARCHAR2(100)    -- 6 nullあり
 16   ,community         VARCHAR2(100)    -- 7 数値データのみ、nullあり
 17   ,fbi_code          VARCHAR2(100)
 18   ,x_coordinate      VARCHAR2(100)
 19   ,y_coordinate      VARCHAR2(100)
 20   ,c_year            VARCHAR2(100)    -- 8 数値データのみ
 21   ,updated_on        VARCHAR2(100)
 22   ,latitude          VARCHAR2(100)
 23   ,longitude         VARCHAR2(100)
 24   ,location          VARCHAR2(100)
 25  )
 26  ORGANIZATION EXTERNAL (
 27    TYPE ORACLE_LOADER
 28    DEFAULT DIRECTORY chicago_crimes_data_dir
 29    ACCESS PARAMETERS (
 30      RECORDS DELIMITED BY NEWLINE
 31      FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 32      MISSING FIELD VALUES ARE NULL
 33      (
 34         id
 35        ,case_number
 36        ,case_Date
 37        ,block
 38        ,iucr
 39        ,primary_type
 40        ,description
 41        ,Location_desc
 42        ,arrest
 43        ,domestic
 44        ,beat
 45        ,district
 46        ,ward
 47        ,community
 48        ,fbi_code
 49        ,x_coordinate
 50        ,y_coordinate
 51        ,c_year
 52        ,updated_on
 53        ,latitude
 54        ,longitude
 55        ,location1
 56        ,location2
 57      )
 58      COLUMN TRANSFORMS
 59      (
 60        location FROM CONCAT
 61        (
 62          location1
 63          ,location2
 64        )
 65      )
 66    )
 67    LOCATION (
 68      'chicago_crimes_2001_20250508.csv'
 69      ,'chicago_crimes_2001_20250508_2.csv'
 70    )
 71  )
 72* REJECT LIMIT UNLIMITED
SCOTT@localhost:1521/freepdb1> /

表が作成されました。

経過: 00:00:00.02
SCOTT@localhost:1521/freepdb1> select count(1) from search_data_org;

  COUNT(1)
----------
   8307418

経過: 00:00:36.80
SCOTT@localhost:1521/freepdb1> ! ls -l *.bad
ls: '*.bad' にアクセスできません: No such file or directory

communitiesマスタ（外部表）の作成

SCOTT@localhost:1521/freepdb1> l
  1  CREATE TABLE communities
  2  (
  3    AREA_NUMBER NUMBER
  4    , COMMUNITY_NAME VARCHAR2(100)
  5    , AREA_NUM_1 NUMBER
  6    , SHAPE_AREA NUMBER
  7    , SHAPE_LEN NUMBER
  8  )
  9  ORGANIZATION EXTERNAL (
 10    TYPE ORACLE_LOADER
 11    DEFAULT DIRECTORY chicago_crimes_data_dir
 12    ACCESS PARAMETERS (
 13      RECORDS DELIMITED BY NEWLINE
 14      SKIP 1
 15      FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 16      MISSING FIELD VALUES ARE NULL
 17      (
 18        AREA_NUMBER
 19        , COMMUNITY_NAME
 20        , AREA_NUM_1
 21        , SHAPE_AREA
 22        , SHAPE_LEN
 23      )
 24    )
 25    LOCATION (
 26      'Boundaries_-_Community_Areas_20250508.csv'
 27    )
 28  )
 29* REJECT LIMIT UNLIMITED
SCOTT@localhost:1521/freepdb1> /

表が作成されました。

経過: 00:00:00.03

SCOTT@localhost:1521/freepdb1> select count(1) from communities;

  COUNT(1)
----------
        77

経過: 00:00:00.03
SCOTT@localhost:1521/freepdb1> ! ls -l *.bad
ls: '*.bad' にアクセスできません: No such file or directory

districtsマスタ（外部表）の作成

SCOTT@localhost:1521/freepdb1> l
  1  CREATE TABLE districts
  2  (
  3     DIST_LABEL VARCHAR2(100)
  4     , DIST_NUMBER NUMBER
  5  )
  6  ORGANIZATION EXTERNAL (
  7    TYPE ORACLE_LOADER
  8    DEFAULT DIRECTORY chicago_crimes_data_dir
  9    ACCESS PARAMETERS (
 10      RECORDS DELIMITED BY NEWLINE
 11      SKIP 1
 12      FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 13      MISSING FIELD VALUES ARE NULL
 14      (
 15         DIST_LABEL
 16         , DIST_NUMBER
 17      )
 18    )
 19    LOCATION (
 20      'PoliceDistrictDec2012_20250508.csv'
 21    )
 22  )
 23* REJECT LIMIT UNLIMITED
SCOTT@localhost:1521/freepdb1> /

表が作成されました。

経過: 00:00:00.02

SCOTT@localhost:1521/freepdb1> ! ls -l *.bad
ls: '*.bad' にアクセスできません: No such file or directory

SCOTT@localhost:1521/freepdb1> select count(1) from districts;

  COUNT(1)
----------
        25

経過: 00:00:00.04

illinoi_uniform_crime_reporting_codesマスタ（外部表）の作成

SCOTT@localhost:1521/freepdb1> l
  1  CREATE TABLE illinoi_uniform_crime_reporting_codes
  2  (
  3    iucr                     VARCHAR2(5)
  4    , primary_description    VARCHAR2(100)
  5    , secondary_description  VARCHAR2(100)
  6    , index_code             CHAR(1)
  7    , active                 BOOLEAN NOT NULL
  8  )
  9  ORGANIZATION EXTERNAL (
 10    TYPE ORACLE_LOADER
 11    DEFAULT DIRECTORY chicago_crimes_data_dir
 12    ACCESS PARAMETERS (
 13      RECORDS DELIMITED BY NEWLINE
 14      FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 15      MISSING FIELD VALUES ARE NULL
 16      (
 17        iucr
 18        ,primary_description
 19        ,secondary_description
 20        ,index_code
 21        ,active
 22      )
 23    )
 24    LOCATION (
 25      'Chicago_Police_Department_-_Illinois_Uniform_Crime_Reporting__IUCR__Codes_20250512.csv'
 26    )
 27  )
 28* REJECT LIMIT UNLIMITED
SCOTT@localhost:1521/freepdb1> /

表が作成されました。

経過: 00:00:00.02

SCOTT@localhost:1521/freepdb1> ! ls -l *.bad
ls: '*.bad' にアクセスできません: No such file or directory

SCOTT@localhost:1521/freepdb1> select count(1) from illinoi_uniform_crime_reporting_codes;

  COUNT(1)
----------
       410

経過: 00:00:00.02

では、最後の仕上げ。VECTOR INDEX以外はこれで完成です！！！, 0.25GB程度の消費ですね。表だけで。VECTOR INDEXは別途作成します。

SCOTT@localhost:1521/freepdb1> l
  1  INSERT INTO search_data
  2  (
  3    id
  4    , primary_description
  5    , description
  6    , location_desc
  7    , district
  8    , ward
  9    , community
 10    , c_year
 11    , vector_desc
 12  )
 13  SELECT
 14    sdo.id                       AS id
 15   , iucrc.primary_description   AS primary_description
 16   , sdo.description             AS description
 17   , sdo.Location_desc           AS Location_desc
 18   , d.dist_label                AS district
 19   , TO_NUMBER(sdo.ward)         AS ward
 20   , c.community_name            AS community
 21   , TO_NUMBER(sdo.c_year)       AS c_year
 22   , VECTOR_EMBEDDING(all_minilm_l6 USING sdo.description AS data) AS vector_desc
 23  FROM
 24    search_data_org sdo
 25    LEFT OUTER JOIN districts d
 26    ON
 27      TO_NUMBER(sdo.district) = d.dist_number
 28    LEFT OUTER JOIN communities c
 29    ON
 30      TO_NUMBER(sdo.community) = c.area_number
 31    LEFT OUTER JOIN illinoi_uniform_crime_reporting_codes iucrc
 32    ON
 33      sdo.iucr = iucrc.iucr
 34  WHERE
 35    TO_NUMBER(sdo.c_year) IN 2024
 36  ORDER BY
 37    id
 38    , c_year
 39* FETCH FIRST 125000 ROWS ONLY
SCOTT@localhost:1521/freepdb1> /

125000 行が作成されました。

経過: 00:39:28.49
SCOTT@localhost:1521/freepdb1> commit;

コミットが完了しました。

経過: 00:00:00.04
SCOTT@localhost:1521/freepdb1> select segment_name,bytes/1024/1024/1024 "GB" 
2 from user_segments where segment_name = 'SEARCH_DATA';

SEGMENT_NAME                           GB
------------------------------ ----------
SEARCH_DATA                      .2421875

経過: 00:00:00.29
SCOTT@localhost:1521/freepdb1> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'SEARCH_DATA',no_invalidate=>false);

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:05.97

ちなみに、前述のIASの実行計画はこんな感じ。

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                |                                       |   551K|    55M|       | 77540   (1)| 00:00:04 |
|   1 |  LOAD TABLE CONVENTIONAL        | SEARCH_DATA                           |       |       |       |            |          |
|   2 |   SORT ORDER BY                 |                                       |   551K|    55M|    61M| 77540   (1)| 00:00:04 |
|*  3 |    HASH JOIN RIGHT OUTER        |                                       |   551K|    55M|       | 64408   (1)| 00:00:03 |
|   4 |     EXTERNAL TABLE ACCESS FULL  | DISTRICTS                             |    25 |   200 |       |     2   (0)| 00:00:01 |
|*  5 |     HASH JOIN RIGHT OUTER       |                                       |   510K|    47M|       | 64405   (1)| 00:00:03 |
|*  6 |      EXTERNAL TABLE ACCESS FULL | ILLINOI_UNIFORM_CRIME_REPORTING_CODES |   410 |  8610 |       |     3   (0)| 00:00:01 |
|*  7 |      HASH JOIN RIGHT OUTER      |                                       |   510K|    37M|       | 64401   (1)| 00:00:03 |
|   8 |       EXTERNAL TABLE ACCESS FULL| COMMUNITIES                           |    77 |  1232 |       |     2   (0)| 00:00:01 |
|*  9 |       EXTERNAL TABLE ACCESS FULL| SEARCH_DATA_ORG                       |   510K|    29M|       | 64398   (1)| 00:00:03 |
---------------------------------------------------------------------------------------------------------------------------------

次回、VECTOR SEARCHの実行計画という名のレントゲンネタのために、VECTOR INDEXサイズの見積サイズを確認しておきましょう。(Hierarchical Navigable Small World索引を利用します)
以下のように

参考） Oracle Database Release 23 / Oracle AI Vector Searchユーザーズ・ガイド / ベクトル索引の様々なカテゴリの管理

https://docs.oracle.com/cd/G11854_01/vecse/manage-different-categories-vector-indexes.html

Oracle Database Release 23 / Oracle AI Vector Searchユーザーズ・ガイド / ベクトル索引の使用に関するガイドライン

https://docs.oracle.com/cd/G11854_01/vecse/guidelines-using-vector-indexes.html

Oracle Database Release 23 / Oracle AI Vector Searchユーザーズ・ガイド / ベクトル索引のオプティマイザ計画

https://docs.oracle.com/cd/G11854_01/vecse/optimizer-plans-vector-indexes.html

Oracle Database Release 23 / Oracle AI Vector Searchユーザーズ・ガイド / 6 ベクトル索引およびハイブリッド・ベクトル索引の作成

https://docs.oracle.com/cd/G11854_01/vecse/create-vector-indexes-and-hybrid-vector-indexes.html#VECSE-GUID-8AF956F3-D951-4968-9B79-A6E180E87456

Oracle Database Release 23 / Oracle AI Vector Searchユーザーズ・ガイド / インメモリー近傍グラフ・ベクトル索引

https://docs.oracle.com/cd/G11854_01/vecse/memory-neighbor-graph-vector-index.html

Oracle Database Release 23 / Oracle AI Vector Searchユーザーズ・ガイド / Hierarchical Navigable Small World索引の理解

https://docs.oracle.com/cd/G11854_01/vecse/understand-hierarchical-navigable-small-world-indexes.html

Oracle Database Release 23 / Oracle AI Vector Searchユーザーズ・ガイド / Hierarchical Navigable Small World索引の構文およびパラメータ

https://docs.oracle.com/cd/G11854_01/vecse/hierarchical-navigable-small-world-index-syntax-and-parameters.html

冒頭で約830万行のデータから125,000行に制限したと書きましたが、その理由はベクトル・プールをSGA内に確保する必要があるからなんですよー。
そもそも、Oracle Database 23ai Free はメモリサイズの制限もあるので、その範囲に収まるようデータ量を制限しました。（ストレージより先にメモリサイズの制限にあたりそうだったので。。。w）

Oracle Database Release 23 / Oracle AI Vector Searchユーザーズ・ガイド / ベクトル・プールのサイズ設定

https://docs.oracle.com/cd/G11854_01/vecse/size-vector-pool.html

以下のVECTOR HNSW INDEXを作成予定なのですが、索引サイズを見積りサイズを確認しておきましょう。（Oracle Databaseには昔から便利なコマンドがあります。Explainで索引サイズの見積もりができるんですよね。

CREATE VECTOR INDEX search_data_hnsw_ix ON search_data ( vector_desc )
  ORGANIZATION
  INMEMORY NEIGHBOR GRAPH
  DISTANCE COSINE
  WITH TARGET ACCURACY 90
/

SCOTT@localhost:1521/freepdb1> l
  1  explain plan for
  2  CREATE VECTOR INDEX search_data_hnsw_ix ON search_data ( vector_desc )
  3    ORGANIZATION
  4      INMEMORY NEIGHBOR GRAPH
  5      DISTANCE COSINE
  6*     WITH TARGET ACCURACY 90
05:41:08 SCOTT@localhost:1521/freepdb1> /

解析されました。

経過: 00:00:00.02
SCOTT@localhost:1521/freepdb1> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 2727344110

----------------------------------------------------------------------------------------------
| Id  | Operation              | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |                     |   125K|   188M| 14689   (1)| 00:00:01 |
|   1 |  VECTOR INDEX BUILD    | SEARCH_DATA_HNSW_IX |       |       |            |          |
----------------------------------------------------------------------------------------------

Note
-----
   - estimated index size: 293M bytes

12GBまでデータを使えるんだけどインメモリー系機能使おうとすると、メモリサイズの制限キツイよねと、思ったりw

Enjoy SQL and Oracle Database.

次回へ続く。

・Oracle Database 23ai freeで試すVector Search - ONNXモデル準備編

投稿日時 2025年6月 4日 (水) 00時00分 PL/SQL, SQL, Oracle Database 23c Free Developer Release, VECTOR, AI | 固定リンク | 0 | コメント (0)

2025年6月 3日 (火)

Oracle Database 23ai freeで試すVector Search - ONNXモデル準備編

Getting Started with Oracle Database 23ai AI Vector Search
https://blogs.oracle.com/database/post/getting-started-with-oracle-database-23ai-ai-vector-search

上記ブログを見て、これも実行計画はSQL文のレントゲン写真ネタにはなりそうだなぁ、と思い。いきなり準備を始めたw　今日はその準備（日本語対応してないモデル）

環境は以下の通り。
VirtualBOX向けPrebuild VMを利用（なお、Arm64ではなく、Intel Mac）
いずれ、Arm64のネタも書く予定ではいますがw　それだと古ーーーーーい Oracleだとこんな結果に。。。みたいな差分比較ネタができないのでw　とりあえず。X86_64環境にて。。。(Arm64ネイティブの環境はまだ作りかけなのでw)

クラウドではなくオフラインでも楽しめる環境（23ai Freeなのでリソース制限の範囲内で遊べる環境にします）を作ります。

*** mac info. ***
ProductName:	macOS
ProductVersion:	12.7.6
BuildVersion:	21H1320
 
*** macOS ver. ***
      Model Name: MacBook
      Processor Name: Dual-Core Intel Core m5
      Processor Speed: 1.2 GHz
      Number of Processors: 1
      Total Number of Cores: 2
      Memory: 8 GB
 
*** VirtualBox ver. ***
7.0.10r158379

[oracle@localhost ~]$ cat /etc/oracle-release /etc/redhat-release
Oracle Linux Server release 8.9
Red Hat Enterprise Linux release 8.9 (Ootpa)
[oracle@localhost ~]$ uname -srpo
Linux 5.15.0-3.60.5.1.el8uek.x86_64 x86_64 GNU/Linux

SCOTT@localhost:1521/freepdb1> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

12.2 ONNXモデルへの事前トレーニング済モデルの変換: エンドツーエンドの手順
https://docs.oracle.com/cd/G28130_01/2-23ai/mlpug/convert-pretrained-models-onnx-model-end-end-instructions.html

の手順に沿ってONNIXモデルの準備をします！。　

ドキュメント斜め読みしながら環境をつくっていたこともあり、インストールするOML4Pyのリリースが異なっていたりしてやり直したりしているログも含めているため読みずらいかもしれません。悪しからず m(_ _)m　　
進める前に、必要なversionなど事前確認しておくとスムーズだと思います。。。。。（お前が言うか〜っw

初っ端からエラー！。なんだろ。（すんなり進むのかこれw）

Pythonのインストール

[oracle@localhost ~]$ sudo yum install libffi-devel openssl openssl-devel tk-devel xz-devel zlib-devel bzip2-devel readline-devel libuuid-devel ncurses-devel libaio
[sudo] oracle のパスワード:
Oracle Linux 8 BaseOS Latest (x86_64)                                             0.0  B/s |   0  B     08:00
Errors during downloading metadata for repository 'ol8_baseos_latest':
  - Curl error (28): Timeout was reached for https://yum-us-phoenix-1.oracle.com/repo/OracleLinux/OL8/baseos/latest/x86_64/repodata/repomd.xml 
  [Connection timed out after 120000 milliseconds]
エラー: repo 'ol8_baseos_latest' のメタデータのダウンロードに失敗しました : Cannot download repomd.xml: Cannot download repodata/repomd.xml: All mirrors were tried

こんなの見つけた〜。多分これ。
yum update not working using Oracle Database 23ai Free Container Image Lite #2900
https://github.com/oracle/docker-images/issues/2900

間違いない！　これが原因だ
書かれている対策をまんま適用して。。。。

[oracle@localhost ~]$ cd /etc/yum/vars/
[oracle@localhost vars]$ ll
合計 8
-rw-r--r--. 1 root root 11  5月  1  2024 ocidomain
-rw-r--r--. 1 root root 14  5月  1  2024 ociregion
[oracle@localhost vars]$ cat ociregion
-us-phoenix-1
[oracle@localhost vars]$ cat ocidomain
oracle.com

[oracle@localhost vars]$ sudo mv ociregion ociregion.org
[sudo] oracle のパスワード:
[oracle@localhost vars]$ sudo su -
[root@localhost ~]#  sudo echo -n "" >  /etc/yum/vars/ociregion
[root@localhost ~]#  cat /etc/yum/vars/ociregion
[root@localhost ~]# exit
logout

気を取り直して、もう一度。

[oracle@localhost ~]$ sudo yum install libffi-devel openssl openssl-devel tk-devel xz-devel zlib-devel bzip2-devel readline-devel libuuid-devel ncurses-devel libaio

Oracle Linux 8 BaseOS Latest (x86_64)                                                5.0 MB/s |  97 MB     00:19
Oracle Linux 8 Application Stream (x86_64)                                           5.1 MB/s |  70 MB     00:13
Latest Unbreakable Enterprise Kernel Release 7 for Oracle Linux 8 (x86_64)           5.1 MB/s |  61 MB     00:12

...略...

パッケージ openssl-1:1.1.1k-12.el8_9.x86_64 は既にインストールされています。
パッケージ libaio-0.3.112-1.el8.x86_64 は既にインストールされています。
依存関係が解決しました。
===============================================================================================================
 パッケージ                            アーキテクチャー       バージョン            リポジトリー             サイズ
===============================================================================================================
インストール:
 bzip2-devel                          x86_64                1.0.6-28.el8_10      ol8_baseos_latest       224 k

 ...略...

 xorg-x11-proto-devel                 noarch                2020.1-3.el8         ol8_appstream           280 k

トランザクションの概要
===============================================================================================================
インストール    32 パッケージ
アップグレード  10 パッケージ

ダウンロードサイズの合計: 17 M
これでよろしいですか? [y/N]: y
パッケージのダウンロード:
(1/42): bzip2-devel-1.0.6-28.el8_10.x86_64.rpm                               1.5 MB/s | 224 kB     00:00

...略...

(42/42): util-linux-2.32.1-46.0.1.el8.x86_64.rpm                             3.6 MB/s | 2.5 MB     00:00
-----------------------------------------------------------------------------------------------------------
合計                                                                         4.3 MB/s |  17 MB     00:03

...略...

トランザクションのテストに成功しました。
トランザクションを実行中
  準備             :                                                                     1/1
  scriptletの実行中: libuuid-2.32.1-46.0.1.el8.x86_64                                    1/1
  アップグレード中 : libuuid-2.32.1-46.0.1.el8.x86_64                                     1/52

...略...

  xz-devel-5.2.4-4.el8_6.x86_64           zlib-devel-1.2.11-25.el8.x86_64

[oracle@localhost ~]$ mkdir -p $HOME/python
[oracle@localhost ~]$ wget https://www.python.org/ftp/python/3.12.0/Python-3.12.0.tgz

--2025-05-13 02:48:50--  https://www.python.org/ftp/python/3.12.0/Python-3.12.0.tgz

...略...

HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 27195214 (26M) [application/octet-stream]
`Python-3.12.0.tgz' に保存中

Python-3.12.0.tgz    100%[===================================>]  25.93M  4.79MB/s 時間 5.4s

2025-05-13 02:48:56 (4.80 MB/s) - `Python-3.12.0.tgz' へ保存完了 [27195214/27195214]

[oracle@localhost ~]$ tar -xvzf Python-3.12.0.tgz --strip-components=1 -C /home/$USER/python
Python-3.12.0/Grammar/
Python-3.12.0/Grammar/python.gram

...略...

Python-3.12.0/Objects/tupleobject.c
Python-3.12.0/install-sh

[oracle@localhost ~]$ cd $HOME/python
[oracle@localhost python]$ ./configure --prefix=$HOME/python

checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu

...略...

configure: creating Modules/Setup.local
configure: creating Makefile
configure:

If you want a release build with all stable optimizations active (PGO, etc),
please run ./configure --enable-optimizations

[oracle@localhost python]$ make clean; make

find . -depth -name '__pycache__' -exec rm -rf {} ';'
find . -name '*.py[co]' -exec rm -f {} ';'
find . -name '*.[oa]' -exec rm -f {} ';'

...略...

# Pristine binaries before BOLT optimization.
rm -f *.prebolt
# BOLT instrumented binaries.
rm -f *.bolt_inst
gcc -pthread -c -fno-strict-overflow -Wsign-compare -DNDEBUG -g -O3 -Wall    
-std=c11 -Wextra -Wno-unused-parameter -Wno-missing-field-initializers -Wstrict-prototypes 
-Werror=implicit-function-declaration -fvisibility=hidden  -I./Include/internal  
-I. -I./Include    -DPy_BUILD_CORE -o Programs/python.o ./Programs/python.c

...略...

LC_ALL=C sed -e 's,\$(\([A-Za-z0-9_]*\)),\$\{\1\},g' < Misc/python-config.sh >python-config
The following modules are *disabled* in configure script:
_sqlite3

The necessary bits to build these optional modules were not found:
_dbm                  _gdbm                 nis
To find the necessary bits, look in configure.ac and config.log.

Checked 111 modules (31 built-in, 75 shared, 1 n/a on linux-x86_64, 1 disabled, 3 missing, 0 failed on import)

[oracle@localhost python]$ make altinstall

Creating directory /home/oracle/python/bin
Creating directory /home/oracle/python/lib

...略...

The necessary bits to build these optional modules were not found:
_dbm                  _gdbm                 nis
To find the necessary bits, look in configure.ac and config.log.

Checked 111 modules (31 built-in, 75 shared, 1 n/a on linux-x86_64, 1 disabled, 3 missing, 0 failed on import)
Creating directory /home/oracle/python/lib/python3.12
Creating directory /home/oracle/python/lib/python3.12/asyncio

...略...

Looking in links: /tmp/tmp9k7fgbr3
Processing /tmp/tmp9k7fgbr3/pip-23.2.1-py3-none-any.whl
Installing collected packages: pip
  WARNING: The script pip3.12 is installed in '/home/oracle/python/bin' which is not on PATH.
  Consider adding this directory to PATH or, if you prefer to suppress this warning, use --no-warn-script-location.
Successfully installed pip-23.2.1

ここまでマニュアルの通り。想定外のエラーもなく順調ですね〜

変数PYTHONHOME、PATHおよび LD_LIBRARY_PATHを設定〜python3およびpip3のシンボリックリンクの作成など

[oracle@localhost python]$ export PYTHONHOME=$HOME/python
[oracle@localhost python]$ export PATH=$PYTHONHOME/bin:$PATH
[oracle@localhost python]$ export LD_LIBRARY_PATH=$PYTHONHOME/lib:$LD_LIBRARY_PATH
[oracle@localhost python]$ cd $HOME/python/bin
[oracle@localhost bin]$ ln -s python3.12 python3
[oracle@localhost bin]$ ln -s pip3.12 pip3
[oracle@localhost bin]$ cd $HOME
[oracle@localhost ~]$ wget https://download.oracle.com/otn_software/linux/instantclient/2340000/instantclient-basic-linux.x64-23.4.0.24.05.zip

--2025-05-13 03:03:13--  https://download.oracle.com/otn_software/linux/instantclient/2340000/instantclient-basic-linux.x64-23.4.0.24.05.zip

...略...

HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 118377607 (113M) [application/zip]
`instantclient-basic-linux.x64-23.4.0.24.05.zip' に保存中

instantclient-basic-linux.x64-23.4.0.24.05.zip  100%[===========================>] 112.89M  5.83MB/s 時間 18s

2025-05-13 03:03:31 (6.38 MB/s) - `instantclient-basic-linux.x64-23.4.0.24.05.zip' へ保存完了 [118377607/118377607]

[oracle@localhost ~]$
[oracle@localhost ~]$ unzip instantclient-basic-linux.x64-23.4.0.24.05.zip
Archive:  instantclient-basic-linux.x64-23.4.0.24.05.zip
replace META-INF/MANIFEST.MF? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
  inflating: META-INF/MANIFEST.MF

...略...

  instantclient_23_4/libocci.so.21.1 -> libocci.so.23.1
  instantclient_23_4/libocci.so.22.1 -> libocci.so.23.1
[oracle@localhost ~]$
[oracle@localhost ~]$ export LD_LIBRARY_PATH=$HOME/instantclient_23_4:$LD_LIBRARY_PATH

.bashrcにも以下環境変数を追加しておきます

export PYTHONHOME=$HOME/python
export PATH=$PYTHONHOME/bin:$PATH
export LD_LIBRARY_PATH=$PYTHONHOME/lib:$LD_LIBRARY_PATH

[oracle@localhost ~]$ vi .bashrc
[oracle@localhost ~]$ cat .bashrc
# .bashrc

# Source global definitions
if [ -f /etc/bashrc ]; then
	. /etc/bashrc
fi

...略...

export TWO_TASK=FREEPDB1
fi


# Environment variables for Python

export PYTHONHOME=$HOME/python
export PATH=$PYTHONHOME/bin:$PATH
export LD_LIBRARY_PATH=$PYTHONHOME/lib:$LD_LIBRARY_PATH

# Note: If Python is used to load models to the database, set this environment variable for the Oracle Instant Client.

export LD_LIBRARY_PATH=$HOME/instantclient_23_4:$LD_LIBRARY_PATH

requirements.txtの作成

[oracle@localhost ~]$ vi requirements.txt
[oracle@localhost ~]$ cat requirements.txt
--extra-index-url https://download.pytorch.org/whl/cpu
pandas==2.1.1
setuptools==68.0.0
scipy==1.12.0
matplotlib==3.8.4
oracledb==2.2.0
scikit-learn==1.4.1post1
numpy==1.26.4
onnxruntime==1.17.0
onnxruntime-extensions==0.10.1
onnx==1.16.0
torch==2.2.0+cpu
transformers==4.38.1
sentencepiece==0.2.0

pip3のアップグレード

[oracle@localhost ~]$ pip3 install --upgrade pip

Requirement already satisfied: pip in ./python/lib/python3.12/site-packages (23.2.1)
Collecting pip
  Obtaining dependency information for pip
    from https://files.pythonhosted.org/packages/29/a2/d40fb2460e883eca5199c62cfc2463fd261f760556ae6290f88488c362c0/pip-25.1.1-py3-none-any.whl.metadata
  Downloading pip-25.1.1-py3-none-any.whl.metadata (3.6 kB)
Downloading pip-25.1.1-py3-none-any.whl (1.8 MB)
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 1.8/1.8 MB 6.3 MB/s eta 0:00:00
Installing collected packages: pip
  Attempting uninstall: pip
    Found existing installation: pip 23.2.1
    Uninstalling pip-23.2.1:
      Successfully uninstalled pip-23.2.1
Successfully installed pip-25.1.1

[oracle@localhost ~]$ pip3 install -r requirements.txt

Looking in indexes: https://pypi.org/simple, https://download.pytorch.org/whl/cpu
Collecting pandas==2.1.1 (from -r requirements.txt (line 2))
  Downloading pandas-2.1.1-cp312-cp312-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata (18 kB)
Collecting setuptools==68.0.0 (from -r requirements.txt (line 3))

...略...

Successfully installed MarkupSafe-3.0.2 certifi-2025.4.26 cffi-1.17.1 charset-normalizer-3.4.2 coloredlogs-15.0.1 contourpy-1.3.2 
cryptography-44.0.3 cycler-0.12.1 filelock-3.18.0 flatbuffers-25.2.10 fonttools-4.58.0 fsspec-2025.3.2 hf-xet-1.1.1 huggingface-hub-0.31.1 
humanfriendly-10.0 idna-3.10 jinja2-3.1.6 joblib-1.5.0 kiwisolver-1.4.8 matplotlib-3.8.4 mpmath-1.3.0 networkx-3.4.2 numpy-1.26.4 onnx-1.16.0 
onnxruntime-1.17.0 onnxruntime-extensions-0.10.1 oracledb-2.2.0 packaging-25.0 pandas-2.1.1 pillow-11.2.1 protobuf-6.30.2 pycparser-2.22 
pyparsing-3.2.3 python-dateutil-2.9.0.post0 pytz-2025.2 pyyaml-6.0.2 regex-2024.11.6 requests-2.32.3 safetensors-0.5.3 scikit-learn-1.4.1.post1 
scipy-1.12.0 sentencepiece-0.2.0 setuptools-68.0.0 six-1.17.0 sympy-1.14.0 threadpoolctl-3.6.0 tokenizers-0.15.2 torch-2.2.0+cpu 
tqdm-4.67.1 transformers-4.38.1 typing-extensions-4.13.2 tzdata-2025.2 urllib3-2.4.0

あ”！
oml-2.1をインストールしてしまっていたので、oml-2.0をインストールしなおし. (oml-2.1でやってもよかったけどw

Oracle Machine Learning for Python Downloads
https://www.oracle.com/database/technologies/oml4py-downloads.html

から、2.0をダウンロード（oml4py-client-linux-x86_64-2.0.zip）して、と。。。。

ということでreinstall

[oracle@localhost ~]$ unzip oml4py-client-linux-x86_64-2.0.zip
Archive:  oml4py-client-linux-x86_64-2.0.zip
  inflating: client/client.pl
  inflating: client/OML4PInstallShared.pm
  inflating: client/oml-2.0-cp312-cp312-linux_x86_64.whl
 extracting: client/oml4py.ver

[oracle@localhost ~]$ pip3 install client/oml-2.0-cp312-cp312-linux_x86_64.whl
Processing ./client/oml-2.0-cp312-cp312-linux_x86_64.whl
Requirement already satisfied: numpy>=1.26.4 in ./python/lib/python3.12/site-packages (from oml==2.0) (2.2.5)

...略...

Requirement already satisfied: six>=1.5 in ./python/lib/python3.12/site-packages (from python-dateutil>=2.7->matplotlib>=3.7.2->oml==2.0) (1.17.0)
Requirement already satisfied: joblib>=1.2.0 in ./python/lib/python3.12/site-packages (from scikit-learn>=1.2.1->oml==2.0) (1.5.0)
Requirement already satisfied: threadpoolctl>=3.1.0 in ./python/lib/python3.12/site-packages (from scikit-learn>=1.2.1->oml==2.0) (3.6.0)
Installing collected packages: oml
  Attempting uninstall: oml
    Found existing installation: oml 2.1
    Uninstalling oml-2.1:
      Successfully uninstalled oml-2.1
Successfully installed oml-2.0

なにか怒られてるな。なぜだ。。。。numpy 1.xが必要っぽい。。。

[oracle@localhost ~]$ python3
Python 3.12.0 (main, May 13 2025, 02:54:53) [GCC 8.5.0 20210514 (Red Hat 8.5.0-20.0.3)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>>
>>> import oml

A module that was compiled using NumPy 1.x cannot be run in
NumPy 2.2.5 as it may crash. To support both 1.x and 2.x
versions of NumPy, modules must be compiled with NumPy 2.0.
Some module may need to rebuild instead e.g. with 'pybind11>=2.12'.

If you are a user of the module, the easiest solution will be to
downgrade to 'numpy<2' or try to upgrade the affected module.
We expect that some modules will need time to support NumPy 2.

Traceback (most recent call last):  File "", line 1, in 
  File "/home/oracle/python/lib/python3.12/site-packages/oml/__init__.py", line 77, in 
    from oml.utils import *
  File "/home/oracle/python/lib/python3.12/site-packages/oml/utils/__init__.py", line 23, in 
    from .embeddings import EmbeddingModelConfig,EmbeddingModel
  File "/home/oracle/python/lib/python3.12/site-packages/oml/utils/_pipeline/__init__.py", line 22, in 
    from .PipelineBuilder import PipelineBuilder

...略...

/home/oracle/python/lib/python3.12/site-packages/torch/nn/modules/transformer.py:20: UserWarning: 
Failed to initialize NumPy: _ARRAY_API not found (Triggered internally at ../torch/csrc/utils/tensor_numpy.cpp:84.)
  device: torch.device = torch.device(torch._C._get_default_device()),  # torch.device('cpu'),

A module that was compiled using NumPy 1.x cannot be run in
NumPy 2.2.5 as it may crash. To support both 1.x and 2.x
versions of NumPy, modules must be compiled with NumPy 2.0.
Some module may need to rebuild instead e.g. with 'pybind11>=2.12'.

If you are a user of the module, the easiest solution will be to
downgrade to 'numpy<2' or try to upgrade the affected module.
We expect that some modules will need time to support NumPy 2.

Traceback (most recent call last):  File "", line 1, in 
  File "/home/oracle/python/lib/python3.12/site-packages/oml/__init__.py", line 77, in 
    from oml.utils import *

    ...略...

    from onnxruntime.capi._pybind_state import ExecutionMode  # noqa: F401
  File "/home/oracle/python/lib/python3.12/site-packages/onnxruntime/capi/_pybind_state.py", line 32, in 
    from .onnxruntime_pybind11_state import *  # noqa
AttributeError: _ARRAY_API not found
/home/oracle/python/lib/python3.12/site-packages/oml/__init__.py:80: UserWarning: oml.utils import failed
  warn('oml.utils import failed')
>>>

ということで、 numpy 1.x にする。。。うまくいった

[oracle@localhost ~]$ pip3 install --upgrade numpy==1.26.4
Collecting numpy==1.26.4
  Using cached numpy-1.26.4-cp312-cp312-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata (61 kB)
Using cached numpy-1.26.4-cp312-cp312-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (18.0 MB)
Installing collected packages: numpy
  Attempting uninstall: numpy
    Found existing installation: numpy 2.2.5
    Uninstalling numpy-2.2.5:
      Successfully uninstalled numpy-2.2.5
Successfully installed numpy-1.26.4

やっふーーーーーーーー！

[oracle@localhost ~]$ python3
Python 3.12.0 (main, May 13 2025, 02:54:53) [GCC 8.5.0 20210514 (Red Hat 8.5.0-20.0.3)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>>
>>>
>>> import oml
>>>
>>> from oml.utils import EmbeddingModel, EmbeddingModelConfig
>>> EmbeddingModelConfig.show_preconfigured()
['sentence-transformers/all-mpnet-base-v2', 'sentence-transformers/all-MiniLM-L6-v2'
 , 'sentence-transformers/multi-qa-MiniLM-L6-cos-v1', 'ProsusAI/finbert'
 ,'medicalai/ClinicalBERT', 'sentence-transformers/distiluse-base-multilingual-cased-v2'
 , 'sentence-transformers/all-MiniLM-L12-v2', 'BAAI/bge-small-en-v1.5'
 , 'BAAI/bge-base-en-v1.5', 'taylorAI/bge-micro-v2', 'intfloat/e5-small-v2', 'intfloat/e5-base-v2'
 , 'prajjwal1/bert-tiny', 'thenlper/gte-base'
 , 'thenlper/gte-small', 'TaylorAI/gte-tiny', 'infgrad/stella-base-en-v2'
 , 'sentence-transformers/paraphrase-multilingual-mpnet-base-v2'
 , 'intfloat/multilingual-e5-base', 'intfloat/multilingual-e5-small'
 , 'sentence-transformers/stsb-xlm-r-multilingual']
>>>

DBMS_VECTOR.LOAD_ONNX_MODELを使用してデータベースに手動でアップロード可能な、ONNXファイルを生成
"事前構成済の埋込みモデルをローカル・ファイルにエクスポートします。oml.utilsからEmbeddingModelをインポートします。これにより、ONNX形式モデルがローカル・ファイル・システムにエクスポートされます。"

ディレクトリオプジェクト向けのディレクトリを先に作成し、そのディレクトリで行うと便利ですね。

[oracle@localhost ~]$ cd work4vector/
[oracle@localhost work4vector]$ pwd
/home/oracle/work4vector
[oracle@localhost work4vector]$ python3
Python 3.12.0 (main, May 13 2025, 02:54:53) [GCC 8.5.0 20210514 (Red Hat 8.5.0-20.0.3)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> from oml.utils import EmbeddingModel
>>> em = EmbeddingModel(model_name="sentence-transformers/all-MiniLM-L6-v2")
>>> em.export2file("all-MiniLM-L6-v2", output_dir=".")
/home/oracle/python/lib/python3.12/site-packages/huggingface_hub/file_download.py:943: FutureWarning: `resume_download` is deprecated 
and will be removed in version 1.0.0. Downloads always resume when possible. If you want to force a new download, use `force_download=True`.
  warnings.warn(
tokenizer_config.json: 100%|████████████████████████████████████████████████| 350/350 [00:00<00:00, 821kB/s]
vocab.txt: 100%|████████████████████████████████████████████████████████████| 232k/232k [00:00<00:00, 565kB/s]
special_tokens_map.json: 100%|██████████████████████████████████████████████| 112/112 [00:00<00:00, 463kB/s]
tokenizer.json: 100%|███████████████████████████████████████████████████████| 466k/466k [00:00<00:00, 1.47MB/s]
config.json: 100%|██████████████████████████████████████████████████████████| 612/612 [00:00<00:00, 3.88MB/s]
model.safetensors: 100%|████████████████████████████████████████████████████| 90.9M/90.9M [00:13<00:00, 6.72MB/s]
>>>

[oracle@localhost work4vector]$ ll all-MiniLM-L6-v2.onnx
-rw-rw-r--. 1 oracle oracle 90621438  5月 13 05:47 all-MiniLM-L6-v2.onnx

[oracle@localhost work4vector]$ sqlplus sys@localhost:1521/freepdb1 as sysdba

...略...

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
に接続されました。
SYS@localhost:1521/freepdb1> create directory onnx_dir as '/home/oracle/work4vector';

ディレクトリが作成されました。

経過: 00:00:00.08
SYS@localhost:1521/freepdb1> !pwd
/home/oracle/work4vector

SYS@localhost:1521/freepdb1> grant read,write on directory onnx_dir to scott;

権限付与が成功しました。

経過: 00:00:00.05
SYS@localhost:1521/freepdb1> grant create mining model to scott;

権限付与が成功しました。

経過: 00:00:00.02

DBMS_VECTOR.LOAD_ONNX_MODELプロシージャを使用して、OMLユーザー・スキーマにモデルをロードする

コードは以下

BEGIN
   DBMS_VECTOR.LOAD_ONNX_MODEL(
     directory => 'ONNX_DIR',
     file_name => 'all-MiniLM-L6-v2.onnx',
     model_name => 'ALL_MINILM_L6');
END;
/

SCOTT@localhost:1521/freepdb1> l
  1  BEGIN
  2     DBMS_VECTOR.LOAD_ONNX_MODEL(
  3       directory => 'ONNX_DIR',
  4       file_name => 'all-MiniLM-L6-v2.onnx',
  5       model_name => 'ALL_MINILM_L6');
  6* END;
SCOTT@localhost:1521/freepdb1> /

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:08.93

SQLを使用してモデルを確認

SQL文は以下

SELECT
  model_name
  , algorithm
  , mining_function
FROM
  user_mining_models
WHERE
  model_name = 'ALL_MINILM_L6'
/

SCOTT@localhost:1521/freepdb1> l
  1  SELECT
  2    model_name
  3    , algorithm
  4    , mining_function
  5  FROM
  6    user_mining_models
  7  WHERE
  8*   model_name = 'ALL_MINILM_L6'
SCOTT@localhost:1521/freepdb1> /

MODEL_NAME                     ALGORITHM                      MINING_FUNCTION
------------------------------ ------------------------------ ------------------------------
ALL_MINILM_L6                  ONNX                           EMBEDDING

経過: 00:00:00.01

ユーザーがアクセスできるモデルを確認するビューからも確認しておきます。

SQLはこんな感じ

SELECT
  view_name
  , view_type
FROM
  user_mining_model_views
WHERE
  model_name = 'ALL_MINILM_L6'
ORDER BY
  view_name
/

SCOTT@localhost:1521/freepdb1> l
  1  SELECT
  2    view_name
  3    , view_type
  4  FROM
  5    user_mining_model_views
  6  WHERE
  7    model_name = 'ALL_MINILM_L6'
  8  ORDER BY
  9*   view_name
SCOTT@localhost:1521/freepdb1> /

VIEW_NAME                      VIEW_TYPE
------------------------------ ------------------------------
DM$VJALL_MINILM_L6             ONNX Metadata Information
DM$VMALL_MINILM_L6             ONNX Model Information
DM$VPALL_MINILM_L6             ONNX Parsing Information

経過: 00:00:00.01

上記ビューを問い合わせモデルの情報を確認:)

SCOTT@localhost:1521/freepdb1> SELECT * FROM dm$vmall_minilm_l6;

NAME                           VALUE
------------------------------ -----------------------------------------------
Producer Name                  onnx.compose.merge_models
Graph Name                     tokenizer_main_graph
Graph Description              Graph combining tokenizer and main_graph
                               tokenizer
                               main_graph
Version                        1
Input[0]                       input:string[?]
Output[0]                      embedding:float32[?,384]

6行が選択されました。

経過: 00:00:00.00

VECTOR_EMBEDDING SQLスコアリング関数を軽く試す！

SQL文はこんな感じ

SELECT VECTOR_EMBEDDING(ALL_MINILM_L6 USING 'RES' as DATA) AS embedding;

SCOTT@localhost:1521/freepdb1> r
  1* SELECT VECTOR_EMBEDDING(ALL_MINILM_L6 USING 'RES' as DATA) AS embedding

EMBEDDING
----------------------------------------------------------------------------------------------------
[-1.16423056E-001,1.54331746E-002,-4.69262414E-002,7.16730766E-003,3.50234732E-002,-4.02988419E-002,
.08232533E-002,4.99225073E-002,-1.86311249E-002,-2.62796488E-002,-3.2601878E-002,5.22731952E-002,-9.

...略...

-003,6.00763485E-002,1.91014066E-001,7.64457136E-002,1.46513591E-002,3.13854888E-002]


経過: 00:00:00.36

関数しか実行していないので面白い実行計画は現れませんが一応、確認だけw

SCOTT@localhost:1521/freepdb1> set autot trace exp stat
SCOTT@localhost:1521/freepdb1> r
  1* SELECT VECTOR_EMBEDDING(ALL_MINILM_L6 USING 'RES' as DATA) AS embedding

経過: 00:00:00.33

実行計画
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       2261  bytes sent via SQL*Net to client
        473  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

user_mining_models 、user_mining_model_attributesビューなんてのありますね。ふむふむ

SCOTT@localhost:1521/freepdb1> SELECT model_name, mining_function, algorithm, algorithm_type, model_size FROM user_mining_models;

MODEL_NAME                     MINING_FUNCTION                ALGORITHM                      ALGORITHM_ MODEL_SIZE
------------------------------ ------------------------------ ------------------------------ ---------- ----------
ALL_MINILM_L6                  EMBEDDING                      ONNX                           NATIVE       90621438

経過: 00:00:00.02

SCOTT@localhost:1521/freepdb1> l
  1  SELECT model_name, attribute_name, attribute_type, data_type, vector_info
  2*      FROM   user_mining_model_attributes
SCOTT@localhost:1521/freepdb1> /

MODEL_NAME                     ATTRIBUTE_NAME                 ATTRIBUTE_TY DATA_TYPE                      VECTOR_INFO
------------------------------ ------------------------------ ------------ ------------------------------ ------------------------------
ALL_MINILM_L6                  ORA$ONNXTARGET                 VECTOR       VECTOR                         VECTOR(384,FLOAT32)
ALL_MINILM_L6                  DATA                           TEXT         VARCHAR2

経過: 00:00:00.01

ONNX形式の埋込みモデルを使用して、ユーザーの入力テキスト文字列「hello」をベクトルに変換してみる。ベクトル眺めても意味わからないけども、とりあえず、できてるみたいw

SCOTT@localhost:1521/freepdb1> col EMBEDDING for a200
SCOTT@localhost:1521/freepdb1> SELECT TO_VECTOR(VECTOR_EMBEDDING(all_minilm_l6 USING 'hello' as data)) AS EMBEDDING;

EMBEDDING
-----------------------------------------------------------------------------------------------
[-6.27717897E-002,5.49588911E-002,5.21648414E-002,8.57899487E-002,-8.27489197E-002,-7.45729804E-
002,6.85546845E-002,1.83963589E-002,-8.20114315E-002,-3.73847559E-002,1.21248914E-002,3.51829384
E-003,-4.13423125E-003,-4.37844135E-002,2.18073577E-002,-5.10276016E-003,1.95467062E-002,-4.2348

...略...

-4.67414372E-002,-1.34112127E-002,6.51347339E-002,5.09059429E-002,5.1483497E-002,7.09215924E-003]


経過: 00:00:00.67

ということで、準備完了（このモデルだと日本語は対応してないみたいだけど、ひとまず、軽く遊べる小さなOracle Database 23ai Free on VirtualBOXの環境の準備完了！

SQL文もここまでくると、何やっているのか理解しながら進めないと、迷子になりそうな気がしないでもないw

次回へつづく。

投稿日時 2025年6月 3日 (火) 00時00分 Oracle, PL/SQL, Linux, SQL, VirtualBox, Oracle Database 23c Free Developer Release, VECTOR, AI | 固定リンク | 0 | コメント (0)

Mac De Oracle

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