Building a Vector Store in Oracle Autonomous Database: Efficient Similarity Search at Scale

In the age of AI and context-based applications where vector embeddings are used, optimising the storage and creation of vector stores has become a necessity rather than an optional step. Traditionally, these embeddings are stored on application servers or custom storage layers, often requiring extra engineering to scale and maintain.

Oracle Autonomous Database (ADB) introduces a game-changing approach: storing vector embeddings directly in the database. This allows developers to perform not only basic similarity search but also Multi-Vector and Hybrid Search.

In this article we will elaborate the following:

• Advantages of Using Oracle ADB for Vector Storage
• How to create a vector store in Oracle Autonomous Database
• How to use it inside a basic RAG Application built with Langchain

Advantages of Using Oracle ADB

1. No need to worry about scaling: Traditionally, vector stores are stored on application servers, if the vector store size increases drastically to cover the application requirements, it becomes very hard to handle it inside the server. In ADB, the database automatically handles storage growth and parallel computation. You just insert vectors like any other data, and it can scale with you.
2. Similarity search becomes native and efficient: Oracle ADB supports different types of search , such as Multi Vector and Hybrid Search . This alleviates the pain of writing custom code to perfom other types of search other than basic similarity search
3. You pay for managed resources, not servers: On a server, you must provision enough memory to hold all vectors even if the server is idle sometimes. With ADB, you only pay for the resources you actually use, and it can auto-scale.
4. Access Control and Privileges: On a server-based system, unless you implement your own authentication layer, anyone with access to the server could potentially read or modify vectors. ADB allows you to define who can access what data using roles and privileges.

With the advantages in mind, let’s dive into how we can actually set up a vector store in Oracle ADB and use it to build a RAG.

Prerequisites

You will need to have an instance or Oracle Autonomous Database, you can follow the steps provided in this *article *to set it up.

The database admin needs to grant the EXECUTE privilege on theDBMS_CLOUDpackages using the following commands:

GRANT execute on DBMS_CLOUD to ADB_USER;

You need to create a credential for a third Party Embedding model you will use for vectorizing the user query to be able to perform the vector search. You can check this *article *to know which providers are supported by Oracle ADB. In this tutorial I will use a model from Oracle Cloud Infrastructure (OCI) Generative AI so I created the credential as follows:

BEGIN
  DBMS_VECTOR.CREATE_CREDENTIAL(
    credential_name => 'OCI_CRED',
    params => JSON_OBJECT(
      'user_ocid'       VALUE '<your_user_ocid>',
      'tenancy_ocid'    VALUE '<your_tenancy_ocid>',
      'compartment_ocid' VALUE '<your_compartment_ocid>',
      'private_key'     VALUE '<your_private_key>',
      'fingerprint'     VALUE '<your_fingerprint>'
    )
  );
END;
/

The admin also needs to grant theCONNECT privilege to allow connection to the third-party host.

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
  host => 'https://your-GENAI-URl',
  ace  =>  xs$ace_type(privilege_list => xs$name_list('connect', 'resolve'),
                       principal_name => 'ADB_USER',
                       principal_type => xs_acl.ptype_db));

Creating a table to store the vectors

To build a RAG (Retrieval-Augmented Generation) system, we need a vector store. We’ll create a table to store text chunks along with their source document ID (the document name), the chunk ID (its position in the document), and the corresponding vector using the following SQL CREATE TABLE statement.

CREATE TABLE DOCUMENT_EMBEDDINGS
(
    "ID" NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    "DOCUMENT_ID" VARCHAR2(100),
    "CHUNK_ID" NUMBER,
    "DOCUMENT_TEXT" CLOB,
    "EMBEDDING" VECTOR(1024, FLOAT32)
);

Populating the table

We will write a basic Python code to perform the preprocessing of documents and load them into the database.

first we need to have the following imports

import sys
from langchain_community.document_loaders import PyPDFLoader
import os
from langchain_community.embeddings import OCIGenAIEmbeddings
from dotenv import load_dotenv
import oracledb

We need to define these constants required for the database and for using Oracle GenAI Models

USER = os.getenv("USER")
PASSWORD = os.getenv("PASSWORD")
TNS_ALIAS = os.getenv("TNS_ALIAS")
WALLET_LOCATION = os.getenv("WALLET_LOCATION")
WALLET_PASSWORD = os.getenv("WALLET_PASSWORD")
SERVICE_ENDPOINT = os.getenv("SERVICE_ENDPOINT")
COMPARTMENT_ID = os.getenv("COMPARTMENT_ID")

Initialize the model that will be used to embed the text.

# Initialize OCIGenAIEmbeddings 
embeddings_model = OCIGenAIEmbeddings(
    model_id="cohere.embed-multilingual-v3.0",
    service_endpoint=SERVICE_ENDPOINT,
    compartment_id=COMPARTMENT_ID,
)

We then need to define a function to load and split PDF Files

def loadPDFFile(file_paths):
    pages = []
    for file_path in file_paths:
        loader = PyPDFLoader(file_path)
        pages.extend(loader.load_and_split())
    return pages

