Build an AI-Powered Search Engine with HeatWave GenAI - Semantic Search Capabilities with Large Language Models

In this presentation, we'll see how to go beyond the limits of traditional SQL to harness the power of LLM-driven semantic search.
This approach significantly enhances the relevance of search results by understanding context, interpreting user intent, and handling synonyms.
During this session, we’ll cover:
- The technology stack used: SQL, Python, and JavaScript-based stored procedures
- The architecture of a complete RAG (retrieval-augmented generation) pipeline, including data extraction, vectorization, storage, and querying within the database
- The process of building a conversational agent (chatbot) for natural language interaction with the AI

Discover how to implement a powerful, AI-enhanced semantic search engine directly within Oracle HeatWave GenAI.

Sources:
Build an AI-Powered Search Engine with HeatWave GenAI (part 1)
https://dasini.net/blog/2025/03/13/build-an-ai-powered-search-engine-with-heatwave-genai-part-1/

Build an AI-Powered Search Engine with HeatWave GenAI (part 2)
https://dasini.net/blog/2025/04/08/build-an-ai-powered-search-engine-with-heatwave-genai-part-2/

Build an AI-Powered Search Engine with HeatWave GenAI (part 3)
https://dasini.net/blog/2025/04/15/build-an-ai-powered-search-engine-with-heatwave-genai-part-3/

Transcript

