OceanBase SQL Optimization Skill

Expert in SQL query optimization for OceanBase distributed database (MySQL & Oracle modes).

Provides best practices for writing efficient SQL queries, designing effective indexes, analyzing execution plans, and tuning slow queries in OceanBase. This skill covers both MySQL mode and Oracle mode.

Mode-Specific Syntax Guide

While OceanBase uses the same underlying optimizer for both MySQL and Oracle modes, there are important syntax differences:

Aspect	MySQL Mode	Oracle Mode
EXPLAIN Syntax	`EXPLAIN [INTO table_name] [SET statement_id = string]`	`EXPLAIN [INTO table_name] [SET STATEMENT_ID = 'string']`
System Views	`oceanbase.GV$OB_SQL_AUDIT`	`GV$OB_SQL_AUDIT` (same name, different schema access)
Data Types	`BIGINT` , `VARCHAR`	`NUMBER` , `VARCHAR2`
Time Functions	`TIME_TO_USEC()` , `usec_to_time()`	`TO_DATE()` , `TO_CHAR()`
Schema Access	`database.table`	`schema.table`
Limit Syntax	`LIMIT n`	`FETCH FIRST n ROWS ONLY`
String Concatenation	`CONCAT()`	`\|\|` operator

Optimization Principles:

✅ Same: Execution plan operators, join algorithms, partition pruning logic
✅ Same: Index design principles, query rewriting strategies
❌ Different: Syntax, system view access, data type names, function names

Examples in this skill are marked with mode indicators: MySQL Mode: or Oracle Mode:

Core Optimization Principles

1. Understand Execution Plans

Always analyze execution plans before optimization:

MySQL Mode:

sql

obclient [SALES_DB]> EXPLAIN SELECT * FROM order_table WHERE order_date >= '2024-01-01';

Oracle Mode:

sql

obclient [SALES_DB]> EXPLAIN SELECT * FROM order_table WHERE order_date >= DATE '2024-01-01';

查询结果如下：

+------------------------------------------------------------------------------------------------+
| Query Plan                                                                                     |
+------------------------------------------------------------------------------------------------+
| ===========================================                                                    |
| |ID|OPERATOR              |NAME      |EST. ROWS|COST |                                        |
| -------------------------------------------                                                    |
| |0 |TABLE SCAN            |order_table|1000    |1000 |                                        |
| |1 | TABLE GET            |order_table|1000    |1000 |                                        |
+===========================================                                                      |
| Outputs & filters:                                                                             |
| -------------------------------------                                                          |
|   0 - output([order_table.order_id], [order_table.customer_id], [order_table.order_date]),   |
|       filter([order_table.order_date >= '2024-01-01']),                                        |
|       access([order_table.order_id], [order_table.customer_id], [order_table.order_date]),    |
|       partitions(p0)                                                                            |
+------------------------------------------------------------------------------------------------+

Key indicators to check:

EST. ROWS: Estimated rows processed (lower is better)
EST.TIME(us): Estimated execution time in microseconds (lower is better)
OPERATOR: Look for
```
TABLE SCAN
```
(full table scan, bad) vs
```
TABLE GET
```
(index lookup, good)
Partitions: Check if partition pruning is working (e.g.,
```
partitions(p1)
```
means only one partition accessed)
is_index_back:
```
false
```
means no index back,
```
true
```
means index back required

2. Index Design Best Practices

Create indexes on frequently queried columns (syntax is the same for both modes):

sql

-- ✅ GOOD: Index on WHERE clause column
obclient [SALES_DB]> CREATE INDEX idx_order_date ON order_table(order_date);

-- ✅ GOOD: Composite index for multi-column queries (column order matters)
obclient [SALES_DB]> CREATE INDEX idx_customer_date ON order_table(customer_id, order_date);

-- ✅ GOOD: Unique index for unique constraints
obclient [SALES_DB]> CREATE UNIQUE INDEX idx_order_number ON order_table(order_number);

-- ✅ GOOD: Index with STORING clause for covering index
obclient [SALES_DB]> CREATE INDEX idx_customer_date ON order_table(customer_id, order_date) 
                     STORING (total_amount, status);

-- ❌ BAD: Index on rarely queried columns
obclient [SALES_DB]> CREATE INDEX idx_notes ON order_table(notes);

OceanBase Index Types:

