Smyekh David-West

Posted on Oct 23

Your First AI-Powered Search on Oracle Cloud: A Beginner's Guide to Vectors

#oracle #ai #database #tutorial

☁️ Pre-Flight Checklist

Before we taxi down the runway, here’s your flight plan. Keep this handy to navigate your flight path. Welcome aboard the cloud!

Enjoy your flight! ☁️

Let's be honest, as developers, we're used to precise, literal searches. If you query your database for status = 'completed', you get records where the status is exactly 'completed'. But what if you wanted to find things that are conceptually similar? What if you wanted to find a "quick automobile" when your data only contains "fast car"?

That's where the world of AI and vector search comes in, and it's not as scary or complicated as it sounds. In fact, if you know basic SQL, you're already halfway there.

Today, we'll break down the fundamentals of vector search and show you how to run your very first similarity search using nothing but your browser and Oracle's completely free SQL sandbox, a service that's part of the Oracle Cloud ecosystem.

The Core Idea: What is a Vector, Anyway?

Forget the complex math for a second. Think of a vector as GPS coordinates for meaning.

An AI model, called an "embedding model," reads a piece of data and converts it into a list of numbers. For our car example, imagine a vector where the first number is performance (higher is better) and the second is fuel consumption (lower is better).

A "sleek, powerful sports car" might get a vector like [9.5, 8] (High performance, high fuel use).
An "efficient, reliable hybrid" might get a vector like [6, 3] (Medium performance, low fuel use).

The goal of vector search is to find the "nearest neighbors" to a query vector by calculating the distance between these coordinates.

Your Free AI Sandbox: No Install Required

Go to https://livesql.oracle.com/
Sign in with a free Oracle account (or create one).
Once you login, you should see the page layout below

That's it! You have a full-featured Oracle Database ready for your commands.

Step 1: Create Your First Vector-Enabled Table

In Oracle Database 23ai, storing these "meaning coordinates" is incredibly simple with the new VECTOR data type.

Let's create a table to store our car profiles. Copy and paste this into your SQL worksheet:

CREATE TABLE car_profiles (
   id NUMBER PRIMARY KEY,
   car_description VARCHAR2(100),
   performance_consumption_vector VECTOR
);

To run the statement after pasting use crtl + Enter or ⌘ + Enter

Once you create a table, FreeSQL automatically saves it for your next session.

Step 2: Insert Your Car Profiles

A Quick Note: In a real application, an AI model would generate these vectors. For the purpose of this demonstration, we'll create simple, 2-dimensional vectors ourselves to focus on learning the powerful database-side of vector search.

Here are the profiles we'll be working with, where the vector represents [Performance, Fuel Consumption].

ID	Description	Vector	Meaning
1	"Perfect high-performance hybrid"	`[10,2]`	Ideal car profile
2	"Sporty fuel-efficient coupe"	`[9,2.5]`	Slightly less powerful but efficient
3	"Old gas-guzzling truck"	`[4,10]`	Poor fuel economy
4	"Balanced midrange sedan"	`[7,5]`	Decent balance
5	"Economy compact car"	`[6,3]`	Modest power, great fuel economy
6	"Luxury performance SUV"	`[9.5,6]`	Powerful but thirsty
7	"Underpowered city car"	`[3,4]`	Low performance, moderate efficiency

Let's insert this data.

INSERT ALL
  INTO car_profiles VALUES (1, 'Perfect high-performance hybrid', VECTOR('[10, 2]'))
  INTO car_profiles VALUES (2, 'Sporty fuel-efficient coupe', VECTOR('[9, 2.5]'))
  INTO car_profiles VALUES (3, 'Old gas-guzzling truck', VECTOR('[4, 10]'))
  INTO car_profiles VALUES (4, 'Balanced midrange sedan', VECTOR('[7, 5]'))
  INTO car_profiles VALUES (5, 'Economy compact car', VECTOR('[6, 3]'))
  INTO car_profiles VALUES (6, 'Luxury performance SUV', VECTOR('[9.5, 6]'))
  INTO car_profiles VALUES (7, 'Underpowered city car', VECTOR('[3, 4]'))
SELECT * FROM DUAL;

