CLARIFY COLUMN METADATA WITH ANNOTATIONS

THE NEW METADATA?

Historically,(since about oracle 7) Developers and Database Administrators have had the ability to provide more information about a database column, unified audit policy, edition, indextype, materialized view, mining model, operator, or view using comments. In Oracle AI Database 26 AI, we can go even further an extend he capability of descriptive text on a database column using annotations.

Annotations, quite simply put provide additional metadata for database objects and are centrally stored in the database. You might be thinking “What’s the point? Why are these better than comments” Well annotations have extended functionality that comments do not such as

• They allow for name-value pairs, making them machine-readable and actionable for applications (e.g., UI hints ).
• You can add multiple annotations to a single object (table, column, view, index, domain), whereas comments are limited to one per object.
• Annotations can be used to provide additional metadata to help AI understand the data, assuming you are making the data available to AI agents.
• They allow for centralized documentation by storing metadata alongside database objects ensuring they stay synchronized rather than in external documents
• They can have different “visibilities”

Okay, so now that we have a bit of knowledge of annotations and what they can be used for, let’s see how to use them

THE HOW

This is the general syntax for creating annotations in oracle

ANNOTATIONS ([ADD|DROP] annotation_name [ annotation_value ] [ , annotation_name [ annotation_value ]… )

They can be defined in the CREATE TABLE statement for example

CREATE TABLE annotated_toys
( toy_id   number
,toy_name varchar2(50) annotations (display 'toyname')
,quantity number       annotations (number_of_toys, column_hidden)
 ) 
 annotations (display 'toys');

alter table annotated_toys annotations (Visibility 'Everyone');
alter table annotated_toys annotations (drop Visibility);
alter table annotated_toys annotations (add Visibility 'Everyone');

Remember, all these annotations are just free text we have made up, with the annotation values having a maximum limit of 4000 characters. They have no meaning to the database or any tool other than the meaning we associate with them. If we need spaces in the key names we must double-quote them. This is true of reserved words also.

VIEWING ANNOTATIONS

We can query the annotations in our database by using the user_annotations and user_annotations_usage views (can also be prefixed with all/dba) for example

select object_name, object_type, annotation_name, annotation_value 
from user_annotations_usage
where column_name is null order by 2,1;

OBJECT_NAME      OBJECT_TYPE ANNOTATION_NAME ANNOTATION_VALUE 
---------------- ----------- --------------- ---------------- 
ANNOTATED_TOYS   TABLE       DISPLAY         toys             

Elapsed: 00:00:00.055
1 rows selected. 

--

select object_name, object_type, column_name, annotation_name, annotation_value 
from user_annotations_usage
where column_name is not null order by 2,1;

OBJECT_NAME      OBJECT_TYPE COLUMN_NAME ANNOTATION_NAME  ANNOTATION_VALUE 
---------------- ----------- ----------- ---------------- ---------------- 
ANNOTATED_TOYS   TABLE       TOY_NAME    DISPLAY          toyname          
ANNOTATED_TOYS   TABLE       QUANTITY    COLUMN_HIDDEN                     
ANNOTATED_TOYS   TABLE       QUANTITY    NUMBER_OF_TOYS                    

Elapsed: 00:00:00.127
3 rows selected. 


9 rows selected.

CHANGING AFTER THE FACT OR NO LONGER WANT THEM?

As mentioned earlier, annotations can be modified using alter table and can also be dropped with the same statement.

example:

Removing an Annotation:

SQL> ALTER TABLE <table_name> MODIFY (id ANNOTATIONS (DROP DESCRIPTION1));

Adding New Annotations:

SQL> ALTER TABLE <table_name> ANNOTATIONS (ADD name1 'value1', name2 'value2');

IN A NUTSHELL

There’s a lot more that we can use these annotations for. For instance we could define display values for the columns, create more categories or add version information. I think, this is a very powerful feature which is quite simple to introduce and provides a lot of added value throughout the lifetime of data models. This is a very useful feature for application developers as i will help to document code easier, provide structure for metadata and also everyone’s favorite at the moment “AI” as it will help LLMs better understand your data.

Cheers 🎊