Create Tables Using the VECTOR Data Type

You can declare a table's column as a VECTOR data type.

The following command shows a simple example:

CREATE TABLE my_vectors (id NUMBER, embedding VECTOR);

In this example, you don't have to specify the number of dimensions or their format, which are both optional. If you don't specify any of them, you can enter vectors of different dimensions with different formats. This is a simplification to help you get started with using vectors in Oracle Database.

Note:

Such vectors typically arise from different embedding models. Vectors from different models (providing a different semantic landscape) are not comparable for use in similarity search.

Here's a more complex example that imposes more constraints on what you can store:

CREATE TABLE my_vectors (id NUMBER, embedding VECTOR(768, INT8)) ;

In this example, each vector that is stored:

• Must have 768 dimensions, and
• Each dimension will be formatted as an INT8.

The number of dimensions must be strictly greater than zero with a maximum of 65535. If you attempt to use larger values, the following error is raised:

ORA-51811: Dimension count exceeded maximum supported value of 65535

The possible dimension formats are:

• INT8 (8-bit integers)
• FLOAT32 (32-bit IEEE floating-point numbers)
• FLOAT64 (64-bit IEEE floating-point numbers)

Oracle Database automatically casts the values as needed.

The following table guides you through the possible declaration format for a VECTOR data type:

Possible Declaration Format	Explanation
VECTOR	Vectors can have an arbitrary number of dimensions and formats.
VECTOR(*, *)	Vectors can have an arbitrary number of dimensions and formats. VECTOR and VECTOR(*,*) are equivalent.
VECTOR(number_of_dimensions, *)	Vectors must all have the specified number of dimensions or an error is thrown. Every vector will have its dimensions stored without format modification.
VECTOR(number_of_dimensions)	Vectors must all have the specified number of dimensions or an error is thrown. Every vector will have its dimensions stored without format modification. VECTOR(number_of_dimensions, *) and VECTOR(number_of_dimensions) are equivalent.
VECTOR(*, dimension_element_format)	Vectors can have an arbitrary number of dimensions, but their format will be up-converted or down-converted to the specified dimension_element_format (INT8, FLOAT32, or FLOAT64).

CREATE TABLE my_vect_tab (
     v1 VECTOR(3, FLOAT32),
     v2 VECTOR(2, FLOAT64),
     v3 VECTOR(1, INT8),
     v4 VECTOR(1, *),
     v5 VECTOR(*, FLOAT32),
     v6 VECTOR(*, *),
     v7 VECTOR
   );

Table created.

DESC my_vect_tab;
 Name                        Null?    Type
 --------------------------- -------- ----------------------------
 V1                                   VECTOR(3 , FLOAT32)
 V2                                   VECTOR(2 , FLOAT64)
 V3                                   VECTOR(1 , INT8)
 V4                                   VECTOR(1 , *)
 V5                                   VECTOR(* , FLOAT32)
 V6                                   VECTOR(* , *)
 V7                                   VECTOR(* , *)

The following SQL*Plus code example shows how the system interprets various vector definitions:

A vector can be NULL but its dimensions cannot (for example, you cannot have a VECTOR with a NULL dimension such as [1.1, NULL, 2.2]).

Note:

Vectors are internally stored as Securefile BLOBs and most popular embedding model vector sizes are between 1.5KB and 12KB in size. You can use the following formula to determine the size of your vectors on disk: number of vectors * number of dimensions * size of your vector dimension type (for example, a FLOAT32 is equivalent to BINARY_FLOAT and is 4 bytes in size).

Restrictions

You currently cannot define VECTOR columns in/as:

• External Tables
• IOTs (neither as Primary Key nor as non-Key column)
• Clusters/Cluster Tables
• Global Temp Tables
• (Sub)Partitioning Key
• Primary Key
• Foreign Key
• Unique Constraint
• Check Constraint
• Default Value
• Modify Column
• Manual Segment Space Management (MSSM) tablespace (only SYS user can create VECTORs as Basicfiles in MSSM tablespace)
• Continuous Query Notification (CQN) queries
• Non-vector indexes such as B-tree, Bitmap, Reverse Key, Text, Spatial indexes, etc

Oracle Database does not support the following SQL constructs with VECTOR columns:

• Distinct, Count Distinct
• Order By, Group By
• Join condition
• Comparison operators (e.g. >, <, =) etc