Author: matheusdba

SQL Domains are powerful, but like any abstraction layer, they work better in some situations than others. Here’s a practical guide to the decision.

Use SQL Domains when:

✓ The same semantic type appears in 3+ columns across your schema. Email addresses, phone numbers, status codes, country codes, monetary amounts — these are domain candidates.

✓ The constraint logic is non-trivial. A REGEXP_LIKE email validation or a multi-value IN list is worth centralizing. A simple NUMBER column without constraints is not.

✓ You want schema-level documentation to drive tooling. Domains are queryable — your data catalog, code generators, or validation tools can introspect them.

✓ You’re building a new schema. Retrofitting domains is harder than designing with them from the start.

Be cautious when:

✗ The constraint might need to vary per table. Domains enforce the same constraint everywhere they’re used. If one table needs CHECK (VALUE IN ('A','B')) and another needs CHECK (VALUE IN ('A','B','C')), that’s two domains, not one.

✗ You have existing applications that don’t expect domain-level errors. Domain constraint violations raise the same ORA-02290 CHECK constraint error as inline constraints, but the constraint name includes the domain name — applications parsing constraint names may need updating.

✗ You’re mid-migration on a legacy schema. Adding domains to existing columns requires an ALTER TABLE MODIFY that adds the domain. This can be done incrementally, but plan it carefully.

Creating domain variations correctly:

-- Don't force one domain to cover all cases
CREATE DOMAIN d_status_basic   AS VARCHAR2(10) CONSTRAINT CHECK (VALUE IN ('ACTIVE','INACTIVE'));
CREATE DOMAIN d_status_extended AS VARCHAR2(10) CONSTRAINT CHECK (VALUE IN ('ACTIVE','INACTIVE','PENDING','ARCHIVED'));

Two focused domains are better than one overly permissive domain or one domain with workaround constraints.

GROUP BY ALL simplifies the common case, but it also works with advanced grouping operations. Let’s look at patterns where it really pays off.

Complex analytical queries with many dimensions:

-- Before 23ai: manually listing 6 non-aggregated columns in GROUP BY
SELECT
    region, country, product_line, product_family, channel, quarter,
    SUM(revenue)       AS total_revenue,
    SUM(units_sold)    AS total_units,
    AVG(unit_price)    AS avg_price
FROM sales_facts
GROUP BY region, country, product_line, product_family, channel, quarter;

-- With GROUP BY ALL:
SELECT
    region, country, product_line, product_family, channel, quarter,
    SUM(revenue), SUM(units_sold), AVG(unit_price)
FROM sales_facts
GROUP BY ALL;

Adding a new dimension (say, sales_rep_id) to the analysis: before 23ai, you must add it to both SELECT and GROUP BY. With GROUP BY ALL, you only add it to SELECT.

GROUP BY ROLLUP ALL — subtotals without listing columns:

SELECT region, country, SUM(revenue) AS total
FROM   sales_facts
GROUP BY ROLLUP ALL;
-- Equivalent to: GROUP BY ROLLUP(region, country)
-- Produces: region+country subtotals, region subtotals, grand total

GROUP BY CUBE ALL — cross-dimensional analysis:

SELECT channel, quarter, SUM(revenue)
FROM   sales_facts
GROUP BY CUBE ALL;
-- All combinations: channel+quarter, channel only, quarter only, grand total

Interaction with window functions:

GROUP BY ALL does not apply to window functions — they’re not aggregate expressions in the GROUP BY sense. Window functions work the same as always:

SELECT region, product_line, SUM(revenue) AS group_total,
       SUM(SUM(revenue)) OVER (PARTITION BY region) AS region_total
FROM   sales_facts
GROUP BY ALL;
-- GROUP BY ALL includes: region, product_line
-- Window function operates on the grouped results

GROUP BY ALL is at its best in analytical queries with 4+ dimensions where GROUP BY maintenance becomes error-prone.

What a year for Oracle SQL. Looking back at what we’ve covered in 2025, Oracle 23ai represents one of the most significant leaps in Oracle’s SQL language in a decade. Here’s the condensed highlight reel.

Developer ergonomics (finally addressed): IF [NOT] EXISTS for DDL, GROUP BY ALL, SELECT without FROM, EXCEPT/EXCEPT ALL, table aliases in DML, the ALIAS clause, and multirow VALUES syntax. These aren’t flashy features — they’re things developers have wanted for years, implemented at last.

