Domains and Annotations in 26ai

 In a previous blog post I had demonstrated Domains for Columns and an Annotation for a table in 23ai.

Domains are "Data Use Case Domains" -- predefined Dictionary Objects that encapsulate properties and constraints for the data being stored in the target columns.

Here's the 26ai documentation on Data Use Case Domains : Data Use Case Domains

"An data use case domain is a high-level dictionary object that belongs to a schema and encapsulates a set of optional properties and constraints."

"...a column can be declared both with a primitive data type such as NUMBER, as well as with a domain for data usage, such as "Temperature" or "Credit Score". Such a use case domain can optionally be associated with different usage properties such as check constraints, display properties, ordering rules, and others."

There are 109 Pre-Built Domains (as in 23.26.0 Free).  

SQL> select owner, builtin, count(*) from all_domains group by owner, builtin order by 1,2;

OWNER	     BUILTIN	   COUNT(*)
------------ ----------- ----------
SYS	     TRUE		109

SQL> 

But you can add your own Custom Domains.  In this demonstration, I show two custom domains.  Although the SALARY_D domain could have been a simple CHECK Constraint on the EMPLOYEES_TBL, I could use this domain in, say, DEPARTMENTS (for MAX_SALARY) or SALES_COMMISSION or CONTRACTOR_REMUNERATION etc.

I build the SALARY_D just as a simple example domain.

Annotations are "Metadata" -- that define and identify the data.  Annotations are very good use cases for implementing AI where Natural Language queries from users are translated to SQL based on Annotations.

Here's the 26ai documentation on Annotations as a New Feature : Schema Annotations

"Oracle AI Database schema annotations are a lightweight declarative facility for developers to centrally register usage properties for database schema objects. Annotations are stored directly inside the database in dictionary tables alongside the data model definition and the data itself, and available to any applications in order to standardize behavior across common data, but are not interpreted by the database in any way. They should be thought of as lightweight standardized markup for database metadata, for use by applications to register and process extended and custom usage properties."

Here is one example of a table using Domains and Annotations :

SQL> @Domains_and_Annotations_Demo.sql
SQL> connect system/oracle
Connected.
SQL> grant create domain to hr
  2  /

Grant succeeded.

SQL> 
SQL> connect hr/oracle
Connected.
SQL> 
SQL> drop table employees_tbl
  2  /

Table dropped.

SQL> 
SQL> create domain if not exists age_d as integer
  2  constraint age_min check (age_d >= 18)
  3  /

Domain created.

SQL> 
SQL> create domain if not exists salary_d as number
  2  constraint salary_max check (salary_d le 20000)  -- I have changed the "less-than-or-equal-to" sign to "le" to preserve HTML parsing
  3  /

Domain created.

SQL> 
SQL> 
SQL> create table employees_tbl
  2  (
  3  employee_id	  number generated by default on null as identity	annotations (PrimaryKey, Sequence),
  4  first_name	          varchar2(250) not null annotations (Display 'Employee First Name'),
  5  middle_name	  varchar2(250) annotations (Display 'Employee Middle Name [optional]'),
  6  last_name	          varchar2(250) not null annotations (Display 'Employee Last Name'),
  7  join_age	          integer domain age_d annotations (Display 'Age at Start of Employment'),
  8  employee_email_id    varchar2(512) domain email_d annotations (Sensitive),
  9  dept_id	          number not null annotations (Display 'Department ID'),
 10  citizen_country      varchar2(3) domain country_code_d annotations (Case 'UPPER', Display 'Citizenship Country ISO Code'),
 11  resident_country     varchar2(3) domain country_code_d annotations (Case 'UPPER', Display 'Residence Country ISO Code'),
 12  current_salary	  number domain salary_d annotations (Display 'Employee Current Salary'),
 13  salary_date	  date	not null annotations (Display 'Current Salary Begin Date')
 14  )
 15  annotations (display 'Employees Table')
 16  /

Table created.

