set serveroutput on;
declare
v_float64 vector(*, float64);
v_float32 vector(*, float32);
v_int8 vector(*, int8);
v_binary vector(*, binary);
begin
v_float64 := to_vector(
'[
-6.28318530717958647692,-1.41421356237309504880,
3.14159265358979323846,-1.61803398874989484820,
2.71828182845904523536,-0.56714329040978387299,
2.39996322972865332223,-0.83462684167407318628
]'
, *, float64);
dbms_output.put_line(vector_serialize(v_float64));
select vector(v_float64, 8, float32) into v_float32;
dbms_output.put_line(vector_serialize(v_float32));
select vector(v_float32, 8, int8) into v_int8;
dbms_output.put_line(vector_serialize(v_int8));
select to_vector(v_int8, 8, binary) into v_binary;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
/
[
-6.2831853071795862E+000,-1.4142135623730951E+000,
3.1415926535897931E+000,-1.6180339887498949E+000,
2.7182818284590451E+000,-5.6714329040978384E-001,
2.3999632297286535E+000,-8.3462684167407319E-001
]
[
-6.28318548E+000,-1.41421354E+000,
3.14159274E+000,-1.61803401E+000,
2.71828175E+000,-5.67143261E-001,
2.39996314E+000,-8.34626853E-001
]
[-6,-1,3,-2,3,-1,2,-1]
ORA-51814: Vector of BINARY format cannot have any operation performed with vector of any other type.We can use the vector constructor to convert between most vector dimension formats…except binary vectors. The above example converts a vector to successively more coarse formats until it runs into an error with final conversion to a binary vector.
A simple function to do the conversion is all we really want to complete this series of conversions:
select to_binary_vector(v_int8) into v_binary;
[42]In this post I will examine an approach to this conversion using json functionality. I will then convert this sql into a scalar macro for later use.
- Getting Started
- Binary Vector Construction Requirements
- Vector Dimension Formats and Storage Requirements
- Binary Vector Quantization
- Designing The Quantization Macro
- Separate Vector Dimensions With JSON_TABLE and Quantize
- Convert Quantized Dimensions To Packed Bytes
- Aggregate to Textual Input For the Binary Vector
- Converting the SQL to a Scalar Macro
- Create Scalar Macro Function
- Compare Macro To Sentence Transformers Quantization
- Conclusion
Getting Started
Scripts for this post are located in vector-search/binary-vector-quantization. These scripts requires a minimum version of Oracle 23ai release 7 to support binary vectors. All scripts have been tested in OCI Autonomous databases and the Oracle 23ai Free database VirtualBox appliance. At the time of writing this article, both the OCI Autonomous Database and the VirtualBox appliance are at Oracle 23ai Release 9.
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.
Binary Vector Construction Requirements
As we saw in the introductory example, the vector constructor can be used to convert between dimensions formats if the vectors involved are float32, float64 or int8. Binary vectors however, cannot be constructed from vectors of any other dimension formats.
binary_construction_requirements.sql
declare
v_textual_input varchar2(100) := '[-6,-1,3,-2,3,-1,2,-1]';
v_int8 vector(*, int8);
v_binary vector(*, binary);
begin
v_int8 := to_vector(v_textual_input, 8, int8);
select to_vector(v_int8, 8, binary) into v_binary;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
/
ORA-51814: Vector of BINARY format cannot have any operation performed with vector of any other type.A binary vector can only be constructed from textual input if each value is in the range of an unsigned 8 bit integer (0-255).
declare
v_textual_input varchar2(100) := '[-6,-1,3,-2,3,-1,2,-1]';
v_binary vector(*, binary);
begin
v_binary := to_vector(v_textual_input, 8, binary);
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).Quantization is the process of converting dimension values to a binary value of 0 or 1. Even if we quantize this input properly, it is still innappropriate for constructing a binary vector. The dimensions need to be packed into 8 dimensions per value in the textual input of the vector constructor.
declare
v_textual_input varchar2(100) := '[0,0,1,0,1,0,1,0]';
v_binary vector(*, binary);
begin
v_binary := to_vector(v_textual_input, 8, binary);
exception
when others then
dbms_output.put_line(sqlerrm);
end;
/
ORA-51867: Dimension count of the constructed vector(64) does not match the dimension count argument (8) of the constructor.To construct this particular binary vector, we need to start with a serialized binary vector as textual input. The binary value 00101010 is represented by the packed uint8 value of 42… Therefore, the correct textual input for a vector with 8 dimensions that quantizes to [0,0,1,0,1,0,1,0] would be [42].
declare
v_textual_input varchar2(100) := '[42]';
v_binary vector(*, binary);
begin
v_binary := to_vector(v_textual_input, 8, binary);
dbms_output.put_line(vector_serialize(v_binary));
end;
/
[42]This packed dimension format is the main advantage of binary vectors, making them extremely compact in comparison to other vector storage formats. Binary vectors trade precision for performance and reduced storage requirements. Because of this reduced precision, it is best if the model used to generate the vectors is optimized for binary vector embeddings.
Vector Dimension Formats and Storage Requirements
When we use a vector data type with binary dimensions, 8 dimension values are packed into each value that we see in the vectors textual representation. The binary vector [42] has 8 dimensions, it represents an int8 vector that serializes to [0,0,1,0,1,0,1,0] with each dimension value limited to the binary values 0 and 1. Each element of a binary vector is a packed unsigned 8-bit integer, so it only takes one byte to store every 8 dimensions. This also limits these packed values to integers that can be represented using only 8 bits: 0 to 255. A 16 dimensional binary vector is represented as [42,255] using this packed format. This vector unpacks to [0,0,1,0,1,0,1,0,1,1,1,1,1,1,1,1] if we want to see the bit values for the actual dimensions.
Because of the way they are stored, binary vectors must have dimension counts that are multiples of 8 so that every 8 dimensions are always stored as a single byte with a uint8 value. If a model generates vectors with 1024 dimensions, these can be represented as binary vectors using only 128 bytes per vector. A million rows with 1024 dimension binary vectors only needs 128 megabytes of storage.
The INT8 vector dimension format represents each dimension using a signed 8-bit integer with seven bits for the value and one bit for the sign (positive or negative), limiting dimension values to the range -128 to +127. An int8 vector needs a byte per dimension, 8 dimensions will require 8 bytes of storage. This is 8x the storage requirement for an 8 dimensional binary vector, but the dimension values are also much more fine grained. For an int8 vector with 1024 dimensions, the storage requirement is 1,024 bytes. For a million rows storing an int8 vector, the storage is just over a gigabyte for the vectors alone.
A float32 vector uses 32 bits to store a floating point number for each dimension value, that is 4 bytes per dimension. An 8 dimensional FLOAT32 vector requires 32 bytes of storage, a 1024 dimension float32 vector needs 4,096 bytes per vector. This is 32x the storage for an 8 dimensional binary vector. Storing a million rows with 1024 dimension float32 vectors would require more than four gigabytes.
Binary Vector Quantization
Converting a vector space to less precise dimension values is called scalar quantization. For example, a float32 vector can be converted to an int8 vector by truncating each dimension values, as long as the values are in the range of -128 to +127. Vectors with greater ranges could be normalized before scaling.
Binary quantization is the simplest form of scalar quantization. If the dimension value is 0 or negative, it becomes a 0. If the dimension value is greater than 0, it is represented by a 1.
If you want to learn more about scalar quantization, Binary and Scalar Embedding Quantization for Significantly Faster & Cheaper Retrieval is a good article on Hugging Face reviewing the concepts of scalar quantization.
There are other approaches to quantization, such as Matryoshka Representation Learning (MRL). This approach puts the most significant dimensions first in the vectors, so that the number of vectors can be reduced without sacrificing accuracy. The mixed bread models on Hugging Face have been designed with MRL and binary quantization, so that smaller models can still produce accurate results. You can learn more about MRL and binary models in this blog on the mixed bread site: 64 bytes per embedding, yee-haw.
Binary vectors can either be directly generated by the LLM, or quantized in a post embedding step. The example models that can be prepared with OML4PY for loading into Oracle Database 23ai all generate float32 vectors as output, the quantization option only applies to the internal weights used by the exported model. Without a loaded model to generate binary vectors, we will have to take the post processing approach to convert vectors to binary dimension formats.
Post processing quantization can also be accomplished in python using the sentence transformers libraries. This provides a way to objectively confirm that we are quantizing the vectors correctly. To convert vectors to binary in python, use the sentence transformers quantization functions with a precision of ubinary to create the same packed uint8 format that we will be using in the Oracle database.
Test some simple examples to confirm the proper approach for packing quantized dimensions as byte values. Create the following script in an environment where you can run Python 3.12. I am using the same Python environment that I set up in VirtualBox for the OML4PY2.1 Quickstart article.
# python.binary-quantization-examples.py
# save in environment set up for sentence transformers and python 3.12
# quantize with sentence transformers to verify dimension ordering
from sentence_transformers import SentenceTransformer
from sentence_transformers.quantization import quantize_embeddings
# use example vectors with dimension values as 0 and 1
embeddings = [
[0,0,1,0,1,0,1,0],
[0,0,0,0,1,0,1,1]
]
print("input embeddings")
print(embeddings)
embeddings_binary = quantize_embeddings(embeddings, precision="ubinary")
print("expected packed binary vectors are [42] and [11]")
print("sentence_transformers.quantization with ubinary precision")
print(embeddings_binary)
embeddings = [
[0,0,1,0,1,0,1,0,0,0,0,0,1,0,1,1]
]
print("input embeddings")
print(embeddings)
embeddings_binary = quantize_embeddings(embeddings, precision="ubinary")
print("expected packed binary vector is [42,11]")
print("sentence_transformers.quantization with ubinary precision")
print(embeddings_binary)Looking at a vector with unpacked dimensions, we number dimensions from left to right, with the leftmost dimension having the lowest index and the rightmost dimension getting the highest dimension index. For each group of 8 dimensions, this would intuitively seem to mean that the first value is mapped to the lowest bit in the byte and the last value is mapped to the highest bit, but the actual usage is reversed.
Running this Python script shows that the binary quantization uses the first dimension on the left as the high value (2 to the 7th power) and the last dimension to the right as the low value (2 to the 0th power) for each set of 8 dimensions.
input embeddings
[[0, 0, 1, 0, 1, 0, 1, 0], [0, 0, 0, 0, 1, 0, 1, 1]]
expected packed binary vectors are [42] and [11]
sentence_transformers.quantization with ubinary precision
[[42]
[11]]
input embeddings
[[0, 0, 1, 0, 1, 0, 1, 0, 0, 0, 0, 0, 1, 0, 1, 1]]
expected packed binary vector is [42,11]
sentence_transformers.quantization with ubinary precision
[[42 11]]Designing The Quantization Macro
The textual representation of a vector is really just an array of numbers, and it even uses json notation for this array (square brackets [1,2,3]). We can use sql json methods to convert a serialized vector to an instance of the json type constructor. The json_table function can then be used to split the array elements into separate rows. With the dimension values isolated as separate rows, they can be quantized into zeroes and ones. At that point, we can group them into sets of 8 dimensions and convert to an unsigned integer using the bin_to_num sql function. Aggregating these integers into an array with json_arrayagg will return the correct textual input for a binary vector using the to_vector constructor with the dimension format specified as binary.
We can use sql to model this thought process step by step. When everything is working as expected, we will have the correct sql statement to create a macro that converts a vector embedding to binary format.
Separate Vector Dimensions With JSON_TABLE and Quantize
Its clear that serialized vectors are the same shape as serialized json arrays.
design.1.01.vectors-arrays-match-json-arrays.sql
select
from_vector(
to_vector(
'[-22,-44,33,-13,26,-11,38,-7]'
, 8, int8
)
) as vector_array_text
/
VECTOR_ARRAY_TEXT
------------------------------
[-22,-44,33,-13,26,-11,38,-7]
select
json_serialize(
json[-22,-44,33,-13,26,-11,38,-7]
) as json_array_text
/
JSON_ARRAY_TEXT
------------------------------
[-22,-44,33,-13,26,-11,38,-7]Putting these together, the serialized vector can be converted to a json array with the json type constructor.
design.1.02.vector-to-json-array.sql
prompt serialize the vector as text and use with json constructor
with base as (
select
to_vector(
'[-22,-44,33,-13,26,-11,38,-7]'
, 8, int8
) as vector_value
)
select
vector_serialize(b.vector_value) as value_as_vector
, json_serialize(
json(
vector_serialize(b.vector_value returning clob)
)
) as value_as_json
from base b
/
VALUE_AS_VECTOR VALUE_AS_JSON
------------------------------ ------------------------------
[-22,-44,33,-13,26,-11,38,-7] [-22,-44,33,-13,26,-11,38,-7] Now we can use json_table to separate the array into its elements. Use the array ordinality to label the dimensions in order. Each row is now the value of one dimension from the input vector.
design.1.3-split-dimensions-with-json-table.sql
with base as (
select
to_vector(
'[-22,-44,33,-13,26,-11,38,-7]'
, 8, int8
) as vec
), jbase as (
select
json(
vector_serialize(b.vec returning clob)
) as jvec
from base b
)
select jt.dim#, jt.dimval
from
jbase b,
json_table (
b.jvec, '$[*]'
columns(
dim# for ordinality
, dimval number path '$'
)
) jt
/
DIM# DIMVAL
---------- ----------
1 -22
2 -44
3 33
4 -13
5 26
6 -11
7 38
8 -7Combine the vector serialization in the json_table source to condense the CTE query.
design.1.04.condense-cte.sql
with base as (
select
to_vector(
'[-22,-44,33,-13,26,-11,38,-7]'
, 8, int8
) as vec
)
select jt.dim#, jt.dimval
from
base b,
json_table (
json(vector_serialize(b.vec returning clob))
, '$[*]'
columns(
dim# for ordinality
, dimval number path '$'
)
) jt
/
DIM# DIMVAL
---------- ----------
1 -22
2 -44
3 33
4 -13
5 26
6 -11
7 38
8 -7Now we are ready to apply our quantization method to each dimension value. Identify the bits and bytes correctly using the dimension numbers based on array ordinality. Notice that the 8th dimension of each byte will show as bit# 0 because we are using mod(dim#, 8) to label the bits in each group of 8.
design.1.05.quantize-dimension-values.sql
with base as (
select
to_vector(
'[-22,-44,33,-13,26,-11,38,-7]'
, 8, int8
) as vec
)
select
jt.dim#
, jt.dimval
, case sign(jt.dimval) when 1 then 1 else 0 end as dim_bitval
, mod(jt.dim#, 8) as bit#
, ceil(jt.dim#/8) as byte#
from
base b,
json_table (
json(vector_serialize(b.vec returning clob))
, '$[*]'
columns(
dim# for ordinality
, dimval number path '$'
)
) jt
/
DIM# DIMVAL DIM_BITVAL BIT# BYTE#
---------- ---------- ---------- ---------- ----------
1 -22 0 1 1
2 -44 0 2 1
3 33 1 3 1
4 -13 0 4 1
5 26 1 5 1
6 -11 0 6 1
7 38 1 7 1
8 -7 0 0 1We can use an input vector with 16 dimensions to be sure that the bytes are correctly identified as consecutive groups of eight bits.
design.1.06.verify-bytes-using-16-dimensions.sql
with base as (
select
to_vector(
'[-22,-44,33,-13,26,-11,38,-7, -4,-12,112,-123,127,-1,17,-77]'
, 16, int8) as vec
)
select
jt.dim#
, jt.dimval
, case sign(jt.dimval) when 1 then 1 else 0 end as dim_bitval
, mod(jt.dim#, 8) as bit#
, ceil(jt.dim#/8) as byte#
from
base b,
json_table (
json(vector_serialize(b.vec returning clob))
, '$[*]'
columns(
dim# for ordinality
, dimval number path '$'
)
) jt
/
DIM# DIMVAL DIM_BITVAL BIT# BYTE#
---------- ---------- ---------- ---------- ----------
1 -22 0 1 1
2 -44 0 2 1
3 33 1 3 1
4 -13 0 4 1
5 26 1 5 1
6 -11 0 6 1
7 38 1 7 1
8 -7 0 0 1
9 -4 0 1 2
10 -12 0 2 2
11 112 1 3 2
12 -123 0 4 2
13 127 1 5 2
14 -1 0 6 2
15 17 1 7 2
16 -77 0 0 2Prepare to pivot the bits in each byte. Remove any unecessary columns to prevent extra grouping levels in the pivot query.
design.1.07.prepare-pivot.sql
with base as (
select
to_vector(
'[-22,-44,33,-13,26,-11,38,-7, -4,-12,112,-123,127,-1,17,-77]'
, 16, int8) as vec
), quantized_base as (
select
case sign(jt.dimval) when 1 then 1 else 0 end as dim_bitval
, mod(jt.dim#, 8) as bit#
, ceil(jt.dim#/8) as byte#
from
base b,
json_table (
json(vector_serialize(b.vec returning clob))
, '$[*]'
columns(
dim# for ordinality
, dimval number path '$'
)
) jt
)
select
b.dim_bitval
, b.bit#
, b.byte#
from quantized_base b
order by b.byte#, decode(b.bit#, 0, 8, b.bit#)
/
DIM_BITVAL BIT# BYTE#
---------- ---------- ----------
0 1 1
0 2 1
1 3 1
0 4 1
1 5 1
0 6 1
1 7 1
0 0 1
0 1 2
0 2 2
1 3 2
0 4 2
1 5 2
0 6 2
1 7 2
0 0 2Convert Quantized Dimensions To Packed Bytes
Now that we have quantized the dimension values and identified all of the bits and bytes correctly, we can convert them to packed unsigned integers in groups of eight. The first step is to pivot the bits, grouping them as bytes. Since dimension 8 is numbered as bit 0, we will pivot it as bit#8 to keep things in the correct order for the dimensions.
design.1.08.pivot-bits-grouped-by-bytes.sql
with base as (
select
to_vector(
'[-22,-44,33,-13,26,-11,38,-7, -4,-12,112,-123,127,-1,17,-77]'
, 16, int8) as vec
), quantized_base as (
select
case sign(jt.dimval) when 1 then 1 else 0 end as dim_bitval
, mod(jt.dim#, 8) as bit#
, ceil(jt.dim#/8) as byte#
from
base b,
json_table (
json(vector_serialize(b.vec returning clob))
, '$[*]'
columns(
dim# for ordinality
, dimval number path '$'
)
) jt
)
select
p.byte#, p.b#1, p.b#2, p.b#3, p.b#4, p.b#5, p.b#6, p.b#7, p.b#8
from quantized_base q
pivot(
max(q.dim_bitval) for bit# in (
1 as b#1, 2 as b#2, 3 as b#3, 4 as b#4
, 5 as b#5, 6 as b#6, 7 as b#7, 0 as b#8)
) p
/
BYTE# B#1 B#2 B#3 B#4 B#5 B#6 B#7 B#8
----- --- --- --- --- --- --- --- ---
1 0 0 1 0 1 0 1 0
2 0 0 1 0 1 0 1 0Now we can use the sql function bin_to_num to convert the bit values to integers in sets of eight. The function bin_to_num accepts a variable number of input arguments with values of 0 or 1. The first argument is always the highest order bit to be converted and the last argument will be the lowest order bit.
Based on our earlier tests with sentence transformers quantization in python, the input vector should quantize as [42, 42], with each byte integer value being 42.
design.1.09.convert-bytes-to-uint8.sql
with base as (
select
to_vector(
'[-22,-44,33,-13,26,-11,38,-7, -4,-12,112,-123,127,-1,17,-77]'
, 16, int8) as vec
), quantized_base as (
select
case sign(jt.dimval) when 1 then 1 else 0 end as dim_bitval
, mod(jt.dim#, 8) as bit#
, ceil(jt.dim#/8) as byte#
from
base b,
json_table (
json(vector_serialize(b.vec returning clob))
, '$[*]'
columns(
dim# for ordinality
, dimval number path '$'
)
) jt
), pivot_to_bytes as (
select
p.byte#
, bin_to_num(p.b#1, p.b#2, p.b#3, p.b#4, p.b#5, p.b#6, p.b#7, p.b#8) as uint_byte
from quantized_base q
pivot(
max(q.dim_bitval) for bit# in (
1 as b#1, 2 as b#2, 3 as b#3, 4 as b#4
, 5 as b#5, 6 as b#6, 7 as b#7, 0 as b#8)
) p
)
select byte#, uint_byte
from pivot_to_bytes
/
BYTE# UINT_BYTE
----- ---------
1 42
2 42Aggregate to Textual Input For the Binary Vector
Now that we have packed up the dimension values as unsigned 8-bit integers, we can put the new packed dimensions together using json_arrayagg. The serialized the json array is ready to serve as textual input for the vector type constructor.
design.1.10.aggregate-to-textual-vector.sql
with base as (
select
to_vector(
'[-22,-44,33,-13,26,-11,38,-7, -4,-12,112,-123,127,-1,17,-77]'
, 16, int8) as vec
), quantized_base as (
select
case sign(jt.dimval) when 1 then 1 else 0 end as dim_bitval
, mod(jt.dim#, 8) as bit#
, ceil(jt.dim#/8) as byte#
from
base b,
json_table (
json(vector_serialize(b.vec returning clob))
, '$[*]'
columns(
dim# for ordinality
, dimval number path '$'
)
) jt
), pivot_to_bytes as (
select
p.byte#
, bin_to_num(p.b#1, p.b#2, p.b#3, p.b#4, p.b#5, p.b#6, p.b#7, p.b#8) as uint_byte
from quantized_base q
pivot(
max(q.dim_bitval) for bit# in (
1 as b#1, 2 as b#2, 3 as b#3, 4 as b#4
, 5 as b#5, 6 as b#6, 7 as b#7, 0 as b#8)
) p
)
select
json_serialize(
json_arrayagg(uint_byte order by byte#)
returning clob) as textual_input
from pivot_to_bytes
/
TEXTUAL_INPUT
--------------------
[42,42] Finally, we can construct the binary vector from the serialized json array of packed uint8 dimension values.
design.1.11.construct-binary-vector.sql
with base as (
select
to_vector(
'[-22,-44,33,-13,26,-11,38,-7, -4,-12,112,-123,127,-1,17,-77]'
, 16, int8) as vec
), quantized_base as (
select
case sign(jt.dimval) when 1 then 1 else 0 end as dim_bitval
, mod(jt.dim#, 8) as bit#
, ceil(jt.dim#/8) as byte#
from
base b,
json_table (
json(vector_serialize(b.vec returning clob))
, '$[*]'
columns(
dim# for ordinality
, dimval number path '$'
)
) jt
), pivot_to_bytes as (
select
p.byte#
, bin_to_num(p.b#1, p.b#2, p.b#3, p.b#4, p.b#5, p.b#6, p.b#7, p.b#8) as uint_byte
from quantized_base q
pivot(
max(q.dim_bitval) for bit# in (
1 as b#1, 2 as b#2, 3 as b#3, 4 as b#4
, 5 as b#5, 6 as b#6, 7 as b#7, 0 as b#8)
) p
)
select
to_vector(
json_serialize(
json_arrayagg(pb.uint_byte order by pb.byte#)
returning clob)
, *, binary) as binary_vector
from pivot_to_bytes pb
/
BINARY_VECTOR
---------------
[42,42] Starting from an int8 vector with 16 dimensions, we now have a binary vector with 16 dimensions in packed uint8 format.
Converting the SQL to a Scalar Macro
The next task will be to condense the query as much as possible and convert the CTE expressions to inline views so that we can use this sql in a macro. Then we will convert the inline views to a correlated subquery so that the sql can work for a scalar macro. Finally, we will compile the sql as a scalar macro function and test the conversions with some examples.
Begin by converting the CTE query blocks to nested inline views and combining expressions where it is possible.
design.2.01.convert-cte-to-inline-views.sql
with base as (
select
to_vector(
'[-22,-44,33,-13,26,-11,38,-7, -4,-12,112,-123,127,-1,17,-77]'
, 16, int8) as vec
)
select
to_vector(
json_serialize(
json_arrayagg(pb.uint_byte order by pb.byte#)
returning clob)
, *, binary) as my_binary_vector
from
(
select
p.byte#
, bin_to_num(p.b#1, p.b#2, p.b#3, p.b#4, p.b#5, p.b#6, p.b#7, p.b#8) as uint_byte
from
(
select
case sign(jt.dimval) when 1 then 1 else 0 end as dim_bitval
, mod(jt.dim#, 8) as bit#
, ceil(jt.dim#/8) as byte#
from
base b,
json_table (
json(vector_serialize(b.vec returning clob))
, '$[*]'
columns(
dim# for ordinality
, dimval number path '$'
)
) jt
) q
pivot(
max(q.dim_bitval) for bit# in (
1 as b#1, 2 as b#2, 3 as b#3, 4 as b#4
, 5 as b#5, 6 as b#6, 7 as b#7, 0 as b#8)
) p
) pb
/
MY_BINARY_VECTOR
--------------------
[42,42]Next we need to convert the query to a correlated subquery that begins with a vector embedding passed from the enclosing query. This will be the right form for the scalar macro.
design.2.02.convert-to-correlated-subquery.sql
with base as (
select
to_vector(
'[-22,-44,33,-13,26,-11,38,-7, -4,-12,112,-123,127,-1,17,-77]'
, 16, int8) as embedding
)
select
b.embedding as embedding
, (
--begin correlated subquery
select
to_vector(
json_serialize(
json_arrayagg(pb.uint_byte order by pb.byte#)
returning clob)
, *, binary)
from
(
select
p.byte#
, bin_to_num(p.b#1, p.b#2, p.b#3, p.b#4, p.b#5, p.b#6, p.b#7, p.b#8) as uint_byte
from
(
select
case sign(jt.dimval) when 1 then 1 else 0 end as dim_bitval
, mod(jt.dim#, 8) as bit#
, ceil(jt.dim#/8) as byte#
from
json_table (
--b.embedding is the correlation to the enclosing query
json(vector_serialize(b.embedding returning clob))
, '$[*]'
columns(
dim# for ordinality
, dimval number path '$'
)
) jt
) q
pivot(
max(q.dim_bitval) for bit# in (
1 as b#1, 2 as b#2, 3 as b#3, 4 as b#4
, 5 as b#5, 6 as b#6, 7 as b#7, 0 as b#8)
) p
) pb
--end correlated subquery
) as embedding_binary
from base b
/
EMBEDDING EMBEDDING_BINARY
------------------------------------------------------------ ----------------
[-22,-44,33,-13,26,-11,38,-7,-4,-12,112,-123,127,-1,17,-77] [42,42] This is the final form of the sql that we will use in the macro.
Create Scalar Macro Function
Create a scalar macro with the input vector as an input parameter. Return the sql from the subquery with the input parameter substituted for the beginning vector.
create.function.to_binary_vector.sql
create or replace function to_binary_vector(
p_input_vector in vector
) return varchar2
sql_macro (scalar)
is
begin
return q'~
select
to_vector(
json_serialize(
json_arrayagg(pb.uint_byte order by pb.byte#)
returning clob)
, *, binary)
from
(
select
p.byte#
, bin_to_num(p.b#1, p.b#2, p.b#3, p.b#4, p.b#5, p.b#6, p.b#7, p.b#8) as uint_byte
from
(
select
case sign(jt.dimval) when 1 then 1 else 0 end as dim_bitval
, mod(jt.dim#, 8) as bit#
, ceil(jt.dim#/8) as byte#
from
json_table (
json(vector_serialize(p_input_vector returning clob))
, '$[*]'
columns(
dim# for ordinality
, dimval number path '$'
)
) jt
) q
pivot(
max(q.dim_bitval) for bit# in (
1 as b#1, 2 as b#2, 3 as b#3, 4 as b#4
, 5 as b#5, 6 as b#6, 7 as b#7, 0 as b#8)
) p
) pb
~';
end to_binary_vector;
/Testing with some sample vectors shows that the conversion is still working with the prototyped subquery implemented as a scalar macro function.
test.function.to_binary_vector.sql
prompt check with binary for 42
select
to_binary_vector(
to_vector('[0,0,1,0,1,0,1,0]',*, int8)
) as binary_vector
/
BINARY_VECTOR
---------------
[42]
prompt check with the test vector that should return [42, 42]
with base as (
select
to_vector(
'[-22,-44,33,-13,26,-11,38,-7, -4,-12,112,-123,127,-1,17,-77]'
, *, int8) as vec
)
select
to_binary_vector(b.vec) as my_binary_vector
from base b
/
MY_BINARY_VECTOR
---------------
[42,42]Compare Macro To Sentence Transformers Quantization
Everything looks correct. We can test in Python to confirm that the macro is producing the same packed binary vectors that we can quantize using sentence transformers.
In the database, run the script generator to build a python test script. This will construct some vectors, convert them to binary using the macro, and script the python calls to do the same process showing the comparision as output. The sql script spools the python test script to a file.
generate.python-validate-quantization.py.sql
--generates the script python-validate-quantization.py to compare to python
/*
# python syntax example
# load Sentence Transformers
from sentence_transformers import SentenceTransformer
from sentence_transformers.quantization import quantize_embeddings
#
# vector quantized with macro in database
# dimensions: 8 format: FLOAT32
# [-1.23000003E-001,-6.53999984E-001,3.44999999E-001,-1.99999996E-002,7.88999975E-001,-5.66999972E-001,8.88000011E-001,0]
print("vector quantized with scalar macro: [42]")
# quantize same vector in sentence transformers:
embeddings = [[-1.23000003E-001,-6.53999984E-001,3.44999999E-001,-1.99999996E-002,7.88999975E-001,-5.66999972E-001,8.88000011E-001,0]]
embeddings_binary = quantize_embeddings(embeddings, precision="ubinary")
print("same vector quantized in python:", embeddings_binary)
*/Now run the python script in a python environment with the sentence transformers package installed.
python-validate-quantization.py
...
vector quantized with scalar macro: [42]
same vector quantized in python: [[42]]
vector quantized with scalar macro: [42,11]
same vector quantized in python: [[42 11]]
vector quantized with scalar macro: [42,10,235]
same vector quantized in python: [[ 42 10 235]]
vector quantized with scalar macro: [42,10,186,51]
same vector quantized in python: [[ 42 10 186 51]]The macro quantizes the vectors in the same way as sentence transformers quantize_embeddings function with ubinary precision would. We have validated that the scalar macro is producing correctly packed uint8 binary vectors.
Conclusion
Now we can convert vectors from other dimension formats to binary vectors with our scalar macro function – without leaving the database.
declare
v_float64 vector(*, float64);
v_float32 vector(*, float32);
v_int8 vector(*, int8);
v_binary vector(*, binary);
begin
v_float64 := to_vector(
'[
-6.28318530717958647692,
-1.41421356237309504880,
3.14159265358979323846,
-1.61803398874989484820,
2.71828182845904523536,
-0.56714329040978387299,
2.39996322972865332223,
-0.83462684167407318628
]'
, *, float64);
dbms_output.put_line(vector_serialize(v_float64));
select vector(v_float64, 8, float32) into v_float32;
dbms_output.put_line(vector_serialize(v_float32));
select vector(v_float32, 8, int8) into v_int8;
dbms_output.put_line(vector_serialize(v_int8));
select to_binary_vector(v_int8) into v_binary;
dbms_output.put_line(vector_serialize(v_binary));
end;
/
[-6.2831853071795862E+000,-1.4142135623730951E+000,3.1415926535897931E+000,-1.6180339887498949E+000,2.7182818284590451E+000,-5.6714329040978384E-001,2.3999632297286535E+000,-8.3462684167407319E-001]
[-6.28318548E+000,-1.41421354E+000,3.14159274E+000,-1.61803401E+000,2.71828175E+000,-5.67143261E-001,2.39996314E+000,-8.34626853E-001]
[-6,-1,3,-2,3,-1,2,-1]
[42]In the next post (Binary Vectors: Semantic Search) we will load large language models in ONNX format, set up test data and generate float vectors. Then we can convert these float vectors to binary vectors with the scalar macro.
This will allow us to do semantic searches using binary vectors and compare search accuracy and performance to the same searches with float vectors. We will also be able to explore the concept of a multi model reranking search.
–Anthony Harper
Practical Pl/Sql 
Leave a reply to Binary Vectors: Semantic Searches – Practical Pl/Sql Cancel reply