Implement RAG using AI Vector Search

Introduction

In this optional lab, you will learn how to implement Vector Search using an enhanced RAG source for the Learn More button in the Highschools App. You will run the app, ask questions about the school and observe how efficiently the information is filtered with reduced content length thereby saving costs without compromising on the accuracy of responses.

Note: The screenshots in this workshop are taken using Dark Mode in APEX 24.2

Estimated Time: 15 minutes

Watch the video below for a quick walk-through of the lab.

Prerequisites

• To run this lab, you should configure an APEX workspace on an Oracle Database 23ai instance.

Objectives

In this lab, you will:

• Implement Vector Search using RAG source
• Run the app and observe the difference

Task 1: Grant necessary privileges to the Parsing Schema

1. This lab assumes that you have provisioned an Autonomous Database with Database version 23ai. To provision an Autonomous Database on Oracle Cloud, refer to the Get Started: Option 2: Autonomous Databse in Oracle Cloud lab.

2. From the details page, click Database Actions and select SQL.

3. Run the following SQL command to grant mining access to your parsing schema:

   CopyGRANT create mining model TO <your parsing schema>

   

   Note: To know your parsing schema, from your App Builder, navigate to Shared Components > Security Attributes > Database Session.

Task 2: Load the ONNX Model

1. Switch tab to your APEX workspace and navigate to SQL Worshop > SQL Commands.

2. Load the ONNX model to the database by running the following PL/SQL code:

   CopyDECLARE
           L_ONNX_BLOB BLOB;
           L_ONNX_MOD_FILE VARCHAR2(100) := 'all_MiniLM_L12_v2.onnx';
           L_LOCATION_URI VARCHAR2(200) := 'https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/eLddQappgBJ7jNi6Guz9m9LOtYe2u8LWY19GfgU8flFK4N9YgP4kTlrE9Px3pE12/n/adwc4pm/b/OML-Resources/o/';
       BEGIN
       -----------------------------------------------------
       -- Read the ONNX model file from Object Storage
       -----------------------------------------------------
   
       L_ONNX_BLOB := APEX_WEB_SERVICE.MAKE_REST_REQUEST_B(P_URL => L_LOCATION_URI || L_ONNX_MOD_FILE, P_HTTP_METHOD => 'GET');
   
       -----------------------------------------
       -- Load the ONNX model to the database
       -----------------------------------------
   
       DBMS_VECTOR.LOAD_ONNX_MODEL(
           MODEL_NAME => 'doc_model',
           MODEL_DATA => L_ONNX_BLOB
       );
   
       END;

   

   Read more about the pre-trained ONNX model being used in this blog: https://blogs.oracle.com/machinelearning/post/use-our-prebuilt-onnx-model-now-available-for-embedding-generation-in-oracle-database-23ai

Task 3: Create a Vector Provider

Vector providers are configured to convert text into an embedding.

1. Navigate to App Builder > Workspace Utilities > All Workspace Utilities.

2. Select Vector Providers.

   

3. Click Create.

   

4. In the Vector Provider Details page, enter/select the following:

   • Provider Type: Database ONNX Model
   • Name: DB ONNX Model
   • Static ID: db_onnx_model
   • ONNX Model Owner: Select your Parsing Schema
   • ONNX Model Name: DOC_MODEL

   Click Create.

   

5. A Vector Provider is successfully created.

   

Task 4: Create Vectors on School Information

1. Navigate to SQL Workshop > SQL Commands.

   

2. Run the following SQL command to create a table called HIGH_SCHOOL_INFO. This table will be used to store the vectorized data.

   CopyCREATE TABLE "HIGH_SCHOOL_INFO"
   (    "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH  1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE,
       "SCHOOL_ID" NUMBER,
       "CHUNK_C" CLOB,
       "CHUNK_V" VECTOR,
       CONSTRAINT "HIGH_SCHOOL_INFO_ID_PK" PRIMARY KEY ("ID")
       USING INDEX  ENABLE
   ) ;

   