UNIQUE Index: Ensures uniqueness, fast lookups
LOCAL Index: Partition-local index (default for partitioned tables)
GLOBAL Index: Cross-partition index
Function Index: Index on expressions (e.g.,
```
CREATE INDEX idx_expr ON t1((c1 + c2))
```
)

Index selection rules:

Index columns used in WHERE clauses
Index columns used in JOIN conditions
Consider composite indexes for multi-column filters (column order matters - most selective first)
Use STORING clause to create covering indexes (avoids index back)
Avoid over-indexing (each index adds write overhead)
For partitioned tables, prefer LOCAL indexes unless cross-partition queries are common

3. Query Writing Best Practices

Use specific column lists instead of SELECT *:

sql

-- ✅ GOOD: Select only needed columns
obclient [SALES_DB]> SELECT order_id, customer_id, total_amount 
                     FROM order_table 
                     WHERE order_date >= '2024-01-01';

-- ❌ BAD: Select all columns
obclient [SALES_DB]> SELECT * FROM order_table WHERE order_date >= '2024-01-01';

Use appropriate WHERE conditions:

sql

-- ✅ GOOD: Use indexed column in WHERE
obclient [SALES_DB]> SELECT * FROM order_table WHERE order_id = 12345;

-- ✅ GOOD: Use range queries on indexed columns
obclient [SALES_DB]> SELECT * FROM order_table 
                     WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- ❌ BAD: Function on indexed column prevents index usage
obclient [SALES_DB]> SELECT * FROM order_table WHERE YEAR(order_date) = 2024;

-- ✅ GOOD: Rewrite to use index
obclient [SALES_DB]> SELECT * FROM order_table 
                     WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

Partition Pruning Optimization

Partition pruning avoids accessing irrelevant partitions, significantly improving SQL execution efficiency. Always check the

partitions

field in the execution plan to verify partition pruning is working.

Hash Partitioning

Ensure partition key is in WHERE clause:

sql

-- ✅ GOOD: Partition key in WHERE clause enables partition pruning
obclient [SALES_DB]> CREATE TABLE order_table (
                     order_id INT,
                     customer_id INT,
                     order_date DATE
                     ) PARTITION BY HASH(order_id) PARTITIONS 5;

obclient [SALES_DB]> SELECT * FROM order_table WHERE order_id = 12345;

查询结果如下：

+------------------------------------------------------------------------------------------------+
| Query Plan                                                                                     |
+------------------------------------------------------------------------------------------------+
| =================================================                                              |
| |ID|OPERATOR  |NAME       |EST. ROWS|EST.TIME(us)|                                            |
| -------------------------------------------------                                              |
| |0 |TABLE SCAN|order_table|990      |383         |                                            |
| =================================================                                              |
| Outputs & filters:                                                                             |
| -------------------------------------                                                          |
|   0 - output([order_table.order_id], [order_table.customer_id], [order_table.order_date]),  |
|       filter(nil),                                                                             |
|       access([order_table.order_id], [order_table.customer_id], [order_table.order_date]),   |
|       partitions(p1)  -- Only one partition accessed!                                         |
+------------------------------------------------------------------------------------------------+

sql

-- ❌ BAD: Missing partition key prevents pruning (scans all partitions)
obclient [SALES_DB]> SELECT * FROM order_table WHERE customer_id = 1001;
-- Check execution plan: partitions(p0, p1, p2, p3, p4) - all partitions scanned

Range Partitioning

Use range conditions that match partition boundaries:

sql

-- ✅ GOOD: Range query matches partition boundaries
obclient [SALES_DB]> CREATE TABLE order_table (
                     order_id INT,
                     customer_id INT,
                     order_date DATE
                     ) PARTITION BY RANGE(order_date) (
                     PARTITION p0 VALUES LESS THAN('2024-01-01'),
                     PARTITION p1 VALUES LESS THAN('2024-02-01'),
                     PARTITION p2 VALUES LESS THAN('2024-03-01')
                     );

obclient [SALES_DB]> SELECT * FROM order_table 
                     WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01';

查询结果如下：