Define a function that will be used to connect to Oracle ADB to be able to insert data into it directly

def create_autonomous_db_connection( max_retries: int = 5) -> object:
    attempt = 0
    while attempt < max_retries:
        print(f"Attempt {attempt + 1}/{max_retries}: Creating database connection")
        try:
            connection = oracledb.connect(
                config_dir=WALLET_LOCATION, 
                user=USER,     
                password=PASSWORD,
                dsn=TNS_ALIAS,
                wallet_location=WALLET_LOCATION,
                wallet_password=WALLET_PASSWORD)
            cursor = connection.cursor()
            print("Connected to Oracle database successfully")
            return cursor
        except Exception as e:
            print(f"Failed to create Oracle database connection on attempt {attempt + 1}: {e}")
            attempt += 1
            if attempt < max_retries:
                print(f"Retrying in to connect to the database")
            else:
                print("Exceeded maximum retry attempts. Exiting.")
                sys.exit(1)

Finally define the function that puts everything together. The embed_and_insert_chunks function takes a list of file paths, loads each document, splits it into chunks, generates embeddings for each chunk using a vector model, and inserts the results into the document_embeddings table in Oracle. ADB.

def embed_and_insert_chunks(file_paths):
    # Connect to Oracle
    cursor = create_autonomous_db_connection()

    for file_path in file_paths:
        # Load chunks for this document
        pages = loadPDFFile([file_path])
        doc_id = file_path.replace('rag_data/', '')  # document ID
        chunk_id = 1  # reset for each document

        for page in pages:
            text = page.page_content

            # Generate embedding
            try:
                vector = embeddings_model.embed_query(text)  # 1024-dim
            except Exception as e:
                print(f"Embedding failed for doc {doc_id}, chunk {chunk_id}: {e}")
                chunk_id += 1
                continue

            # Insert into Oracle
            try:
                sql = """
                    INSERT INTO document_embeddings
                    (document_id, chunk_id, document_text, embedding)
                    VALUES (:document_id, :chunk_id, :document_text, VECTOR(:embedding));
                """
                cursor.execute(sql, {
                    'document_id': doc_id,
                    'chunk_id': chunk_id,
                    'document_text': text,
                    'embedding': str(vector)  # works if your driver accepts string VECTOR binding
                })
            except Exception as e:
                print(f"Failed to insert doc {doc_id}, chunk {chunk_id} into DB: {e}")

            chunk_id += 1  # increment per chunk of this document

    # Commit and close
    cursor.connection.commit()
    cursor.connection.close()
    print("All chunks embedded and inserted successfully")

Call the function with the documents file path

file_paths = ['rag_data/ChapterSeven_en.pdf',
                        'rag_data/ChapterSix_en.pdf',
                        'rag_data/ChapterTwelve_en.pdf',
                        ]
embed_and_insert_chunks(file_paths)

After running this code, you should see that the document_embeddings table has been populated with data.

Query the documents using Similiraity Search

Now that we have populated the table with all the documents and their corresponding embeddings, the next step is to query these documents to begin implementing a Retrieval-Augmented Generation (RAG) pipeline.

To enable the application to query the database, we need to create an ORDS REST endpoint that exposes the required data. This endpoint will allow the application we build later to access the database programmatically.

The ORDS endpoint is implemented as a POST request and uses the following PL/SQL body to process the incoming query, perform a similarity search, and return the most relevant documents.

BEGIN
    OPEN :output FOR
        SELECT document_text
        FROM document_embeddings
        ORDER BY VECTOR_DISTANCE(
                   embedding,
                   DBMS_VECTOR.UTL_TO_EMBEDDING(
                       :user_question,
                       JSON('{
                           "provider": "ocigenai",
                           "credential_name": "OCI_CRED",
                           "url": "<your_oci_url>/20231130/actions/embedText",
                           "model": "cohere.embed-multilingual-v3.0",
                           "batch_size": 10
                       }')
                   ),
                   EUCLIDEAN
               )
        FETCH FIRST 4 ROWS ONLY;
END;

For more information on how to use UTL_TO_EMBEDDING you can refer to this *link *This endpoint will take the user question, embed it using the same model that was previously used to embed the documents, performs a similarity search to retrieve the top four most similar documents, and returns their corresponding text.

We then need to implement a function in Python that calls this endpoint as follows.

base_url = os.getenv("BASE_URL")
print(f"Base URL: {base_url}")
def get_relevant_documents(query):
    """
    Fetch relevant documents from an external API based on the provided query.

    Args:
        query (str): The search query to find relevant documents.
        api_url (str): The URL of the external API to fetch documents from.

    Returns:
        list: A list of relevant documents returned by the API.
    """
    try:
        response = requests.post(f"{base_url}/rag_demo/similarity_search", json={"user_question": query})
        response.raise_for_status()  # Raise an error for bad responses
        documents = response.json().get("output", [])
        return documents
    except requests.RequestException as e:
        print(f"An error occurred while fetching documents: {e}")
        return []

