外部表を使用したセマンティック類似検索の実行
外部表をセマンティック類似検索のデータ・セットとして使用する方法を示すSQL計画の例を参照してください
select id
の実行計画の例を次に示します。ext_table_3
から埋込みを行い、order by vector_distance('[1,1]', embedding, cosine)
を使用して、ターゲット精度が90%のデータの最初の3行のみを近似的に返します:
SQL> select * from table(dbms_xplan.display('plan_table', null, 'advanced predicate'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1784440045
--------------------------------------------------------------------------------
---------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Co
st (%CPU)| Time |
--------------------------------------------------------------------------------
---------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 48945 | | 1
466K (2)| 00:00:58 |
|* 1 | COUNT STOPKEY | | | | |
| |
| 2 | VIEW | | 102K| 1588M| | 1
466K (2)| 00:00:58 |
|* 3 | SORT ORDER BY STOPKEY | | 102K| 1589M| 798M| 1
466K (2)| 00:00:58 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 4 | EXTERNAL TABLE ACCESS FULL| EXT_TABLE_3 | 102K| 1589M| |
362 (7)| 00:00:01 |
--------------------------------------------------------------------------------
---------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - SEL$2
2 - SEL$1 / "from$_subquery$_002"@"SEL$2"
3 - SEL$1
4 - SEL$1 / "EXT_TABLE_3"@"SEL$1"
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "EXT_TABLE_3"@"SEL$1")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
NO_ACCESS(@"SEL$2" "from$_subquery$_002"@"SEL$2")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_fix_control' '6670551:0')
OPT_PARAM('_optimizer_cost_model' 'fixed')
DB_VERSION('26.1.0')
OPTIMIZER_FEATURES_ENABLE('26.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=3)
3 - filter(ROWNUM<=3)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "from$_subquery$_002"."ID"[NUMBER,22], "from$_subquery$_002"."EMBEDDING"[
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
VECTOR,32600]
2 - "from$_subquery$_002"."ID"[NUMBER,22], "from$_subquery$_002"."EMBEDDING"[
VECTOR,32600]
3 - (#keys=1) VECTOR_DISTANCE(VECTOR('[1,1]', *, *, * /*+ USEBLOBPCW_QVCGMD
*/ ),
"EMBEDDING" /*+ LOB_BY_VALUE */ , COSINE)[BINARY_DOUBLE,8], "ID"[NUMBER,2
2], "EMBEDDING" /*+
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
LOB_BY_VALUE */ [VECTOR,32600]
4 - "ID"[NUMBER,22], "EMBEDDING" /*+ LOB_BY_VALUE */ [VECTOR,32600],
VECTOR_DISTANCE(VECTOR('[1,1]', *, *, * /*+ USEBLOBPCW_QVCGMD */ ), "EMB
EDDING" /*+
LOB_BY_VALUE */ , COSINE)[BINARY_DOUBLE,8]
Query Block Registry:
---------------------
SEL$1 (PARSER) [FINAL]
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SEL$2 (PARSER) [FINAL]
+
親トピック: 外部表のベクトル