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.

  1. 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'))
);
  2. 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]');
  3. Create a GLOBAL vector index:

    1. Create a GLOBAL IVF index on column vec

      CREATE VECTOR INDEX VIDXP ON sales_data(vec)
ORGANIZATION NEIGHBOR PARTITIONS
WITH TARGET ACCURACY 95 DISTANCE EUCLIDEAN
PARAMETERS(type IVF, NEIGHBOR PARTITION 2);

    2. Create a GLOBAL HNSW index on column vec: 

      CREATE VECTOR INDEX VIDXP ON sales_data(vec)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
WITH TARGET ACCURACY 95 DISTANCE EUCLIDEAN
PARAMETERS (type HNSW, efConstruction 500);

    3. 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;

    4. Check the status of the IVF index:

      SELECT 
  index_name,
  status
FROM user_indexes
WHERE index_type = 'VECTOR' AND table_name = 'SALES_DATA';
  4. Add a partition:
    ALTER TABLE sales_data 
ADD PARTITION sales_2025
VALUES LESS THAN (TO_DATE('01-JAN-2026','DD-MON-YYYY'));
 
commit;
  5. Drop a partition:
    ALTER TABLE sales_data 
DROP PARTITION sales_2022 
UPDATE GLOBAL INDEXES;

commit;
  6. 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;
  7. Split a partition:
    ALTER TABLE sales_data
SPLIT PARTITION sales_2025
AT (TO_DATE('01-NOV-2025', 'DD-MON-YYYY')) 
UPDATE GLOBAL INDEXES;

commit;
  8. 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;
  9. Truncate a partition:
    ALTER TABLE sales_data 
TRUNCATE PARTITION sales_2021 
UPDATE GLOBAL INDEXES;

commit;
  10. Create a table partitioned on HASH to test the COALESCE 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);
  11. Create a GLOBAL vector index on column vec:
    1. 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);
    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);
  12. RENAME a partition:
    ALTER TABLE sales RENAME PARTITION P1 TO NewP1;

commit;
  13. COALESCE partitions:
    ALTER TABLE sales COALESCE PARTITION UPDATE INDEXES:

commit;

Parent topic: Partition Maintenance Operations with GLOBAL Vector Indexes