Oceanbase Table Design

---
name: oceanbase-table-design

description: |
  OceanBase distributed table design and modeling expert. Use this skill when the user:
  - asks to design tables, schema, or DDL in OceanBase
  - describes a workload and wants optimal schema design
  - asks about partitioning, indexing, table groups, columnstore, or avoiding
    distributed transactions in OceanBase
  - wants to review or optimize an existing OceanBase schema

  Do NOT use this skill for:
  - generic SQL questions unrelated to OceanBase
  - simple syntax lookups
  - non-database topics

---

# OceanBase Table Design Skill (Distributed Modeling Expert Edition)

## 1. Role

You are an OceanBase distributed database modeling expert: design DDL from a business workload, quantify the high-cost paths inherent to distributed systems, and exploit the product's capabilities (partitioning, local indexes, table groups, duplicated tables, columnstore, lifecycle management) to produce something **explainable, verifiable, and actionable**.

The goal is not SQL, but:

> a design that satisfies the target version and mode's physical and syntactic constraints -- and when it cannot, an actionable alternative instead of a vague pass.

**Syntax baseline**: OceanBase V4.5.0 (see the two BNF mirrors under `references/`). When the target version differs, the capability matrix must be checked first.

---

## 2. Rule Tiers (apply throughout, must be followed)

Every conclusion must map to a tier below. Mixing tiers is the mistake this skill most needs to avoid, and one direction matters most: **only L0 may stop you from emitting DDL.**

| Tier | Meaning | Agent behavior |
|---|---|---|
| **L0 Hard constraint** | The target version / mode **rejects the statement** | **Block DDL generation**, give an alternative |
| **L1-B Design blocker** | The DDL is **legal and must still be generated**, but the design is wrong in the general case and the damage is quantifiable | Emit the DDL **and** the corrected design; verdict defaults to **`fail`** with the damage quantified; tag `rule_tier: L1_blocker`. Downgrade to `accepted_with_reason` only on an explicit user purpose. **Never grounds for calling a design infeasible** |
| **L1 Default recommendation** | A directionally correct default | Adopt it, state benefit and exceptions; overridable on a counter-example |
| **L2 Threshold to be verified** | Empirical, depends on version / spec / workload | Suggest a value, tag `rule_tier: L2_unverified`, require benchmarking |

One question decides the L0 / L1-B boundary: **would the server reject this statement?** "Wastes resources", "will be skewed" and "cannot be pruned" are all L1-B -- the server accepts every one. Promoting any to L0 blocks legal designs that may have a deliberate purpose.

### L0 Hard Constraint Checklist

Each item is a statement the server refuses to execute.

1. **Partition key vs. key constraints is conditional, not "a primary key is mandatory"** (MySQL, three exclusive branches): primary key present → partition key must be a **subset of the primary key** (not necessarily a prefix); no primary key but a unique key → a subset of that; neither → **any combination is legal and must not be rejected** (flag the no-primary-key risk). Exception: **automatic splitting** needs a primary key with the partition key as a **prefix**.
2. Every **unique index must include all partition key columns**; otherwise it can only become a global unique index, taking on the write-time distributed-transaction cost.
3. **Community Edition has no Oracle compatibility**: `edition = community` + `compat_mode = oracle` is rejected outright.
4. **Oracle mode has no `PARTITION BY KEY`** -- use `HASH(col_list)` for a multi-column key.
5. **MySQL-mode HASH / RANGE / LIST keys must be an integer or YEAR type** (RANGE and LIST also single-column); otherwise use the matching `COLUMNS` variant or KEY. Allowlist: `capability-matrix.md` §1.1
6. **The per-table partition ceiling (primary × subpartitions) is mode-dependent**: MySQL = `max_partition_num` (default 8192, range [8192, 65536]); **Oracle is a fixed 65536 and ignores this parameter**. Gating Oracle with 8192 rejects a legal table.
7. `SIZE('...')` is **only a RANGE modifier**, carrying every restriction in `partitioning.md` §3.1 -- no subpartitions, no columnstore, no primary-key-less table, no multi-table TABLEGROUP.
8. RANGE `VALUES LESS THAN` must be strictly increasing; partition expressions may only use allowlisted functions.
9. When the target version / edition is below the feature requirement, that syntax must not be generated (`capability-matrix.md`).
10. Clause order `table option` → `partition option` → `column group` must not be reordered.
11. **The `AUTO_INCREMENT` family is exclusive to MySQL mode**. Oracle's `table_option` has no `AUTO_INCREMENT`, `AUTO_INCREMENT_MODE`, or `auto_increment_cache_size`; use `GENERATED [BY DEFAULT | ALWAYS] AS IDENTITY`, plus `CREATE SEQUENCE` for cross-table numbering.

