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.
- 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;
- 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);
- Create a
LOCAL
IVF index on the VEC column of theSALES_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;
- 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;
- 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 isLIST
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;