Run Oracle database 23ai in your laptop with podman or docker and start playing with vectors easily

If you are a developer take a look how easy is to deploy an Oracle 23ai instance and start working with new features such vectors in few minutes.

Create a directory in your disk, for example:

mkdir ~/u01/data

Put the appropriate values in the command below, copy to your terminal and hit enter (sorry if you need to remove some extra decoration in the callout, the cow wanted to chat a little bit…):

  _________________________________
 /                                 \
| docker run --name 23ai            |
| -p 1521:1521                      |
| -e ORACLE_PWD=W31c0m31##AI2024    |
| -e ENABLE_ARCHIVELOG=false        |
| -e ENABLE_FORCE_LOGGING=false     |
| -v ~/u01/data:/opt/oracle/oradata |
| container-registry.oracle.com/    |
| database/free:latest              |
 \                                 /
  =================================
                                 \
                                  \
                                    ^__^
                                    (oo)\_______
                                    (__)\       )\/\
                                        ||----w |
                                        ||     ||

More details about the docker image available here.

Download and install sqlcl.

Connect to instance as sys:

sqlcl/bin/sql sys@localhost:1521/FREE as sysdba

Change to pluggable database and create a user with some privileges in it:

alter session set container=FREEPDB1;
create user vector identified by W31c0m31##2024 default tablespace user temporary tablespace temp quota unlimited on users;
grant connect, resource to vector;                   

Disconnect and connect with user vector:

sqlcl/bin/sql vector@localhost:1521/FREEPDB1

Create some stuff data:

CREATE TABLE galaxies (id NUMBER, name VARCHAR2(50), doc VARCHAR2(500), embedding VECTOR);
INSERT INTO galaxies VALUES (1, 'M31', 'Messier 31 is a barred spiral galaxy in the Andromeda constellation which has a lot of barred spiral galaxies.', '[0,2,2,0,0]');
INSERT INTO galaxies VALUES (2, 'M33', 'Messier 33 is a spiral galaxy in the Triangulum constellation.', '[0,0,1,0,0]');
INSERT INTO galaxies VALUES (3, 'M58', 'Messier 58 is an intermediate barred spiral galaxy in the Virgo constellation.', '[1,1,1,0,0]');
INSERT INTO galaxies VALUES (4, 'M63', 'Messier 63 is a spiral galaxy in the Canes Venatici constellation.', '[0,0,1,0,0]');
INSERT INTO galaxies VALUES (5, 'M77', 'Messier 77 is a barred spiral galaxy in the Cetus constellation.', '[0,1,1,0,0]');
INSERT INTO galaxies VALUES (6, 'M91', 'Messier 91 is a barred spiral galaxy in the Coma Berenices constellation.', '[0,1,1,0,0]');
INSERT INTO galaxies VALUES (7, 'M49', 'Messier 49 is a giant elliptical galaxy in the Virgo constellation.', '[0,0,0,1,1]');
INSERT INTO galaxies VALUES (8, 'M60', 'Messier 60 is an elliptical galaxy in the Virgo constellation.', '[0,0,0,0,1]');
INSERT INTO galaxies VALUES (9, 'NGC1073', 'NGC 1073 is a barred spiral galaxy in Cetus constellation.', '[0,1,1,0,0]');
COMMIT;

Perform some queries and see the difference (consult the documentation to understand distance operators):

select id, TO_VECTOR('[1,1,1,1,1]') <-> embedding d from galaxies order by TO_VECTOR('[1,1,1,1,1]') <-> embedding
/
select id, TO_VECTOR('[1,1,1,1,1]') <=> embedding d from galaxies order by TO_VECTOR('[1,1,1,1,1]') <=> embedding
/
select id, TO_VECTOR('[1,1,1,1,1]') <#> embedding d from galaxies order by TO_VECTOR('[1,1,1,1,1]') <=> embedding
/

That’s all, in future post we’ll create vectors using a model.