-- To confirm that the data has been inserted into the table
SELECT * FROM car_profiles
ORDER BY id;

In Oracle SQL:

A single-row INSERT INTO ... VALUES (...) statement doesn’t need a source because it’s one direct action.
A multi-row insert ('INSERT ALL') is conceptually a query-based insert.

When you want to insert multiple literal rows (not coming from another table), you don’t have a real source. Oracle solves that by letting you use the DUAL table, which is a built-in one-row, one-column table.

So SELECT * FROM DUAL acts as a placeholder that makes Oracle happy.

Step 3: Find Similar Cars with SQL

The key to vector search is the VECTOR_DISTANCE function. It's like a ruler that measures the distance between our query vector and all the vectors in our table. Let's use it to rank all cars in our database by how similar they are to our "Ideal Car Profile" ([10, 2]). A lower distance means a better match.

SELECT car_description, ROUND(VECTOR_DISTANCE(VECTOR('[10, 2]'), performance_consumption_vector), 2) AS similarity_score
FROM car_profiles
ORDER BY similarity_score ASC

The ROUND()function makes numbers easier to read by limiting how many decimal places are shown.

ASC stands for ASCENDING order — it’s part of the ORDER BY clause in SQL.

Understanding the similarity scores

In the previous query, we compared every car’s vector against our “ideal” car vector [10, 2].

The VECTOR_DISTANCE function measures how far apart two vectors are, just like measuring the straight-line distance between two points on a map.

A lower number means the car is more similar to our ideal profile:

Almost identical: 0.00
Very close: 0.05
Somewhat different: 0.25
Quite different: 0.45

So in our results:

The “Perfect high-performance hybrid” and “Sporty fuel-efficient coupe” are closest to the ideal (0.00).
The “Economy compact car” and “Luxury performance SUV” are also good matches but slightly further.
The “Old gas-guzzling truck” is farthest away, it’s the least similar in performance and fuel use.

This simple ranking shows the core idea behind AI-powered vector search: finding items that are conceptually similar, not just textually identical.

From SQL to Application Logic with PL/SQL

SQL is for asking questions. But what about building application logic? For that, we turn to PL/SQL, Oracle's native programming language that lives inside the database. It lets you use variables, create logic, and handle errors, turning the database into a powerful processing engine.

What is PL/SQL?

PL/SQL (Procedural Language/SQL) was designed to overcome the limitations of pure SQL by adding procedural constructs—variables, loops, and error handling. Its key advantage is its tight integration with the SQL engine, which eliminates network overhead and results in significant performance gains for data-intensive operations.

The Anatomy of PL/SQL

If you're coming from a language like Python, the structure of a basic PL/SQL block will feel surprisingly familiar:

DECLARE
  -- This is your setup area where you declare variables.
BEGIN
  -- This is your main execution block where logic runs.
EXCEPTION
  -- This is your "try...except" block for handling errors.
END;
/

PL/SQL in Action: An Interactive Similarity Report

Let's apply this structure to build a simple, interactive report. The script will ask you to pick a car's ID and then generate a similarity report comparing that car to our "Ideal Car Profile."

Copy and paste this entire block into your worksheet and run it.

DECLARE
  -- 1. Our benchmark: the "Perfect Car Profile"
  ideal_car_profile_vector VECTOR := VECTOR('[10, 2]'); 

  -- 2.  Variables to hold the data we fetch from the table
  selected_car_vector VECTOR;
  selected_car_desc VARCHAR2(100);

  -- 3. Variable to hold the calculated distance (similarity score)
  similarity_score NUMBER;

  -- 4. This will prompt the user to enter a car ID to compare (e.g. 2 for "Sporty fuel-efficient coupe")
  car_id_to_check NUMBER := &car_id_to_compare;