Note: In this tutorial the vector embeddings are generated by accessing a third part model. There are other methods supported by Oracle that can be found **here.**

RAG implementation

The next step is to build a RAG system that uses the database as a vector store to retrieve relevant context for answering user questions.

import os
from dotenv import load_dotenv
import requests
from langchain_openai.chat_models import ChatOpenAI
from langchain_core.output_parsers import StrOutputParser
from langchain_core.runnables import RunnableParallel, RunnablePassthrough, RunnableLambda, RunnableBranch
from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder, PromptTemplate
load_dotenv()
base_url = os.getenv("BASE_URL")

def get_relevant_documents_from_db(query):
    """
    Fetch relevant documents from an external API based on the provided query.

    Args:
        query (str): The search query to find relevant documents.
        api_url (str): The URL of the external API to fetch documents from.

    Returns:
        list: A list of relevant documents returned by the API.
    """
    try:
        response = requests.post(f"{base_url}/rag_demo/similarity_search", json={"user_question": query})
        response.raise_for_status()  # Raise an error for bad responses
        documents = response.json().get("output", [])
        return documents
    except requests.RequestException as e:
        print(f"An error occurred while fetching documents: {e}")
        return []
def combine_db_docs(docs):
    combined_content = "\n\n".join(doc['document_text'] for doc in docs)
    return combined_content
# Define a prompt template for condensing a chat history and follow-up question
standalone_template_en = """ Given the following conversation and the follow-up question, rephrase the follow up question to be a standalone question only if it may be related to previous conversation. rephrase it using it's original language.
chat History :
{chat_history}
Follow Up Input: {question}
Standalone question:"""   
STANDALONE_QUESTION_PROMPT_EN = PromptTemplate.from_template(standalone_template_en)

rag_template_en = """You are an assistant for a company called Kites having a conversation with a human about the documents retreived.\
    Use the following pieces of retrieved documents to answer the user question.\
    If the user question is considered as 'polite greetings', 'small talk', 'social niceties', or 'pleasantries', reply with a related answer to the conversation.\
    If the user question doesn't relate to Context retreived don't try to make up an answer just say 'I am designed to answer questions about Kites HR Policies only'.\
    Formulate your response in clear points.\

    --------- 
    DOCUMENTS: 
    {context}
    ---------
    """
# Create a ChatPromptTemplate for answer synthesis
ANSWER_PROMPT_EN = ChatPromptTemplate.from_messages(
    [
        ("system", rag_template_en),
        MessagesPlaceholder(variable_name="chat_history"),
        ("user", "{question}"),
    ]
)
# Define a branch for the search query in the conversational retrieval process
_search_query = RunnableBranch(
    # If input includes chat_history, condense it with the follow-up question
    (
        RunnableLambda(lambda x: bool(x.get("chat_history"))).with_config(
            run_name="HasChatHistoryCheck"
        ),  # Condense follow-up question and chat into a standalone_question
        RunnablePassthrough.assign(
            chat_history=lambda x: x["chat_history"],
            question = lambda x: x["question"]
        )
        |  STANDALONE_QUESTION_PROMPT_EN 
        | ChatOpenAI(model="gpt-4o",temperature=0, verbose=True)
        | StrOutputParser(),
    ),
    # Else, we have no chat history, so just pass through the question
    RunnableLambda(lambda x: x["question"]),
).with_config({"run_name": "searchQueryChain"})

# Define runnables for parallel processing of inputs
_inputs = RunnableParallel(
    {
        "question": lambda x: x["question"],
        "chat_history": lambda x: x["chat_history"],
    }
) | RunnablePassthrough.assign(
    context = (_search_query | get_relevant_documents_from_db | combine_db_docs).with_config({"run_name": "contextChain"}) ,   
)

# Define the final chain of runnables for answer synthesis
_gen_chain = ANSWER_PROMPT_EN  | ChatOpenAI(model="gpt-4o", temperature=0,streaming=True).with_config({"run_name": "generationChain"})  | StrOutputParser()
chain =  _inputs | _gen_chain

Final Application

Finally, we can build a simple web application using FastAPI as the backend and a basic HTML, CSS, and JavaScript frontend. You can find the complete source code for this application *here*.

RAG Application

References:

• Github repo: https://github.com/Arwa-ehab/RAG_with_database_retriever
• Create credential: https://docs.oracle.com/en/database/oracle/oracle-database/26/vecse/create_credential-dbms_vector.html
• Access Vector Generation Models using APIs: https://docs.oracle.com/en/database/oracle/oracle-database/26/vecse/access-third-party-models-vector-generation-leveraging-third-party-rest-apis.html#GUID-F3C6AEAE-2995-4867-BE94-30907D6087BC
• Create Tables Using the VECTOR Data Type: https://docs.oracle.com/en/database/oracle/oracle-database/26/vecse/create-tables-using-vector-data-type.html
• Query data with similarity and hybrid search: https://docs.oracle.com/en/database/oracle/oracle-database/26/vecse/query-data-similarity-and-hybrid-searches.html