Vector index creation in Oracle 23ai

As you are aware there is AI embedded in Oracle 23ai powered by Vector Search, you can create vector indexes in Oracle 23ai, To allow vector index creation, you must enable a new memory area stored in the SGA called the Vector Pool. The Vector Pool is a memory allocated in SGA to store Hierarchical Navigable Small World (HNSW) vector indexes and all associated metadata. It is also used to speed up Inverted Flat File (IVF) index creation as well as DML operations on base tables with IVF indexes.

To size the Vector Pool, use the VECTOR_MEMORY_SIZE initialization parameter. You can dynamically modify this parameter at the following levels:

1) At the CDB level VECTOR_MEMORY_SIZE specifies the current size of the Vector Pool. Reducing the parameter value will fail if there is current vector usage.

2) At the PDB level VECTOR_MEMORY_SIZE specifies the maximum Vector Pool usage allowed by a PDB. Reducing the parameter value will be allowed even if current vector usage exceeds the new quota.

3) You can change the value of a parameter in a parameter file in the following ways:

3a) By editing an initialization parameter file. In most cases, the new value takes effect the next time you start an instance of the database.

3b) By issuing an ALTER SYSTEM SET ... SCOPE=SPFILE statement to update a server parameter file.

3c) By issuing an ALTER SYSTEM RESET statement to clear an initialization parameter value and set it back to its default value.

Lets see a demo how you can do it :

Connected to:

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.4.0.24.05

SQL> show con_name

CON_NAME

------------------------------

FREEPDB1

SQL>  show user

USER is "SYS"

SQL>  show parameter vector_memory_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

vector_memory_size                   big integer 0

SQL> SELECT ISPDB_MODIFIABLE FROM   V$SYSTEM_PARAMETER WHERE  NAME='vector_memory_size';

ISPDB

-----

TRUE

SQL>  ALTER SYSTEM SET vector_memory_size=1G SCOPE=BOTH;

System altered.

SQL> show parameter vector_memory_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

vector_memory_size                   big integer 1G

V$VECTOR_MEMORY_POOL, is the view you can query to monitor the Vector Pool.

Reference : https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/size-vector-pool.html