外部表を使用したセマンティック類似検索の実行

外部表をセマンティック類似検索のデータ・セットとして使用する方法を示す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]
+