For more information on what’s new in Oracle Database 23ai, see: Oracle Database 23 New Features Guide.

Binary VECTOR dimensions

The vector data type enables you to store the output of embedding models in the database. The default dimension format for these is float32.

A binary vector stores the values as packed uint8 bytes, meaning that a single byte represents exactly 8 BINARY dimensions and no less. The number of dimensions in a binary vector must be a multiple of 8.

The advantages of this format are: 

  • The storage footprint of vectors can be reduced by a factor of 32 compared to the default float32 format.  

  • Distance computations between two vectors are up to 40 times faster. 

The downside of binary vectors is they can be less accurate. However, the loss is often not very substantial and their accuracy often remians greater than 90% compared to float32 vectors. 

The following example creates a table with a binary vector column that has 16 binary dimensions. The second and third insert statements fail because:

  • The second statement includes the value 256 which is outside the range of a single byte (0-255), so is too big to fit in the first dimension. 

  • The third statement has three values in the array. This corresponds to 3 bytes, which is greater than the 16 dimensions, i.e., the 2-byte limit defined for the column.

create table my_vectors ( 
  id        integer,  
  embedding vector ( 16, binary ) 
); 

insert into my_vectors values ( 1, ‘[1,2]’ ); 
1 row inserted. 

insert into my_vectors values ( 2, ‘[256,2]’ ); 
ORA-51806: Vector column is not properly formatted (dimension value 1 is outside the allowed precision range). 

insert into my_vectors values ( 3, ‘[1,2,3]’ ); 
ORA-51803: Vector dimension count must match the dimension count specified in the column definition (actual: 24, required: 16). 

 

For more information, see: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/binary-vectors.html

JSON Collections

Oracle Database 21c added the JSON data type to optimize and enhance storing JSON data. JSON collection tables added in 23.5 further simplify storing JSON values. 

JSON collection tables are special, one column tables to store JSON data in a document-store-compatible format. This includes a mandatory _id attribute whose values must be unique within a collection. 

When you store JSON documents in a collection without an _id, the database will autogenerate this with a unique value. 

The database will also store a hash of the document in a _metadata.etag attribute if you use the with etag clause when creating the collection. 

For example:

CREATE JSON COLLECTION TABLE json_data WITH ETAG; 

INSERT INTO json_data VALUES ( ‘ { “attr” : “value” }’ ); 

SELECT * FROM json_data;

__________________________________________________
{
  “_id” : “681a97e15b9acc7ee3118613”,
  “_metadata” :
  {
    “etag” : “3480C81186147EE3E0635100000A9ACC”
  },
  “attr” : “value”
}

 

For more information, see: https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/json-collections.html

TIME_AT_DBTIMEZONE Parameter

By default, the time functions SYSDATE and SYSTIMETAMP return values from the operating system the database runs on. If the server is running on New Zealand time and the database has a US west coast time zone this can lead to unexpected results. 

With the time_at_dbtimezone parameter you can change these functions to report their time using the PDBs time zone instead. 

For example, this changes a database running in UTC time to use India Standard Time (+05:30). This changes the time reported from around 1:30pm to just after 7pm the same day:

SELECT SYSDATE, SYSTIMESTAMP, DBTIMEZONE; 

SYSDATE                  SYSTIMESTAMP                           DBTIME 
——————– ——————————— —— 
05-SEP-2025 13:36:05 05/09/2025 13:36:05.903475000 GMT +00:00 
 

ALTER SYSTEM SET time_at_dbtimezone = database SCOPE = spfile; 
ALTER DATABASE SET time_zone = ‘Asia/Kolkata’; 

 
SHUTDOWN — need to restart to take effect 
STARTUP 

SELECT SYSDATE, SYSTIMESTAMP, DBTIMEZONE; 

SYSDATE              SYSTIMESTAMP                        DBTIMEZONE   
——————– ———————————— ———– 
05-SEP-2025 19:06:43 05/09/2025 19:06:43.420536000 +05:30 Asia/Kolkata 

 

Values for the time_at_dbtimezone parameter are:

  • OFF – For all time-dependent operations, the database uses the time zone of the database host system that was in effect when the database was started. This is the behavior in releases prior to Oracle Database 23ai. 

  • USER_SQL – The SQL functions SYSDATE and SYSTIMESTAMP return the date and time based on the DBTIMEZONE setting for the PDB. For all other time-dependent operations, the database uses the time zone of the database host system that was in effect when the database was started. 

  • DATABASE – For all time-dependent operations, the database uses the time zone specified by the DBTIMEZONE setting for the PDB. These operations include the job scheduler, materialized view refreshes, and Flashback operations. 

For more information, see: https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/TIME_AT_DBTIMEZONE.html

JSON_ID SQL Function

Oracle Database has long had the sys_guid function for generating 16 byte unique identifiers. How this generates values is platform dependent. The json_id function in 23.5 gives you more options for creating unique values. 

It has one parameter which controls the format of its output: 

  • OID – a 12 byte raw that is compatible with Oracle Database API for MongoDB

  • UUID – a 16 byte raw that respects the IETF Universally Unique IDentifiers (UUIDs) proposed standard, RFC 9562.

For example:

SELECT
  json_id ( ‘OID’ ) raw_12_byte,
  json_id ( ‘UUID’ ) raw_16_byte; 

 RAW_12_BYTE                 RAW_16_BYTE
___________________________ ___________________________________
681166880875B20110DB9986    6B99E7C0A0B04F68BF0487270315DFBD

 

For more information, see: https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/json-collections.html

This blog post was co-authored with Chris Saxon.