Experiment with Global IVF and HNSW Indexes
You can start experimenting with GLOBAL
IVF and HNSW
indexes using the included code. These are not complete scenarios but rather a series of SQL
commands to help you get started on your own testing.
- Create a table partitioned on
RANGE
:CREATE TABLE sales_data (product_id NUMBER, sale_date DATE, vec vector(2)) PARTITION BY RANGE (sale_date) ( PARTITION sales_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022','DD-MON-YYYY')), PARTITION sales_2022 VALUES LESS THAN (TO_DATE('01-JAN-2023','DD-MON-YYYY')), PARTITION sales_2023 VALUES LESS THAN (TO_DATE('01-JAN-2024','DD-MON-YYYY')), PARTITION sales_2024 VALUES LESS THAN (TO_DATE('01-JAN-2025','DD-MON-YYYY')) );
- Populate the table with data for all 4
partitions:
INSERT INTO sales_data (product_id, sale_date, vec) VALUES (101, TO_DATE('20-DEC-2021', 'DD-MON-YYYY'), '[0.1, 0.1]'), (102, TO_DATE('15-NOV-2021', 'DD-MON-YYYY'), '[0.2, 0.2]'), (103, TO_DATE('05-OCT-2021', 'DD-MON-YYYY'), '[0.3, 0.3]'), (104, TO_DATE('20-SEP-2021', 'DD-MON-YYYY'), '[0.4, 0.4]'), (105, TO_DATE('10-AUG-2021', 'DD-MON-YYYY'), '[0.5, 0.5]'); INSERT INTO sales_data (product_id, sale_date, vec) VALUES (201, TO_DATE('20-DEC-2022', 'DD-MON-YYYY'), '[10.1, 10.1]'), (202, TO_DATE('15-NOV-2022', 'DD-MON-YYYY'), '[10.2, 10.2]'), (203, TO_DATE('05-OCT-2022', 'DD-MON-YYYY'), '[10.3, 10.3]'), (204, TO_DATE('20-SEP-2022', 'DD-MON-YYYY'), '[10.4, 10.4]'), (205, TO_DATE('10-AUG-2022', 'DD-MON-YYYY'), '[10.5, 10.5]'); INSERT INTO sales_data (product_id, sale_date, vec) VALUES (301, TO_DATE('20-DEC-2023', 'DD-MON-YYYY'), '[0.1, 0.1]'), (302, TO_DATE('15-NOV-2023', 'DD-MON-YYYY'), '[0.2, 0.2]'), (303, TO_DATE('05-OCT-2023', 'DD-MON-YYYY'), '[0.3, 0.3]'), (304, TO_DATE('20-SEP-2023', 'DD-MON-YYYY'), '[0.4, 0.4]'), (305, TO_DATE('10-AUG-2023', 'DD-MON-YYYY'), '[0.5, 0.5]'); INSERT INTO sales_data (product_id, sale_date, vec) VALUES (401, TO_DATE('20-DEC-2024', 'DD-MON-YYYY'), '[10.1, 10.1]'), (402, TO_DATE('15-NOV-2024', 'DD-MON-YYYY'), '[10.2, 10.2]'), (403, TO_DATE('05-OCT-2024', 'DD-MON-YYYY'), '[10.3, 10.3]'), (404, TO_DATE('20-SEP-2024', 'DD-MON-YYYY'), '[10.4, 10.4]'), (405, TO_DATE('10-AUG-2024', 'DD-MON-YYYY'), '[10.5, 10.5]');
- Create a
GLOBAL
vector index:-
Create a
GLOBAL
IVF index on columnvec
CREATE VECTOR INDEX VIDXP ON sales_data(vec) ORGANIZATION NEIGHBOR PARTITIONS WITH TARGET ACCURACY 95 DISTANCE EUCLIDEAN PARAMETERS(type IVF, NEIGHBOR PARTITION 2);
-
Create a
GLOBAL
HNSW index on columnvec
:CREATE VECTOR INDEX VIDXP ON sales_data(vec) ORGANIZATION INMEMORY NEIGHBOR GRAPH WITH TARGET ACCURACY 95 DISTANCE EUCLIDEAN PARAMETERS (type HNSW, efConstruction 500);
-
Check the partitions of the base table:
SELECT object_name, subobject_name, object_id, data_object_id FROM user_objects WHERE object_name = 'SALES_DATA' ORDER BY subobject_name;
-
Check the status of the IVF index:
SELECT index_name, status FROM user_indexes WHERE index_type = 'VECTOR' AND table_name = 'SALES_DATA';
-
- Add a
partition:
ALTER TABLE sales_data ADD PARTITION sales_2025 VALUES LESS THAN (TO_DATE('01-JAN-2026','DD-MON-YYYY')); commit;
- Drop a
partition:
ALTER TABLE sales_data DROP PARTITION sales_2022 UPDATE GLOBAL INDEXES; commit;
- Merge two partitions into a new
one:
ALTER TABLE sales_data MERGE PARTITIONS sales_2023, sales_2024 INTO PARTITION sales_2023_2024 UPDATE GLOBAL INDEXES; commit;
- Split a
partition:
ALTER TABLE sales_data SPLIT PARTITION sales_2025 AT (TO_DATE('01-NOV-2025', 'DD-MON-YYYY')) UPDATE GLOBAL INDEXES; commit;
- Exchange a partition with a source
table:
--create another table to exchange data with the table sales_data CREATE TABLE sales_data2 (product_id NUMBER, sale_date DATE, vec VECTOR(2)); INSERT INTO sales_data2 (product_id, sale_date, vec) VALUES (111, TO_DATE('22-DEC-2021', 'DD-MON-YYYY'), '[110.1, 20.1]'), (112, TO_DATE('22-NOV-2021', 'DD-MON-YYYY'), '[110.1, 20.1]'), (113, TO_DATE('22-OCT-2021', 'DD-MON-YYYY'), '[110.1, 20.1]'), (114, TO_DATE('22-SEP-2021', 'DD-MON-YYYY'), '[110.1, 20.1]'), (115, TO_DATE('22-AUG-2021', 'DD-MON-YYYY'), '[110.1, 20.1]'); --exchange the data between sales_data and sales_data2 ALTER TABLE sales_data EXCHANGE PARTITION sales_2021 WITH TABLE sales_data2 UPDATE GLOBAL INDEXES; commit;
- Truncate a
partition:
ALTER TABLE sales_data TRUNCATE PARTITION sales_2021 UPDATE GLOBAL INDEXES; commit;
- Create a table partitioned on
HASH
to test theCOALESCE
operation:CREATE TABLE sales ( part_no NUMBER, sale_date DATE, vec VECTOR(2), pcount NUMBER ) PARTITION BY HASH (pcount) (PARTITION P1, PARTITION P2, PARTITION P3); --populate data into the table INSERT INTO sales (part_no, sale_date, vec, pcount) VALUES (101, TO_DATE('20-DEC-2021', 'DD-MON-YYYY'), '[1.01, 1.01]', 101), (102, TO_DATE('15-NOV-2021', 'DD-MON-YYYY'), '[1.02, 1.02]', 102), (103, TO_DATE('05-OCT-2021', 'DD-MON-YYYY'), '[1.03, 1.03]', 103), (104, TO_DATE('20-SEP-2021', 'DD-MON-YYYY'), '[1.04, 1.04]', 104), (105, TO_DATE('10-AUG-2021', 'DD-MON-YYYY'), '[1.05, 1.05]', 105), (201, TO_DATE('20-DEC-2022', 'DD-MON-YYYY'), '[2.01, 2.01]', 201), (202, TO_DATE('15-NOV-2022', 'DD-MON-YYYY'), '[2.02, 2.02]', 202), (203, TO_DATE('05-OCT-2022', 'DD-MON-YYYY'), '[2.03, 2.03]', 203), (204, TO_DATE('20-SEP-2022', 'DD-MON-YYYY'), '[2.04, 2.04]', 204), (205, TO_DATE('10-AUG-2022', 'DD-MON-YYYY'), '[2.05, 2.05]', 205);
- Create a
GLOBAL
vector index on columnvec
:- Create a
GLOBAL
IVF index:CREATE VECTOR INDEX VIDXP ON sales(vec) ORGANIZATION NEIGHBOR PARTITIONS WITH TARGET ACCURACY 95 DISTANCE EUCLIDEAN PARAMETERS(type IVF, NEIGHBOR PARTITION 2);
- Create a
GLOBAL
HNSW index:CREATE VECTOR INDEX VIDXP ON sales(vec) ORGANIZATION INMEMORY NEIGHBOR GRAPH WITH TARGET ACCURACY 95 DISTANCE EUCLIDEAN PARAMETERS(type HNSW, efConstruction 500);
- Create a
RENAME
a partition:ALTER TABLE sales RENAME PARTITION P1 TO NewP1; commit;
COALESCE
partitions:ALTER TABLE sales COALESCE PARTITION UPDATE INDEXES: commit;