+------------------------------------------------------------------------------------------------+
| Query Plan                                                                                     |
+------------------------------------------------------------------------------------------------+
| =================================================                                              |
| |ID|OPERATOR  |NAME       |EST. ROWS|EST.TIME(us)|                                            |
| -------------------------------------------------                                              |
| |0 |TABLE SCAN|order_table|1        |46          |                                            |
| =================================================                                              |
| Outputs & filters:                                                                             |
| -------------------------------------                                                          |
|   0 - output([order_table.order_id], [order_table.customer_id], [order_table.order_date]),  |
|       filter([order_table.order_date >= '2024-01-01'], [order_table.order_date < '2024-02-01']),
|       access([order_table.order_id], [order_table.customer_id], [order_table.order_date]),   |
|       partitions(p1)  -- Only partition p1 accessed!                                        |
+------------------------------------------------------------------------------------------------+

sql

-- ❌ BAD: Function prevents partition pruning
obclient [SALES_DB]> SELECT * FROM order_table WHERE MONTH(order_date) = 1;
-- Check execution plan: partitions(p0, p1, p2) - all partitions scanned

-- ✅ GOOD: Rewrite to use partition pruning
obclient [SALES_DB]> SELECT * FROM order_table 
                     WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01';

Partition Pruning Rules:

For Hash/List partitions: Partition key must be in WHERE clause with equality or IN conditions
For Range partitions: Use range conditions that match partition boundaries
Avoid functions on partition keys in WHERE clauses
For expressions as partition keys, the expression must appear as a whole in WHERE clause

Join Optimization

OceanBase supports three join algorithms: Nested Loop Join (NLJ), Hash Join (HJ), and Merge Join (MJ). The optimizer automatically selects the best algorithm based on statistics and cost estimation.

Join Algorithms

1. Nested Loop Join (NLJ)

Best for: Small outer table, indexed inner table
Use when: Join condition has index on inner table
Hint:
```
/*+ USE_NL(table1, table2) */
```

sql

-- ✅ GOOD: NLJ with indexed join column
obclient [SALES_DB]> CREATE INDEX idx_customer_id ON order_table(customer_id);

obclient [SALES_DB]> EXPLAIN SELECT /*+USE_NL(c, o)*/ o.order_id, c.customer_name 
                     FROM customer_table c
                     INNER JOIN order_table o ON c.customer_id = o.customer_id
                     WHERE c.customer_type = 'VIP';

查询结果如下：

+------------------------------------------------------------------------------------------------+
| Query Plan                                                                                     |
+------------------------------------------------------------------------------------------------+
| ===========================================                                                    |
| |ID|OPERATOR        |NAME  |EST. ROWS|EST.TIME(us)|                                           |
| -------------------------------------------                                                    |
| |0 |NESTED-LOOP JOIN|      |990      |37346       |                                           |
| |1 | TABLE SCAN     |c     |999      |669         |                                           |
| |2 | TABLE GET      |o     |1        |36          |  -- Index lookup!                         |
| ===========================================                                                    |
| Outputs & filters:                                                                             |
| -------------------------------------                                                          |
|   0 - output([o.order_id], [c.customer_name]), filter(nil),                                   |
|       conds(nil), nl_params_([c.customer_id])                                                 |
|   1 - output([c.customer_id], [c.customer_name]), filter([c.customer_type = 'VIP']),         |
|       access([c.customer_id], [c.customer_name]), partitions(p0)                             |
|   2 - output([o.order_id]), filter(nil),                                                      |
|       access([o.order_id]), partitions(p0),                                                    |
|       range_key([o.customer_id]), range([? = o.customer_id])  -- Index used!                  |
+------------------------------------------------------------------------------------------------+

2. Hash Join (HJ)

Best for: Large datasets, equal join conditions
Use when: Both tables are large and join condition is equality
Hint:
```
/*+ USE_HASH(table1, table2) */
```

sql

-- ✅ GOOD: Hash Join for large table joins
obclient [SALES_DB]> EXPLAIN SELECT /*+USE_HASH(o, c)*/ o.order_id, c.customer_name 
                     FROM order_table o
                     INNER JOIN customer_table c ON o.customer_id = c.customer_id;

查询结果如下：

