Virtual Columns, Defaults, Invisible Columns, Wide-Table Awareness, and Data-Integrity Design

Learning outcomes

ServiceHub's core schema is now owned, typed, constrained, and keyed correctly. The final design challenge is evolution: add derived values, defaults, compatibility columns, and sometimes unusually wide records without breaking existing applications or encoding fragile logic in every client.

1. Virtual columns centralize deterministic row-derived logic

A virtual column is defined by an expression over other row data rather than by application-supplied storage. It can make a repeated derivation visible to SQL and metadata and, when eligible, participate in indexing/constraints. The expression must obey Oracle's restrictions; do not hide nondeterministic session/environment behavior inside a schema rule merely to avoid application code.

DROP TABLE IF EXISTS servicehub_evolution_probe PURGE;CREATE TABLE servicehub_evolution_probe (  work_id       NUMBER PRIMARY KEY,  urgency       NUMBER(1) NOT NULL,  customer_tier VARCHAR2(12 CHAR) NOT NULL,  priority_score NUMBER GENERATED ALWAYS AS    (urgency * CASE customer_tier       WHEN 'PLATINUM' THEN 3       WHEN 'GOLD'     THEN 2       ELSE 1     END) VIRTUAL);INSERT INTO servicehub_evolution_probe(work_id, urgency, customer_tier)VALUES (1, 4, 'GOLD');SELECT work_id, urgency, customer_tier, priority_scoreFROM servicehub_evolution_probe;

The virtual column is an integrity/derivation contract: clients cannot independently disagree about the formula. If the formula changes, treat the DDL change like application code—test result changes and dependent indexes/constraints before rollout.

2. DEFAULT and DEFAULT ON NULL answer different insertion questions

A normal DEFAULT applies when an INSERT omits the column. An explicit NULL normally remains NULL. DEFAULT ON NULL changes that contract for INSERT by substituting the default when the inserted expression evaluates to NULL. Current 26ai metadata exposes whether a column uses default-on-null behavior. Choose it only when “caller supplied NULL” is semantically equivalent to “caller omitted the value.”

ALTER TABLE servicehub_evolution_probe ADD (  lifecycle_state VARCHAR2(12 CHAR)    DEFAULT ON NULL 'NEW' NOT NULL);INSERT INTO servicehub_evolution_probe(work_id, urgency, customer_tier, lifecycle_state)VALUES (2, 3, 'STANDARD', NULL);SELECT work_id, lifecycle_stateFROM servicehub_evolution_probeWHERE work_id = 2;SELECT column_name, data_default, default_on_null, nullableFROM user_tab_columnsWHERE table_name = 'SERVICEHUB_EVOLUTION_PROBE'AND column_name = 'LIFECYCLE_STATE';

3. Invisible columns are an application-compatibility tool, not a security boundary

An invisible column is omitted from an unqualified SELECT * and from INSERT statements that omit a column list, but it remains addressable explicitly by name. That makes invisible columns useful during phased application evolution: old clients can keep their positional assumptions while new clients opt into the column. Invisibility is not authorization or masking; a user with SELECT privilege on the table can explicitly select the invisible column.

ALTER TABLE servicehub_evolution_probe ADD (  migration_tag VARCHAR2(40 CHAR) INVISIBLE);-- SELECT * does not include MIGRATION_TAG.SELECT * FROM servicehub_evolution_probe ORDER BY work_id;-- Explicit reference does.SELECT work_id, migration_tagFROM servicehub_evolution_probeORDER BY work_id;SELECT column_name, column_id, hidden_column, virtual_columnFROM user_tab_colsWHERE table_name = 'SERVICEHUB_EVOLUTION_PROBE'ORDER BY internal_column_id;

When the application is ready, ALTER TABLE ... MODIFY (... VISIBLE) can expose the column to wildcard projections. A mature application should still list columns explicitly rather than using SELECT * as an API contract.

4. Deliberately wrong approach: “invisible means secret”

A team adds INTERNAL_RISK_SCORE INVISIBLE and assumes runtime users cannot read it. That is false. Invisibility affects default column enumeration, not object privileges.

