Oracle SQLマクロ表: パラメータのあるビュー! (2025/05/01)

Oracle SQLマクロ表: パラメータのあるビュー! (2025/05/01)

https://medium.com/oracledevs/oracle-sql-macro-tables-finally-views-with-parameters-bc78286ab010

投稿者:vijay balebail

View with parameters

Oracleでは、強力でエレガントな機能であるSQLマクロが導入されました。この記事では、インライン・ビューのように動作するがスマートなパラメータ化されたSQLブロックを作成できるSQLマクロ表のみに焦点を当てます。


ビューにパラメータを渡したいと思ったことがあるなら、それはあなたの解決策です。


📌SQLマクロ表は「パラメータ付きビュー」と考えてください。


通常のSQLと同様に解析および最適化された問合せが返されます。ただし、複雑なPL/SQL構造に依存することなく、引数を渡すことができます。



SQLマクロテーブルで解決する問題


次のことを行います。


  • 動的入力(検索語やしきい値など)の埋込み
  • SQLロジックでの入力の使用(フィルタリングや順序付けなど)
  • 多数の問合せまたはツールでロジックを再利用


ビューを本能的に使用しようとする場合もありますが、ビューはパラメータを使用できません。回避策は次のとおりです。


  • パイプラインPL/SQLファンクションの記述
  • カスタムSQLタイプの定義(オブジェクトと表)
  • ループとPIPE ROWでロジックをラップ


しかし、これはオプティマイザーフレンドリーではありません。


SQLマクロテーブルは、これをクリーンに解決します。



例: Top-N Vector類似性検索(Oracle 23ai)


OracleのVECTOR_EMBEDDING関数を使用して質問を埋め込み、ベクトル・ストアから最も類似した上位N件の結果を取得するとします。


ベクトル検索を行うSQL問合せの例を次に示します。このクエリは、sqlplus から実行できます。vector_embdding関数を使用して質問を埋め込み、vector_distanceを使用してベクトル検索を実行することに注意してください。


WITH a AS (
SELECT VECTOR_EMBEDDING(ALL_MINILM_L6V2MODEL USING 'Help troubleshoot SSL in windows'AS data) AS embed
FROM dual
)
SELECT embed_data
FROM troubleshoot_servicenow, a
WHERE doc_id = 10
ORDER BY VECTOR_DISTANCE(embed_vector, a.embed, COSINE)
FETCH FIRST 10 ROWS ONLY


SQLマクロを使用すると、純粋なSQLでこれを実行し、毎回検索質問を渡すことができます。


CREATE OR REPLACE FUNCTION 
get_topn_embeddings (
p_question VARCHAR2,
p_doc_id NUMBER,
p_topn NUMBER
) RETURN VARCHAR2
SQL_MACRO(TABLE)
IS
BEGIN
RETURN q'[
WITH a AS (
SELECT VECTOR_EMBEDDING(ALL_MINILM_L6V2MODEL USING p_question AS data) AS embed
FROM dual
)
SELECT embed_data
FROM troubleshoot_servicenow, a
WHERE doc_id = p_doc_id
ORDER BY VECTOR_DISTANCE(embed_vector, a.embed, COSINE)
FETCH FIRST p_topn ROWS ONLY
]';
END;
/

SELECT * 
FROM get_topn_embeddings('troubleshoot SSL issues in windows', 7, 5);


バックグラウンドで、Oracleは、WITH a AS (...)ブロック全体を書き込むかのように、コンパイル時に戻されたSQLをインライン化します。結果は、完全にオプティマイザで可視化され、構成可能です。


はい- Oracleはそれらを「パラメータ化されたビュー」とは呼びませんが、まさにそのように動作します。


SQLマクロ表= ビュー+パラメータ+オプティマイザ統合



How We Did This Before (The Old Way)シングル


マクロの前に、カスタムSQL型、PL/SQLパイプライン・ファンクションを作成し、プロシージャ・ループを記述する必要がありました。


古い学校のアプローチを使用して、同じget_topn_embeddingsロジックがどのように表示されるかを示します。


1. タイプの作成:


書き込む必要があるすべての列を含むオブジェクト・タイプを作成する必要があります。問合せを変更して列を追加する場合は、オブジェクト・タイプも変更する必要があります。


CREATE OR REPLACE TYPE embed_row_type AS OBJECT (
embed_data CLOB
);
/
CREATE OR REPLACE TYPE embed_table_type AS TABLE OF embed_row_type;
/


2. 関数を作成


CREATE OR REPLACE FUNCTION get_topn_embeddings_pipe (
p_question VARCHAR2,
p_doc_id NUMBER,
p_topn NUMBER
) RETURN embed_table_type PIPELINED
IS
v_embed VECTOR;
BEGIN
SELECT TO_VECTOR(VECTOR_EMBEDDING(ALL_MINILM_L6V2MODEL USING p_question AS data))
INTO v_embed
FROM dual;

FOR rec IN (
SELECT embed_data
FROM troubleshoot_servicenow
WHERE doc_id = p_doc_id
ORDER BY VECTOR_DISTANCE(embed_vector, v_embed, COSINE)
FETCH FIRST p_topn ROWS ONLY
) LOOP
PIPE ROW(embed_row_type(rec.embed_data));
END LOOP;

RETURN;
END;
/


3. 検索


SELECT * 
FROM TABLE(get_topn_embeddings('login fails', 7, 5));


それは動作しますが、あなたは価格を支払います:


  • ボイラープレート・タイプ
  • オプティマイザが関数の内部に表示されない
  • 下流の結合/フィルターが困難


ファイナル思考


Oracle SQLマクロ表は、過小評価されていますが、画期的な機能です。これにより、オプティマイザに対して完全に透過的な状態を維持しながら、パラメータのあるビューのように動作するモジュラSQLロジックを記述できます。


再利用可能なSQLを動的なものにするためにパイプライン関数を現在作成している場合は停止します。かわりにSQLマクロ表を試してください。あなたの質問はあなたに感謝します。


コメント

このブログの人気の投稿

Oracle Database 19cサポート・タイムラインの重要な更新 (2024/11/20)

Oracle GoldenGate 23aiでMicrosoft Fabricでのオープン・ミラーリングがサポートされるようになりました (2024/11/19)

Oracle Database Service for Azure(ODSA)とOracle Interconnect for Azureの比較 (2022/08/15)