+------------------------------------------------------------------------------------------------+
| Query Plan                                                                                     |
+------------------------------------------------------------------------------------------------+
| =======================================                                                        |
| |ID|OPERATOR   |NAME|EST. ROWS|EST.TIME(us)|                                                   |
| ---------------------------------------                                                        |
| |0 |HASH JOIN  |    |98010000 |66774608    |                                                   |
| |1 | TABLE SCAN|o   |100000   |68478       |                                                   |
| |2 | TABLE SCAN|c   |100000   |68478       |                                                   |
| =======================================                                                        |
| Outputs & filters:                                                                             |
| -------------------------------------                                                          |
|   0 - output([o.order_id], [c.customer_name]), filter(nil),                                    |
|       equal_conds([o.customer_id = c.customer_id]), other_conds(nil)                           |
+------------------------------------------------------------------------------------------------+

3. Merge Join (MJ)

Best for: Pre-sorted data, ordered results needed
Use when: Both inputs are already sorted or can be sorted efficiently
Hint:
```
/*+ USE_MERGE(table1, table2) */
```

sql

-- ✅ GOOD: Merge Join when data is sorted
obclient [SALES_DB]> EXPLAIN SELECT /*+USE_MERGE(o, c)*/ o.order_id, c.customer_name 
                     FROM order_table o
                     INNER JOIN customer_table c ON o.customer_id = c.customer_id
                     ORDER BY o.customer_id;

Join Order

Use LEADING hint to control join order:

sql

-- ✅ GOOD: Control join order explicitly
obclient [SALES_DB]> SELECT /*+LEADING(c, o)*/ o.order_id, c.customer_name 
                     FROM customer_table c
                     INNER JOIN order_table o ON c.customer_id = o.customer_id
                     WHERE c.customer_type = 'VIP'
                     AND o.order_date >= '2024-01-01';

-- ❌ BAD: Let optimizer choose suboptimal order
obclient [SALES_DB]> SELECT o.order_id, c.customer_name 
                     FROM order_table o
                     INNER JOIN customer_table c ON o.customer_id = c.customer_id
                     WHERE c.customer_type = 'VIP';

Index on Join Columns

Always index foreign key columns:

sql

-- ✅ GOOD: Index on join column enables efficient joins
obclient [SALES_DB]> CREATE INDEX idx_customer_id ON order_table(customer_id);

-- Then join queries can use index (NLJ with TABLE GET)
obclient [SALES_DB]> SELECT o.*, c.customer_name 
                     FROM order_table o
                     INNER JOIN customer_table c ON o.customer_id = c.customer_id;

Aggregation Optimization

Use Appropriate Aggregation Functions

sql

-- ✅ GOOD: COUNT(*) is optimized
obclient [SALES_DB]> SELECT COUNT(*) FROM order_table WHERE order_date >= '2024-01-01';

-- ✅ GOOD: COUNT(column) when you need non-NULL count
obclient [SALES_DB]> SELECT COUNT(customer_id) FROM order_table;

-- ❌ BAD: COUNT(DISTINCT) on large datasets can be slow
obclient [SALES_DB]> SELECT COUNT(DISTINCT customer_id) FROM order_table;

-- ✅ GOOD: Use GROUP BY with LIMIT (MySQL) or FETCH FIRST (Oracle) for top N
-- MySQL Mode:
obclient [SALES_DB]> SELECT customer_id, SUM(total_amount) AS total
                     FROM order_table
                     GROUP BY customer_id
                     ORDER BY total DESC
                     LIMIT 10;

-- Oracle Mode:
obclient [SALES_DB]> SELECT customer_id, SUM(total_amount) AS total
                     FROM order_table
                     GROUP BY customer_id
                     ORDER BY total DESC
                     FETCH FIRST 10 ROWS ONLY;

Subquery Optimization

Convert Correlated Subqueries to JOINs

sql

-- ❌ BAD: Correlated subquery (executes for each row)
obclient [SALES_DB]> SELECT o.order_id, o.total_amount
                     FROM order_table o
                     WHERE o.total_amount > (
                         SELECT AVG(total_amount) 
                         FROM order_table 
                         WHERE customer_id = o.customer_id
                     );

-- ✅ GOOD: Convert to JOIN
obclient [SALES_DB]> SELECT o.order_id, o.total_amount
                     FROM order_table o
                     INNER JOIN (
                         SELECT customer_id, AVG(total_amount) as avg_amount
                         FROM order_table
                         GROUP BY customer_id
                     ) avg_orders ON o.customer_id = avg_orders.customer_id
                     WHERE o.total_amount > avg_orders.avg_amount;