1. Build an AI-Powered Search Engine with HeatWave GenAI Semantic Search Capabilities with Large Language Models Olivier Dasini Cloud Solutions Architect @ Oracle olivier.dasini@oracle.com Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin : www.linkedin.com/in/olivier-dasini Slides : https://speakerdeck.com/freshdaz
2. Safe harbor statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. 2 Copyright © 2025, Oracle and/or its affiliates. All rights reserved.
3. Me, Myself & I 3 Olivier DASINI Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 3 Ø Data Geek ü Addicted to MySQL for 15+ years ü Playing with databases for 20+ years Ø Writer, Blogger and Speaker ü Also former : DBA, Consultant, Architect, Trainer, ... Ø Cloud Solutions Architect at Oracle Ø Stay up to date! ü Blog: www.dasini.net/blog/en ü Linkedin: www.linkedin.com/in/olivier-dasini/ ü Slides: https://speakerdeck.com/freshdaz 3
4. Introduction 4 Search engines Copyright © 2025, Oracle and/or its affiliates. All rights reserved.
5. Search Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 5 Traditional SQL Search l SQL search - Traditional SQL search relies on structured queries (SELECT, WHERE, JOIN, …) and exact or partial matches based on conditions (e.g., WHERE name = 'Olivier' / WHERE name LIKE '%Olivier%') - A typical query may look like: - While efficient for structured data, it has limited flexibility for search variations and fails to grasp context or intent SELECT title FROM articles WHERE Category = 'HeatWave' OR tag LIKE "%AI%";
6. Search Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 6 SQL Full-Text Search l SQL full-text search - SQL Full-Text Search (FTS), enables efficient keyword-based searches across large text datasets - For example, MySQL implements FTS using MATCH and AGAINST (e.g. MATCH(name) AGAINST('Olivier')) - This feature indexes text content within database columns, allowing for advanced search capabilities such as phrase matching, proximity searches, and relevance scoring - A typical query may look like: - FTS is usually faster and more relevant than basic SQL searches, efficiently handling large text fields. However, it remains keyword-based rather than semantic, meaning it may overlook context-based variations SELECT title FROM articles WHERE MATCH (title, excerpt) AGAINST ('HeatWave' IN NATURAL LANGUAGE MODE);
7. Search Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 7 AI-powered search using large language models a.k.a. semantic search l Semantic search - Another option is AI-powered search using large language models (LLMs), also known as semantic search - Unlike keyword-based methods, it leverages embeddings — vector representations of words or sentences — to understand meaning - This enables it to handle synonyms, paraphrasing, and contextual relationships (e.g., searching for ‘AI’ may also return articles on ‘machine learning’) - Additionally, it often integrates retrieval-augmented generation (RAG) to enhance responses with external knowledge - We’ll dive deeper into AI-powered search using an LLM with the help of HeatWave GenAI
8. HeatWave 8 Get faster insights from all your data Copyright © 2025, Oracle and/or its affiliates. All rights reserved.
9. The MySQL universe - The view from the moon… Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 9 Community, Enterprise, HeatWave (Cloud Services) MySQL Community MySQL Server MySQL Client, Workbench MySQL Shell MySQL GR plugin & InnoDB Cluster & Router MySQL Operator for Kubernetes MySQL Connector (C API, Java, Node.js, others) MySQL Support for MS VS Code (Preview) … MySQL Cluster NDB MySQL NDB Storage Engine MySQL NDB Operator for Kubernetes MySQL Enterprise MySQL Community + MySQL Enterprise Backup MySQL Enterprise Monitor MySQL Enterprise Authentication MySQL Enterprise Audit MySQL Enterprise TDE MySQL Enterprise Masking MySQL Enterprise Firewall MySQL Technical Support … MySQL Cluster CGE MySQL Cluster NDB + MySQL Enterprise + MySQL Cluster Manager HeatWave (Cloud Services) HeatWave MySQL HeatWave Analytics / DataWarehouse HeatWave Lakehouse HeatWave AutoML HeatWave GenAI
10. HeatWave
11. HeatWave Cluster - In-Memory Hybrid-Columnar Format Query Accelerator 11 For OLTP, OLAP, ML & GenAI https://dev.mysql.com/doc/heatwave/en/mys-hw-architecture.html
12. *Benchmark queries are derived from the TPC-H benchmark, but results are not comparable to published TPC-H benchmark results since they do not comply with the TPC-H specification 400G, 64 cores HeatWave dramatically speeds up analytic queries Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 12 HeatWave MySQL
13. HeatWave AutoML 13 Build, train, deploy, & explain machine learning models within HeatWave Copyright © 2025, Oracle and/or its affiliates. All rights reserved. https://www.oracle.com/heatwave/features/#automl
14. HeatWave AutoML Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 14 Build, train, deploy, & explain machine learning models within HeatWave • HeatWave AutoML makes it easy to use machine learning, whether you are a novice user or an experienced ML practitioner • You provide the data, and HeatWave AutoML analyzes the characteristics of the data and creates an optimized machine learning model that you can use to generate predictions and explanations • An ML model makes predictions by identifying patterns in your data and applying those patterns to unseen data • HeatWave AutoML explanations help you understand how predictions are made, such as which features of a dataset contribute most to a prediction
15. HeatWave AutoML automates the ML lifecycle & all models can be explained Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 15 HeatWave AutoML leverages Oracle AutoML technology to automate the process of training a ML model
16. HeatWave AutoML uses a set of SQL routines Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 16 Machine Learning with HeatWave is so simple You only need to use a limited set of SQL routines: • ML_TRAIN: Trains a machine learning model for a given training dataset • ML_PREDICT_ROW: Makes predictions for one or more rows of data • ML_PREDICT_TABLE: Makes predictions for a table of data • ML_EXPLAIN_ROW: Explains predictions for one or more rows of data • ML_EXPLAIN_TABLE: Explains predictions for a table of data • ML_SCORE: Computes the quality of a model • ML_MODEL_LOAD: Loads a machine learning model for predictions and explanations • ML_MODEL_UNLOAD: Unloads a machine learning model • In addition, with HeatWave AutoML, there is no need to move or reformat your data • Data and machine learning models never leave the HeatWave Service, which saves you time and effort while keeping your data and models secure
17. Fully automated in-database machine learning Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 17 Training, inference, explanation with HeatWave AutoML, at no additional cost • In-database • Secure • Fully automated • 25x faster than Redshift ML • Explainable • No additional cost
18. HeatWave GenAI 18 Generative AI and Vector Store Integrated & Automated Copyright © 2025, Oracle and/or its affiliates. All rights reserved.
19. HeatWave GenAI Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 19 Generative AI Integrated and Automated, at no additional cost • HeatWave GenAI lets you communicate with unstructured data in HeatWave using natural-language queries • Using HeatWave GenAI, you can perform natural-language searches in a single step using either in- database or external large language models (LLMs) • It uses a familiar SQL interface which makes it is easy to use for content generation, summarization, sentiment analysis, retrieval-augmented generation (RAG), … • All the elements that are necessary to use HeatWave GenAI with proprietary data are integrated and optimized to work with each other • HeatWave GenAI lets you integrate generative AI into the applications, providing an integrated end-to-end pipeline including vector store generation, vector search using RAG, and an inbuilt chatbot
20. HeatWave GenAI enables new use cases and apps Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 20
21. Part 2 Building GenAI apps with non-Oracle databases is complex Use vector store with LLMs Copyright © 2025, Oracle and/or its affiliates Part 1 Create a vector store Discover user documents Parse data from documents Extract metadata Split data into segments Choose embedding model Create vector embeddings Design vector store Insert metadata + segments + embeddings into vector store Ensure consistency of ML model when querying Ask a question Choose embedding model Create query embedding Select Vector store to search Select search algorithm Select search results Create prompt with search results and guard rails Select LLM Get results
22. Part 2 Building GenAI apps with HeatWave is very easy Use vector store with LLMs Copyright © 2025, Oracle and/or its affiliates Part 1 Create a vector store Discover user documents Parse data from documents Extract metadata Split data into segments Choose embedding model Create vector embeddings Design vector store Insert metadata + segments + embeddings into vector store Ensure consistency of ML model when querying Ask a question Choose embedding model Create query embedding Select Vector store to search Select search algorithm Select search results Create prompt with search results and guard rails Select LLM Get results SQL> call sys.heatwave_load(schema_name, @source_location)
23. Part 2 Building GenAI apps with HeatWave is very easy Use vector store with LLMs Copyright © 2025, Oracle and/or its affiliates Part 1 Create a vector store Discover user documents Parse data from documents Extract metadata Split data into segments Choose embedding model Create vector embeddings Design vector store Insert metadata + segments + embeddings into vector store Ensure consistency of ML model when querying Ask a question Choose embedding model Create query embedding Select Vector store to search Select search algorithm Select search results Create prompt with search results and guard rails Select LLM Get results SQL> call sys.heatwave_load(schema_name, @source_location) SQL> sys.ML_RAG("What is HeatWave?", @NL_response, @optional_search_params)
24. Javascript integrated with HeatWave GenAI Copyright © 2025, Oracle and/or its affiliates 24 • Use JavaScript to invoke HeatWave GenAI capabilities; data stays in the database Vector Store LLM JavaScript JavaScript Prompt LLM output Convert into Action Tables • Prompt Generation: Build prompts using JavaScript’s strings and regex capability • Process all your data: Use LLMs with both structured and unstructured data • LLM Output Processing: Process LLM text output or convert into actions using JavaScript
25. Build an AI-powered search engine 25 Copyright © 2025, Oracle and/or its affiliates. All rights reserved. Using Python
26. The goal Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 26 Build an AI-powered search engine • The goal is to build an AI-powered search engine for an application, designed to provide users with the most relevant articles based on their queries, using semantic search • I’ll be using the data from my WordPress-based blog — https://dasini.net/blog/ — with the AI component powered by HeatWave GenAI • This leverages its in-database large language models and vector store capabilities (In-Database Embedding Models, The VECTOR Type, Vector Functions)
27. Data structure Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 27 wp_posts table • In WordPress the blog posts are stored in the table: wp_posts • The important columns to reach our goal are: • ID bigint unsigned NOT NULL AUTO_INCREMENT, • post_title text NOT NULL • post_excerpt text NOT NULL • post_content longtext NOT NULL • guid varchar(255) NOT NULL DEFAULT ” • The columns contains respectively, the unique identifier of the post, its title, a short excerpt (hopefully), and the HTML content of the post and the URL of the article
28. Data structure Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 28 Example wordpress SQL> SELECT ID, post_title, post_excerpt, guid FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post' AND ID = 1234\G *************************** 1. row *************************** ID: 1234 post_title: HeatWave GenAI: Your AI-Powered Content Creation Partner post_excerpt: Generative artificial intelligence (GenAI) is reshaping the content creation landscape. By training on vast datasets, these "intelligent" systems can produce new, human- quality content across a multitude of domains. Oracle's HeatWave GenAI (starting with version 9.0.1) is at the forefront of this revolution, offering an integrated platform that combines in-database large language models (LLMs), vector stores, and scale-out vector processing to streamline content generation. This article explores how HeatWave GenAI is empowering businesses to produce high-quality content rapidly and effectively, making it an indispensable tool for industries demanding speed, accuracy, and security. guid: https://dasini.net/blog/?p=1234
29. Steps 29 Copyright © 2025, Oracle and/or its affiliates. All rights reserved.
30. What are we going to do? Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 30 1. Defining the embeddings storage table 2. Fetch articles from the database 3. Remove HTML tags and normalize whitespace 4. Split articles into overlapping chunks of words 5. Generate embeddings for a given article 6. Insert article chunks with their embeddings into HeatWave 7. Encode the query into a vector embedding 8. Similarity search
31. Defining the embeddings storage table 31 Copyright © 2025, Oracle and/or its affiliates. All rights reserved.
32. Defining the embeddings storage table Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 32 Why? • Table wp_post_chunks_embeddings_minilm stores the embeddings generated from article chunks: • chunk_embedding: embedding of the articles • chunk_text: contains the part of the articles. Mostly useful for debug • and some IDs, • chunk_id: id of each chunk • post_id: useful to join the wp_posts table and get article information • and finally, the table primary key: id • From wp_posts table, I’ll use the ID (the primary key) and post_content which contains the article in HTML format • All these information will be used by the semantic AI-powered search engine
33. Defining the embeddings storage table Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 33 Python Code SHOW CREATE TABLE wp_post_chunks_embeddings_minilm\G *************************** 1. row *************************** Table: wp_post_chunks_embeddings_minilm Create Table: CREATE TABLE `wp_post_chunks_embeddings_minilm` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `post_id` bigint unsigned DEFAULT NULL, `chunk_id` bigint unsigned DEFAULT NULL, `chunk_text` text, `chunk_embedding` vector(2048) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
34. Fetch articles from the database 34 Copyright © 2025, Oracle and/or its affiliates. All rights reserved.
35. Fetch articles from the database Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 35 Why? • We need to grab a batch of published blog articles from HeatWave MySQL • Think of it as scooping out a small chunk of posts at a time from a big pool of content • Pagination is used in order to manage the memory consumption
36. Fetch articles from the database Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 36 Python Code def fetch_articles_batch(db_config: Dict[str, Any], limit: int = 10, offset: int = 0) -> List[Dict[str, Any]]: """Fetch a batch of articles from the database""" with mysql.connector.connect(**db_config) as conn: with conn.cursor(dictionary=True) as cursor: cursor.execute(""" SELECT ID, post_content FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post' LIMIT %s OFFSET %s """, (limit, offset)) return cursor.fetchall()
37. Remove HTML tags and normalize whitespace 37 Copyright © 2025, Oracle and/or its affiliates. All rights reserved.
38. Remove HTML tags and normalize whitespace Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 38 Why? • Articles are stored in HTML format in the database • The content must be cleaned because we need the raw text • ie without all the formatting tags • Depending of the context, extra processing can be done like stemming or lemmatization • With this function we: • Remove all the HTML tags • Replace them with spaces (so you don’t accidentally smash words together) • Collapse extra whitespace • Trimming off any leading or trailing spaces
39. Remove HTML tags and normalize whitespace Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 39 Python Code def strip_html_tags_with_space(html_string: str) -> str: """Remove HTML tags and normalize whitespace""" if not html_string: return "" text = re.sub(r'<[^>]+>', ' ', html_string) text = re.sub(r'\s+', ' ', text) return text.strip()
40. Split articles into overlapping chunks of words 40 Copyright © 2025, Oracle and/or its affiliates. All rights reserved.
41. Split articles into overlapping chunks of words Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 41 Why? • Each article is a big block of text and it must be split it into smaller overlapping chunks • It is like slicing a loaf of bread, where each slice overlaps a bit with the one before it • Chunking in RAG systems presents several challenges, including issues with chunk size (too small or too large), semantic coherence, and context understanding • These challenges can negatively impact retrieval accuracy, efficiency, and precision • Addressing them requires a carefully balanced approach to optimize chunking strategies while preserving computational efficiency and maintaining nuanced contextual understanding
42. Split articles into overlapping chunks of words Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 42 Python Code def split_text_by_words(text: str, post_id: int, chunk_size: int = 400, overlap: int = 80) -> List[Dict[str, Any]]: """Split text into overlapping chunks of words""" words = text.split() chunks = [] start = 0 chunk_id = 0 while start < len(words): end = start + chunk_size chunk_words = words[start:end] chunk_text = ' '.join(chunk_words) char_start = len(' '.join(words[:start])) char_end = len(' '.join(words[:end])) chunks.append({ "post_id": post_id, "chunk_id": chunk_id, "char_start": char_start, "char_end": char_end, "text": chunk_text }) start += chunk_size - overlap chunk_id += 1 return chunks
43. Split articles into overlapping chunks of words Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 43 Code details • chunk_size: Defines the number of words per chunk. In this case, I’ve chosen 400 words • The ideal value can vary depending on your specific use case and content structure. • overlap: Specifies how much each chunk overlaps with the next one—here, it’s set to 20% of the chunk size • This overlap helps preserve context across chunks, improving continuity and retrieval accuracy. • Each chunk contains the id of the article (post_id), the id of the chunk (chunk_id) and the chunk itself (test) • char_start & char_end are only there for debug purpose.
44. Generate embeddings for a given article 44 Copyright © 2025, Oracle and/or its affiliates. All rights reserved.
45. Generate embeddings for a given article Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 45 Why? • Generate an embedding (aka a numerical vector) for each chunk • Think of an embedding as a way to turn text into numbers so that machines can understand and compare it • We use the HeatWave’s ML_EMBED_ROW stored function to generate embeddings
46. Generate embeddings for a given article Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 46 Python Code def embed_row(db_config: Dict[str, Any], search_item: str, embed_options: str = '{"model_id": "minilm"}') -> str: """Generate an embedding for a given text using the ML_EMBED_ROW function""" with mysql.connector.connect(**db_config) as conn: with conn.cursor() as cursor: sql_embed_row = 'SELECT VECTOR_TO_STRING(vect) FROM (SELECT sys.ML_EMBED_ROW(%s, %s) AS vect) AS dt;' cursor.execute(sql_embed_row, (search_item, embed_options)) result = cursor.fetchone() return "".join(result) if result else ""
47. Generate embeddings for a given article Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 47 Code details • The trick here in Python is obtaining the string representation of the vector, which we can do using the VECTOR_TO_STRING function • embed_options: specifies the settings for the embedding model. Here we are using "minilm"
48. Insert article chunks with their embeddings into HeatWave 48 Copyright © 2025, Oracle and/or its affiliates. All rights reserved.
49. Insert article chunks with their embeddings into HeatWave Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 49 Why? • Store the text chunks and their corresponding embeddings in the vector store — HeatWave — specifically into the wp_post_chunks_embeddings_minilm table designed for this purpose
50. Insert article chunks with their embeddings into HeatWave Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 50 Python Code def insert_chunks_into_mysql(chunks: List[Dict[str, Any]], db_config: Dict[str, Any], batch_size: int = 900): """Insert text chunks with embeddings into the database""" with mysql.connector.connect(**db_config) as conn: with conn.cursor() as cursor: insert_query = """ INSERT INTO wp_post_chunks_embeddings_minilm (post_id, chunk_id, chunk_text, chunk_embedding) VALUES (%s, %s, %s, STRING_TO_VECTOR(%s)) """ buffer = [] for i, chunk in enumerate(chunks, 1): chunk_embedding = embed_row(db_config, chunk['text']) buffer.append(( chunk['post_id'], chunk['chunk_id'], chunk['text'], chunk_embedding )) if i % batch_size == 0: cursor.executemany(insert_query, buffer) conn.commit() buffer.clear() if buffer: cursor.executemany(insert_query, buffer) conn.commit() print(f"Inserted {len(chunks)} chunks into database.")
51. Insert article chunks with their embeddings into HeatWave Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 51 Code details • batch_size: defines how many chunks are inserted into the database at once • In this case, once the buffer reaches 900 chunks, the data is flushed in bulk — a much more efficient approach than inserting each chunk individually, both in terms of performance and memory usage • A key step here is converting the embeddings into HeatWave’s native vector type before insertion • This is done using the STRING_TO_VECTOR function
52. Encode the query into a vector embedding 52 Copyright © 2025, Oracle and/or its affiliates. All rights reserved.
53. Encode the query into a vector embedding Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 53 Why? • Transform the user’s search query into a vector embedding, a numerical representation that captures its underlying meaning • This process, known as query encoding, allows us to go beyond simple keyword matching • Usage of the ML_EMBED_ROW routine • Applies the specified embedding model to encode the given text into a vector representation • The routine returns a VECTOR containing the numerical embedding of the text.
54. Encode the query into a vector embedding Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 54 SQL Code SET @embeddOptions = '{"model_id": "minilm"}'; SET @searchItem = "Generative artificial intelligence"; -- Encode the query using the embedding model SELECT sys.ML_EMBED_ROW(@searchItem, @embeddOptions) into @searchItemEmbedding;
55. Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 55 Python Code def query_encoding(cursor: MySQLCursor, model_id: str, searchItem: str) -> Optional[Tuple]: # Convert model_id into a proper JSON string embedd_options = json.dumps({"model_id": model_id}) # Query the embedding model sql_similarity_search = "SELECT VECTOR_TO_STRING(sys.ML_EMBED_ROW(%s, %s))" cursor.execute(sql_similarity_search, (searchItem, embedd_options)) # Fetch and return the result return cursor.fetchone() Encode the query into a vector embedding
56. Similarity search 56 Copyright © 2025, Oracle and/or its affiliates. All rights reserved.
57. Similarity search Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 57 Why? • To retrieve relevant blog content, we perform vector similarity calculations using the DISTANCE function • This function computes the distance between two vectors using COSINE, DOT, or EUCLIDEAN distance metrics • Here, the two vectors being compared are the encoded query (@searchItemEmbedding) and the precomputed embeddings stored in the wp_posts & wp_post_chunks_embeddings_minilm tables
58. Similarity search Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 58 SQL Code: search result display SQL > -- Find the most semantically relevant WordPress posts SELECT post_title, post_excerpt, guid, min_distance FROM ( SELECT p.post_title, p.post_excerpt, p.guid, DISTANCE(@searchItemEmbedding, e.chunk_embedding, 'COSINE') AS min_distance, ROW_NUMBER() OVER (PARTITION BY p.post_title ORDER BY DISTANCE(@searchItemEmbedding, e.chunk_embedding, 'COSINE')) AS rn FROM wp_posts p INNER JOIN wp_post_chunks_embeddings_minilm e ON p.ID = e.post_id ) AS ranked WHERE rn = 1 ORDER BY min_distance ASC LIMIT 5\G
59. Similarity search Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 59 SQL Code: result *************************** 1. row *************************** post_title: HeatWave GenAI: Sentiment Analysis Made Easy-Peasy post_excerpt: This new AI tech, called generative AI (or GenAI), can dive deep into what people are saying and tell us if they’re feeling positive, negative, or neutral. Let’s see how HeatWave GenAI, can help you to enhance your understanding of customer sentiment, improve decision-making, and drive business success. guid: https://dasini.net/blog/?p=3456 min_distance: 0.4673360586166382 *************************** 2. row *************************** post_title: HeatWave GenAI: Your AI-Powered Content Creation Partner post_excerpt: Generative artificial intelligence (GenAI) is reshaping the content creation landscape. By training on vast datasets, these "intelligent" systems can produce new, human-quality content across a multitude of domains. Oracle's HeatWave GenAI (starting with version 9.0.1) is at the forefront of this revolution, offering an integrated platform that combines in-database large language models (LLMs), vector stores, and scale-out vector processing to streamline content generation …
60. Encode the query into a vector embedding Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 60 SQL Code details • This SQL query is designed to retrieve the top 5 WordPress posts that are most semantically similar to a given embedding vector (@searchItemEmbedding), using cosine distance for comparison — but with a twist: it only considers the single best-matching chunk per post • Methods like reranking or incorporating additional fields—such as the article title—while fine-tuning the weights can significantly enhance result relevance
61. Build an AI-powered search engine 61 Copyright © 2025, Oracle and/or its affiliates. All rights reserved. Using HeatWave GenAI Routines
62. Steps 62 Copyright © 2025, Oracle and/or its affiliates. All rights reserved.
63. What are we going to do? Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 63 1. Remove HTML tags and normalize whitespace & Copy articles to the OCI object store 2. Create a vector store: sys.HEATWAVE_LOAD or sys.VECTOR_STORE_LOAD 3. Similarity search
64. Remove HTML tags and normalize whitespace & Copy articles to the OCI object store 64 Copyright © 2025, Oracle and/or its affiliates. All rights reserved.
65. Remove HTML tags and normalize whitespace Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 65 Why? • Articles are stored in HTML format in the database • The content must be cleaned because we need the raw text • ie without all the formatting tags • Depending of the context, extra processing can be done like stemming or lemmatization • With this function we: • Remove all the HTML tags • Replace them with spaces (so you don’t accidentally smash words together) • Collapse extra whitespace • Trimming off any leading or trailing spaces
66. Copy articles to the OCI object store Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 66 Why? • Files must be exported to the object store in one of the following formats: • PPT • TXT • HTML • DOC • PDF
67. Remove HTML tags and normalize whitespace Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 67 JavaScripst Code inside a stored routine CREATE FUNCTION sp.stripHtmlTags(htmlString LONGTEXT) RETURNS LONGTEXT NO SQL LANGUAGE JAVASCRIPT AS $$ if (!htmlString) { return ""; } // Replace HTML tags with a space return htmlString .replace(/<[^>]+>/g, " ") // Replace all tags with a space .replace(/\s+/g, " ") // Replace multiple spaces with a single space .trim(); $$ ;
68. 68 Copyright © 2025, Oracle and/or its affiliates. All rights reserved. Create a vector store: sys.heatwave_load()
69. Create a vector store: sys.heatwave_load() Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 69 SQL Code SET @dl_tables = '[ { "db_name": "WP_embeddings", "tables": [ {"table_name": "wp_articles", "engine_attribute": { "dialect": {"format": "txt"}, "file": [ {"par": "https://objectstorage..../wordpress_articles/o/"} ]}}] }]'; SET @options = JSON_OBJECT('mode', 'normal'); CALL sys.HEATWAVE_LOAD(CAST(@dl_tables AS JSON), @options);
70. Create a vector store: sys.heatwave_load() Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 70 Why? CREATE TABLE `wp_articles` ( `document_name` varchar(1024) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=VARLEN', `metadata` json NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=VARLEN', `document_id` int unsigned NOT NULL, `segment_number` int unsigned NOT NULL, `segment` varchar(1024) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=VARLEN', `segment_embedding` vector(384) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=VARLEN' /*!80021 ENGINE_ATTRIBUTE '{"model": "minilm"}' */, PRIMARY KEY (`document_id`,`segment_number`) • ) ENGINE=Lakehouse DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=RAPID /*!80021 ENGINE_ATTRIBUTE='{"file": [{"par": "https://objectstorage.eu-fra.../wordpress_articles/o/"}], "dialect": {"format": "txt"}}' */
71. Similarity search 71 Copyright © 2025, Oracle and/or its affiliates. All rights reserved.
72. Similarity search Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 72 Why? • To retrieve relevant blog content, we perform vector similarity calculations using the DISTANCE function • This function computes the distance between two vectors using COSINE, DOT, or EUCLIDEAN distance metrics • Here, the two vectors being compared are the encoded query (@searchItemEmbedding) and the precomputed embeddings stored in the wp_posts & wp_post_chunks_embeddings_minilm tables
73. Similarity search Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 73 SQL Code SET @embeddOptions = '{"model_id": "minilm"}'; SET @searchItem = "Generative artificial intelligence"; -- Encode the query using the embedding model SELECT sys.ML_EMBED_ROW(@searchItem, @embeddOptions) into @searchItemEmbedding; SELECT document_name, document_id, segment, DISTANCE(@searchItemEmbedding, segment_embedding, 'COSINE') AS min_distance FROM WP_embeddings.wp_articles ORDER BY min_distance ASC LIMIT 3\G
74. Similarity search Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 74 SQL Code : result *************************** 1. row *************************** document_name: https://objectstorage..../wordpress_articles/o/1234.txt document_id: 256 segment: GenAI: Your AI-Powered Content Creation Partner demo Moving forward While the examples provided offer a glimpse of generative AI's potential, it's important to note that significant improvements can be achieved through advanced prompt engineering techniques. Exploring these techniques in depth is beyond the scope of this article. In upcoming articles, we'll delve deeper into HeatWave GenAI's capabilities, showcasing its application in text translation and Retrieval Augmented Generation (RAG). min_distance: 0.46372365951538086 *************************** 2. row *************************** document_name: https://objectstorage.eu-fra.../b/wordpress_articles/o/1234.txt document_id: 256 segment: Generative artificial intelligence (GenAI) is reshaping the content creation landscape. By training on vast datasets, these " intelligent " systems can produce new, human-quality content across a multitude of domains. Oracle's HeatWave GenAI (starting with version 9.0.1) is at the forefront of this revolution, offering an integrated platform that combines in-database large language models (LLMs), vector stores , and scale-out vector processing to streamline content generation. This article min_distance: 0.47047221660614014 *************************** 3. row *************************** document_name: https://objectstorage..../b/wordpress_articles/o/5678.txt document_id: 265 segment: generative AI without requiring AI expertise, data transfer, or extra costs. The key benefits of such architecture are: Effortless LLM Integration : Simple SQL queries unlock AI power…
75. Similarity search Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 75 SQL Code: search result display SELECT post_title, url, min_distance FROM ( SELECT p.post_title, CONCAT('https://dasini.net/blog/?p=', REGEXP_SUBSTR(document_name, '[0- 9]+(?=\\.txt)')) AS url, DISTANCE(@searchItemEmbedding, segment_embedding, 'COSINE') AS min_distance, ROW_NUMBER() OVER (PARTITION BY CONCAT('https://dasini.net/blog/?p=', REGEXP_SUBSTR(document_name, '[0-9]+(?=\\.txt)')) ORDER BY DISTANCE(@searchItemEmbedding, segment_embedding, 'COSINE')) AS rn FROM WP_embeddings.wp_articles INNER JOIN wp_posts p ON p.guid = CONCAT('https://dasini.net/blog/?p=', REGEXP_SUBSTR(document_name, '[0-9]+(?=\\.txt)')) ) AS ranked WHERE rn = 1 ORDER BY min_distance ASC LIMIT 5;
76. Similarity search Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 76 SQL Code: search result display *************************** 1. row *************************** post_title: HeatWave GenAI: Your AI-Powered Content Creation Partner url: https://dasini.net/blog/?p=1234 min_distance: 0.4637235999107361 *************************** 2. row *************************** post_title: Simplifying AI Development: A Practical Guide to HeatWave GenAI’s RAG & Vector Store Features url: https://dasini.net/blog/?p=2345 min_distance: 0.4732730984687805 *************************** 3. row *************************** post_title: HeatWave GenAI: Sentiment Analysis Made Easy-Peasy url: https://dasini.net/blog/?p=3456 min_distance: 0.5550680160522461 *************************** 4. row *************************** post_title: In-Database LLMs for Efficient Text Translation with HeatWave GenAI url: https://dasini.net/blog/?p=4567 min_distance: 0.5793039798736572 *************************** 5. row *************************** post_title: Building an Interactive LLM Chatbot with HeatWave Using Python url: https://dasini.net/blog/?p=5678 min_distance: 0.6013844013214111
77. 77 Copyright © 2025, Oracle and/or its affiliates. All rights reserved. HeatWave Chat Interact with your documents using natural language
78. Copyright © 2024, Oracle and/or its affiliates Chat Lakehouse Navigator Global and refined search Interact with your documents using natural language. Context is preserved to enable conversations with follow-up questions. Guide LLMs to retrieve information from specific datasets across the database, HeatWave Lakehouse, and HeatWave Vector Store to increase speed and accuracy. Query all the vector stores or limit the scope of the search to a particular schema. Why? HeatWave Chat
79. HeatWave Chat Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 79 Running the Chat • -- Setup HeatWave Chat options SET @chat_options = JSON_OBJECT("tables", JSON_ARRAY(JSON_OBJECT("schema_name", "`WP_embeddings`","table_name","`wp_articles`")), "model_options", JSON_OBJECT("model_id", "mistral-7b-instruct-v1", "temperature", 0)); • -- Query in natural language (en, fr, de, es, hi...) SET @question = "What is HeatWave GenAI?"; • -- Add your query to HeatWave Chat CALL sys.HEATWAVE_CHAT(@question)\G
80. HeatWave Chat Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 80 Running the Chat • -- Add your query to HeatWave Chat CALL sys.HEATWAVE_CHAT(@question)\G *************************** 1. row *************************** response: HeatWave GenAI is a subset of Artificial Intelligence (AI) that involves the creation of new, original content through the use of algorithms and machine learning models. It provides all the tools needed within a single ecosystem to enhance customer generation, search and retrieval tasks, or build interactive chatbots. The key features of HeatWave GenAI include effortless integration with large language models, vector stores, and SQL queries, which enable tasks like content generation, chatbot, and retrieval-augmented generation (RAG). It empowers users to interact with unstructured data seamlessly using natural language.
81. 81 Copyright © 2024, Oracle and/or its affiliates HeatWave GenAI: Benefits Summary Automated Simplicity Lower cost Simplify the development applications while providing performance, security, & cost benefits
82. 82 Copyright © 2025, Oracle and/or its affiliates. All rights reserved. Resources
83. Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 83 § Build an AI-Powered Search Engine with HeatWave GenAI (part 1) § https://dasini.net/blog/2025/03/13/build-an-ai-powered-search-engine-with-heatwave-genai-part-1/ § Build an AI-Powered Search Engine with HeatWave GenAI (part 2) § https://dasini.net/blog/2025/04/08/build-an-ai-powered-search-engine-with-heatwave-genai-part-2/ § Build an AI-Powered Search Engine with HeatWave GenAI (part 3) § https://dasini.net/blog/2025/04/15/build-an-ai-powered-search-engine-with-heatwave-genai-part-3/ § Building an Interactive LLM Chatbot with HeatWave Using Python § https://dasini.net/blog/2025/02/11/building-an-interactive-llm-chatbot-with-heatwave-using-python/ § Simplifying AI Development: A Practical Guide to HeatWave GenAI’s RAG & Vector Store Features § https://dasini.net/blog/2024/12/10/simplifying-ai-development-a-practical-guide-to-heatwave-genais-rag-vector-store-features/ § HeatWave GenAI: Sentiment Analysis Made Easy-Peasy § https://dasini.net/blog/2024/09/10/heatwave-genai-sentiment-analysis-made-easy-peasy/ § In-Database LLMs for Efficient Text Translation with HeatWave GenAI § https://dasini.net/blog/2024/08/13/in-database-llms-for-efficient-text-translation-with-heatwave-genai/ § HeatWave GenAI: Your AI-Powered Content Creation Partner § https://dasini.net/blog/2024/08/07/heatwave-genai-your-ai-powered-content-creation-partner/ My HeatWave Generative AI playlist
84. Merci! Q&R Olivier Dasini Cloud Solutions Architect @ Oracle olivier.dasini@oracle.com Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin : www.linkedin.com/in/olivier-dasini Slides : https://speakerdeck.com/freshdaz
86. HeatWave GenAI Copyright © 2025, Oracle and/or its affiliates. All rights reserved. 86 Letting you take advantage of generative AI without AI expertise, data movement, or additional cost • Provides integrated and automated generative AI with in-database large language models (LLMs) • An automated, in-database vector store & scale-out vector processing • The ability to have contextual conversations in natural language