반응형
벡터 인덱스
인덱스가 왜 필요한가?
- 지금 lab_doc_chunks처럼 몇 줄 안 되는 테이블에서는 인덱스가 없어도 Full Scan으로 가장 가까운 벡터를 찾음
- 이런걸 Exact(완전탐색) 검색이라고 하고, 100% 정확하지만, 데이터가 수백만 건이 되면 매 쿼리마다 전부 거리 계산을 해야 해서 느려짐.
Approximate(근사) 검색
- 미리 '지도'를 만들어두고 그 지도를 따라가면서 '거의 확실히 가까운 것들'만 빠르게 찾는 방식
- 100% 정확하진 않을 수 있지만, 훨씬 빠릅니다. 이 '지도'가 바로 벡터 인덱스.
HNSW (Hierarchical Navigable Small World)
- '여러 레이어로 구성 된 고속도로 지도'라고 생각 하면 됨.
- 맨 위층은 큰 거점 도시들만 연결된 고속도로, 아래로 내려갈수록 촘촘한 국도/골목길 형태로 구성.
- 검색할 때 위에서부터 목적지 방향으로 내려오면서 빠르게 근처까지 도달
- HNSW는 Vector Memory Pool에 상주.
[oracle@rhel10nm ~]$ #데이터셋 확장
[oracle@rhel10nm ~]$ id
uid=5001(oracle) gid=500(dba) groups=500(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@rhel10nm ~]$ env | grep SID
ORACLE_SID=CDB1
[oracle@rhel10nm ~]$
[oracle@rhel10nm ~]$ sqlplus "/as sysdba"
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> --VECLAB User로 PDB1 접속
SQL> CONNECT veclab/VecLab_2026#@//localhost:1521/pdb1
Connected.
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> show user
USER is "VECLAB"
SQL> --서로 다른 주제의 문서 4개를 더 추가해서, 의미가 비슷한 것과 아닌 것이 실제로 구분되는지 확인
SQL> INSERT INTO lab_raw_docs (doc_id, doc_title, doc_text) VALUES (
2 2, 'RAC Guide',
3 'Oracle Real Application Clusters allows multiple database instances to access a single shared database concurrently. Each instance runs on a separate server node, and the Cache Fusion technology synchronizes data blocks across instances using a high-speed interconnect. RAC primarily addresses high availability at the instance level, whereas Data Guard protects against complete site failures by maintaining physically or logically separate copies of the database.'
4 );
1 row created.
SQL>
SQL> INSERT INTO lab_raw_docs (doc_id, doc_title, doc_text) VALUES (
2 3, 'RMAN Guide',
3 'Recovery Manager, commonly known as RMAN, is Oracle''s built-in utility for backup and recovery operations. It can perform full or incremental backups, and supports block change tracking to speed up incremental backups. RMAN backups can be validated for corruption, and Oracle recommends periodically testing restore procedures to ensure that recovery is possible when needed.'
4 );
1 row created.
SQL>
SQL> INSERT INTO lab_raw_docs (doc_id, doc_title, doc_text) VALUES (
2 4, 'ASM Guide',
3 'Automatic Storage Management, or ASM, is Oracle''s built-in volume manager and file system designed specifically for database files. ASM stripes and mirrors data across multiple disks automatically, removing the need for a separate logical volume manager. ASM disk groups can be configured with different redundancy levels to protect against disk failures.'
4 );
1 row created.
SQL>
SQL> INSERT INTO lab_raw_docs (doc_id, doc_title, doc_text) VALUES (
2 5, 'GoldenGate Guide',
3 'Oracle GoldenGate provides real-time data replication and integration between heterogeneous database systems. Unlike Data Guard, which typically replicates an entire database, GoldenGate can replicate selected tables or transactions, and supports replication between databases from different vendors. This makes GoldenGate a common choice for zero-downtime migrations and active-active replication topologies.'
4 );
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> -- Run the same chunk+embed pipeline from Module 3 for these 4 new docs
SQL> INSERT INTO lab_doc_chunks (doc_id, chunk_id, chunk_text, chunk_embedding)
2 SELECT
3 dt.doc_id,
4 et.embed_id AS chunk_id,
5 et.embed_data AS chunk_text,
6 TO_VECTOR(et.embed_vector) AS chunk_embedding
7 FROM lab_raw_docs dt,
8 DBMS_VECTOR_CHAIN.UTL_TO_EMBEDDINGS(
9 DBMS_VECTOR_CHAIN.UTL_TO_CHUNKS(
10 dt.doc_text,
11 JSON('{"by":"words", "max":"30", "overlap":"8", "normalize":"all"}')
12 ),
13 JSON('{"provider":"database", "model":"ALL_MINILM_L12_V2"}')
14 ) t,
15 JSON_TABLE(t.column_value, '$[*]'
16 COLUMNS (
17 embed_id NUMBER PATH '$.embed_id',
18 embed_data VARCHAR2(4000) PATH '$.embed_data',
19 embed_vector CLOB PATH '$.embed_vector'
20 )
21 ) et
22 WHERE dt.doc_id IN (2,3,4,5);
12 rows created.
SQL> COMMIT;
Commit complete.
SQL> --결과 확인
SQL> SELECT doc_id, COUNT(*) AS chunk_count FROM lab_doc_chunks GROUP BY doc_id ORDER BY doc_id;
DOC_ID CHUNK_COUNT
---------- -----------
1 5
2 3
3 3
4 3
5 3
SQL>
SQL> --HNSW 인덱스 생성
SQL> CREATE VECTOR INDEX lab_chunks_hnsw_idx
2 ON lab_doc_chunks (chunk_embedding)
3 ORGANIZATION INMEMORY NEIGHBOR GRAPH
4 DISTANCE COSINE
5 WITH TARGET ACCURACY 95;
Index created.
SQL>
SQL> --PDB1에 SYS User 접속 해서 v$vector_memory_pool확인
SQL> connect sys/welcome1@//localhost:1521/pdb1 as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> SELECT pool,
2 ROUND(alloc_bytes/1024/1024, 2) AS alloc_mb,
3 ROUND(used_bytes/1024/1024, 2) AS used_mb
4 FROM v$vector_memory_pool;
POOL ALLOC_MB USED_MB
-------------------------- ---------- ----------
1MB POOL 224 1
64KB POOL 24 .31
SQL> --USED_MB가 이제 0보다 큰 값인지 확인
SQL> exit
[oracle@rhel10nm ~]$
[oracle@rhel10nm ~]$ --##Exact vs Approximate 검색 비교
[oracle@rhel10nm ~]$ id
uid=5001(oracle) gid=500(dba) groups=500(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@rhel10nm ~]$ env | grep SID
ORACLE_SID=CDB1
[oracle@rhel10nm ~]$ sqlplus "/as sysdba"
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> CONNECT veclab/VecLab_2026#@//localhost:1521/pdb1
Connected.
SQL>
SQL> SET LINESIZE 150
SQL> COLUMN chunk_text FORMAT A55
SQL> COLUMN distance FORMAT 0.9999
SQL> --Exact search
SQL> SELECT doc_id, chunk_id, chunk_text,
2 VECTOR_DISTANCE(
3 chunk_embedding,
4 VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'How do I switch over to a standby database during an outage?' AS DATA),
5 COSINE
6 ) AS distance
7 FROM lab_doc_chunks
8 ORDER BY VECTOR_DISTANCE(
9 chunk_embedding,
10 VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'How do I switch over to a standby database during an outage?' AS DATA),
11 COSINE
12 )
13 FETCH EXACT FIRST 3 ROWS ONLY;
DOC_ID CHUNK_ID CHUNK_TEXT DISTANCE
---------- ---------- ------------------------------------------------------- --------
1 2 or more standby databases that are transactionally cons 0.3477
istent
copies of a production database. When the production da
tabase becomes unavailable, Data Guard can switch any s
tandby to the production
1 4 the production role, minimizing downtime. Active 0.4607
Data Guard extends this capability by allowing the stan
dby database to be opened for read-only queries and rep
orting while apply
DOC_ID CHUNK_ID CHUNK_TEXT DISTANCE
---------- ---------- ------------------------------------------------------- --------
1 3 Guard can switch any standby to the production role, mi 0.5322
nimizing downtime. Active
SQL>
SQL> -- Approximate search
SQL> SELECT doc_id, chunk_id, chunk_text,
2 VECTOR_DISTANCE(
3 chunk_embedding,
4 VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'How do I switch over to a standby database during an outage?' AS DATA),
5 COSINE
6 ) AS distance
7 FROM lab_doc_chunks
8 ORDER BY VECTOR_DISTANCE(
9 chunk_embedding,
10 VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'How do I switch over to a standby database during an outage?' AS DATA),
11 COSINE
12 )
13 FETCH APPROX FIRST 3 ROWS ONLY WITH TARGET ACCURACY 90;
DOC_ID CHUNK_ID CHUNK_TEXT DISTANCE
---------- ---------- ------------------------------------------------------- --------
1 2 or more standby databases that are transactionally cons 0.3477
istent
copies of a production database. When the production da
tabase becomes unavailable, Data Guard can switch any s
tandby to the production
1 4 the production role, minimizing downtime. Active 0.4607
Data Guard extends this capability by allowing the stan
dby database to be opened for read-only queries and rep
orting while apply
DOC_ID CHUNK_ID CHUNK_TEXT DISTANCE
---------- ---------- ------------------------------------------------------- --------
1 3 Guard can switch any standby to the production role, mi 0.5322
nimizing downtime. Active
SQL> --Exact와 Approximate 결과 일치했고(distance 0.3477, 0.4607, 0.5322), 상위 3개 전부 Data Guard 문서(doc_id=1)
SQL> exit
[oracle@rhel10nm ~]$
IVF (Inverted File)
- '동네별로 미리 나눠놓은 우편번호 시스템'이라고 생각 하면 됨
- 전체 데이터를 여러 클러스터(파티션)로 미리 묶어두고, 검색할 때 질문과 가장 가까운 클러스터 몇 개만 조회 함.
- 디스크에 있어도 되고(메모리 제약이 없음), 데이터가 아주 클 때 유리.
※HNSW는 로컬 메모리에 있기 때문에 Oracle RAC에서는 지원되지 않고, RAC 환경에서는 IVF를 써야 함
[oracle@rhel10nm ~]$
[oracle@rhel10nm ~]$ --##IVF 인덱스 & Multi-Vector 검색
[oracle@rhel10nm ~]$ sqlplus "/as sysdba"
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> CONNECT veclab/VecLab_2026#@//localhost:1521/pdb1
Connected.
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL>
SQL> show user
USER is "VECLAB"
SQL>
SQL> --IVF 인덱스로 교체해서 비교
SQL> --한 컬럼엔 벡터 인덱스를 하나만 걸 수 있어서, 기존 HNSW를 지우고 IVF로 생성
SQL> DROP INDEX lab_chunks_hnsw_idx;
Index dropped.
SQL>
SQL> CREATE VECTOR INDEX lab_chunks_ivf_idx
2 ON lab_doc_chunks (chunk_embedding)
3 ORGANIZATION NEIGHBOR PARTITIONS
4 DISTANCE COSINE
5 WITH TARGET ACCURACY 95;
Index created.
SQL>
SQL> --결과 확인
SQL> SET LINESIZE 150
SQL> COLUMN chunk_text FORMAT A55
SQL> COLUMN distance FORMAT 0.9999
SQL> SELECT doc_id, chunk_id, chunk_text,
2 VECTOR_DISTANCE(
3 chunk_embedding,
4 VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'How do I switch over to a standby database during an outage?' AS DATA),
5 COSINE
6 ) AS distance
7 FROM lab_doc_chunks
8 ORDER BY VECTOR_DISTANCE(
9 chunk_embedding,
10 VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'How do I switch over to a standby database during an outage?' AS DATA),
11 COSINE
12 )
13 FETCH APPROX FIRST 3 ROWS ONLY WITH TARGET ACCURACY 90;
DOC_ID CHUNK_ID CHUNK_TEXT DISTANCE
---------- ---------- ------------------------------------------------------- --------
1 2 or more standby databases that are transactionally cons 0.3477
istent
copies of a production database. When the production da
tabase becomes unavailable, Data Guard can switch any s
tandby to the production
1 4 the production role, minimizing downtime. Active 0.4607
Data Guard extends this capability by allowing the stan
dby database to be opened for read-only queries and rep
orting while apply
DOC_ID CHUNK_ID CHUNK_TEXT DISTANCE
---------- ---------- ------------------------------------------------------- --------
1 3 Guard can switch any standby to the production role, mi 0.5322
nimizing downtime. Active
SQL> --IVF 인덱스로 바꾼 뒤에도 HNSW 때와 완전히 동일한 결과(distance 0.3477 / 0.4607 / 0.5322)
SQL>
SQL> --Multi-Vector 패턴 — 제목/본문 가중치 조합 검색
SQL> CREATE TABLE lab_multi_vec_docs (
2 doc_id NUMBER,
3 doc_title VARCHAR2(100),
4 doc_body VARCHAR2(500),
5 title_embedding VECTOR(384, FLOAT32),
6 body_embedding VECTOR(384, FLOAT32)
7 );
Table created.
SQL> desc lab_multi_vec_docs
Name Null? Type
----------------------------------------- -------- ----------------------------
DOC_ID NUMBER
DOC_TITLE VARCHAR2(100)
DOC_BODY VARCHAR2(500)
TITLE_EMBEDDING VECTOR(384, FLOAT32, DENSE)
BODY_EMBEDDING VECTOR(384, FLOAT32, DENSE)
SQL>
SQL>
SQL> set echo on
SQL> INSERT INTO lab_multi_vec_docs (doc_id, doc_title, doc_body, title_embedding, body_embedding)
2 SELECT 1, 'Data Guard Failover Steps',
3 'Switch the standby database to the primary role using the broker or manual SQL commands.',
4 VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Data Guard Failover Steps' AS DATA),
5 VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Switch the standby database to the primary role using the broker or manual SQL commands.' AS DATA)
6 FROM dual;
1 row created.
SQL>
SQL> INSERT INTO lab_multi_vec_docs (doc_id, doc_title, doc_body, title_embedding, body_embedding)
2 SELECT 2, 'RMAN Backup Validation',
3 'Run a validate backup command to check for corruption before you actually need to restore.',
4 VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'RMAN Backup Validation' AS DATA),
5 VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Run a validate backup command to check for corruption before you actually need to restore.' AS DATA)
6 FROM dual;
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> --결과 확인 테이블 생성 확인
SQL> set linesize 150
SQL> col DOC_TITLE for a50
SQL> SELECT doc_id, doc_title, VECTOR_DIMS(title_embedding) AS title_dims, VECTOR_DIMS(body_embedding) AS body_dims
2 FROM lab_multi_vec_docs;
DOC_ID DOC_TITLE TITLE_DIMS BODY_DIMS
---------- -------------------------------------------------- ---------- ----------
1 Data Guard Failover Steps 384 384
2 RMAN Backup Validation 384 384
SQL> --TITLE_DIMS, BODY_DIMS가 384로 나와야 함
SQL>
SQL> --결과 확인 조합 검색 — 제목 30% + 본문 70% 가중치
SQL> SELECT doc_id, doc_title,
2 ROUND(
3 0.3 * VECTOR_DISTANCE(title_embedding, VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'how to fail over a database' AS DATA), COSINE) +
4 0.7 * VECTOR_DISTANCE(body_embedding, VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'how to fail over a database' AS DATA), COSINE)
5 , 4) AS combined_distance
6 FROM lab_multi_vec_docs
7 ORDER BY combined_distance
8 FETCH EXACT FIRST 2 ROWS ONLY;
DOC_ID DOC_TITLE COMBINED_DISTANCE
---------- -------------------------------------------------- -----------------
1 Data Guard Failover Steps .6295
2 RMAN Backup Validation .7648
SQL> --doc_id=1(Data Guard Failover Steps)이 combined_distance 0.6295로 1등, RMAN 문서는 0.7648로 2등으로 반환 됨
SQL> exit
[oracle@rhel10nm ~]$
반응형