Use EXISTS instead of IN for large datasets

Syntax is the same for both modes:

sql

-- ✅ GOOD: EXISTS stops at first match
obclient [SALES_DB]> SELECT * FROM order_table o
                     WHERE EXISTS (
                         SELECT 1 FROM customer_table c
                         WHERE c.customer_id = o.customer_id
                         AND c.customer_type = 'VIP'
                     );

-- ⚠️ OK: IN works but may be slower for large lists
obclient [SALES_DB]> SELECT * FROM order_table
                     WHERE customer_id IN (SELECT customer_id FROM customer_table WHERE customer_type = 'VIP');

Slow Query Tuning

Enable SQL Audit

Configure SQL Audit settings:

sql

-- Enable SQL Audit
obclient> ALTER SYSTEM SET enable_sql_audit = true;

-- Set SQL Audit memory percentage (default: 3%, range: [0,80])
obclient> SET GLOBAL ob_sql_audit_percentage = 3;

Identify Slow Queries

Query TOP SQL by CPU usage:

MySQL Mode: Use

oceanbase.GV$OB_SQL_AUDIT

with

TIME_TO_USEC(NOW())

Oracle Mode: Use

GV$OB_SQL_AUDIT

with

SYSDATE - INTERVAL '30' MINUTE

sql

-- Find TOP SQL by CPU usage in last 30 minutes
obclient> SELECT sql_id, COUNT(*) AS executions, SUM(execute_time) AS tot_cpu_time,
                AVG(execute_time) AS avg_cpu_time,
                SUM(execute_time)/(30*60*1000*1000) AS cpu_cnt, query_sql
         FROM oceanbase.GV$OB_SQL_AUDIT  -- MySQL: add 'oceanbase.' prefix
         WHERE tenant_id = 'mysql001'  -- MySQL: string, Oracle: number
           AND request_time BETWEEN (TIME_TO_USEC(NOW())-30*60*1000*1000) AND TIME_TO_USEC(NOW())  -- MySQL
           -- Oracle: BETWEEN (SYSDATE - INTERVAL '30' MINUTE) AND SYSDATE
           AND is_executor_rpc = 0
         GROUP BY sql_id
         HAVING COUNT(*) > 1
         ORDER BY cpu_cnt DESC
         LIMIT 10;  -- MySQL: LIMIT, Oracle: FETCH FIRST 10 ROWS ONLY

Query slow queries by execution time:

MySQL Mode: Use

usec_to_time()

function
Oracle Mode: Use

TO_CHAR()

function

sql

-- Find queries with execution time > 100ms
obclient> SELECT request_id,
                usec_to_time(request_time) AS request_time,  -- MySQL
                -- TO_CHAR(request_time, 'YYYY-MM-DD HH24:MI:SS.FF') AS request_time,  -- Oracle
                elapsed_time, queue_time, execute_time, flt_trace_id, query_sql
         FROM oceanbase.v$ob_sql_audit  -- MySQL: add 'oceanbase.' prefix
         WHERE elapsed_time > 100000  -- > 100ms in microseconds
         ORDER BY elapsed_time DESC
         LIMIT 10;  -- MySQL: LIMIT, Oracle: FETCH FIRST 10 ROWS ONLY

Analyze Query Performance

Get detailed execution statistics:

MySQL Mode: Use

oceanbase.gv$ob_sql_audit

Oracle Mode: Use

GV$OB_SQL_AUDIT

(no schema prefix)

sql

-- Get detailed statistics for a specific SQL
obclient> SELECT sql_id, query_sql, executions, elapsed_time, execute_time,
                queue_time, return_rows, affected_rows, partition_cnt,
                table_scan, is_hit_plan, plan_id
         FROM oceanbase.gv$ob_sql_audit  -- MySQL: add 'oceanbase.' prefix
         WHERE sql_id = 'your_sql_id'
         ORDER BY elapsed_time DESC;

Get execution plan for slow query:

MySQL Mode: Use

oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT

Oracle Mode: Use

GV$OB_PLAN_CACHE_PLAN_STAT

(no schema prefix)

sql

-- Get execution plan from plan cache
obclient> SELECT tenant_id, svr_ip, svr_port, sql_id, plan_id,
                last_active_time, first_load_time, outline_data
         FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT  -- MySQL: add 'oceanbase.' prefix
         WHERE tenant_id = 1002 AND sql_id = 'your_sql_id'
           AND svr_ip = 'xxx.xxx.xxx.xxx' AND svr_port = 35046;

