Access paths determine how the database retrieves data from a table—typically via primary key scans or secondary indexes. The efficiency of a query hinges on selecting the optimal path, as full table scans scale linearly with data volume. When an index exists, it can drastically reduce I/O by limiting the scanned rows. However, if the optimizer ignores a usable index, investigate whether the index is absent, or if its scan range is too broad, leading to higher estimated costs.

• Available Paths: Primary key, secondary index
• Selection Criteria: Rule-based heuristics (forward matching and pruning) and cost-based evaluation
• Key Factors: Scan range size, need for row lookup (table access), index width, filter selectivity, and interesting order

Index Lookups (Table Access)

When a index does not contain all required columns, the database must perform a row lookup to the primary table—a process called "index back." OceanBase uses B+ tree indexes exclusively.

• GET: Used when all index key components are equality conditions (e.g., WHERE a = 1 AND b = 2)
• SCAN: Used for range queries returning ordered results
• Wildcard Limitations: Prefix matches like 'T%' can use the index; '%T' or '%T%' cannot
• Ordering: The optimizer automatically determines scan direction based on query requirements

Covering Indexes

A covering index includes all columns referenced in the query, eliminating the need for a table lookup.

CREATE TABLE t2 (
  c1 INT PRIMARY KEY,
  c2 INT,
  c3 INT,
  c4 INT,
  INDEX t2_c2(c2)
);

-- Query only c2: uses covering index
EXPLAIN SELECT c2 FROM t2;

-- Query c1 and c2: still uses t2_c2 because c1 (PK) is included in the index entry
EXPLAIN SELECT c1, c2 FROM t2;

In OceanBase, secondary index entries implicitly include the primary key columns. Thus, even when the query selects the primary key, the index can still serve as a covering index if all other requested columns are present.

Interesting Order

The optimizer leverages the natural ordering of index scans to avoid explicit sorting. If the query’s ORDER BY clause matches an index’s sort order, the sort operation is eliminated.

CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);

EXPLAIN SELECT * FROM t1 ORDER BY c1;
-- Output: TABLE SCAN without SORT operator — order preserved by PK index

Reverse Index Scans

For descending sorts, OceanBase performs reverse index scans when the index supports it. This avoids materializing and sorting results.

EXPLAIN SELECT * FROM t1 ORDER BY c1 DESC;
-- Output: TABLE SCAN (Reverse) — scan proceeds backward through index

Index Selection Heuristics

OceanBase applies rule-based filters before cost estimation:

• Forward Rules: Immediate selection if a unique index matches the query exactly
• Skyline Pruning: Compares candidate indexes and eliminates inferior ones based on:
  • Query range coverage
  • Need for table lookup
  • Sort order alignment

CREATE TABLE t1(
  a INT,
  b INT,
  c INT,
  UNIQUE KEY idx1(a, b),
  KEY idx2(b)
);

-- Query: a=1 AND b=1 → unique index hit
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1 AND b = 1;
-- optimization_method=rule_based, heuristic_rule=unique_index_with_indexback

-- Query: a=1 ORDER BY b → idx2 pruned, idx1 retained
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1 ORDER BY b;
-- pruned_index_name[idx2], available_index_name[idx1]

Join Order Optimization

Join order significantly impacts performance. OceanBase primarily uses left-deep trees due to their lower memory footprint and pipeline efficiency.

• Advantages: Smaller search space, efficient pipelining, reduced memory usage
• Limits: Cannot fully exploit parallelism; may miss better plans
• Alternative: Bushy trees offer better parallelism but at high planning cost
• Hint Support: Join order can be forced using hints
• Priority: Explicit join conditions are preferred over Cartesian products

Designing Efficient Indexes

• Index tables are physical structures; updates to the base table trigger synchronous index updates
• Include all frequently queried columns in the index to avoid lookups
• Place equality predicates first in the index key
• Position high-selectivity columns early
• Use index columns in WHERE, JOIN, and ORDER BY clauses
• Avoid indexing expressions or functions—use functional indexes instead
• Balance query performance gains against write overhead and storage cost
• Re-evaluate indexes on frequently updated columns

Creating Indexes

OceanBase supports local and global indexes on partitioned and non-partitioned tables. For unique indexes on partitioned tables, the partitioning key must be part of the unique constraint.

-- MySQL/Oracle mode
CREATE [UNIQUE] INDEX idx_name ON table_name (col1, col2) [LOCAL | GLOBAL];

-- MySQL mode
ALTER TABLE table_name ADD INDEX idx_name (col1, col2);

Equality Queries

For index (A, B, C):

• Matched: WHERE A = ? AND B = ? AND C = ?, WHERE A = ? AND B = ?, WHERE A = ?
• Not Matched: WHERE B = ? AND C = ?, WHERE C = ?

Column order in the WHERE clause does not affect index usage—only the order in the index definition matters.

Range Queries

For index (A, B, C):

• Matched: WHERE A > ? AND B > ? AND C < ?, WHERE A > ? AND B > ?, WHERE A > ?
• Not Matched: WHERE B > ? AND C < ?, WHERE C IN (?, ?)

Once a range condition is encountered (e.g., >, BETWEEN), subsequent columns in the index are not used for filtering.

Combined Equality and Range

For index (A, B, C):

• Matched:
  • WHERE A = ? AND B = ? AND C > ?
  • WHERE A = ? AND B > ? AND C = ?
  • WHERE A = ? AND B > ? AND C > ?
• Not Matched: WHERE B > ? AND C < ?, WHERE C = ?

Performance ranking: WHERE A = ? AND B = ? AND C > ? > WHERE A = ? AND B > ? AND C = ? ≈ WHERE A = ? AND B > ? AND C > ?