SQL> 
SQL> 
SQL> describe employees_tbl
 Name									  Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 EMPLOYEE_ID								  NOT NULL NUMBER
 FIRST_NAME								  NOT NULL VARCHAR2(250)
 MIDDLE_NAME									   VARCHAR2(250)
 LAST_NAME								  NOT NULL VARCHAR2(250)
 JOIN_AGE									   NUMBER(38) HR.AGE_D
 EMPLOYEE_EMAIL_ID								   VARCHAR2(512) SYS.EMAIL_D
 DEPT_ID								  NOT NULL NUMBER
 CITIZEN_COUNTRY								   VARCHAR2(3) SYS.COUNTRY_CODE_D
 RESIDENT_COUNTRY								   VARCHAR2(3) SYS.COUNTRY_CODE_D
 CURRENT_SALARY 								   NUMBER HR.SALARY_D
 SALARY_DATE								  NOT NULL DATE

SQL> 
SQL> 
SQL> set pagesize 600
SQL> set linesize 132
SQL> set long 2000
SQL> set longchunksize 1000
SQL> set serveroutput on
SQL> select dbms_metadata.get_ddl('TABLE','EMPLOYEES_TBL')
  2  
SQL> 
SQL> col columm_name format a21
SQL> col annotation_name format a21
SQL> col domain_name format a21
SQL> col annotation_value format a31
SQL> 
SQL> select column_name, domain_name, annotation_name, annotation_value
  2  from user_annotations_usage
  3  where object_name = 'EMPLOYEES_TBL'
  4  and object_type = 'TABLE'
  5  order by column_name, annotation_name
  6  /

COLUMN_NAME		       DOMAIN_NAME	     ANNOTATION_NAME	   ANNOTATION_VALUE
------------------------------ --------------------- --------------------- -------------------------------
CITIZEN_COUNTRY 	       COUNTRY_CODE_D	     ADDRESS
CITIZEN_COUNTRY 				     CASE		   UPPER
CITIZEN_COUNTRY 				     DISPLAY		   Citizenship Country ISO Code
CURRENT_SALARY					     DISPLAY		   Employee Current Salary
DEPT_ID 					     DISPLAY		   Department ID
EMPLOYEE_EMAIL_ID	       EMAIL_D		     PERSON_INFO
EMPLOYEE_EMAIL_ID				     SENSITIVE
EMPLOYEE_ID					     PRIMARYKEY
EMPLOYEE_ID					     SEQUENCE
FIRST_NAME					     DISPLAY		   Employee First Name
JOIN_AGE					     DISPLAY		   Age at Start of Employment
LAST_NAME					     DISPLAY		   Employee Last Name
MIDDLE_NAME					     DISPLAY		   Employee Middle Name [optional]
RESIDENT_COUNTRY	       COUNTRY_CODE_D	     ADDRESS
RESIDENT_COUNTRY				     CASE		   UPPER
RESIDENT_COUNTRY				     DISPLAY		   Residence Country ISO Code
SALARY_DATE					     DISPLAY		   Current Salary Begin Date
						     DISPLAY		   Employees Table

18 rows selected.

SQL> 
SQL> -- the output above does NOT show the Custom Domains, only the preseeded COUNTRY_CODE_D and EMAIL_D
SQL> col domain_name format a21
SQL> 
SQL> select name as domain_name, builtin, type
  2  from user_domains
  3  order by name
  4  /

DOMAIN_NAME	      BUILTIN	  TYPE
--------------------- ----------- ----------
AGE_D		      FALSE	  REGULAR
SALARY_D	      FALSE	  REGULAR

SQL> 
SQL> 
SQL> 

So, I have now created the Table using Domains and Annotations.

Now, I test the defined Domains for validation.

SQL> -- test the AGE_D domain for Join Age
insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',17,'abc@co.com',10,'US','US',8000,sysdate)
/
SQL>   2    3    4    5  insert into employees_tbl
*
ERROR at line 1:
ORA-11534: check constraint (HR.SYS_C0013676) involving column JOIN_AGE due to domain constraint HR.AGE_MIN of domain HR.AGE_D
violated
Help: https://docs.oracle.com/error-help/db/ora-11534/


SQL> 

SQL> insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',21,'abc@co.com',10,'US','US',8000,sysdate)
/
  2    3    4    5  
1 row created.

SQL> 


-- test the SALARY domain for Salary
SQL> insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',21,'abc@co.com',10,'US','US',32000,sysdate)
/
  2    3    4    5  insert into employees_tbl
*
ERROR at line 1:
ORA-11534: check constraint (HR.SYS_C0013674) involving column CURRENT_SALARY due to domain constraint HR.SALARY_MAX of domain
HR.SALARY_D violated
Help: https://docs.oracle.com/error-help/db/ora-11534/