Data model modernization: SQL Domains, the Boolean data type, Annotations, and enhanced JSON functions (JSON_TRANSFORM, JSON_MERGEPATCH, JSON Schema validation). Oracle’s data model is now closer to a modern standard than at any point in its history.

AI integration: The VECTOR data type and AI Vector Search are the most strategically significant additions. Combined with Select AI (natural language to SQL) and the integration with LLM providers via DBMS_CLOUD_AI, Oracle has made a credible bid to be the primary data store for AI workloads.

Graph and streaming: SQL/PGQ property graphs and GRAPH_TABLE bring graph analytics into the SQL layer. Kafka-compatible APIs for Transactional Event Queues connect Oracle to the streaming ecosystem.

Concurrency and performance: Lock-Free Reservations, Priority Transactions, True Cache, and Memoptimized Rowstore enhancements address real scalability pain points at the architecture level.

Security: SQL Firewall is a meaningful addition to Oracle’s security stack — kernel-level, transparent, and zero-code-change for applications.

If you’re on Oracle 19c or 21c and haven’t evaluated 23ai yet, 2026 is the year to start that assessment. The feature set justifies the upgrade conversation.

JSON Relational Duality Views were introduced in April. Having had more time to work with them, let’s talk about what works well in production and where to be careful.

What works well:

Simple document APIs: If your documents map to a small number of normalized tables (2-4 tables), duality views are excellent. The REST API you get via ORDS is clean, consistent, and requires almost no backend code.

Write consistency: Because duality views write through to relational tables in a single transaction, you get ACID guarantees that no external MongoDB + Oracle sync can provide. Write to the duality view; both your JSON API and your SQL reports see the same data, immediately.

-- ORDS exposes this as a REST endpoint automatically
-- GET /api/orders/42 → returns the JSON document
-- PUT /api/orders/42 → updates through to relational tables

Where to be careful:

Complex joins: Duality views over many tables with complex join conditions become difficult to reason about. If your document model doesn’t align reasonably with your relational model, the duality view definition becomes a maintenance burden.

Aggregations: Duality views represent individual documents (rows/graphs), not aggregations. You cannot expose aggregated data (counts, sums) as a duality view — that’s still a job for regular views or materialized views.

Caching behavior: Applications that cache JSON responses aggressively may see stale data if they don’t respect Oracle’s ETags that duality views generate for optimistic locking.

Design rule of thumb: Use duality views when your document model has a clear primary entity (Order, Customer, Product) that owns related data. Avoid them for cross-cutting concerns or heavily aggregated responses.

Annotations (introduced in March’s post) become particularly powerful in enterprise environments where data governance, self-service tooling, and schema documentation are critical. Let’s look at advanced patterns.

Data classification for security:

ALTER TABLE customers MODIFY (
    ssn         ANNOTATIONS (ADD DataClass 'PII', ADD Sensitivity 'Critical', ADD EncryptionRequired 'true'),
    email       ANNOTATIONS (ADD DataClass 'PII', ADD Sensitivity 'High'),
    birth_date  ANNOTATIONS (ADD DataClass 'PII', ADD Sensitivity 'Medium'),
    customer_id ANNOTATIONS (ADD DataClass 'Internal')
);

A data catalog tool can query USER_ANNOTATIONS_USAGE to automatically generate a data classification report without anyone maintaining a separate spreadsheet.

Deprecation management:

ALTER TABLE legacy_orders ANNOTATIONS (ADD Deprecated 'true', ADD DeprecatedSince '2024-01-01', ADD MigratedTo 'orders_v2');

CI/CD pipelines can query for deprecated objects and fail builds that reference them.

UI metadata for low-code applications:

ALTER TABLE employees MODIFY (
    first_name ANNOTATIONS (ADD UILabel 'First Name', ADD DisplayOrder '1', ADD UIRequired 'true'),
    last_name  ANNOTATIONS (ADD UILabel 'Last Name',  ADD DisplayOrder '2', ADD UIRequired 'true'),
    salary     ANNOTATIONS (ADD UILabel 'Annual Salary', ADD UIReadOnly 'true', ADD UIFormat 'currency')
);

Low-code platforms like Oracle APEX can read these annotations to auto-generate forms with the correct labels, ordering, and validation.

Queryable catalog:

-- Find all columns marked as PII across the schema
SELECT owner, object_name, column_name, annotation_value AS data_class
FROM   dba_annotations_usage
WHERE  annotation_name = 'DataClass'
AND    annotation_value = 'PII'
ORDER BY owner, object_name;

Annotations turn the Oracle data dictionary into a live, queryable governance layer — no external catalog required.