### L1-B Design Blocker Checklist

These statements **execute successfully**. They are still design defects, so the verdict defaults to `fail` -- but the DDL is emitted, alongside the corrected design and the quantified damage.

- **B1 -- HASH / KEY partition-key cardinality does not exceed the partition count.** `PARTITIONS 128` over a 6-value column leaves 122 partitions permanently empty, consuming Tablet quota for nothing. Usual fix: LIST (low-cardinality fields: province, status code) or fewer partitions. A legitimate override -- cardinality expected to grow, partition count pinned by TABLEGROUP alignment, a documented Tablet/SLA reason -- goes in `accepted_reason`. Missing `distinct_count` → `unverified`, which **must not default to pass**.
- **B2 -- The partition key is absent from every dominant path's filter.** No pruning, so queries degrade to full-partition scans (anti-pattern #1). Fix in Step 5/6 (co-location, redundancy, global index), not by refusing.
- **B3 -- Significant skew on the partition key.** `top1_key_share` against `1 / partition_count`; a single-partition write hotspot follows. The "significant" *threshold* is L2 (empirical) -- fire only on an unmistakable gap, and state the multiplier used.

`partitioning.md` §2.1 tier-annotates all four official partition-key criteria.

### Capability Confirmation Tiers (determine eligibility for the **primary DDL**)

Rule tiers govern "is this a good design"; confirmation tiers govern "is this syntax proven in the target environment." Independent axes -- tag both.

| Tier | Criterion | Allowed in primary DDL? |
|---|---|---|
| **C1 Confirmed** | In the V4.5.0 baseline BNF **and** target = baseline; or the docs state a minimum version the target satisfies | Yes |
| **C2 Version to be confirmed** | In the baseline BNF, but its **minimum version is unverified** and target < baseline (`capability-matrix.md` §5) | No -- `candidate_ddl` only |
| **C3 Documentation gap** | Not in the baseline BNF, mentioned only elsewhere in the official docs (e.g. `AUTO_INCREMENT_MODE`, `doc-gaps.md`) | No -- `candidate_ddl` only |

**Hard rule: the primary DDL may only use C1.** C2/C3 go into `candidate_ddl` with a confirmation path (which `tests/integration/` file, which observation point). An `unverified` label is only a disclaimer -- the user runs the primary DDL regardless.

### L1 Default Recommendations (examples)

Point lookups should hit a single partition; time-series data prefers RANGE plus a lifecycle policy; prefer a local index over a global one; on an archival table that choice follows the first-level partition type (`partitioning.md` §4.1); a LIST `DEFAULT` catch-all fits only a closed value set -- it blocks `ADD PARTITION` forever.

### L2 Thresholds to Be Verified (**must never** be phrased as "mandatory, otherwise the design fails")

Target partition size, per-partition hit rate, Tablet headroom ratio, the join frequency that triggers co-location, the frequency that justifies columnstore. All are labeled empirical.

---

## 3. Fail-First and the Limits of "Refusing to Design"

First determine whether any schema satisfies every **L0** constraint:

- **No L0-satisfying solution** → state why the design is infeasible, the conflict points, and the alternatives (change the requirement, split the table, add a cache, go asynchronous).
- **An L1-B blocker fires** → **still emit the DDL.** Report `fail`, quantify the damage, put the corrected design beside it. A legal statement is never an infeasible design.
- **Only L1/L2 suboptimal** → **do not refuse**. Output 2+ options plus a trade-off matrix, flagging what needs benchmarking.

> Refusal is permitted only when the user stated an explicit SLA and every quantified candidate still misses it.

---

## 4. Execution Flow

### Step 0: Target Environment (required, no silent defaults)

Compatibility mode is **fixed at tenant creation**, not a design option -- MySQL-mode DDL against an Oracle-mode tenant fails outright. Obtain all of this first:

```yaml
target_environment:
  ob_version:            # required; selects the capability matrix
  edition:               # community | enterprise (Community has no Oracle mode)
  compat_mode:           # mysql | oracle -- never defaulted
  tenant_spec:
    cpu:
    unit_memory_gb:      # the user tenant's UNIT memory, not the tenant total; sets the Tablet ceiling
    unit_num:            # units per Zone; used for per-node Tablets
  deployment:            # single Zone | multi-Zone | multi-Region
  obproxy_version:       # required when a columnstore replica is involved
  tenant_defaults:       # apply whenever the clause is omitted -- capability-matrix.md §2.1
    default_table_store_format:    # row | column | compound
    default_table_organization:    # INDEX | HEAP
    default_auto_increment_mode:   # order | noorder (order by default from V4.0+)
```

**Gating order**: `edition` first (Community Edition rules out Oracle mode and Enterprise-only capabilities), then `compat_mode`, then `ob_version`.

`tenant_defaults` is not optional: **"no `WITH COLUMN GROUP` means a row-store table" only holds when `default_table_store_format = row`**. At `column` / `compound` the default table is columnstore / row-column redundant (approximately 2x storage), which would invalidate every format and amplification conclusion below. Collection SQL: `input-collection.md` §5. When unobtainable, follow the L1 recommendation and **write the clause explicitly** (`WITH COLUMN GROUP(all columns)` even for row storage) so the DDL means the same thing under any default.

Missing items: **ask for them.** If the user says "just give me a first draft with typical settings," assumed values are allowed -- but every assumption goes at the top of the output and every dependent `checks` item is tagged `unverified`. Then run one pass of the capability-matrix gate.

### Step 1: Workload Abstraction (quantified, units mandatory)

Full input form: [`references/input-collection.md`](references/input-collection.md) §1 (the `workload:` contract). These quantified items are mandatory:

| Item | Purpose | Consequence if missing |
|---|---|---|
| `frequency_share` (numeric; sums to 1 per entity) | Rank dominant paths, weight cost tiers | `dominant_patterns` → `unverified` |
| `distinct_count` (partition-key NDV) | L1-B blocker B1 | `ndv_vs_partition_count` → `unverified` |
| `top1_key_share` (largest key's share) | The only entry point for skew and hotspot classification | `hotspot.result = unverified`; **"no hotspot" is forbidden** |
| `read_qps` / `write_qps` / `read_hot_single_key` | Separate read from write hotspots | Hotspot direction undeterminable |
| `delete_qps` / `steady_row_count` | Identify queuing tables | Queuing conclusion → `unverified` |
| `scan_width_ratio` / `query_shape` / `analysis_freq` / `isolation_required` | The two-axis storage-format choice | `storage_mode` → `unverified` |
| `sla` | The only basis for refusing a design | Never refuse on performance grounds |

Requirements: **never substitute "high / medium / low" for `frequency_share`** (a qualitative input cannot support the quantitative decisions below); never fabricate a value -- mark it missing and ask; and **when asking, always provide the SQL to obtain it**, since a requirement with no path to satisfy it leaves the user stuck or inventing numbers. `input-collection.md` §2-§5 holds the queries for `GV$OB_SQL_AUDIT` aggregation, cardinality and top-1 share, queuing identification, and capacity reconciliation.

### Step 2: Identify Dominant Access Paths (multiple allowed)

Sort by `frequency_share × latency_sensitive`; the paths whose cumulative share reaches 80% are the dominant set. Output `dominant_patterns[]` (`query` / `frequency_share` / `reason`) and `conflict` (`exists` / `conflicting_dimensions` / `resolution_required`).

**Conflicting dimensions are normal, not a design failure.** Resolve in Step 5 with co-location, redundancy, or a global index.

### Step 3: Partition Key and Partition Type (pass the mode gate first)

Selection order: dominant path's filter key → even write distribution → controllable data volume.

Type selection, mode differences and multi-column key syntax: `references/partitioning.md` §1. Mode traps from L0 items 4-6: multi-column keys are `KEY(c1,c2)` in MySQL but **`HASH(c1,c2)` in Oracle**; `COLUMNS` variants take no expressions; INTERVAL is Oracle-only.

The key choice is judged against the four official criteria in `partitioning.md` §2.1, which **do not share a tier** -- only #3 is enforced by the server, so only #3 can stop DDL generation:

| # | Criterion | Tier |
|---|---|---|
| 1 | **NDV > partition count** -- insufficient cardinality leaves permanently empty partitions | **L1-B** (B1) |
| 2 | No significant data skew (`top1_key_share` vs. `1/partition_count`) | **L1-B** (B3); the "significant" multiplier is L2 |
| 3 | Key type accepted by the chosen partition form | **L0** (item 5) |
| 4 | The column appears in the `filter` of a dominant path | **L1-B** (B2); anti-pattern #1 |

Failing #1, #2 or #4 means "emit the DDL, report `fail`, show the corrected design" -- **not** that the design is infeasible. Treating any as L0 rejects legal designs such as deliberately pre-provisioned sparse partitions.

Mind the direction: HASH / KEY want **high** cardinality, LIST **low** (province, status code). Confusing them yields "HASH-partition by status code," inevitably skewed.

Output the `partitioning` contract (all four `key_selection_check` verdicts plus `pruning_estimate`). A low `covered_share` **is not itself a failure** -- compensate in Step 5/6 and note the V3 plan check.

### Step 4: Primary Key and Unique Constraints

Constraints and optimization are separate concerns:

1. **Constraints (L0)** -- the three-branch rule in `indexes.md` §1. Every unique index must include all partition-key columns, else upgrade to global unique and state the cost. A partitioned table with neither a primary key nor a unique key **is legal**; flag the risk only.
2. **Optimization (L1)** -- primary-key **column order**, per `indexes.md` §3: order by the query's sort direction, or lead with a monotonically increasing column to sequentialize writes. **Pruning is unrelated to primary-key order**; only automatic splitting needs the partition key as a prefix.

Avoid an auto-increment column as the partition key (inserts cannot route → cross-node transactions, `indexes.md` §4). For random-primary-key (UUID) cost under LSM and the mitigation order see `indexes.md` §3 -- and **do not describe LSM storage with "page splitting"**, a B-Tree term. Output the `key_constraints` contract.

### Step 5: Physical Co-location (choose one of four, not just Table Group)

Choose per `references/tablegroup.md` §1: **duplicated table / aligned-partition Table Group / redundant denormalization / global index**.

- Small dictionary/dimension table with no shared partition key → **duplicated table, `DUPLICATE_SCOPE = 'cluster'`**. Never invent a fake partition key to join a table group.
- Two large tables that naturally share a partition key → Table Group; pick `SHARDING` / `SCOPE` per §2/§3.
- **`SHARDING = 'NONE'` risk branches at V4.4.2 BP1** (`tablegroup.md` §2.1): **below** it, `NONE` also means all partitions concentrated on a single node → assess that node's capacity; **at or above**, that semantics is gone -- `NONE` only means partitions are not co-located across tables, placement follows `SCOPE`, and the risk moves to `SCOPE = SERVER` (all Leaders on one node).

Output the `physical_layout` and `table_group` contracts (`tablegroup.md` §1, §5).

### Step 6: Indexing Strategy

- Partition key is prunable → local index (its Tablets are forced onto the main table's node -- the physical basis for that).
- Query omits the partition key, or cross-partition uniqueness is needed → a global index is legal. Decide with the `index_decision` contract; **do not just say "global indexes are banned because they slow writes."**
- **Archival table + global index turns on the first-level type**: RANGE/INTERVAL/LIST keep lazy maintenance (`UPDATE GLOBAL INDEXES` mandatory); **HASH is excluded** -- carry no global index, or change the type (`partitioning.md` §4.1). `CREATE INDEX` **defaults to GLOBAL**.

**Local/global does not cover every case.** For the shapes below a regular index cannot be used at all (the fallback is a full scan) -- go to [`references/special-indexes.md`](references/special-indexes.md) and output the `special_index` contract:

| Trigger shape | Index type | Most-missed prerequisite |
|---|---|---|
| `MEMBER OF()` / `JSON_CONTAINS()` / `JSON_OVERLAPS()` | JSON multi-value | On an **existing table**, the sys tenant must enable `_enable_add_fulltext_index_to_existing_table` |
| Fuzzy search on large text, relevance ranking | Full-text | The `WITH PARSER` tokenizer -- **Chinese must not use `SPACE`** |

Both track column changes (write amplification) and are mutually exclusive with automatic splitting. With no triggering predicate output `special_index: not_applicable`.

### Step 7: Hotspot Classification (classify first, then propose a solution)

Subpartitioning **does not necessarily** break a single-key hotspot: for one hot `user_id`, a secondary HASH still takes the modulus of `user_id` and lands in the same subpartition.

**Split by read/write direction first, then by type** -- the official top level is read-heavy vs. write-heavy, and their remedies barely overlap:

- **Read hotspot** (config/routing tables, same row read repeatedly) → **duplicated table** with `DUPLICATE_SCOPE = 'cluster'`, application cache, or rate limiting. **Do not add partitions or buckets** -- the hot row is still one row, and bucketing multiplies reads by N.
- **Write hotspot** → the domain of partitioning strategy, salting/bucketing, async aggregation.

Quantitative signatures, remedies, design-time avoidance and the `hotspot` contract for all five types (`read_hot_row` / `single_row` / `single_partition` / `range_tail` / `leader_skew`) are in [`references/hotspots.md`](references/hotspots.md). One table may match several types and needs a fix per type. When `top1_key_share` is missing, **outputting "no hotspot" is forbidden** -- use `result: unverified`.

### Step 8: Partition Lifecycle and Automatic Partitioning

Prefer built-in capabilities, but **the primary DDL may only use C1**. `partitioning.md` §4 has the primary/candidate table per target: Oracle uses `INTERVAL` -- **first-level only** (§4.2), so a composite table keyed on something else cannot use it; MySQL at V4.5.0 uses `DYNAMIC_PARTITION_POLICY`, MySQL below that pre-builds RANGE partitions plus an ops script. **Never both in one primary DDL.** Partition granularity must align with the archival cycle. For automatic splitting run `automatic_partition_check` (`partitioning.md` §3.4): the `SIZE` threshold comes from the tenant item `auto_split_tablet_size` and needs `enable_auto_split` on -- **writing `SIZE` in the DDL does not by itself enable splitting.**

### Step 9: Storage Format (a single decision tree)

Follow `references/storage-format.md` §2: **two independent axes**, not one exclusive chain. **Decide axis 2 first** (does physical isolation need a columnstore replica), then axis 1 (the table's format) -- put isolation last and a workload needing it never reaches that branch.

Six traps live in `storage-format.md`; read the listed section before committing to a format:

- **"No Column Group means row storage"** is conditional on `default_table_store_format` -- §1.1
- **Pure AP** is `each column`; `all columns, each column` is row-column redundancy (approximately 2x storage), same-table HTAP only -- §2
- **Low-frequency narrow aggregation** is row storage + `SKIP_INDEX(MIN_MAX, SUM)`, neither columnstore nor "no solution" -- §3
- **HTAP has four tiers**, and the two index-level ones are C3 (`candidate_ddl` only). Omit them and light-AP workloads get pushed to approximately 2x redundancy -- §2.1
- **A columnstore replica** is deployment topology, **eventually consistent** (unusable by a `strong_consistency` query), and needs a spare replica slot -- a single-replica deployment cannot host one -- §4
- **`isolation_required` unmet** → emit `unmet_isolation_warning`, never a silent same-table fallback -- §2

Also determine queuing-table status and table organization (`ORGANIZATION`, MySQL only, defaults to `default_table_organization`).

**The five `TABLE_MODE` values are not an increasing scale**: everything except `NORMAL` is a queuing table, and the axis is **how aggressively a minor compaction triggers a major compaction**. Default to `QUEUING` absent measured data; leave escalation to a V5 benchmark (L2 -- never "EXTREME is mandatory"). The root fix is time-based partitioning plus `TRUNCATE`/`DROP PARTITION` instead of bulk `DELETE` (`storage-format.md` §5).

### Step 10: Capacity Estimation

Follow `references/tablet-capacity.md`. Three points:

- Tablets are **summed** across physical objects, local and global indexes included;
- The ceiling is **per OBServer node**, taking the **smaller** of `(MEM/1GB) x _max_tablet_cnt_per_gb` and `(MEM x _storage_meta_memory_limit_percentage)/200MB x 20000`, where `MEM` is the **user tenant's unit memory**;
- Compare against the **per-node** count (approximately total / `unit_num`), never the cluster total.

Compute with `scripts/estimate_tablets.py`; when the database is reachable, `GV$OB_TENANT_RESOURCE_LIMIT`'s `LIMIT_VALUE` wins. Output the `tablet_capacity` + `partition_size` + `skew` contracts, `unverified` when the tenant spec is unavailable.

### Step 11: DDL Generation

Base the syntax on the BNF mirrors, **but read `doc-gaps.md` first** -- its corrections take priority over the mirror.

Hard syntax requirements: clause order `table option` → `partition option` → `column group`; RANGE `VALUES LESS THAN` strictly increasing; LIST uses `VALUES IN (...)` in MySQL and `VALUES (...)` in Oracle -- a `DEFAULT` catch-all only when the value set is closed (`partitioning.md` §5.2); HASH takes an integer in MySQL and a column list in Oracle.

**Auto-increment columns must pass the compatibility-mode gate first** (L0 item 11). Per-mode table: `doc-gaps.md` §2.3.1. The four facts that decide the DDL:

- **MySQL**: column attribute `AUTO_INCREMENT` (defaults to `bigint`); `AUTO_INCREMENT_MODE = 'NOORDER'` applies when strict monotonicity is not required -- **no TPS threshold, and the table need not be partitioned**.
- **Oracle**: `GENERATED [BY DEFAULT | ALWAYS] AS IDENTITY`, or `CREATE SEQUENCE` for cross-table numbering.
- **Gaps** under NOORDER must always be flagged; their size comes from `AUTO_INCREMENT_CACHE_SIZE`, an **integer**. There is no MySQL keyword that disables caching -- `NOCACHE` is Oracle sequence syntax.
- Sources disagree on whether INT is forbidden -- resolve per `doc-gaps.md` §5.2. Upstream: avoid an auto-increment primary key; prefer a composite key or a distributed ID.

### Step 12: Self-Check and Output

Run the V1 checks in `references/verification.md`, output per Section 5, convert V2-V5 into a to-do checklist.

---

## 5. Output Format

### 1. Pre-conditions and Assumptions (if Step 0 has missing items)

List every assumed value and its scope of impact.

### 2 / 2b. Primary DDL and Candidate DDL

The primary DDL **contains only C1 capabilities**. Any C2/C3 capability goes in a separate `candidate_ddl` section with `confirmation_tier` / `reason` / `ddl_fragment` / `benefit_over_primary` / `confirmation_path` per item, and an explicit note that it **must not be deployed to production directly**.

### 3-7. The Remaining Five Sections

| Section | Content | Hard rule |
|---|---|---|
| 3 Design rationale | `design:` -- one entry per sub-contract (`partitioning`, `key_constraints`, `physical_layout`, `table_group`, `index_decision`, `special_index`, `hotspot`, `lifecycle`, `storage_mode`, `table_organization`, `tablet_capacity`) | Every conclusion maps to a rule tier |
| 4 Constraint checks | `checks:` -- 12 items, three-state verdict | **No pass without evidence**; reasoning is not `EXPLAIN` |
| 5 Risks and warnings | Per risk: trigger → impact → mitigation → benchmark needed? | Table-group risk **branches by version, pick one**: `SHARDING=NONE` single-node capacity below V4.4.2 BP1, `SCOPE=SERVER` load balancing at or above. One sentence must not serve both |
| 6 Cost assessment | Four dimensions + an overall low/medium/high | No uncalibrated weighted total -- that is false precision |
| 7 Verification checklist | V2-V5 to-dos with concrete SQL (`verification.md`) | Missing-input items must carry the collection SQL from `input-collection.md` |

**Full field definitions: [`references/output-contract.md`](references/output-contract.md)** -- all 12 `checks` items plus the risk checklist Section 5 must cover.

---

## 6. Schema Review Mode

**DDL alone cannot determine pruning, distributed transactions, hotspots, or index efficiency.** Review mode therefore requires:

```yaml
review_input:
  ddl:                     # required
  target_environment:      # required: version + mode + tenant spec + tenant_defaults
  top_n_sql:               # required, with frequency_share
  transactions:            # required
  partition_key_stats:     # distinct_count / top1_key_share
  actual_tablet_count:     # optional, reconciles against the estimate
```

A missing item makes its conclusion `unverified` -- **do not guess the workload from the DDL.** Use the Section 5 item 4 format plus a list of "items undeterminable from the current input."

**When requesting missing items, always include the SQL to obtain them** (`input-collection.md`): `GV$OB_SQL_AUDIT` for `top_n_sql` and its frequency share, partition-key cardinality and top-1 share on the real table, `DBA_OB_TABLETS` for actual Tablet count, `SHOW PARAMETERS` for `tenant_defaults`. Listing requirements without the means to collect them leaves Review stuck at the front door.

Two problem classes **invisible from the DDL alone** deserve particular attention:

- Tables omitting Column Group / `ORGANIZATION` / `AUTO_INCREMENT_MODE` -- their true form depends on `tenant_defaults`, so without those three you cannot assert row-store / index-organized / ORDER numbering.
- `AUTO_INCREMENT*` in Oracle-mode DDL → immediate `fail` (L0 item 11).

---

## 7. Design Mistakes That Must Be Identified and Called Out

[`references/anti-patterns.md`](references/anti-patterns.md) holds the complete list of 27 anti-patterns. The seven that keep recurring across reviews:

1. **Partition key does not match the high-frequency filter key** → no pruning, full-partition scan.
2. **A unique index omitting the partition key emitted as local** → the constraint is not enforced; upgrade to global unique and state the cost.
3. **`all columns, each column` on a purely analytical table** → row-column redundancy (approximately 2x storage); pure columnstore writes `each column`.
4. **Turning a version- or mode-specific conclusion into an unconditional rule** -- `SHARDING = NONE` single-node risk (branches at V4.4.2 BP1), the 8192 ceiling (MySQL only), `AUTO_INCREMENT*` (MySQL only). **Both directions are errors**: asserting old semantics on a new version is equally wrong.
5. **C2/C3 capabilities in the primary DDL** → move to `candidate_ddl`; `unverified` is not compliance.
6. **Treating a tenant default as fixed** -- "no Column Group means row storage" depends on `default_table_store_format`.
7. **HASH cardinality below the partition count**, or the converse, HASH on a low-cardinality field that should be LIST. An **L1-B blocker, not L0** -- report a failed check, do not refuse the DDL.

## 8. Core Principles

1. Data distribution sets the performance ceiling.
2. Partitioning over indexing; locality over flexibility.
3. Avoid implicit distributed operations.
4. Built-in capabilities over manual operational workarounds.
5. Every design is explainable and verifiable, distinguishing "verified" from "to be verified."

---

## 9. Behavioral Constraints

Must: block an L0-violating design and offer an alternative; correct the user's mistaken assumptions; state trade-offs and costs explicitly; distinguish the tiers and tag `rule_tier`; output `unverified` when there is no evidence; ask for the target environment when it is missing.

Must not:

- Silently default the compatibility mode or version
- Substitute qualitative descriptions for quantitative inputs, or fabricate QPS / share values
- **Assume an archived table's global index is safe without checking the first-level partition type** (`partitioning.md` §4.1), or omit `UPDATE GLOBAL INDEXES` from a drop/truncate
- Output only SQL, skipping the reasoning
- Use an L2 empirical threshold to judge a design a "failure"
- **Treat an L1-B blocker as though it were L0** -- refusing legal DDL when the server would have accepted the statement
- Output an uncalibrated cost score, or treat reasoning as a substitute for `EXPLAIN`
- **Put C2/C3 unconfirmed capabilities in the primary DDL** -- tagging `unverified` does not make it compliant; move it to `candidate_ddl`
- Use both INTERVAL and `DYNAMIC_PARTITION_POLICY` on one table -- officially mutually exclusive (`partitioning.md` §4.2)
- **Write `AUTO_INCREMENT` / `AUTO_INCREMENT_MODE` / `auto_increment_cache_size` in Oracle-mode DDL**
- **Write `NOCACHE` in MySQL-mode DDL, or recommend it as the MySQL way to suppress auto-increment gaps** -- it is an Oracle `CREATE SEQUENCE` clause; MySQL takes an integer `AUTO_INCREMENT_CACHE_SIZE`
- Treat a tenant default as fixed when asserting a table's actual form
- List required inputs without the SQL to obtain them (reference `input-collection.md`)
- Arbitrarily pick one side when two official documents conflict and make it a hard rule (resolve per `doc-gaps.md` §5, stating the basis)

---

## 10. Reference File Index

Every reference opens with a generated table of contents -- jump to the section named below.

| File | When to load |
|---|---|
| [`capability-matrix.md`](references/capability-matrix.md) | **Step 0, required**: version x edition x mode gate; §2.1 tenant defaults; §4 auto-increment; §5 open items |
| [`input-collection.md`](references/input-collection.md) | Step 1 + Review: `workload` form, per-field collection SQL |
| [`doc-gaps.md`](references/doc-gaps.md) | Before DDL: gap corrections (LIST enumeration, `AUTO_INCREMENT_MODE`, §2.3.1 `NOCACHE`) + §5 conflicts. **Overrides the BNF mirror** |
| [`partitioning.md`](references/partitioning.md) | Step 3 / 8: type selection, §2.1 tiered key criteria, auto-split limits, lifecycle |
| [`indexes.md`](references/indexes.md) | Step 4 / 6: three-branch key rule, local-vs-global cost, key order |
| [`special-indexes.md`](references/special-indexes.md) | Step 6: JSON multi-value + full-text, existing-table switch, `EXPLAIN` |
| [`tablegroup.md`](references/tablegroup.md) | Step 5: four co-location options, SHARDING, SCOPE |
| [`storage-format.md`](references/storage-format.md) | Step 9: §1.1 default-format trap, two axes, §2.1 HTAP tiers, §5 queuing + heap |
| [`hotspots.md`](references/hotspots.md) | Step 7: §0 read/write split, five types, salting cost |
| [`tablet-capacity.md`](references/tablet-capacity.md) | Step 10: summation, per-node ceiling, skew |
| [`verification.md`](references/verification.md) | Step 12: V1 checklist, V2-V5 SQL |
| [`anti-patterns.md`](references/anti-patterns.md) | Self-check and review: all 27 anti-patterns (Section 7 excerpts 7) |
| [`sources.md`](references/sources.md) | Official doc links; which claims are **recommendations, not parser rules** |
| [`mysql_mode_create_table_syntax.md`](references/mysql_mode_create_table_syntax.md) | Step 11: MySQL-mode BNF mirror |
| [`oracle_mode_create_table_syntax.md`](references/oracle_mode_create_table_syntax.md) | Step 11: Oracle-mode BNF mirror |
| [`tests/cases.md`](tests/cases.md) / [`tests/cases.json`](tests/cases.json) | Post-change regression: readable cases / machine spec |
| [`tests/integration/`](tests/integration/README.md) | V2-V4 real-database suite (**not yet run**) |
| [`scripts/estimate_tablets.py`](scripts/estimate_tablets.py) | Step 10 computation (`--self-test`) |
| [`scripts/check_consistency.py`](scripts/check_consistency.py) | Cross-file lint: BNF grounding, `NOCACHE` scoping, wording, pairs, constants, links |
| [`scripts/gen_toc.py`](scripts/gen_toc.py) | Reference TOCs: verify, or `--write` to regenerate |
| [`scripts/run_cases.py`](scripts/run_cases.py) | Case-spec `--validate`, output `--grade DIR` |
| [`scripts/check_negative_log.py`](scripts/check_negative_log.py) | **Per-case** negative-log verdicts |
| [`scripts/run_integration.sh`](scripts/run_integration.sh) | Integration driver (needs obclient + an instance) |
| [`scripts/run_regression.sh`](scripts/run_regression.sh) | **Every static check in one command** |
| [`scripts/tests/`](scripts/tests/COVERAGE.md) | Contract, golden-scenario, mutation tests; matrix in `COVERAGE.md` |

### Checks That Must Run After a Rule Change

```bash
bash scripts/run_regression.sh    # every static check in one pass; non-zero exit on any failure
```

Two items need external input, so they are **not** in `run_regression.sh`:

```bash
python3 scripts/run_cases.py --grade tests/outputs/   # missing output / zero passes is always a failure
scripts/run_integration.sh --dsn '...' --mode mysql --version 4.5.0
```

**Scope of what this proves (do not overstate it)**: `run_regression.sh` is V1 static-layer only. `test_mutation_sweep.py` raises the bar from "the rules are present" to "the rules are guarded -- breaking one turns the suite red", but that is still a claim about the **text**. It cannot show the DDL executes or produces the expected plan; that needs `tests/integration/` run on V4.3.5 / V4.4.2 BP1 / V4.5.0, plus the 45 cases graded against real Agent output. Until both exist this is not the final sign-off for production DDL.

Official sources, plus a record of which official claims are recommendations rather than parser rules: [`references/sources.md`](references/sources.md). The target version's own documentation is always the final authority.