BEGIN
  -- Fetch the chosen car’s vector and description from the table
  SELECT performance_consumption_vector, car_description
  INTO selected_car_vector, selected_car_desc
  FROM car_profiles
  WHERE id = car_id_to_check;

  -- Calculate how far this car is from the ideal
  similarity_score := VECTOR_DISTANCE(ideal_car_profile_vector, selected_car_vector);

  -- Display a readable report
  DBMS_OUTPUT.PUT_LINE('--- Car Similarity Report ---');
  DBMS_OUTPUT.PUT_LINE('Selected Car:      ' || selected_car_desc);
  DBMS_OUTPUT.PUT_LINE('Ideal Profile:     [10, 2] (High performance, Low fuel use)');
  DBMS_OUTPUT.PUT_LINE('Car''s Profile:        ' || TO_CHAR(selected_car_vector)); -- TO_CHAR converts the vector into a readable string
  DBMS_OUTPUT.PUT_LINE('Similarity Score:  ' || ROUND(similarity_score, 2));
  DBMS_OUTPUT.PUT_LINE('(A lower score means a better match!)');

END;
/

When you run this, it will prompt you: Enter value for car_id_to_compare:.

The & syntax is a feature of the SQL client that prompts you for input, allowing us to make the script interactive. And || is used to concatenate strings.

Enter 2 and press Enter. The script will fetch the "Sporty fuel-efficient coupe" and generate a report showing its low similarity score, proving it's a great match!

To make this even clearer, let's imagine a real-world project: building a "Similar Products" feature for an online bookstore.

The Benchmark: A customer is looking at the page for "Project Hail Mary," a popular science-fiction book. The vector for this book, which represents its genre, writing style, and core themes (space, problem-solving, humor), becomes our ideal_profile_vector.
The Candidates: Your database contains vectors for thousands of other books.
The Logic: In the background, the application runs a process just like our simple PL/SQL script. It calculates the VECTOR_DISTANCE between the "Project Hail Mary" vector and all other books in the catalog.
The Result: The books with the lowest distance score are displayed to the customer under a "You Might Also Like..." section. The system would correctly suggest other hard sci-fi novels or books with similar narrative styles, rather than a random cookbook or history textbook, because their "meaning coordinates" are closest.

Our simple, interactive script, which compares one benchmark to one new item, is the tiny engine that powers each one of those individual recommendations. By running that logic for many items, you build a complete feature.

Next Steps: Performance and Other Metrics

Distance Metrics: The default distance metric is COSINE, which is excellent for semantic text search. For our coordinate-based example, EUCLIDEAN (the straight-line distance) is also a great choice. You can specify it like this: VECTOR_DISTANCE(v1, v2, EUCLIDEAN).
Indexing for Speed: Searching through millions of vectors requires an index. Just like a book index helps you find a topic instantly, a vector index helps the database find the "nearest neighbors" without a full table scan.
This is critical for production applications, as it’s the difference between a sub-second response and a query that takes minutes on a large dataset.

CREATE VECTOR INDEX car_profiles_idx ON car_profiles (performance_consumption_vector)
ORGANIZATION INMEMORY NEIGHBOR GRAPH;

Conclusion: Your Journey into AI on OCI

Congratulations! You've just taken your first and most important step into the world of AI-powered search.
You've seen that vectors are just coordinates for meaning, and that Oracle makes it incredibly simple to store, manage, and query them using familiar SQL and PL/SQL.

This skill is a cornerstone for building modern, intelligent applications. Whether you're working with text, images, or any other unstructured data, the ability to find "conceptually similar" items is a superpower. By integrating these AI capabilities directly into the database, Oracle Cloud Infrastructure (OCI) provides a powerful, scalable, and secure platform for your next generation of smart applications. Keep experimenting! Happy Building! ☁️

The Big Picture: Technical Architecture

While our example focused on the core SQL and PL/SQL commands, it's helpful to see how AI Vector Search fits into a larger application architecture. The diagram at the link below from Oracle's official documentation illustrates how an application, the database, and embedding models work together to provide similarity search on your data.

Oracle AI Vector Search Technical Architecture Diagram

Source: Oracle

Next Steps: Generating Vectors in Your Application

In this guide, we manually created vectors to focus on the search functionality. In a real-world application, you would use an AI "embedding model" to automatically generate these vectors from your data (like text descriptions, image pixels, or other attributes).

Ready to take the next step? The official Oracle documentation provides a detailed guide: Oracle AI Vector Search User's Guide. This is the perfect follow-up to what we've covered here.