Vector Math: Basics

declare
    x vector := vector('[1,2]');
    y vector := vector('[3,4]');
    z vector;
begin

$if dbms_db_version.version >= 23 $then
    z := x + y;
    z := x - y;
    z := x * y;
    --z := x / y;
    dbms_output.put_line('Wow, vector math in Oracle 23AI Release 7!');
$else
    dbms_output.put_line('Time to upgrade!');
$end

end;
/

The Oracle 23ai Release 7 update introduced a new and powerful feature for working with vectors: vector arithmetic. This feature opens new possibilities for working with vectors that I will explore in the next post. In this post I will review the basic functionality of vector arithmetic in PL/SQL.

Getting Started

The source code for all articles here on practicalplsql.org is available in the practicalplsql public repository on github in the scripts/articles directory organized by concept and article. You can use create.user.practicalplsql.sql to create the practicalplsql schema. Sample data for articles can be found in the scripts/examples directory organized by example name.

Scripts for this post are located in vector-math/part-1-basics. This post requires a minimum version of Oracle 23ai release 7. All scripts have been tested in OCI Autonomous databases and the Oracle 23ai Free database VirtualBox appliance where this release update is available.

All scripts should be executed with serveroutput on, so this repetition is omitted for clarity. Some queries or code build on previous examples, these omit the repeated code for brevity, the source code has no omissions. Finally, when several scripts produce the same correct output, the output will only be shown once to avoid repetition.

Displaying Vector Arithmetic Equations

Before we go into the new functionality, let’s create some functions to display the results of the test scripts.

We will need a function to display a vector, including its dimension format. It should also show when the vector is null.

1.1.function.get_vector_details.sql

create or replace function get_vector_details(
    v in vector
    ) return varchar2
is
begin
    return 
        case v 
            when is null then 'NULL' 
            else 
                from_vector(v) 
                || ' ' || vector_dimension_format(v) 
        end;
end get_vector_details;
/

Unit testing the display function with different vectors shows that it’s working correctly.

declare
    v vector;
begin
    --display a null vector
    dbms_output.put_line(get_vector_details(v));
    
    v := to_vector('[1,2,3]',3,int8);
    dbms_output.put_line(get_vector_details(v));
    
    v := to_vector('[150,775,357]',3,float32);
    dbms_output.put_line(get_vector_details(v));
    
    v := to_vector('[15000,77500,357000]',3,float64);
    dbms_output.put_line(get_vector_details(v));    

end;
/

NULL
[1,2,3] INT8
[1.5E+002,7.75E+002,3.57E+002] FLOAT32
[1.5E+004,7.75E+004,3.57E+005] FLOAT64

Next, we can create a function that will display the arithmetic operation, both input vector operands and the resulting vector.

1.2.procedure.show_vector_math.sql

--Create a test procedure to print a vector equation with its result
create or replace procedure show_vector_math(
    operation in varchar2, 
    v1 in vector, 
    v2 in vector, 
    v3 in vector
)
is
begin
    dbms_output.put_line(
        get_vector_details(v1) 
        || ' ' || operation || ' '
        || get_vector_details(v2) 
        || ' = ' 
        || get_vector_details(v3));
end show_vector_math;
/

Testing the procedure with some vectors confirms that it works.

set serveroutput on;
declare 
    v1 vector;
    v2 vector;
    v3 vector;
begin
    v1 := to_vector('[1,2]', 2, int8);
    v2 := to_vector('[3,4]', 2, float32);
    v3 := to_vector('[5,6]', 2, float64);
    show_vector_math('JUST TESTING', v1, v2, v3);
end;
/

[1,2] INT8 JUST TESTING [3.0E+000,4.0E+000] FLOAT32 = [5.0E+000,6.0E+000] FLOAT64

Dimension-wise Arithmetic

Vector arithmetic is always dimension-wise. For two vectors x and y with n dimensions, the resulting vector for each dimension is the same operation applied to that dimension for each operand:

F(vx, vy)= [ F(vx₍₁₎, vy₍₁₎), F(vx₍₂₎, vy₍₂₎)…F(vx_(n), vy_(n) ]

Substituting actual values makes this general form more clear:

[1,2,38] + [3,5,4] = [ 1 + 3, 2 + 5, 28 + 4 ] = [4,7,42]

Because vector arithmetic works at the vector dimension level, both operands must always have the same number of dimensions. Arithmetic operations are not possible with vectors with different dimension counts.

Addition

All arithmetic operations with vectors are dimension-wise operations. The script link has a query to display an arithmetic operation with two vectors. This can be done by serializing the vectors, converting to json arrays, using json_table to unnest the individual dimensions and pivoting so that each dimension value can be worked with separately.

For addition, we add the individual dimension values of each operand vector to come up with the result dimension value:

2.0-dimension-wise-addition.sql

[1.25E+002,2.4E+001,1.15E+002,4.2E+001]FLOAT32
plus 
[2.5E+001,3.5E+001,2.6E+001,2.8E+001]FLOAT32

dimension 1 = 125 + 25
dimension 2 = 24 + 35
dimension 3 = 115 + 26
dimension 4 = 42 + 28

equals: 
[1.5E+002,5.9E+001,1.41E+002,7.0E+001]FLOAT32

In PL/SQL we just need to add the two vectors to get the result.

2.1-vector-addition.sql

declare 
    v1 vector;
    v2 vector;
begin
    v1 := vector('[1,2]', *, int8);
    v2 := vector('[3,4]', *, int8);    
    show_vector_math('+', v1, v2, v1 + v2);
    
    v1 := vector('[15,23.5]', *, float32);
    v2 := vector('[275,365]', *, float32);    
    show_vector_math('+', v1, v2, v1 + v2);
end;
/

[1,2] INT8 + [3,4] INT8 = [4,6] INT8
[1.5E+001,2.35E+001] FLOAT32 + [2.75E+002,3.65E+002] FLOAT32 = [2.9E+002,3.885E+002] FLOAT32

Similarly to any arithmetic operations, adding a null vector to another vector always returns null.

declare 
    v1 vector := vector('[4,2]', *, int8);
begin
    show_vector_math('+', v1, null, v1 + null);
end;
/
[4,2] INT8 + NULL = NULL

Arithmetic operations with vectors that are the same format will always return that format.

When the operand vectors have different dimension formats, the result will always be in the format with the most precision. An INT8 vector added to a FLOAT32 vector will return a FLOAT32 vector. With a FLOAT64 vector in the equation, the result will be FLOAT64.

2.2-vector-addition-dimension-formats.sql

declare 
    v1 vector;
    v2 vector;
begin
    dbms_output.put_line('int8 + float32 = float32');
    v1 := vector('[1,2]', *, int8);
    v2 := vector('[3,4]', *, float32);
    show_vector_math('+', v1, v2, v1 + v2);
    
    dbms_output.put_line('int8 + float64 = float64');
    v1 := vector('[1,2]', *, int8);
    v2 := vector('[3,4]', *, float64);
    show_vector_math('+', v1, v2, v1 + v2);
    
    dbms_output.put_line('float32 + float64 = float64');
    v1 := vector('[1,2]', *, float32);
    v2 := vector('[3,4]', *, float64);
    show_vector_math('+', v1, v2, v1 + v2);
end;
/

int8 + float32 = float32
[1,2] INT8 + [3.0E+000,4.0E+000] FLOAT32 = [4.0E+000,6.0E+000] FLOAT32
int8 + float64 = float64
[1,2] INT8 + [3.0E+000,4.0E+000] FLOAT64 = [4.0E+000,6.0E+000] FLOAT64
float32 + float64 = float64
[1.0E+000,2.0E+000] FLOAT32 + [3.0E+000,4.0E+000] FLOAT64 = [4.0E+000,6.0E+000] FLOAT64

Since an arithmetic operation will always return a result with a more precise dimension format, we cannot assign that result to a less precise format. This example adds an INT8 vector to a FLOAT64 vector, so the result will be in FLOAT64 format. Assigning the result to an INT8 variable will generate a run-time exception.

declare 
    v1 vector := vector('[1,2]', *, int8);
    v2 vector := vector('[3,4]', *, float64);
    v3 vector(*, int8);
begin
    v3 := v1 + v2;
    show_vector_math('+', v1, v2, v3);
exception
    when others then
        dbms_output.put_line(sqlerrm);
end;
/
ORA-51868: Assignment is not supported for vectors with different element formats (FLOAT64, INT8).

We can explicitly convert the result format to avoid this error:

declare 
    v1 vector := vector('[1,2]', *, int8);
    v2 vector := vector('[3,4]', *, float64);
    v3 vector(*, int8);
begin
    v3 := to_vector(from_vector(v1 + v2), *, int8);
    show_vector_math('+', v1, v2, v3);
exception
    when others then
        dbms_output.put_line(sqlerrm);
end;
/
[1,2] INT8 + [3.0E+000,4.0E+000] FLOAT64 = [4,6] INT8

There is no support for binary vectors in vector arithmetic. Attempting arithmetic with binary vectors will generate a run-time exception.

declare 
    v1 vector := vector('[42]', 8, binary);
    v2 vector := vector('[108]', 8, binary);
begin
    show_vector_math('+', v1, v2, v1 + v2);
exception
    when others then
        dbms_output.put_line(sqlerrm);
end;
/
ORA-51838: Input vectors with BINARY format are not allowed in vector arithmetic operations.

With an INT8 vector, each dimension is limited to the range of -128 to 127. According to the documentation, if the result of an operation goes out of this range an exception will be raised.

Testing this shows that an overflow error returns a null vector.

2.3-vector-addition-overflow-errors.sql

declare 
    v1 vector := vector('[127,-128]', *, int8);
    v2 vector := vector('[0, 0]', *, int8);
begin
    show_vector_math('+', v1, v2, v1 + v2);
    
    v2 := vector('[1,0]', *, int8);
    show_vector_math('+', v1, v2, v1 + v2);
    
    v2 := vector('[0,-1]', *, int8);
    show_vector_math('+', v1, v2, v1 + v2);
exception
    when others then
        dbms_output.put_line(sqlerrm);
end;
/

[127,-128] INT8 + [0,0] INT8 = [127,-128] INT8
[127,-128] INT8 + [1,0] INT8 = NULL
[127,-128] INT8 + [0,-1] INT8 = NULL

If we attempt the vector arithmetic operation in SQL we can see the overflow error clearly.

declare 
    v1 vector := vector('[41,127]', *, int8);
    v2 vector := vector('[1, 1]', *, int8);
    v3 vector;
begin
    select v1 + v2 into v3;
    show_vector_math('+', v1, v2, v3);
exception
    when others then
        dbms_output.put_line(sqlerrm);
end;
/
ORA-51806: Vector column is not properly formatted (dimension value 0 is outside the allowed precision range).

Notice that the error message is not reporting the dimension number correctly here. This operation resulted in a vector that would be [42,128] if it could be represented as an INT8 vector. The error message should say that dimension value 1 is outside the allowed precision range.

Simply changing one of the operands to a more precise format fixes the overflow error because the result is now in a more precise format.

declare 
    v1 vector := vector('[41,127]', *, int8);
    v2 vector := vector('[1, 1]', *, float32);
begin
    show_vector_math('+', v1, v2, v1 + v2);
exception
    when others then
        dbms_output.put_line(sqlerrm);
end;
/
[41,127] INT8 + [1.0E+000,1.0E+000] FLOAT32 = [4.2E+001,1.28E+002] FLOAT32

Except for division, vector arithmetic operations have the same behavior as addition, just using a different operation. Let’s look at each operation separately.

Subtraction

For vector subtraction, we subtract each dimension value of the second operand from each dimension value of the first operand to find the dimension value for the result.

3.0-dimension-wise-subtraction.sql

[1.25E+002,2.4E+001,1.15E+002,4.2E+001]FLOAT32
minus 
[2.5E+001,3.5E+001,2.6E+001,2.8E+001]FLOAT32

dimension 1 = 125 - 25
dimension 2 = 24 - 35
dimension 3 = 115 - 26
dimension 4 = 42 - 28

equals: 
[1.0E+002,-1.1E+001,8.9E+001,1.4E+001]FLOAT32

In PL/SQL we simply subtract the two vectors.

3.1-vector-subtraction.sql

declare 
    v1 vector;
    v2 vector;
begin
    v1 := vector('[1,2]', *, int8);
    v2 := vector('[3,4]', *, int8);    
    show_vector_math('-', v1, v2, v1 - v2);
    
    v1 := vector('[15,23.5]', *, float32);
    v2 := vector('[275,365]', *, float32);    
    show_vector_math('-', v1, v2, v1 - v2);
end;
/

[1,2] INT8 - [3,4] INT8 = [-2,-2] INT8
[1.5E+001,2.35E+001] FLOAT32 - [2.75E+002,3.65E+002] FLOAT32 = [-2.6E+002,-3.415E+002] FLOAT32

Just like addition, subtracting null from a vector returns null.

declare 
    v1 vector := vector('[4,2]', *, int8);
begin
    show_vector_math('-', v1, null, v1 - null);
end;
/
[4,2] INT8 - NULL = NULL

When subtraction creates an overflow, the result is null.

declare 
    v1 vector := vector('[-128,42]', *, int8);
    v2 vector := vector('[1, 0]', *, int8);
begin
    show_vector_math('-', v1, v2, v1 - v2);
exception
    when others then
        dbms_output.put_line(sqlerrm);
end;
/
[-128,42] INT8 - [1,0] INT8 = NULL

Changing one of the operands to a different format fixes the overflow error.

declare 
    v1 vector := vector('[-128,42]', *, int8);
    v2 vector := vector('[1, 0]', *, float32);
begin
    show_vector_math('-', v1, v2, v1 - v2);
exception
    when others then
        dbms_output.put_line(sqlerrm);
end;
/
[-128,42] INT8 - [1.0E+000,0] FLOAT32 = [-1.29E+002,4.2E+001] FLOAT32

Multiplication

Vector multiplication is also performed dimension-wise by multiplying each dimension separately from both operands to find the value of that dimension in the resulting vector.

4.0-dimension-wise-multiplication.sql

[1.25E+002,2.4E+001,1.15E+002,4.2E+001]FLOAT32
multiplied by 
[2.5E+001,3.5E+001,2.6E+001,2.8E+001]FLOAT32

dimension 1 = 125 * 25
dimension 2 = 24 * 35
dimension 3 = 115 * 26
dimension 4 = 42 * 28

equals: 
[3.125E+003,8.4E+002,2.99E+003,1.176E+003]FLOAT32

In PL/SQL we simply multiply the two vectors.

--4.1-vector-multiplication.sql

declare 
    v1 vector;
    v2 vector;
begin
    v1 := vector('[1,2]', *, int8);
    v2 := vector('[3,4]', *, int8);    
    show_vector_math('*', v1, v2, v1 * v2);
    
    v1 := vector('[15,23.5]', *, float32);
    v2 := vector('[275,365]', *, float32);    
    show_vector_math('*', v1, v2, v1 * v2);
end;
/
[1,2] INT8 * [3,4] INT8 = [3,8] INT8
[1.5E+001,2.35E+001] FLOAT32 * [2.75E+002,3.65E+002] FLOAT32 = [4.125E+003,8.5775E+003] FLOAT32

Multiplying a vector by a null returns null.

declare 
    v1 vector := vector('[4,2]', *, int8);
begin
    show_vector_math('*', v1, null, v1 * null);
end;
/
[4,2] INT8 * NULL = NULL

The result dimension format is always the more precise format when vectors with different formats are multiplied.

declare 
    v1 vector;
    v2 vector;
begin
    v1 := vector('[1,2]', *, int8);
    v2 := vector('[3,4]', *, float32);
    show_vector_math('*', v1, v2, v1 * v2);
    
    v1 := vector('[1,2]', *, int8);
    v2 := vector('[3,4]', *, float64);
    show_vector_math('*', v1, v2, v1 * v2);
    
    v1 := vector('[1,2]', *, float32);
    v2 := vector('[3,4]', *, float64);
    show_vector_math('*', v1, v2, v1 * v2);
end;
/

[1,2] INT8 * [3.0E+000,4.0E+000] FLOAT32 = [3.0E+000,8.0E+000] FLOAT32
[1,2] INT8 * [3.0E+000,4.0E+000] FLOAT64 = [3.0E+000,8.0E+000] FLOAT64
[1.0E+000,2.0E+000] FLOAT32 * [3.0E+000,4.0E+000] FLOAT64 = [3.0E+000,8.0E+000] FLOAT64

Overflow errors in vector multiplication also result in a null value instead of raising an exception as documented.

declare 
    v1 vector := vector('[42,11]', *, int8);
    v2 vector := vector('[8, 22]', *, int8);
begin
    show_vector_math('*', v1, v2, v1 * v2);
end;
/
[42,11] INT8 * [8,22] INT8 = NULL

Changing one of the operands to a different format will prevent the overflow error.

declare 
    v1 vector := vector('[42,11]', *, int8);
    v2 vector := vector('[8, 22]', *, float32);
begin
    show_vector_math('*', v1, v2, v1 * v2);
end;
/
[42,11] INT8 * [8.0E+000,2.2E+001] FLOAT32 = [3.36E+002,2.42E+002] FLOAT32

Division

In principle, division should be the inverse of multiplication. If we can multiply dimension-wise, we should be able to divide.

The test query can accomplish this by dividing each dimension value separately.

5.0-dimension-wise-division.sql

[1.25E+002,2.4E+001,1.15E+002,4.2E+001]FLOAT32
divided by 
[2.5E+001,3.5E+001,2.6E+001,2.8E+001]FLOAT32

dimension 1 = 125 / 25
dimension 2 = 24 / 35
dimension 3 = 115 / 26
dimension 4 = 42 / 28

equals: 
[5.0E+000,6.85714304E-001,4.42307711E+000,1.5E+000]FLOAT32

However, in PL/SQL at this time, division is not supported. The fact that the compile error says that this may be a temporary restriction suggests that we will be able to divide vectors eventually.

5.1-vector-division.sql

declare 
    v1 vector := vector('[27,32]', *, int8);
    v2 vector := vector('[3,8]', *, int8);
begin
    show_vector_math('/', v1, v2, v1/v2);
exception
    when others then
        dbms_output.put_line(sqlerrm);
end;
/
PLS-00999: implementation restriction (may be temporary) Dimension-wise vector divide

Summary

Vector arithmetic operations allow adding, subtracting and multiplying vectors. A vector embedding essentially converts the meaning of a word or a chunk of writing into a vector in an n-dimensional space that represents that meaning. As we will see in the next post in this series, this opens up some interesting possibilities for using vector arithmetic to combine and compare meanings.

–Anthony Harper