Artificial Intelligence Vectors and Semantic Search
Oracle Database 23ai introduces semantic search capabilities using Artificial Intelligence (AI) vector search. These capabilities include a new vector data type, vector indexes, and vector search SQL operators that allow the database to store semantic document content, images, and other unstructured data as vectors and then run fast performing similarity queries. The key innovation is that the database better understands user intent and the search context to find similar matches, rather than only find exact matches.
Building upon this capability, the Oracle database can use Generative AI constructs, such as Retrieval Augmented Generation (RAG) to combine large language models (LLMs) and private business data to respond to natural language questions. RAG provides higher accuracy and avoids exposing any of the private data to the LLM training data.
Vector Data Type
Starting with version 23.3.2, managed ODP.NET and ODP.NET Core support these new semantic search capabilities via a new native vector data type.
ODP.NET vector data types are flexible to use. They have one or more dimensions, up to 65,536 (64K) dimensions. Developers can define vectors with a fixed or variable number of dimensions. The numeric format of each dimension in a vector can be of the same numeric format or a different numeric format for each dimension:
| Vector Numeric Format | Description | Mapped .NET Type | Size | Example |
|---|---|---|---|---|
|
|
signed byte |
|
2 bytes |
|
|
|
32-bit floating point |
|
4 bytes |
|
|
|
64-bit floating point |
|
8 bytes |
|
For example, a one dimension INT8 vector would be Vector(1, INT8). A four dimension FLOAT32 vector would be Vector(4, FLOAT32). A variable dimension FLOAT64 vector would be Vector(*, FLOAT64).
To specify that each vector dimension can be either an INT8, FLOAT32, or FLOAT64 data type, use the * notation, such as Vector(4, *) to represent a four dimensional vector. You may specify a vector as Vector(*, *) to have a variable number of dimensions and variable data type vector.
ODP.NET can retrieve the database vector data type in .NET as any of the following:
| Data Type | ODP.NET or .NET Data Type | Description |
|---|---|---|
|
.NET numeric array |
.NET |
This array type can be |
|
.NET string |
.NET |
This contains the vector representation in JSON format. |
|
OracleString |
ODP.NET |
This contains the vector representation in JSON format. |
ODP.NET can bind vector data as the following .NET types for SQL or stored procedure execution:
| Data Type | .NET or ODP.NET Data Type | Bind Type | Description |
|---|---|---|---|
|
Any numeric array |
.NET |
|
This array type can be any numeric array type. ODP.NET will infer the numeric array type and bind the Vector data type as |
|
Any numeric array |
.NET |
|
This array type can be any numeric array type. ODP.NET will bind the Vector data type based on the binding type accordingly. |
|
.NET string, OracleString |
.NET or ODP.NET |
|
This contains the vector representation in JSON format. ODP.NET will bind the Vector data type as |
|
.NET string, OracleString |
.NET or ODP.NET |
|
This contains the vector representation in JSON format. ODP.NET will bind the Vector data type based on the binding type accordingly. |
|
OracleClob |
ODP.NET |
|
This contains the vector representation in JSON format. |
When using vectors with OracleDataAdapter ReturnProviderSpecificTypes set to false, a .NET numeric array will populate the DataTable or DataSet after the Fill method is called. When true, OracleString will be used.
For vectors of '*' numeric format and ReturnProviderSpecificTypes set to false, double[] will be populated upon an OracleDataAdapter Fill(). For all other formats, short[], float[], or double[] will be populated based on the Vector data type numeric format.
Since vectors cannot be compared directly with each other, they cannot be used as JOIN keys, ORDER BY keys, GROUP BY keys, or other related scenarios. Thus, for OracleCommandBuilder, vector comparison is not included in the SQL WHERE clause of the generated commands.
Vectors as CLOB or VARCHAR2 Data Type
Unmanaged ODP.NET, as well as managed ODP.NET and ODP.NET Core prior to version 23.3.2, support vectors using existing CLOB or VARCHAR2 data types. In these cases, vectors are stored with a JSON format. Data retrieval and manipulation use existing CLOB or VARCHAR2 or .NET APIs.
In later managed ODP.NET and ODP.NET Core versions, vectors can remain stored as CLOBs or VARCHAR2s for backwards compatibility purposes by setting the OracleConfiguration MapVectorColumnAsClob property to true.
ODP.NET Vector Sample Code
//This ODP.NET artificial intelligence (AI) vector sample demonstrates how to insert,
retrieve, update, and delete multi-dimensional FLOAT64, FLOAT32, and INT8 vector data
types using the Oracle database.
//Requires ODP.NET 23ai (23.3.2) or higher and Oracle Database 23ai (23.4) or higher.
//Add User Id, Password, and Data Source, such as Easy Connect Plus or TNS, to the connection
string to connect to the DB.
using Oracle.ManagedDataAccess.Client;
namespace VectorDemo
{
public class VectorDemo
{
//Provide User Id, Password, and Data Source values for your database.
public const string conStr = "User Id=<USER>;Password=<PASSWORD>;Data Source=<DATA SOURCE>;";
public const int id = 1;
public static void Main(string[] args)
{
using (OracleConnection con = new OracleConnection(conStr))
{
using (OracleCommand cmd = con.CreateCommand())
{
try
{
con.Open();
cmd.CommandText = "begin " +
"execute immediate 'drop table VectorTable';" +
"exception when others then if sqlcode <> -942 then raise;" +
"end if;" +
"end;";
cmd.ExecuteNonQuery();
//Create table with 2 dimensional FLOAT64, 3 dimensional FLOAT32, and 4 dimensional INT8 vector columns
cmd.CommandText = "create table VectorTable (id number, float64s vector(2, float64),
float32s vector(3, float32), int8s vector(4, INT8), constraint pk primary key (id))";
cmd.ExecuteNonQuery();
//Insert vector row into DB
InsertVectors();
//Update vector values in DB
UpdateVectors();
//Delete vector values in DB
DeleteVectors();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
public static void InsertVectors()
{
string sql = "insert into VectorTable values (:id, :float64, :float32, :int8)";
try
{
double[] doubles = new double[] { 1.234, 2.345 };
float[] floats = new float[] { 1.23f, 2.34f, 3.45f };
short[] int16s = new short[] { 1, 2, 3, 4 };
OracleConnection con = new OracleConnection(conStr);
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.Add("id", OracleDbType.Int16, 0, id, System.Data.ParameterDirection.Input);
//Binding FLOAT64 variable as vector - doubles
cmd.Parameters.Add("float64", OracleDbType.Vector, 0, doubles, System.Data.ParameterDirection.Input);
//Binding FLOAT32 variable as vector - floats
cmd.Parameters.Add("float32", OracleDbType.Vector, 0, floats, System.Data.ParameterDirection.Input);
//Binding INT8 variable as vector - int16s
cmd.Parameters.Add("int8", OracleDbType.Vector, 0, int16s, System.Data.ParameterDirection.Input);
// Insert vectors into VectorTable
cmd.ExecuteNonQuery();
//Retrieve vector values from DB
RetrieveVectors();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public static void UpdateVectors()
{
string sql = "update VectorTable set float64s=:float64, float32s=:float32, int8s=:int8 where id=:id";
try
{
double[] doubles = new double[] { 9.876, 8.765 };
float[] floats = new float[] { 9.87f, 8.76f, 7.65f };
short[] int16s = new short[] { 9, 8, 7, 6 };
OracleConnection con = new OracleConnection(conStr);
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
//Binding FLOAT64 variable as vector - doubles
cmd.Parameters.Add("float64", OracleDbType.Vector_Float64, 0, doubles, System.Data.ParameterDirection.Input);
//Binding FLOAT32 variable as vector - floats
cmd.Parameters.Add("float32", OracleDbType.Vector_Float32, 0, floats, System.Data.ParameterDirection.Input);
//Binding INT8 variable as vector - int16s
cmd.Parameters.Add("int8", OracleDbType.Vector_Int8, 0, int16s, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("id", OracleDbType.Int16, 0, id, System.Data.ParameterDirection.Input);
// Update vectors in VectorTable
cmd.ExecuteNonQuery();
Console.WriteLine("Database vector values updated!");
//Retrieve vector values from DB
RetrieveVectors();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public static void DeleteVectors()
{
string sql = "delete from VectorTable where id=:id";
try
{
OracleConnection con = new OracleConnection(conStr);
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.Add("id", OracleDbType.Int16, 0, id, System.Data.ParameterDirection.Input);
// Delete row with vector values from VectorTable
cmd.ExecuteNonQuery();
Console.WriteLine("Database vector values deleted!");
//Confirm vectors removed
RetrieveVectors();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public static void RetrieveVectors()
{
string sql = "select * from VectorTable";
try
{
OracleConnection con = new OracleConnection(conStr);
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataReader reader = cmd.ExecuteReader();
//Use ODP.NET vector accessors to retrieve data
if (reader.Read())
{
Console.WriteLine("Retrieve FLOAT64S Vector value:");
double[] vecD = reader.GetDoubleArray(1);
PrintDoubles(vecD);
Console.WriteLine("Retrieve FLOAT32S Vector value:");
float[] vecF = reader.GetFloatArray("FLOAT32S");
PrintFloats(vecF);
Console.WriteLine("Retrieve INT8S vector value:");
short[] vecInt8s = reader.GetInt16Array(3);
PrintInt16s(vecInt8s);
Console.WriteLine();
}
//Return no results if no vector row is found
else
Console.WriteLine("No vector row found.");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
static public void PrintDoubles(double[] doubles)
{
Console.Write("{");
for (int i = 0; i < doubles.Length - 1; i++)
Console.Write(doubles[i].ToString() + ", ");
Console.WriteLine(doubles[doubles.Length - 1].ToString() + "}");
}
static public void PrintFloats(float[] floats)
{
Console.Write("{");
for (int i = 0; i < floats.Length - 1; i++)
Console.Write(floats[i].ToString() + ", ");
Console.WriteLine(floats[floats.Length - 1].ToString() + "}");
}
static public void PrintInt16s(Int16[] int16s)
{
Console.Write("{");
for (int i = 0; i < int16s.Length - 1; i++)
Console.Write(int16s[i].ToString() + ", ");
Console.WriteLine(int16s[int16s.Length - 1].ToString() + "}");
}
}
}