Experiment with Local IVF Indexes

You can start experimenting with LOCAL IVF 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 the base table using the partition scheme of your choice:

    RANGE partitioning:

    DROP TABLE sales_data PURGE;
    
    
    CREATE TABLE sales_data
    (
        product_id NUMBER,
        customer_id NUMBER,
        sale_date DATE,
        amount_sold NUMBER,
        vec vector(8),
        region VARCHAR2(20)
    )
    PARTITION BY RANGE (product_id)
    (
        PARTITION sales_1 VALUES LESS THAN (100),
        PARTITION sales_2 VALUES LESS THAN (200),
        PARTITION sales_3 VALUES LESS THAN (300),
        PARTITION sales_4 VALUES LESS THAN (400),
        PARTITION sales_5 VALUES LESS THAN (500),
        PARTITION sales_6 VALUES LESS THAN (600),
        PARTITION sales_7 VALUES LESS THAN (700),
        PARTITION sales_8 VALUES LESS THAN (800),
        PARTITION sales_9 VALUES LESS THAN (900),
        PARTITION sales_10 VALUES LESS THAN (1000),
        PARTITION sales_default VALUES LESS THAN (1000000)
    );

    LIST partitioning:

    DROP TABLE sales_data PURGE;
    
    CREATE TABLE sales_data
    (
        product_id   NUMBER,
        customer_id  NUMBER,
        sale_date    DATE,
        amount_sold  NUMBER,
        vec          VECTOR(8),
        region       VARCHAR2(20)
    )
    PARTITION BY LIST (region)
    (
        PARTITION RegionA_Partition VALUES ('RegionA1','RegionA2','RegionA3','RegionA4','RegionA5'),
        PARTITION RegionB_Partition VALUES ('RegionB1','RegionB2','RegionB3','RegionB4','RegionB5'),
        PARTITION RegionC_Partition VALUES ('RegionC1','RegionC2','RegionC3','RegionC4','RegionC5'),
        PARTITION RegionD_Partition VALUES ('RegionD1','RegionD2','RegionD3','RegionD4','RegionD5'),
        PARTITION RegionE_Partition VALUES ('RegionE1','RegionE2','RegionE3','RegionE4','RegionE5'),
        PARTITION RegionF_Partition VALUES ('RegionF1','RegionF2','RegionF3','RegionF4','RegionF5'),
        PARTITION RegionG_Partition VALUES ('RegionG1','RegionG2','RegionG3','RegionG4','RegionG5'),
        PARTITION RegionH_Partition VALUES ('RegionH1','RegionH2','RegionH3','RegionH4','RegionH5'),
        PARTITION RegionI_Partition VALUES ('RegionI1','RegionI2','RegionI3','RegionI4','RegionI5'),
        PARTITION RegionJ_Partition VALUES ('RegionJ1','RegionJ2','RegionJ3','RegionJ4','RegionJ5'),
        PARTITION Other_Region_Partition VALUES (DEFAULT)
    );

    HASH partitioning:

    DROP TABLE sales_data PURGE;
    
    CREATE TABLE sales_data
    (
        product_id   NUMBER,
        customer_id  NUMBER,
        sale_date    DATE,
        amount_sold  NUMBER,
        vec          VECTOR(8),
        region       VARCHAR2(20)
    )
    PARTITION BY HASH (product_id)
    PARTITIONS 10;
  2. Use this procedure to randomly insert data into the SALES_DATA table:
    CREATE OR REPLACE PROCEDURE insert_sales_data(numRows IN INTEGER, maxProductId IN INTEGER) AS
      TYPE vec_array IS VARRAY(8) OF NUMBER;
    BEGIN
      DBMS_RANDOM.INITIALIZE(100);
        FOR i IN 1..numRows LOOP
          INSERT INTO sales_data (product_id, customer_id, sale_date, amount_sold, vec, region)
            VALUES (round(DBMS_RANDOM.VALUE(1, MaxProductId)),    -- Random product_id between 1 and 1000
                    round(DBMS_RANDOM.VALUE(1, 10000)),   -- Random customer_id between 1 and 10000
                    (DATE '2024-05-10' - DBMS_RANDOM.VALUE(1, 1460)), -- Random sale_date within the last 4 years
                    DBMS_RANDOM.VALUE(10, 10000),  -- Random amount_sold between 10 and 1000
                    '[' ||
                        to_char(DBMS_RANDOM.VALUE(0, 1000)) || ',' ||
                        to_char(DBMS_RANDOM.VALUE(0, 1000)) || ',' ||
                        to_char(DBMS_RANDOM.VALUE(0, 1000)) || ',' ||
                        to_char(DBMS_RANDOM.VALUE(0, 1000)) || ',' ||
                        to_char(DBMS_RANDOM.VALUE(0, 1000)) || ',' ||
                        to_char(DBMS_RANDOM.VALUE(0, 1000)) || ',' ||
                        to_char(DBMS_RANDOM.VALUE(0, 1000)) || ',' ||
                        to_char(DBMS_RANDOM.VALUE(0, 1000)) ||
                    ']',
                    CASE MOD(i, 10)
                        WHEN 0 THEN 'RegionA' || MOD(i,5)
                        WHEN 1 THEN 'RegionB' || MOD(i,5)
                        WHEN 2 THEN 'RegionC' || MOD(i,5)
                        WHEN 3 THEN 'RegionD' || MOD(i,5)
                        WHEN 4 THEN 'RegionE' || MOD(i,5)
                        WHEN 5 THEN 'RegionF' || MOD(i,5)
                        WHEN 6 THEN 'RegionG' || MOD(i,5)
                        WHEN 7 THEN 'RegionH' || MOD(i,5)
                        WHEN 8 THEN 'RegionI' || MOD(i,5)
                        ELSE        'RegionJ' || MOD(i,5)
                    END);
    
            IF MOD(i, 10000) = 0 THEN
                COMMIT;
            END IF;
        END LOOP;
        COMMIT;
    END insert_sales_data;
    /
    
    EXEC insert_sales_data(20000, 501);
  3. Create a LOCAL IVF index on the VEC column of the SALES_DATA table:
    CREATE VECTOR INDEX vidxivf ON sales_data(vec) 
    ORGANIZATION NEIGHBOR PARTITIONS 
    WITH TARGET ACCURACY 95 
    DISTANCE EUCLIDEAN PARAMETERS(TYPE IVF, NEIGHBOR PARTITION 20) LOCAL;
  4. Check all index partitions state before and after running Partition Maintenance Operation commands:
    SELECT INDEX_NAME, PARTITION_NAME, STATUS 
    FROM USER_IND_PARTITIONS 
    WHERE index_name LIKE upper('vidxivf') 
    ORDER BY 1, 2;
  5. Test the following ALTER TABLE commands to see what is supported and what is not:

    If the table is RANGE partitioned:

    ALTER TABLE sales_data ADD PARTITION sales_1000000 VALUES LESS THAN (2000000);
    
    ALTER TABLE sales_data
    SPLIT PARTITION sales_2
    AT (150)
    INTO (
          PARTITION sales_21,
          PARTITION sales_22
    );
    
    ALTER TABLE sales_data DROP PARTITION sales_3;
    If the table is LIST partitioned:
    ALTER TABLE sales_data 
    SPLIT PARTITION RegionE_Partition VALUES ('RegionE1', 'RegionE2', 'RegionE3') 
    INTO 
      ( PARTITION RegionE1_Partition,
        PARTITION RegionE2_Partition
      );
    
    ALTER TABLE sales_data DROP PARTITION RegionB_Partition;

    If the table is HASH partitioned:

    ALTER TABLE sales_data ADD PARTITION;
    
    SELECT partition_name FROM user_tab_partitions WHERE table_name='SALES_DATA';
    
    ALTER TABLE sales_data COALESCE PARTITION;