SQL> 

SQL> insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',21,'abc@co.com',10,'US','US',19000,sysdate)
/  2    3    4    5  

1 row created.

SQL> 

-- test the EMAIL_D domain for Email Address
insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',21,'abc@xyz',10,'US','US',32000,sysdate)
/
SQL>   2    3    4    5  insert into employees_tbl
*
ERROR at line 1:
ORA-11534: check constraint (HR.SYS_C0013675) involving column EMPLOYEE_EMAIL_ID due to domain constraint SYS.SYS_DOMAIN_C0030 of
domain SYS.EMAIL_D violated
Help: https://docs.oracle.com/error-help/db/ora-11534/


SQL> 

SQL> insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',21,'abc@co.com',10,'US','US',19000,sysdate)
/
  2    3    4    5  
1 row created.

SQL> 


SQL> -- test the COUNTRY_CODE_D
insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',21,'abc@co.com',10,'XYZ','US',19000,sysdate)
/
SQL>   2    3    4    5  
1 row created.

SQL> 
-- Note that the country_code_d does not seem tocheck validity against real ISO codes in my current 23.26.0 FREE release.  It seems to check only that a string of characters is entered as a COUNTRY_CODE

So, the Domain Rules for AGE_D (custom domain defined by me), SALARY_D (custom domain defined by me) and EMAIL_D (system domain preseeded in the database) are all working as expected.

Partition by Expression

 Oracle 26.1 now allows using an Expression in the Partitioning Clause.  In earlier releases, you could do this with a Virtual Column defined in the table.  However, 26.1 does not require the Virtual Column if it is used simply for the Partitioning Clause.

Here is a demo in 19.12 comparing the two methods :

-- using a Virtual Column "ORDER_MONTH"

SQL> l
  1  CREATE TABLE ORDERS_2026_old (
  2      order_id      NUMBER NOT NULL,
  3      order_date    DATE NOT NULL,
  4      customer_id   NUMBER,
  5      order_month   NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(order_date, 'MM'))) VIRTUAL
  6  )
  7  PARTITION BY RANGE (order_month)
  8  (
  9      PARTITION orders_q1 VALUES LESS THAN (4),  -- Months 1, 2, 3
 10      PARTITION orders_q2 VALUES LESS THAN (7),  -- Months 4, 5, 6
 11      PARTITION orders_q3 VALUES LESS THAN (10), -- Months 7, 8, 9
 12      PARTITION orders_q4 VALUES LESS THAN (13)  -- Months 10, 11, 12
 13* )
SQL> /

Table created.

SQL>

-- try the Expression in the Partitioning Clause

SQL> l
  1  CREATE TABLE ORDERS_2026_new (
  2      order_id      NUMBER NOT NULL,
  3      order_date    DATE NOT NULL,
  4      customer_id   NUMBER
  5  )
  6  PARTITION BY RANGE ((TO_NUMBER(TO_CHAR(order_date, 'MM'))))
  7  (
  8      PARTITION orders_q1 VALUES LESS THAN (4),  -- Months 1, 2, 3
  9      PARTITION orders_q2 VALUES LESS THAN (7),  -- Months 4, 5, 6
 10      PARTITION orders_q3 VALUES LESS THAN (10), -- Months 7, 8, 9
 11      PARTITION orders_q4 VALUES LESS THAN (13)  -- Months 10, 11, 12
 12* )
SQL> /
PARTITION BY RANGE ((TO_NUMBER(TO_CHAR(order_date, 'MM'))))
                    *
ERROR at line 6:
ORA-00904: : invalid identifier


SQL>

And now in 26.1 :

-- using the Virtual Column "ORDER_MONTH",  works as expected

SQL> l
  1  CREATE TABLE ORDERS_2026_old (
  2      order_id      NUMBER NOT NULL,
  3      order_date    DATE NOT NULL,
  4      customer_id   NUMBER,
  5      order_month   NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(order_date, 'MM'))) VIRTUAL
  6  )
  7  PARTITION BY RANGE (order_month)
  8  (
  9      PARTITION orders_q1 VALUES LESS THAN (4),  -- Months 1, 2, 3
 10      PARTITION orders_q2 VALUES LESS THAN (7),  -- Months 4, 5, 6
 11      PARTITION orders_q3 VALUES LESS THAN (10), -- Months 7, 8, 9
 12      PARTITION orders_q4 VALUES LESS THAN (13)  -- Months 10, 11, 12
 13* )
