{"id":13386,"date":"2024-06-15T20:45:43","date_gmt":"2024-06-15T18:45:43","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=13386"},"modified":"2024-12-06T13:04:47","modified_gmt":"2024-12-06T12:04:47","slug":"islandsql-episode-8-whats-new-in-oracle-database-23-4","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2024\/06\/15\/islandsql-episode-8-whats-new-in-oracle-database-23-4\/","title":{"rendered":"IslandSQL Episode 8: What’s New in Oracle Database 23.4?"},"content":{"rendered":"\n

Introduction<\/h2>\n\n\n\n

In the last episode<\/a>, we looked at some features in PostgreSQL which I miss in the Oracle Database. The IslandSQL<\/a> grammar now covers PL\/SQL and the related DDL statements. The implementation was more complex than expected, mainly because of the incompatibilities between PostgreSQL and the Oracle Database. I will probably deal with this topic in a future blog post.<\/p>\n\n\n\n

Now I’d like to talk about the new features in Oracle Database 23ai. Not about all features in the New Features Guide<\/a>, but only about some changes since the release of 23.3, which was known as 23c. It’s hard to find changes from 23.3 to 23.4 in the documentation. So, I guess it’s worth a blog post. I focus on the features that are relevant to the IslandSQL grammar. In other words, the interesting ones from a developer’s perspective.<\/p>\n\n\n\n

1. Vector Data Type<\/h2>\n\n\n\n

A vector is a number array for which you can optionally define the number of dimensions (size) and the data type (int8, float32 or float64) of the dimension values. This data type is the basis for the vector search functionality.<\/p>\n\n\n\n

Here’s a slightly amended example from the documentation<\/a> creating and populating a table with a vector data type.<\/p>\n\n\n\n

1) Table with vector column<\/span><\/path><\/path><\/svg><\/span>
drop<\/span> <\/span>table<\/span> <\/span>if<\/span> <\/span>exists<\/span> galaxies purge;<\/span><\/span>\ncreate<\/span> <\/span>table<\/span> galaxies (<\/span><\/span>\n   id        <\/span>number<\/span>             <\/span>not<\/span> <\/span>null<\/span> <\/span>primary key<\/span>,<\/span><\/span>\n   <\/span>name<\/span>      <\/span>varchar2<\/span>(<\/span>10<\/span> <\/span>char<\/span>)  <\/span>not<\/span> <\/span>null<\/span> <\/span>unique<\/span>,<\/span><\/span>\n   embedding vector(<\/span>5<\/span>, int8)    <\/span>not<\/span> <\/span>null<\/span>, <\/span>-- 5 dimensions, stored as int8<\/span><\/span>\n   doc       <\/span>varchar2<\/span>(<\/span>120<\/span> <\/span>char<\/span>) <\/span>not<\/span> <\/span>null<\/span><\/span>\n);<\/span><\/span>\n<\/span>\ninsert<\/span> <\/span>into<\/span> galaxies <\/span><\/span>\n   (id, <\/span>name<\/span>, embedding, doc)<\/span><\/span>\nvalues<\/span> <\/span><\/span>\n   (<\/span>1<\/span>, <\/span>'M31'<\/span>,     <\/span>'[0,2,2,0,0]'<\/span>, <\/span>'Messier 31 is a barred spiral galaxy in the Andromeda constellation which has a lot of barred spiral galaxies.'<\/span>),<\/span><\/span>\n   (<\/span>2<\/span>, <\/span>'M33'<\/span>,     <\/span>'[0,0,1,0,0]'<\/span>, <\/span>'Messier 33 is a spiral galaxy in the Triangulum constellation.'<\/span>),<\/span><\/span>\n   (<\/span>3<\/span>, <\/span>'M58'<\/span>,     <\/span>'[1,1,1,0,0]'<\/span>, <\/span>'Messier 58 is an intermediate barred spiral galaxy in the Virgo constellation.'<\/span>),<\/span><\/span>\n   (<\/span>4<\/span>, <\/span>'M63'<\/span>,     <\/span>'[0,0,1,0,0]'<\/span>, <\/span>'Messier 63 is a spiral galaxy in the Canes Venatici constellation.'<\/span>),<\/span><\/span>\n   (<\/span>5<\/span>, <\/span>'M77'<\/span>,     <\/span>'[0,1,1,0,0]'<\/span>, <\/span>'Messier 77 is a barred spiral galaxy in the Cetus constellation.'<\/span>),<\/span><\/span>\n   (<\/span>6<\/span>, <\/span>'M91'<\/span>,     <\/span>'[0,1,1,0,0]'<\/span>, <\/span>'Messier 91 is a barred spiral galaxy in the Coma Berenices constellation.'<\/span>),<\/span><\/span>\n   (<\/span>7<\/span>, <\/span>'M49'<\/span>,     <\/span>'[0,0,0,1,1]'<\/span>, <\/span>'Messier 49 is a giant elliptical galaxy in the Virgo constellation.'<\/span>),<\/span><\/span>\n   (<\/span>8<\/span>, <\/span>'M60'<\/span>,     <\/span>'[0,0,0,0,1]'<\/span>, <\/span>'Messier 60 is an elliptical galaxy in the Virgo constellation.'<\/span>),<\/span><\/span>\n   (<\/span>9<\/span>, <\/span>'NGC1073'<\/span>, <\/span>'[0,1,1,0,0]'<\/span>, <\/span>'NGC 1073 is a barred spiral galaxy in Cetus constellation.'<\/span>);<\/span><\/span>\ncommit<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The vector in this example has 5 dimensions with the following meaning:<\/p>\n\n\n\n
    \n
  1. Number of occurrences of intermediate<\/code> in the doc<\/code> column<\/li>\n\n\n\n
  2. Number of occurrences of barred<\/code> in the doc<\/code> column<\/li>\n\n\n\n
  3. Number of occurrences of spiral<\/code> in the doc<\/code> column<\/li>\n\n\n\n
  4. Number of occurrences of giant<\/code> in the doc<\/code> column<\/li>\n\n\n\n
  5. Number of occurrences of elliptical<\/code> in the doc<\/code> column<\/li>\n<\/ol>\n\n\n\n
    2. Vector Functions and PL\/SQL Packages<\/h2>\n\n\n\n
    The vector_distance<\/a> function is a key functionality for similarity searches. Let’s say we want to see galaxies that are similar to NGC1073<\/code> (barred<\/code> and spiral<\/code>). The following query shows how vector functions can help to get this result:<\/p>\n\n\n\n
    2) Different vector_distance metrics in action<\/span><\/path><\/path><\/svg><\/span>
    with<\/span> ngc1073 <\/span>as<\/span> (<\/span>select<\/span> vector(<\/span>'[0,1,1,0,0]'<\/span>, <\/span>5<\/span>, int8) <\/span>as<\/span> query_vector)<\/span><\/span>\nselect<\/span> <\/span>name<\/span>, <\/span><\/span>\n       <\/span>round<\/span>(vector_distance(embedding, query_vector, cosine), <\/span>3<\/span>) <\/span>as<\/span> cosine_distance,<\/span><\/span>\n       <\/span>round<\/span>(vector_distance(embedding, query_vector, dot), <\/span>3<\/span>) <\/span>as<\/span> inner_product,<\/span><\/span>\n       <\/span>round<\/span>(vector_distance(embedding, query_vector, euclidean), <\/span>3<\/span>) <\/span>as<\/span> l2_distance,<\/span><\/span>\n       <\/span>round<\/span>(vector_distance(embedding, query_vector, euclidean_squared), <\/span>3<\/span>) <\/span>as<\/span> l2_squared,<\/span><\/span>\n       <\/span>round<\/span>(vector_distance(embedding, query_vector, hamming), <\/span>3<\/span>) <\/span>as<\/span> hamming_distance,<\/span><\/span>\n       <\/span>round<\/span>(vector_distance(embedding, query_vector, manhattan), <\/span>3<\/span>) <\/span>as<\/span> l1_distance<\/span><\/span>\n  <\/span>from<\/span> galaxies, ngc1073<\/span><\/span>\n <\/span>order by<\/span> cosine_distance;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/path><\/path><\/svg><\/span>
    NAME       COSINE_DISTANCE INNER_PRODUCT L2_DISTANCE L2_SQUARED HAMMING_DISTANCE L1_DISTANCE<\/span><\/span>\n---------- --------------- ------------- ----------- ---------- ---------------- -----------<\/span><\/span>\nM31                      0            -4       1.414          2                2           2<\/span><\/span>\nM77                      0            -2           0          0                0           0<\/span><\/span>\nM91                      0            -2           0          0                0           0<\/span><\/span>\nNGC1073                  0            -2           0          0                0           0<\/span><\/span>\nM58                   .184            -2           1          1                1           1<\/span><\/span>\nM63                   .293            -1           1          1                1           1<\/span><\/span>\nM33                   .293            -1           1          1                1           1<\/span><\/span>\nM60                      1             0       1.732          3                3           3<\/span><\/span>\nM49                      1             0           2          4                4           4<\/span><\/span>\n<\/span><\/span>\n9 rows selected.<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    M77<\/code> and M91<\/code> have identical vectors and they are expected to be very similar. However, M31<\/code> is interesting. It has a similar shape to NGC1073<\/code> but the words barred<\/code> and spiral<\/code> appear twice. This is a good match only for some distance metrics.<\/p>\n\n\n\n
    A conventional query, e.g. based on Oracle Text might be good enough to find similar galaxies. However, if you have vectors with tons of dimensions then a vector similarity search might become appealing. Especially, since you can index vector columns to speed up your queries.<\/p>\n\n\n\n
    The following SQL functions are provided for the vector data type in the Oracle Database:<\/p>\n\n\n\n