Join Reordering & Structural Optimization — When Metadata Becomes an Optimization Surface

Structural normalization and determinism

Join Reordering & Structural Optimization — When Metadata Becomes an Optimization Surface

Structural normalization and determinism

SQL optimizers inside databases already reorder joins.

So why would a metadata-driven framework care?

Because there are two very different concerns:

Runtime execution optimization (handled by the database engine)
Structural normalization and determinism (handled by MDDE)

These are not the same problem.

MDDE does not attempt to replace the database optimizer.

It ensures that the structural representation of joins is:

Explicit
Deterministic
Canonical
Traceable

Before SQL ever reaches execution.

The Structural Problem

In real systems, equivalent queries are often written differently:

FROM orders o  
JOIN customers c ON o.customer_id = c.id  
JOIN order_items oi ON oi.order_id = o.id

FROM order_items oi  
JOIN orders o ON oi.order_id = o.id  
JOIN customers c ON o.customer_id = c.id

These are logically equivalent.

But structurally different.

Which means:

Lineage graphs differ
Canonical SQL differs
Diffs become noisy
Optimizer diagnostics become unstable

MDDE aims for structural determinism.

Canonical Join Ordering

When SQL is parsed, joins are represented as relationship metadata:

metadata.relationship  
metadata.entity_mapping

Each relationship has:

Parent entity
Child entity
Join condition
Join type
Cardinality (when available)

Instead of preserving textual order, MDDE builds a join graph.

Then applies deterministic ordering rules.

Deterministic Ordering Strategy

A simple canonical rule might be:

Root entity first
Then breadth-first expansion
Alphabetical fallback for ties

Example canonical output:

FROM customers c  
JOIN orders o ON o.customer_id = c.id  
JOIN order_items oi ON oi.order_id = o.id

This ensures:

Stable regeneration
Clean diffs
Predictable lineage
Deterministic SQL

The goal is not runtime optimization.

The goal is structural stability.

Join Graph Modeling

Join structure is represented internally as a graph:

Nodes:

Entities

Edges:

Relationships

Example query:

customers  
   │  
   └── orders  
           │  
           └── order_items

MDDE stores this as relational metadata.

Then generates join order based on graph traversal.

Cost-Based Hints (Optional Layer)

MDDE can optionally incorporate:

Relationship cardinality
Known primary/foreign keys
Join selectivity metadata
Historical row count statistics

Not to replace the database optimizer.

But to:

Detect problematic patterns
Flag cross joins
Identify fan-out explosions
Suggest safer join anchors

Example diagnostic:

SELECT *  
FROM metadata.optimizer_diagnostics  
WHERE pass_name = 'join_analysis';

Output:

Structural awareness improves architectural quality.

Avoiding Semantic Drift

Join reordering must be safe.

MDDE does not reorder joins when:

Outer joins are involved in unsafe positions
Window functions depend on join order
Filter placement changes semantics

Semantic protection rules are embedded in optimizer passes.

Reordering is only applied when provably equivalent.

Why This Matters in Migration

In migration programs:

Equivalent SQL is often rewritten
Join order changes create noise
Reviewers struggle with diff overload
Structural drift hides logical drift

Canonical join ordering eliminates cosmetic differences.

It isolates real semantic changes.

That reduces migration risk.

🔧 Technical Deep Dive

Inspect Join Graph

SELECT  
    parent.entity_name AS parent,  
    child.entity_name  AS child,  
    r.join_condition  
FROM metadata.relationship r  
JOIN metadata.entity parent  
  ON r.parent_entity_id = parent.entity_id  
JOIN metadata.entity child  
  ON r.child_entity_id = child.entity_id  
WHERE parent.model_id = 'mdl_sales';

Detect Potential Many-to-Many Joins

SELECT *  
FROM metadata.relationship  
WHERE cardinality = 'many-to-many';

Regenerate Canonical SQL

python -m mdde.scripts.export_model mdl_sales --canonical

Closing

Databases optimize execution.

MDDE optimizes structure.

When structure is deterministic:

Diffs are clean
Reviews are faster
Lineage is stable
Governance is reliable

Optimization begins before runtime.

It begins in metadata.