3. Run the following SQL commands to insert data into the HIGH_SCHOOL_INFO table.

   CopyBEGIN
   -- Fetching data using FOR loop
   FOR rec IN (
       SELECT
           ID,
           'Overview of the school : ' || OVERVIEW_PARAGRAPH AS OVERVIEW_PARAGRAPH,
           'The following Language Courses are taught here : ' || LANGUAGE_CLASSES AS LANGUAGE_CLASSES,
           'The following Advanced Placement Courses are taught : ' || ADVANCEDPLACEMENT_COURSES AS ADVANCED_PLACEMENT_COURSES,
           'The following is the Diversity in Admission Policy for this school: ' || DIADETAILS AS DIVERSITY_IN_ADMISSION_POLICY,
           'The below extra curricular activities are available : ' || EXTRACURRICULAR_ACTIVITIES AS EXTRACURRICULAR_ACTIVITIES,
           'The below are Public Schools Athletic League (PSAL) sports for boys: ' || PSAL_SPORTS_BOYS AS PSAL_SPORTS_BOYS,
           'The below are Public Schools Athletic League (PSAL) sports for girls : ' || PSAL_SPORTS_GIRLS AS PSAL_SPORTS_GIRLS,
           'Other facilities in this school : ' || ADDTL_INFO1 AS ADDITIONAL_INFO1,
           'The following academic opportunities are available : ' || ACADEMIC_OPPORTUNITIES AS ACADEMIC_OPPORTUNITIES,
           'Attendance rate : ' || ATTENDANCE_RATE AS ATTENDANCE_RATE,
           'Graduation rate : ' || GRADUATION_RATE AS GRADUATION_RATE
       FROM HIGHSCHOOLS
   )
   LOOP
       -- Inserting into HIGH_SCHOOL_INFO table
       INSERT INTO HIGH_SCHOOL_INFO (SCHOOL_ID, CHUNK_C)
       VALUES (rec.ID, rec.OVERVIEW_PARAGRAPH);
   
       INSERT INTO HIGH_SCHOOL_INFO (SCHOOL_ID, CHUNK_C)
       VALUES (rec.ID, rec.LANGUAGE_CLASSES);
   
       INSERT INTO HIGH_SCHOOL_INFO (SCHOOL_ID, CHUNK_C)
       VALUES (rec.ID, rec.ADVANCED_PLACEMENT_COURSES);
   
       INSERT INTO HIGH_SCHOOL_INFO (SCHOOL_ID, CHUNK_C)
       VALUES (rec.ID, rec.DIVERSITY_IN_ADMISSION_POLICY);
   
       INSERT INTO HIGH_SCHOOL_INFO (SCHOOL_ID, CHUNK_C)
       VALUES (rec.ID, rec.EXTRACURRICULAR_ACTIVITIES);
   
       INSERT INTO HIGH_SCHOOL_INFO (SCHOOL_ID, CHUNK_C)
       VALUES (rec.ID, rec.PSAL_SPORTS_BOYS);
   
       INSERT INTO HIGH_SCHOOL_INFO (SCHOOL_ID, CHUNK_C)
       VALUES (rec.ID, rec.PSAL_SPORTS_GIRLS);
   
       INSERT INTO HIGH_SCHOOL_INFO (SCHOOL_ID, CHUNK_C)
       VALUES (rec.ID, rec.ADDITIONAL_INFO1);
   
       INSERT INTO HIGH_SCHOOL_INFO (SCHOOL_ID, CHUNK_C)
       VALUES (rec.ID, rec.ACADEMIC_OPPORTUNITIES);
   
       INSERT INTO HIGH_SCHOOL_INFO (SCHOOL_ID, CHUNK_C)
       VALUES (rec.ID, rec.ATTENDANCE_RATE);
   
       INSERT INTO HIGH_SCHOOL_INFO (SCHOOL_ID, CHUNK_C)
       VALUES (rec.ID, rec.GRADUATION_RATE);
   END LOOP;
   END;

   

4. Run the following SQL command to convert the data into vector chunks. Note that this may take a while.

   Copyupdate HIGH_SCHOOL_INFO set chunk_v = apex_ai.get_vector_embeddings(p_value => chunk_c, p_service_static_id => 'db_onnx_model');

   

Task 5: Create a Search Configuration

Search Configuration contains information about a searchable data source.

1. From your application homepage, navigate to Shared Components.

   

2. Under Navigation and Search, select Search Configurations.

   

3. Click Create.

   

4. In the Create Search Configuration dialog, enter/select the following:

   • Name: Highschool Info
   • Search Type: Oracle Vector Search

   Click Next.

   

5. Enter/select the following:

   • Vector Provider: DB ONNX Model
   • Table/View Name: HIGH_SCHOOL_INFO

   Click Next.

   

6. Map the table columns to our search configuration. Enter/select the following:

   • Primary Key Column: ID (Number)
   • Vector Column: CHUNK_V (Vector)
   • Title Column: CHUNK_C (Clob)

   Click Create Search Configuration.

   

7. Ensure that the Static ID is highschool_info in the search configuration.

   

8. Under the Source group, for Where Clause, enter the following:

   CopySCHOOL_ID = :P2_SCHOOL_ID

   

9. Scroll down and for Maximum Rows to Return, enter 1. Click Apply Changes.

   

Task 6: Update RAG Source to use AI Vector Search

In this task, we update the RAG source to use the vector we created. The Learn More AI assisted chat will now be able to provide answers based on the vectorized data.

1. Navigate to Shared Components.

   

2. Under Generative AI, select AI Configurations.

   

3. Select Learn More AI.

   

4. Under RAG Sources, select School Context to edit.

   

5. Replace the SQL query with the following SQL command and click Apply Changes:

   Copyselect title
       from table( apex_search.search(
                  p_search_static_ids => apex_t_varchar2( 'highschool_info'),
                  p_search_expression => :APEX$AI_LAST_USER_PROMPT ) );

   

Task 7: Run the Application

1. From the Generative AI Configuration page, click Run App to see vector search in action.

   

2. Click Learn More icon on a School Card of your choice.

   

3. Select a Quick Action like 'What is the graduation rate?'.

   

4. The chat assistant provides a suitable response. Click Edit App from the developer toolbar.

   

5. Navigate to SQL Workshop > SQL commands.

   

6. Run the following SQL command to see how the CONTENT_LENGTH column shows significant decrease in the length:

   Copyselect * from APEX_WEBSERVICE_LOG order by request_date desc;

   

Summary

You now know how to implement Vector Search in your APEX app. You understood how vectors can be used in AI configurations to optimize RAG implementations by reducing the content sent to large language models, thereby saving costs without compromising on the accuracy of responses.

Acknowledgments

• Authors - Toufiq Mohammed, Senior Product Manager; Apoorva Srinivas, Senior Product Manager
• Last Updated By/Date - Apoorva Srinivas, Senior Product Manager, February 2025