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;