SQL> /

Table created.

SQL>

-- using the Expression for the Partitioning Clause

SQL> l
  1  CREATE TABLE ORDERS_2026_NEW (
  2      order_id      NUMBER NOT NULL,
  3      order_date    DATE NOT NULL,
  4      customer_id   NUMBER
  5  )
  6  PARTITION BY RANGE ((TO_NUMBER(TO_CHAR(order_date, 'MM'))))
  7  (
  8      PARTITION orders_q1 VALUES LESS THAN (4),  -- Months 1, 2, 3
  9      PARTITION orders_q2 VALUES LESS THAN (7),  -- Months 4, 5, 6
 10      PARTITION orders_q3 VALUES LESS THAN (10), -- Months 7, 8, 9
 11      PARTITION orders_q4 VALUES LESS THAN (13)  -- Months 10, 11, 12
 12* )
SQL> /

Table created.

SQL>
SQL> insert into orders_2026_new values (1, sysdate, 1);

1 row created.

SQL>
QL> insert into orders_2026_new values (2, to_date('15-JUN-2026','DD-MON-RRRR'),2);

1 row created.

SQL> commit;

Commit complete.

SQL>

-- gather statistics


SQL> exec dbms_stats.gather_table_stats('','ORDERS_2026_NEW');

PL/SQL procedure successfully completed.

SQL>

-- check number of rows in each partition

SQL> col partition_name format a32
SQL> select partition_name, num_rows
  2  from user_tab_partitions
  3  where table_name = 'ORDERS_2026_NEW'
  4  /

PARTITION_NAME                     NUM_ROWS
-------------------------------- ----------
ORDERS_Q1                                 1
ORDERS_Q2                                 1
ORDERS_Q3                                 0
ORDERS_Q4                                 0

SQL>

-- check rows in the specific partitions

-- get all the rows where the month number is less than 4

SQL> select * from orders_2026_new
  2  partition for (3)
  3  /

  ORDER_ID ORDER_DATE         CUSTOMER_ID
---------- ------------------ -----------
         1 15-FEB-26                    1

SQL>

-- get all the rows where the month number is less than 7

SQL> select * from orders_2026_new
  2  partition for (6)
  3  /

  ORDER_ID ORDER_DATE         CUSTOMER_ID
---------- ------------------ -----------
         2 15-JUN-26                    2

SQL>

Since partitioning is by Month Number (would have been the Virtual Column ORDER_MONTH) in the 19c example, I can use  the "PARTITION FOR (<month_number>)"  in my SELECT statement to query a specific partition.

I can confirm that ORDER_ID 1 is in the first partition (Month Number less than 4) and ORDER_ID 2 is in the second partition (Month Number less than 7)

The new FILTER clause in Aggregations

 Oracle 26.1 introduces the new FILTER clause that can be used in aggregations.  

Thus, for example, in a SUM aggregation you can use FILTER WHERE ...

Here is a quick example :

SQL> create table obj_list as select * from dba_objects;

Table created.

SQL> select sum(object_id) from obj_list;

SUM(OBJECT_ID)
--------------
    2604221816

SQL> select object_type, sum(object_id) from obj_list group by object_type order by 1;

OBJECT_TYPE             SUM(OBJECT_ID)
----------------------- --------------
CLUSTER                           3036
CONSUMER GROUP                  435231
CONTEXT                        1048288
...... deleted a number of object types
SEQUENCE                       7551346
SYNONYM                      473741064
TABLE                         44661426   --> sum of object_ids for object_type TABLE
TABLE PARTITION               15047252
TABLE SUBPARTITION              603760
...... deleted a few more object types
XML SCHEMA                      511732

46 rows selected.


SQL> select sum(object_id) filter (where object_type = 'TABLE')  as Table_ObjID_Sum from obj_list;

TABLE_OBJID_SUM
---------------
       44661426


SQL>

For more information see : 

1. Oracle Documentation New Features Guide: Aggregation Filters

2. Oracle Documentation SQL For Application Developers : 14.2 Aggregation Filters 

3. Markus Winand's Modern SQL Reference : Filter