ALTER TABLE servicehub_evolution_probe ADD (  internal_risk_score NUMBER INVISIBLE);UPDATE servicehub_evolution_probeSET internal_risk_score = 42WHERE work_id = 1;COMMIT;-- Explicit selection still returns the value to a user with table SELECT privilege.SELECT work_id, internal_risk_scoreFROM servicehub_evolution_probeWHERE work_id = 1;

The repair is authorization: expose a governed view/API, use column/object/data security features appropriate to the requirement, and grant only what the runtime role needs. Do not substitute UI metadata for access control.

5. Wide Tables are a gated 26ai capability

Oracle AI Database 26ai can raise the maximum columns per table or view from 1000 to 4096 when MAX_COLUMNS=EXTENDED. Current Reference documentation requires COMPATIBLE >= 23.0.0.0 for that setting, marks MAX_COLUMNS as non-dynamic, and notes that pre-26ai clients cannot access more than 1000 columns. The default remains STANDARD. This is therefore a database/PDB, restart, client, and rollback-compatibility decision—not permission to denormalize ordinary OLTP models into thousands of columns.

SELECT name, value, issys_modifiable, ispdb_modifiableFROM v$parameterWHERE name IN ('compatible','max_columns');SELECT SYS_CONTEXT('USERENV','CON_NAME') AS con_name FROM dual;-- Mandatory lab stops at observation. Do not switch MAX_COLUMNS in a shared lab.

Changing from STANDARD to EXTENDED can be done only with the documented static-parameter workflow; returning to STANDARD is allowed only when no table/view exceeds 1000 columns. Because this is a database-level compatibility choice, this chapter does not alter it.

6. Wide-table awareness does not replace relational design

Wide Tables exist for legitimate workloads such as very high-dimensional attributes, machine-learning/IoT feature sets, or compatibility with source systems that already have a large fixed attribute surface. They do not invalidate normalization, JSON/document modeling, child tables, or sparse/vector representations. Before enabling a wide design, compare query shape, row size, DDL churn, client metadata cost, indexing limits, application mappings, export/import behavior, and downstream tool support.

7. Hands-on lab: a safe schema-evolution rehearsal

Use the disposable probe table to rehearse a four-step expand/contract pattern: add a defaulted column, add an invisible compatibility column, update new code to name columns explicitly, then expose or remove the compatibility column only after verification. Capture USER_TAB_COLUMNS/USER_TAB_COLS before and after each step. No restart or MAX_COLUMNS change is required.

SELECT column_name, data_type, data_default,       default_on_null, nullable, virtual_column, hidden_columnFROM user_tab_colsWHERE table_name = 'SERVICEHUB_EVOLUTION_PROBE'ORDER BY internal_column_id;ALTER TABLE servicehub_evolution_probe  MODIFY (migration_tag VISIBLE);SELECT * FROM servicehub_evolution_probe ORDER BY work_id;DROP TABLE IF EXISTS servicehub_evolution_probe PURGE;

Your rollback record should state which step is metadata-only, which step changes returned column shape, which clients were tested, and whether any generated/virtual expression has dependent indexes or constraints.

8. Production judgment and chapter bridge

Use virtual columns to centralize stable deterministic row-derived expressions; defaults when omission has a clear semantic value; DEFAULT ON NULL only when explicit NULL means the same thing as omission; and invisible columns as a migration-compatibility aid, never as security. Treat wide tables as an explicit 26ai architectural mode with COMPATIBLE, static MAX_COLUMNS, client, restart, and rollback consequences.

Chapter 04 has translated portable relational design into Oracle ownership, names, types, constraints, generated identifiers, and evolution metadata. Chapter 05 now returns to SQL itself—joins, subqueries, set operations, and Oracle hierarchical-query semantics—using these Oracle-specific schema rules as the foundation.

Authoritative references

• MAX_COLUMNS — 26ai wide-table limit, COMPATIBLE, PDB and client prerequisites
• Logical database limits — 1000 versus 4096 column limits and related logical limits
• Database Concepts — tables and columns — invisible-column purpose and schema concepts
• ALL_TABLE_VIRTUAL_COLUMNS — 26ai virtual-column expression metadata
• Oracle AI Database SQL Language Reference — CREATE/ALTER TABLE defaults, virtual and invisible column syntax