はじめに
OracleDB で新たに導入された Vector型について、いくつか制約があるのでデモを交えて紹介します。
デモで使用するテーブルとデータ
SQL> info testtab1
TABLE: TESTTAB1
LAST ANALYZED:
ROWS :
SAMPLE SIZE :
INMEMORY :DISABLED
COMMENTS :
Columns
NAME DATA TYPE NULL DEFAULT COMMENTS
ID NUMBER Yes
VEC VECTOR(*,*,DENSE) Yes
SQL> info testtab2
TABLE: TESTTAB2
LAST ANALYZED:
ROWS :
SAMPLE SIZE :
INMEMORY :DISABLED
COMMENTS :
Columns
NAME DATA TYPE NULL DEFAULT COMMENTS
ID NUMBER Yes
VEC VECTOR(*,*,DENSE) Yes
SQL> select * from testtab1;
ID VEC
_____ ______________________
1 [2.0E+000,8.0E+000]
2 [8.0E+000,0]
3 [6.0E+000,6.0E+000]
4 [0,9.0E+000]
5 [3.0E+000,1.0E+000]
6 [8.0E+000,0]
7 [5.0E+000,7.0E+000]
8 [9.0E+000,2.0E+000]
9 [1.0E+000,9.0E+000]
9 rows selected.
SQL> select * from testtab2;
ID VEC
_____ ______________________
1 [7.0E+000,5.0E+000]
2 [0,0]
3 [9.0E+000,6.0E+000]
4 [2.0E+000,4.0E+000]
5 [8.0E+000,0]
6 [3.0E+000,8.0E+000]
7 [5.0E+000,1.0E+000]
8 [8.0E+000,9.0E+000]
9 [9.0E+000,7.0E+000]
9 rows selected.
SQL>
比較演算子で直接比較することは不可
等号、不等号といった比較演算子でベクトルデータを比較することはできません。(ORA-22848: cannot use VECTOR type as comparison key)
SQL> --等号
SQL> select * from testtab1 where vec = to_vector('[2, 1]', 2, float32);
Error starting at line : 1 in command -
select * from testtab1 where vec = to_vector('[2, 1]', 2, float32)
Error at Command Line : 1 Column : 30
Error report -
SQL Error: ORA-22848: cannot use VECTOR type as comparison key
https://docs.oracle.com/error-help/db/ora-22848/22848. 00000 - "cannot use %s type as comparison key"
*Cause: An attempt was made to perform a comparison against a type
without comparison support.
*Action: Use only types with comparison support.
More Details :
https://docs.oracle.com/error-help/db/ora-22848/
SQL>
SQL>
SQL> --不等号
SQL> select * from testtab1 where vec > to_vector('[2, 1]', 2, float32);
Error starting at line : 1 in command -
select * from testtab1 where vec > to_vector('[2, 1]', 2, float32)
Error at Command Line : 1 Column : 30
Error report -
SQL Error: ORA-22848: cannot use VECTOR type as comparison key
https://docs.oracle.com/error-help/db/ora-22848/22848. 00000 - "cannot use %s type as comparison key"
*Cause: An attempt was made to perform a comparison against a type
without comparison support.
*Action: Use only types with comparison support.
More Details :
https://docs.oracle.com/error-help/db/ora-22848/
DISTINCT 不可
DISTINCTで重複排除することはできません。(ORA-22848: cannot use VECTOR type as comparison key)
どうしても重複排除したい場合は、vector_serializeで文字列にキャストすれば可能です。
SQL> select distinct vec from testtab1;
Error starting at line : 1 in command -
select distinct vec from testtab1
Error at Command Line : 1 Column : 17
Error report -
SQL Error: ORA-22848: cannot use VECTOR type as comparison key
https://docs.oracle.com/error-help/db/ora-22848/22848. 00000 - "cannot use %s type as comparison key"
*Cause: An attempt was made to perform a comparison against a type
without comparison support.
*Action: Use only types with comparison support.
More Details :
https://docs.oracle.com/error-help/db/ora-22848/
SQL>
SQL> --回避策
SQL> select distinct vector_serialize(vec) from testtab1;
VECTOR_SERIALIZE(VEC)
________________________
[9.0E+000,2.0E+000]
[8.0E+000,0]
[3.0E+000,1.0E+000]
[2.0E+000,8.0E+000]
[0,9.0E+000]
[5.0E+000,7.0E+000]
[1.0E+000,9.0E+000]
[6.0E+000,6.0E+000]
8 rows selected.
SQL>
GROUP BY 不可
GROUP BYでグルーピングすることはできません。(ORA-22848: cannot use VECTOR type as comparison key )
こちらも、どうしてもグルーピングしたい場合は、vector_serializeで文字列にキャストすれば可能です。
SQL> select count(*) from testtab1 group by vec;
Error starting at line : 1 in command -
select count(*) from testtab1 group by vec
Error at Command Line : 1 Column : 40
Error report -
SQL Error: ORA-22848: cannot use VECTOR type as comparison key
https://docs.oracle.com/error-help/db/ora-22848/22848. 00000 - "cannot use %s type as comparison key"
*Cause: An attempt was made to perform a comparison against a type
without comparison support.
*Action: Use only types with comparison support.
More Details :
https://docs.oracle.com/error-help/db/ora-22848/
SQL>
SQL> --回避策
SQL> select count(*) from testtab1 group by vector_serialize(vec);
COUNT(*)
___________
1
2
1
1
1
1
1
1
8 rows selected.
SQL>
結合キーに指定不可
結合キーに指定することはできません。(ORA-22848: cannot use VECTOR type as comparison key)
例のごとく、どうしても結合キーに指定したい場合は、vector_serializeで文字列にキャストすれば可能です。
SQL> select testtab1.*, testtab2.* from testtab1 join testtab2 on testtab1.vec = testtab2.vec;
Error starting at line : 1 in command -
select testtab1.*, testtab2.* from testtab1 join testtab2 on testtab1.vec = testtab2.vec
Error at Command Line : 1 Column : 62
Error report -
SQL Error: ORA-22848: cannot use VECTOR type as comparison key
https://docs.oracle.com/error-help/db/ora-22848/22848. 00000 - "cannot use %s type as comparison key"
*Cause: An attempt was made to perform a comparison against a type
without comparison support.
*Action: Use only types with comparison support.
More Details :
https://docs.oracle.com/error-help/db/ora-22848/
SQL>
SQL>
SQL> --回避策
SQL> select testtab1.*, testtab2.* from testtab1 join testtab2 on vector_serialize(testtab1.vec) = vector_serialize(testtab2.vec);
ID VEC ID VEC
_____ _______________ _____ _______________
2 [8.0E+000,0] 5 [8.0E+000,0]
6 [8.0E+000,0] 5 [8.0E+000,0]
SQL>
主キーや一意制約の設定、通常の B-Tree索引やビットマップ索引の作成も不可です。
(ORA-02329: Column of data type VECTOR cannot be unique or a primary key., ORA-02327: cannot create index on expression with data type VECTOR)
SQL> --主キー
SQL> alter table testtab1 add primary key (vec);
Error starting at line : 1 in command -
alter table testtab1 add primary key (vec)
Error report -
ORA-02329: Column of data type VECTOR cannot be unique or a primary key.
https://docs.oracle.com/error-help/db/ora-02329/
02329. 00000 - "Column of data type %s cannot be unique or a primary key."
*Cause: An attempt was made to place a UNIQUE or a PRIMARY KEY constraint
on a column of data type VARRAY, nested table, object, LOB, FILE,
or REF.
*Action: Change the column data type or remove the constraint. Then retry
the operation.
*Params: 1) data_type
SQL>
SQL>
SQL> --一意制約
SQL> alter table testtab1 add constraint test unique(vec);
Error starting at line : 1 in command -
alter table testtab1 add constraint test unique(vec)
Error report -
ORA-02329: Column of data type VECTOR cannot be unique or a primary key.
https://docs.oracle.com/error-help/db/ora-02329/
02329. 00000 - "Column of data type %s cannot be unique or a primary key."
*Cause: An attempt was made to place a UNIQUE or a PRIMARY KEY constraint
on a column of data type VARRAY, nested table, object, LOB, FILE,
or REF.
*Action: Change the column data type or remove the constraint. Then retry
the operation.
*Params: 1) data_type
SQL>
SQL> --B-Tree索引
SQL> create index t1_idx on testtab1(vec);
Error starting at line : 1 in command -
create index t1_idx on testtab1(vec)
Error report -
ORA-02327: cannot create index on expression with data type VECTOR
https://docs.oracle.com/error-help/db/ora-02327/
02327. 00000 - "cannot create index on expression with data type %s"
*Cause: An attempt was made to create an index on an expression with
an unsupported data type: VARRAY, nested table, ADT,
LOB, or REF.
*Action: Change the column data type or do not create the index on an
expression whose data type is one of VARRAY, nested table,
ADT, LOB, or REF.
*Params: 1) data_type
SQL>
SQL>
SQL> --ビットマップ索引
SQL> create bitmap index t1_idx on testtab1(vec);
Error starting at line : 1 in command -
create bitmap index t1_idx on testtab1(vec)
Error report -
ORA-02327: cannot create index on expression with data type VECTOR
https://docs.oracle.com/error-help/db/ora-02327/
02327. 00000 - "cannot create index on expression with data type %s"
*Cause: An attempt was made to create an index on an expression with
an unsupported data type: VARRAY, nested table, ADT,
LOB, or REF.
*Action: Change the column data type or do not create the index on an
expression whose data type is one of VARRAY, nested table,
ADT, LOB, or REF.
*Params: 1) data_type
SQL>
次元数の最大は 65,535次元
65,536次元以上のベクトルデータを格納しようとするとエラーになります。(ORA-51801: Invalid VECTOR type specification: Invalid dimension count ('65536'). )
現時点で最も次元数の多い Embeddingモデルは Qwen3-Embedding-8B の 4,096次元のようですので、通常困ることはない上限値かと思います。
Qwen3-Embedding-8B has the following features:
Model Type: Text Embedding
Supported Languages: 100+ Languages
Number of Paramaters: 8B
Context Length: 32k
Embedding Dimension: Up to 4096, supports user-defined output dimensions ranging from 32 to 4096
SQL> --65,535次元のデータ (問題なし)
SQL> INSERT /*+ APPEND */ INTO testtab1 (vec)
2 SELECT v.vec
3 FROM (
4 SELECT LEVEL AS rn
5 FROM dual
6 CONNECT BY LEVEL <= 1
7 ) t
8 CROSS APPLY (
9 SELECT TO_VECTOR(
10 JSON_ARRAYAGG(val ORDER BY dim RETURNING CLOB),
11 65535,
12 FLOAT32
13 ) AS vec
14 FROM (
15 SELECT LEVEL AS dim,
16 ROUND(DBMS_RANDOM.VALUE(0, 1) + 0 * t.rn, 6) AS val
17 FROM dual
18 CONNECT BY LEVEL <= 65535
19 )
20* ) v;
1 row inserted.
SQL>
SQL>
SQL> --65,536次元のデータ (エラー)
SQL> INSERT /*+ APPEND */ INTO testtab1 (vec)
2 SELECT v.vec
3 FROM (
4 SELECT LEVEL AS rn
5 FROM dual
6 CONNECT BY LEVEL <= 1
7 ) t
8 CROSS APPLY (
9 SELECT TO_VECTOR(
10 JSON_ARRAYAGG(val ORDER BY dim RETURNING CLOB),
11 65536,
12 FLOAT32
13 ) AS vec
14 FROM (
15 SELECT LEVEL AS dim,
16 ROUND(DBMS_RANDOM.VALUE(0, 1) + 0 * t.rn, 6) AS val
17 FROM dual
18 CONNECT BY LEVEL <= 65536
19 )
20* ) v;
Error starting at line : 1 in command -
INSERT /*+ APPEND */ INTO testtab1 (vec)
SELECT v.vec
FROM (
SELECT LEVEL AS rn
FROM dual
CONNECT BY LEVEL <= 1
) t
CROSS APPLY (
SELECT TO_VECTOR(
JSON_ARRAYAGG(val ORDER BY dim RETURNING CLOB),
65536,
FLOAT32
) AS vec
FROM (
SELECT LEVEL AS dim,
ROUND(DBMS_RANDOM.VALUE(0, 1) + 0 * t.rn, 6) AS val
FROM dual
CONNECT BY LEVEL <= 65536
)
) v
Error at Command Line : 11 Column : 12
Error report -
SQL Error: ORA-51801: Invalid VECTOR type specification: Invalid dimension count ('65536'). Valid values can either be * (i.e. flexible) or an integer between 1
and 65535.
https://docs.oracle.com/error-help/db/ora-51801/
More Details :
https://docs.oracle.com/error-help/db/ora-51801/
SQL>
SQL>