-- Get plan details
obclient> SELECT operator, name, rows, cost
         FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_EXPLAIN  -- MySQL: add 'oceanbase.' prefix
         WHERE tenant_id = 1002 AND plan_id = 741
           AND svr_ip = 'xxx.xxx.xxx.xxx' AND svr_port = 35046;

Find Currently Running Slow Queries

MySQL Mode:

sql

obclient> SELECT user, tenant, sql_id, concat(time, 's') AS time, info,
                svr_ip, svr_port, trace_id
         FROM oceanbase.GV$OB_PROCESSLIST
         WHERE state = 'ACTIVE' ORDER BY time DESC LIMIT 10;

Oracle Mode:

sql

obclient> SELECT user_name, tenant_name, sql_id, time || 's' AS time, info,
                svr_ip, svr_port, trace_id
         FROM GV$OB_PROCESSLIST
         WHERE state = 'ACTIVE' ORDER BY time DESC FETCH FIRST 10 ROWS ONLY;

Real-World Optimization Case

Case: Type Conversion Preventing Index Usage

Problem: SQL execution time was 2 seconds, CPU usage exceeded 70% of server resources.

Root Cause Analysis:

Identify TOP SQL:

sql

obclient> SELECT sql_id, COUNT(*) AS executions, AVG(execute_time) AS avg_cpu_time
         FROM oceanbase.GV$OB_SQL_AUDIT
         WHERE tenant_id = 'mysql001'
           AND request_time BETWEEN (TIME_TO_USEC(NOW())-30*60*1000*1000) AND TIME_TO_USEC(NOW())
         GROUP BY sql_id
         ORDER BY SUM(execute_time) DESC
         LIMIT 1;

Analyze Execution Plan:

sql

obclient> EXPLAIN SELECT ... FROM v_tt01 WHERE COL001 IN (20017476);

Execution plan showed:

High cost on
```
TBL3(IDX_TBL3_COL170)
```
index scan
```
range(MIN,MIN ; MAX,MAX)always true
```
- index not used for matching

filter([20017476 = cast(cast(TBL3.COL170, VARCHAR2(20 BYTE)), NUMBER)])

- type conversion happening

Root Cause:
- View
```
V_TT01
```
  had
```
TO_NUMBER
```
  conversion on
```
COL001
```
- Table
```
TBL3.COL170
```
  was
```
VARCHAR2(20)
```
  but compared with
```
NUMBER
```
- Type conversion prevented index usage

Optimization Steps:

Remove unnecessary TO_NUMBER conversion:

sql

-- Before: View had TO_NUMBER conversion
CREATE VIEW V_TT01_OLD AS
SELECT To_number("tt01"."col001") AS "COL001", ...

-- After: Remove TO_NUMBER (COL001 is already NUMBER)
CREATE VIEW V_TT01 AS
SELECT "tt01"."col001" AS "COL001", ...

Result: SQL RT reduced from 2s to 150ms

Unify data types:

sql

-- Before: TBL3.COL170 was VARCHAR2(20)
CREATE TABLE TBL3 (
  COL170 VARCHAR2(20) NOT NULL,
  ...
);

-- After: Change to NUMBER(20) to match join condition
ALTER TABLE TBL3 MODIFY COL170 NUMBER(20) NOT NULL;

Result: SQL RT further reduced to 20ms

Partition and table group optimization:

sql

-- Add hash partitioning on join keys
ALTER TABLE TBL1 PARTITION BY HASH(COL001) PARTITIONS 8;
ALTER TABLE TBL2 PARTITION BY HASH(COL004) PARTITIONS 8;
ALTER TABLE TBL3 PARTITION BY HASH(COL170) PARTITIONS 8;
ALTER TABLE TT01 PARTITION BY HASH(COL001) PARTITIONS 8;

-- Create table group to avoid cross-partition joins
CREATE TABLEGROUP order_tg PARTITION BY HASH(COL001) PARTITIONS 8;
ALTER TABLE TBL1 SET TABLEGROUP order_tg;
ALTER TABLE TBL2 SET TABLEGROUP order_tg;
ALTER TABLE TBL3 SET TABLEGROUP order_tg;
ALTER TABLE TT01 SET TABLEGROUP order_tg;

