【博客文章2024】Oracle Database 23ai：AI Vector Search笔记与实验3---在数据库中导入提示文档并创建向量索引实现RAG
实验2的返回结果非常不令人满意。本实验将首先向量化与实验2相同的用户问题，然后从Oracle 23ai向量数据库中检索出与该问题相似的内容，接着将相似内容和用户问题一起组成大语言模型的输入。这样的过程被称为提示工程或RAG（Retrieval-Augmented Generation）。返回的结果令人满意。
1. 在23ai数据库中创建表，用于存储提示文档： 1）在插件数据库pdb1_1中，以本地用户hr创建表：
create table t1(id number, data blob);
|
2）下载以下文档（当然也可以准备其他文档）存放于目录对象dir1中： 3）以大对象方式导入以上文档进入t1表：
create or replace package pack_blob
is
procedure insert_blob(p_id number, p_dirname varchar2, p_filename varchar2);
end;
/
create or replace package body pack_blob
is
procedure insert_blob(p_id number, p_dirname varchar2, p_filename varchar2)
is
v_f bfile;
v_b blob;
begin
insert into t1 values( p_id, empty_blob()) return data into v_b;
v_f:= bfilename(p_dirname, p_filename);
DBMS_LOB.FILEOPEN (v_f, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE (v_b, v_f, DBMS_LOB.GETLENGTH(v_f));
DBMS_LOB.FILECLOSE(v_f);
commit;
end;
end;
/
|
begin
pack_blob.insert_blob(1, 'DIR1', 'database-concepts.pdf');
end;
/
begin
pack_blob.insert_blob(2, 'DIR1', 'ai-vector-search-users-guide.pdf');
end;
/
|
导入成功：
SQL> conn hr/cloud_4U@pdb1_1
Connected.
SQL> select id, dbms_lob.getlength(data) from t1;
ID DBMS_LOB.GETLENGTH(DATA)
---------- ------------------------
1 12685223
2 5016485
|
2. 在数据库中存储数据chunks和存储嵌入向量：
创建一个新表来存储非结构化的数据chunks和相关的嵌入向量。这个表将引入一个叫做vector类型的列：
| create table t1chunks (doc_id number, chunk_id number, chunk_data varchar2(4000), chunk_embedding vector); |
插入数据时，使用insert语句从t1表的data列读取PDF文档：
insert into t1chunks
select t.id doc_id, c.embed_id chunk_id, c.embed_data chunk_data,
to_vector(c.embed_vector) chunk_embedding
from t1 t,
dbms_vector_chain.utl_to_embeddings(
dbms_vector_chain.utl_to_chunks(dbms_vector_chain.utl_to_text(t.data),
json('{"normalize":"all"}')
),
json('{"provider":"database", "model":"ALL_MINILM_L12_V2"}')
) a,
json_table(a.column_value,
'$[*]' columns(embed_id number path '$.embed_id',
embed_data varchar2(4000) path '$.embed_data',
embed_vector clob path '$.embed_vector')
) c;
commit;
6,527 rows inserted.
Commit complete. |
以上语句首先会转换PDF文档为文本，然后将文本分块成数据chunks，最后为每一个数据chunk使用语言模型生成嵌入向量。这些事情都由上面这条语句完成。dbms_vector_chain包中的utl_to_text将PDF数据转换成文本；utl_to_chunks将文本分块；utl_to_embeddings为每一个文本分块生成嵌入向量。 以下是在没有GPU加持下，执行上述insert语句时候的SQL实时监控：
3. 尝试执行向量查询：
满足以下两个条件就可以使用嵌入向量执行相似性查询： 1）表数据向量化（使用to_vector函数）； 2）使用与表数据向量化相同的语言模型，对查询条件进行向量化（使用 VECTOR_EMBEDDING函数）。为了方便操作，我写了以下这个函数完成查询条件向量化（后面将整合它到pack_ollama包中）：
create or replace function func_text_input(p_text varchar2)
return clob
is
v_query_vector clob;
begin
select vector_embedding(ALL_MINILM_L12_V2 using p_text as data)
into v_query_vector;
return v_query_vector;
end;
/ |
3）测试执行相似性查询（比如查询与datapump相似的内容）：
SELECT doc_id, chunk_id, chunk_data FROM t1chunks
ORDER BY vector_distance(chunk_embedding , func_text_input('datapump'), COSINE)
fetch first 4 rows only; |
查询结果：
以下是在没有GPU加持下，执行上述select语句时候的SQL实时监控（运行非常慢，运行了36多分钟）：
4. 尝试加速执行向量查询：
1）创建向量索引：
create vector index t1chunks_hnsw_idx on t1chunks(chunk_embedding)
organization inmemory neighbor graph
distance COSINE
with target accuracy 95 ;
Error report -
ORA-51961: The vector memory area is out of space.
|
报错处理：ORA-51961错误表示Oracle在进行向量操作时，向量内存区域已经用尽。向量内存区域是Oracle在处理查询时用于存储向量化执行计划的内存区域。当这个区域的内存耗尽时，Oracle会抛出这个错误。解决方法：增加向量区域的内存大小：可以通过调整初始化参数vector_memory_size来增加向量内存区域的大小。这个参数定义了向量区域的默认大小。必需在根容器上操作：
[oracle@station1 apex]$ . oraenv
ORACLE_SID = [cdb1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@station1 apex]$ sqlplus /nolog
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri Dec 20 21:08:52 2024
Version 23.5.0.24.07
Copyright (c) 1982, 2024, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> alter system set vector_memory_size=500M;
alter system set vector_memory_size=52428800
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-51950: The Oracle Database Vector Memory size cannot be increased.
Help: https://docs.oracle.com/error-help/db/ora-02097/
|
需要重新启动数据库：
SQL> alter system set vector_memory_size=52428800 scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 4292414272 bytes
Fixed Size 5368640 bytes
Variable Size 2111832064 bytes
Database Buffers 2113929216 bytes
Redo Buffers 8855552 bytes
Vector Memory Area 52428800 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open ;
Pluggable database altered. |
创建向量索引成功：
create vector index t1chunks_hnsw_idx on t1chunks(chunk_embedding)
organization inmemory neighbor graph
distance COSINE
with target accuracy 95 ;
Vector INDEX created. |
再次执行查询，只花费了0.413秒。比原先无索引状态的36.53分钟，快了5307倍。查询结果相同。
SELECT doc_id, chunk_id, chunk_data FROM t1chunks
ORDER BY vector_distance(chunk_embedding , func_text_input('datapump'), COSINE)
fetch first 4 rows only;
|
以下是执行上述select语句时候的SQL实时监控：
5. 编写带有提示工程的PL/SQL包来实现Ollama和Oracle Apex联合运行：
1）包头部分，声明proc_ollama_rag公共过程（保留了实验2的proc_ollama_norag公共过程用于对比，同时把完成查询条件向量化的函数func_text_input也集成到pack_ollama包中）：
create or replace package pack_ollama
is
procedure proc_ollama_norag(p_question varchar2);
procedure proc_ollama_rag(p_question varchar2);
function func_text_input(p_text varchar2) return clob;
end;
/ |
2）包体部分，proc_ollama_rag公共过程的变量名已经声明了变量的含义：
create or replace package body pack_ollama
is
function func_text_input(p_text varchar2)
return clob
is
v_query_vector clob;
begin
select vector_embedding(all_minilm_l12_v2 using p_text as data)
into v_query_vector;
return v_query_vector;
end;
procedure proc_ollama_norag(p_question varchar2)
is
v_ollama_in clob;
v_apex_out clob;
v_json apex_json.t_values;
v_result clob;
begin
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/json';
v_ollama_in := '{
"model": "qwen2:7b-instruct",
"messages": [
{"role": "user", "content": "' || p_question || '"}
]
}';
v_apex_out := apex_web_service.make_rest_request(
p_url => 'http://172.25.250.1:11434/v1/chat/completions',
p_http_method => 'POST',
p_body => v_ollama_in,
p_transfer_timeout => 300000
);
apex_json.parse(v_json, v_apex_out);
v_result:= apex_json.get_varchar2(p_path => 'choices[%d].message.content', p0 => 1, p_values => v_json);
dbms_output.put_line('*** Result: ' || chr(10) || v_result);
end;
procedure proc_ollama_rag(p_question varchar2)
is
v_ollama_in clob;
v_rag_hint clob;
v_apex_out clob;
v_json apex_json.t_values;
v_result clob;
begin
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/json';
--Search by using vector embedding
for rec in (select chunk_data from t1chunks order by vector_distance(chunk_embedding , func_text_input(p_question), cosine)
fetch first 5 rows only)
loop
v_rag_hint := v_rag_hint || rec.chunk_data || chr(10);
end loop;
v_rag_hint := replace(replace(replace(v_rag_hint, '''', ''), '"', '\"'), chr(10), '\n');
--
v_ollama_in := '{
"model": "qwen2:7b-instruct",
"messages": [
{"role": "system", "content": "' || v_rag_hint || '"},
{"role": "user", "content": "' || p_question || '"}
]
}';
v_apex_out := apex_web_service.make_rest_request(
p_url => 'http://172.25.250.1:11434/v1/chat/completions',
p_http_method => 'POST',
p_body => v_ollama_in,
p_transfer_timeout => 300000
);
apex_json.parse(v_json, v_apex_out);
v_result:= apex_json.get_varchar2(p_path => 'choices[%d].message.content', p0 => 1, p_values => v_json);
dbms_output.put_line('*** Result: ' || chr(10) || v_result);
end;
end;
/ |
总体思路是：将提问向量化后与知识库进行关联匹配。v_rag_hint从向量数据库中检索出与问题相似的内容，然后将v_rag_hint传递进{"role": "system", "content": "' || v_rag_hint || '"}形成提示工程。换句话说，就是将相似内容和用户问题一起，组成大语言模型的输入。通过交叉参考权威知识源，能够在各种情境下回答用户问题。 对于cpu模式的环境，建议在apex_web_service.make_rest_request中添加以上红色标注的p_transfer_timeout参数来延长超时时间，否则容易产生utl_http超时报错。
6. 实现100%本地化RAG：
完全断掉Internet连接（使用route del -net 0.0.0.0断网），进行本地运行：
[root@station1 ~]# netstat -nr
Kernel IP routing table
Destination Gateway Genmask Flags MSS Window irtt Iface
0.0.0.0 172.25.250.93 0.0.0.0 UG 0 0 0 ens160
172.25.250.0 0.0.0.0 255.255.255.0 U 0 0 0 ens160
192.168.122.0 0.0.0.0 255.255.255.0 U 0 0 0 virbr0
[root@station1 ~]# route del -net 0.0.0.0
[root@station1 ~]# netstat -nr
Kernel IP routing table
Destination Gateway Genmask Flags MSS Window irtt Iface
172.25.250.0 0.0.0.0 255.255.255.0 U 0 0 0 ens160
192.168.122.0 0.0.0.0 255.255.255.0 U 0 0 0 virbr0
[root@station1 ~]# ping 202.101.98.55
connect: Network is unreachable
[root@station1 ~]# ollama list
NAME ID SIZE MODIFIED
all-minilm:latest 1b226e2802db 45 MB 4 hours ago
qwen2:7b-instruct dd314f039b9d 4.4 GB 7 hours ago
|
多次执行，发现我们的调用方式是成功的：
SET SERVEROUTPUT ON;
exec pack_ollama.proc_ollama_norag('What is new in Oracle 23ai ?');
|
第一次运行：
*** Result:
Based on your passage, the following are the key updates included under Oracle Database 23ai:
1. **AI and Vector Search**: The document highlights some notable AI Vector Search (AVS) updates that have been introduced with this version.
2. **Release Update Details**:
- July 2024 Release Update: This includes specific Oracle AI Vector Search features as part of the mentioned update.
3. **Database Security & Audit**:
- It covers enhanced details on audit records, including fine-grained audits and unified audit trail concepts related to security operations such as database audit vaults and firewall management.
4. **Database Performance Enhancements**
- The document talks about improved performance features like autonomous databases updates, focusing on sessions' memory usage in the large pool, efficient use of out-of-place refresh methods, enhancements for outer joins in query processing, among others.
5. **Data Management Tools**:
- Provides information on automated index creation, auto big table caching functionalities, and capabilities related to automating diagnostics using Automatic Database Diagnostic Monitor (ADDM).
Hence, Oracle 23ai introduces AI Vector Search advancements along with improvements to database security, query processing efficiency, data management tools, and overall automation for database operations.
PL/SQL 过程已成功完成。
|
第二次运行：
*** Result:
Based on the provided documentation, it seems like there are several new features and updates introduced with Oracle 23ai. Here's a summary
of some of the main points:
1. **Autonomous Database Updates**: It mentions Autonomous Database upgrades include new AI Vector Search capabilities.
2.**Oracle AI Vector Search Features**: There are specific AI Vector Search enhancements that have been added in Oracle Database 23ai as part of certain updates, likely indicating improvements in AI Vector Search technology and its integration into the database.
3. **Database Concepts**: The document goes through various concepts related to databases such as Enterprise Grids, Oracle Flex Clusters, Reverse-key indexes, Oracle Secure Backup, Oracle Sharding, and
Oracle Spatial and Graph capabilities.
4. **Oracle Database Updates**: Specific
dates like July 2024 for Release Update 23.5 are mentioned where new features
were introduced which include some noteworthy updates under the heading "AI
Vector Search" as part of Oracle Database 23ai.
It appears to be quite a robust
update covering areas such as auditing, security (database vaults and label
security), data access monitoring, unified audit trail capabilities in addition
to AI vector search enhancements. Therefore, it seems you could expect
improvements focusing on artificial intelligence, machine learning, data
visualization features along with various database management aspects like
enhanced indexation techniques (like automated indexes) and caching mechanisms
among others.
Please note that this isn't an exhaustive list and there might be
multiple new features or developments which have not been mentioned here. It
would be wise to review the detailed documentation for more specific and
comprehensive details on these new additions in Oracle 23ai.
|
第三次运行：
*** Result:
In the document you provided, there are references to several updates and enhancements included in Oracle Database version 23ai. However, a full list of all new features for Oracle 23ai isn't entirely detailed here.
Among some highlights:
- **Autonomous Database Updates**: New AI Vector Search features have been introduced as part of the release update.
- **AI
Vector Search Features**: New updates are included in Oracle Database 23ai as part of Release Update 23.5 for AI Vector search capabilities.
- **Security
Enhancements**: This includes improvements related to audit records, fine-grained auditing options, and enhancements to products like Oracle Audit Vault, Database Firewall, Oracle Database Vault, Oracle Label Security, and Unified Audit Trail.
- **Performance Optimization**: Updates on database optimization such as automated indexes, automatic big table caching, and Automatic Diagnostic Monitoring tools have been made.
The exact new features will depend on specifics within each release note for the individual components mentioned. To provide a definitive list of what's new in Oracle 23ai for every aspect you've asked about would require much more detailed information than is provided here.
PL/SQL 过程已成功完成。 |
7. 总结：
以上三次运行，基本上都提到了最重要的Oracle Database 23ai的新特性，基本上都强调了AI Vector Search。运行结果再没出现在实验2中出现的不了解23ai是什么版本的幻觉（hallucination）。 任何一个LLM（比如我们使用的qwen2:7b-instruct）的训练数据都是是静态的，并且在其知识上有一个截止日期。LLM运行时，在它的知识盲区，它会胡编答案（称为幻觉）：在用户期望特定、当前响应时，提供过时或通用信息；从非权威来源创建响应；由于术语混淆而创建不准确的响应，不同的训练来源使用相同的术语来谈论不同的事情。RAG是解决其中一些挑战的一种方法。通过提示工程，在RAG的应用场景中，特定领域的知识会被发送给LLM。当使用外部模型或服务时，用户将面临私有数据暴露给第三方的风险。这正是使用Oracle Database 23ai的知识库实现100%本地化RAG的意义。
|
