oceanbase-sql-optimization_skill

Overview

This skill provides SQL optimization best practices for OceanBase (MySQL and Oracle modes). It guides query rewriting, index design, execution plan analysis, partition pruning, join strategy selection, and slow-query tuning to improve performance and reduce resource consumption.

How this skill works

The skill inspects execution plans, index usage, partition access, join operators, and optimizer estimates to identify inefficiencies. It recommends concrete changes: creating or reordering indexes, rewriting predicates to enable index and partition pruning, choosing appropriate join algorithms via hints or rewrites, and tuning slow queries based on audit and profile information.

When to use it

• Investigating slow queries or high CPU/IO from specific SQL statements
• Designing or reviewing indexes for high-traffic tables
• Verifying partition pruning and reducing cross-partition scans
• Optimizing joins in complex multi-table queries
• Preparing queries for OceanBase MySQL or Oracle compatibility

Best practices

• Always read the EXPLAIN plan first: check operators, EST. ROWS, partitions, and is_index_back flags
• Create indexes on WHERE and JOIN columns; prefer composite indexes with the most selective column first
• Avoid functions on indexed or partition keys—rewrite predicates to preserve index/partition use
• Use covering indexes (STORING clause) to prevent index back lookups for hot read paths
• Prefer LOCAL indexes on partitioned tables unless cross-partition queries require GLOBAL indexes
• Limit SELECT *; fetch only required columns to reduce IO and enable covering indexes

Example use cases

• Tune a slow order-report query by adding a composite index on (customer_id, order_date) and rewriting date filters
• Fix a starved partitioned table by rewriting queries to include the partition key so partition pruning occurs
• Choose NLJ for small outer tables with indexed inner tables or HJ/MJ for large join sets after reviewing EXPLAIN
• Convert functions like YEAR(order_date)=2024 into range predicates to enable index usage
• Diagnose slow statements using GV$OB_SQL_AUDIT (Oracle mode) or oceanbase.GV$OB_SQL_AUDIT (MySQL mode)