Result: SQL RT further reduced to 4ms

Final Execution Plan: Shows

PX PARTITION ITERATOR

(partition pruning working) and

TABLE SCAN

with index

INDEX_TBL3_COL170

(index used efficiently)

Key Learnings:

Always check execution plan for type conversions in filters
Ensure join column data types match
Use partition pruning and table groups for distributed queries
Remove unnecessary type conversions in views

Common Anti-patterns

❌ Avoid These Patterns

1. N+1 Query Problem:

sql

-- ❌ BAD: Multiple queries in loop
-- Instead, use JOIN or batch queries

2. SELECT * in Production:

sql

-- ❌ BAD: SELECT * returns unnecessary data
SELECT * FROM order_table;

-- ✅ GOOD: Select only needed columns
SELECT order_id, customer_id, total_amount FROM order_table;

3. Functions on Indexed Columns:

sql

-- ❌ BAD: Prevents index usage
WHERE UPPER(customer_name) = 'JOHN'

-- ✅ GOOD: Store normalized data or use function-based index
WHERE customer_name = 'JOHN'

4. Implicit Type Conversions:

sql

-- ❌ BAD: String to number conversion
WHERE order_id = '12345'

-- ✅ GOOD: Match data types
WHERE order_id = 12345

Performance Monitoring

Key Metrics to Monitor

sql

-- Check table statistics
obclient [SALES_DB]> ANALYZE TABLE order_table;

-- View table size and row count
obclient [SALES_DB]> SELECT 
                         table_name,
                         table_rows,
                         data_length,
                         index_length
                     FROM information_schema.tables
                     WHERE table_schema = 'SALES_DB';

-- Check index usage
obclient [SALES_DB]> SHOW INDEX FROM order_table;

Optimization Checklist

Before deploying SQL queries to production:

Execution plan reviewed (EXPLAIN)
Appropriate indexes created
Partition pruning enabled (if partitioned)
SELECT columns limited to needed fields
WHERE conditions use indexed columns
JOIN conditions indexed
Subqueries optimized (JOINs preferred)
Aggregations use efficient functions
No functions on indexed columns in WHERE
Data types match in comparisons
Query tested with production-like data volume

Quick Reference

Execution Plan Operators

Operator	Meaning	Optimization
TABLE SCAN / TABLE FULL SCAN	Full table scan	Add index or use partition key
TABLE RANGE SCAN	Range scan on table	✅ Good, but consider index
TABLE GET	Direct row access via primary key	✅ Best
HASH JOIN	Hash join algorithm	✅ Good for large equal joins
NESTED-LOOP JOIN	Nested loop join	✅ Good for small outer table with indexed inner
MERGE JOIN	Merge join algorithm	✅ Good for pre-sorted data
SORT	Sorting operation	Add ORDER BY index or use sorted index
AGGREGATE	Aggregation	Consider materialized views
PX PARTITION ITERATOR	Parallel partition iterator	✅ Good, partition pruning working
EXCHANGE OUT DISTR	Distributed exchange	Indicates distributed execution

Index Types

Primary Key: Automatically indexed, unique
Unique Index: Ensures uniqueness, fast lookups
Composite Index: Multiple columns, order matters
Covering Index: Contains all query columns

oceanbase-sql-optimization

NPX Install

Tags

SKILL.md Content

OceanBase SQL Optimization Skill

Mode-Specific Syntax Guide

Core Optimization Principles

1. Understand Execution Plans

2. Index Design Best Practices

3. Query Writing Best Practices

Partition Pruning Optimization

Hash Partitioning

Range Partitioning

Join Optimization

Join Algorithms

Join Order

Index on Join Columns

Aggregation Optimization

Use Appropriate Aggregation Functions

Subquery Optimization

Convert Correlated Subqueries to JOINs

Use EXISTS instead of IN for large datasets

Slow Query Tuning

Enable SQL Audit

Identify Slow Queries

Analyze Query Performance

Find Currently Running Slow Queries

Real-World Optimization Case

Case: Type Conversion Preventing Index Usage

Common Anti-patterns

❌ Avoid These Patterns

Performance Monitoring

Key Metrics to Monitor

Optimization Checklist

Quick Reference

Execution Plan Operators

Index Types