oceanbase-schema-design_skill

Overview

This skill provides practical schema design best practices for OceanBase in both MySQL and Oracle modes. It focuses on table and primary key design, partitioning strategies, table groups, index design, and schema optimization principles. The guidance balances normalization with performance, promoting designs that scale in a distributed environment.

How this skill works

It inspects schema choices and recommends concrete changes: primary key selection, partition key alignment, partition type, table group usage, and index layouts. It enforces rules such as including partition keys in primary/unique keys, preferring LOCAL indexes for partitioned tables (MySQL mode), and using partition pruning-friendly predicates. Mode-specific syntax and behavior differences (data types, default index behavior, time/string functions) are highlighted to avoid mistakes.

When to use it

• Designing new OLTP or history tables in OceanBase
• Creating partitioning strategy for large or time-series datasets
• Preparing tables that will be frequently joined across nodes
• Evaluating index choices for query performance and DML impact
• Converting schemas between MySQL and Oracle modes

Best practices

• Use business fields as primary keys where possible; avoid hidden auto-generated keys for distributed joins.
• Ensure every primary/unique key on a partitioned table includes at least one partition key column.
• Choose partition type by access pattern: HASH for high-cardinality point lookups, RANGE for time-series, LIST for discrete sets.
• Prefer LOCAL indexes for partitioned tables in MySQL mode; avoid unnecessary GLOBAL indexes to reduce cross-partition overhead.
• Design composite indexes using leftmost-prefix and place higher-cardinality columns first; use covering indexes to eliminate table lookups.
• Group frequently joined tables in a Table Group with matching partitioning to enable Partition Wise Join.

Example use cases

• Order table: HASH partition on customer_id with primary key including customer_id to enable point lookups and pruning.
• Log table: RANGE partition by log_date with monthly partitions and primary key including log_date for efficient historical queries and purging.
• Reporting JOINs: Place customer and order tables in the same table group with identical first-level partitions to avoid cross-shard joins.
• High-throughput insert table: Use LOCAL indexes and avoid global indexes to minimize distributed DML overhead.
• Schema migration: Convert data types and function calls when switching modes (BIGINT/VARCHAR vs NUMBER/VARCHAR2, NOW() vs SYSDATE).