Optimizing Nested-Loop Join Performance in OceanBase: Analysis, Rewrites, and Results

1 Problem Description

A Join query that uses the first, second, and fourth columns of a composite primary key is executed with a Nested‑Loop Join. The driver table can only match the first two key columns, resulting in a low estimated cost (cost1) but poor actual performance because the driver table's fan‑out (n) is severely underestimated (estimated 5,000 rows vs. actual 600,000 rows).

Join total cost ≈ (driver table cost + n × cost1)

Since the driver table cost is fixed and n is underestimated, the optimizer selects Nested‑Loop Join, leading to a query that runs for more than 500 seconds.

2 Analysis Process

2.1 Analyze Execution Plan

The SQL statement (execution time >500 s) is shown below:

select count(*) from (
  SELECT JGBM AS QYDJID,
         SEGMENT3 AS FNUMBER,
         PERIOD_NAME AS SSQJ,
         ...
  FROM (
    SELECT ...
    FROM DC_ACCOUNTBALANCE_TEMP A,
         DEF_ACCOUNTCONFIG B,
         DC_ACCOUNT C,
         NVAT_ACCANDTAXIDMAPFORP07 D,
         BI_CHOICEOFUNIT E
    WHERE A.SEGMENT1 = D.ZTJGBM
      AND D.SBDWID = E.SBDWID
      AND B.JGBM = E.DEPTCODE
      AND B.YXQSNY <= (substr(A.PERIOD_NAME,4,6)||substr(A.PERIOD_NAME,1,2))
      AND (substr(A.PERIOD_NAME,4,6)||substr(A.PERIOD_NAME,1,2)) <= B.YXJZNY
      AND C.QYDJID = B.SYZT
      AND C.FNUMBER = A.SEGMENT3
      AND C.ACCOUNTYEAR = substr(A.PERIOD_NAME,4,6)
      AND a.period_name = '10-2023'
  ) SUB
  GROUP BY JGBM, SEGMENT3, PERIOD_NAME
) X
LEFT JOIN DC_ACCOUNTBALANCE A
ON (A.SSQJ = X.SSQJ AND A.QYDJID = X.QYDJID AND A.FNUMBER = X.FNUMBER);

The execution plan (relevant part) shows that the sub‑plan X finishes quickly (≈5 s) and returns 610,000 rows, while the driver table A is scanned with a Nested‑Loop Join, causing 610,000 index lookups. The plan indicates that only the first two primary‑key columns are used (range_key on SSQJ and QYDJID), which explains the poor performance.

X is the result of joining five tables (A, B, C, D, E). Its cost is low; the sub‑plan runs in about 5 seconds.

The Nested‑Loop Join on table A scans the index range for each of the 610,000 rows because only the first two key columns are matched; the estimated rows per lookup are 391, leading to high total cost.

The optimizer’s cost model underestimates the driver table’s fan‑out, causing it to choose Nested‑Loop Join.

=================================================================================
|ID|OPERATOR               |NAME                                 |EST. ROWS|COST   |
---------------------------------------------------------------------------------
|0 |SCALAR GROUP BY        |                                     |1        |3947739|
|1 | NESTED-LOOP OUTER JOIN|                                     |5123     |3947543|
|2 |  SUBPLAN SCAN          |X                                    |5123     |3154937|
|3 |   HASH GROUP BY       |                                     |5123     |3154861|
|4 |    HASH JOIN          |                                     |5123     |3149203|
|5 |     TABLE SCAN        |C                                    |81314    |31453  |
|6 |     HASH JOIN        |                                     |63573    |2940900|
|7 |      HASH JOIN       |                                     |1898     |35447  |
|8 |       TABLE SCAN     |D(IDX_ACCANDTAXIDMAPFORP07_CMB1)    |2011     |778    |
|9 |       HASH JOIN      |                                     |1736     |32462  |
|10|        TABLE SCAN    |E(IDX_BI_CHOICEOFUNIT_CMB1)         |1704     |660    |
|11|        TABLE SCAN    |B                                    |29154    |11277  |
|12|      TABLE SCAN      |A(IDX_DC_ACCOUNTBALANCE_TEMP_TEST)   |639387   |2468263|
|13|  TABLE SCAN          |A                                    |1        |154    |
=================================================================================

2.2 Analyze Table Statistics

The primary‑key of table A consists of four columns (SSQJ, QYDJID, DATAUSE, FNUMBER). The NDV (number of distinct values) for SSQJ and QYDJID are low (85 and 972 respectively), giving an estimated 391 rows per index range (physical_range_rows:391). Since the query executes this lookup 610,000 times, the total cost becomes huge.

Column DATAUSE always equals 1, so adding a predicate A.DATAUSE = 1 would allow the optimizer to use all four primary‑key columns, reducing the per‑lookup rows to 1.

-- Query table statistics
select column_name, num_distinct from all_tab_col_statistics where table_name='DC_ACCOUNTBALANCE';
-- Result
column_name   num_distinct
SSQJ          85
QYDJID        972
DATAUSE       1
FNUMBER       2616

2.3 Rewrite

Method 1: Add AND A.DATAUSE = 1

Adding the filter reduces execution time from 500 seconds to 8 seconds. The new plan shows that table A now scans only one row per lookup.

=================================================================================
|ID|OPERATOR               |NAME                                 |EST. ROWS|COST   |
---------------------------------------------------------------------------------
|0 |SCALAR GROUP BY        |                                     |1        |3214924|
|1 | NESTED-LOOP OUTER JOIN|                                     |5123     |3214729|
|... (remaining operators similar) ...
|13|  TABLE GET            |A                                    |1        |11     |
=================================================================================

Method 2: Add Hint to Force Hash Join

Using the hint /*+ leading(X A) use_hash(A) */ forces a Hash Join. The query runs in about 40 seconds, still slower than the filtered version because the driver table A must be fully scanned.

=============================================================================
|ID|OPERATOR          |NAME                                 |EST. ROWS|COST    |
-----------------------------------------------------------------------------
|0 |SCALAR GROUP BY   |                                     |1        |52828380|
|1 | HASH OUTER JOIN  |                                     |5123     |52828184|
|... (remaining operators) ...
|13|  TABLE SCAN       |A                                    |32310843 |12497986|
=============================================================================

3 Summary

This case illustrates a classic problem where a Join involving many tables leads the optimizer to choose an inefficient plan because the driver table’s fan‑out is severely underestimated. The underestimation is amplified when the Join result itself becomes the driver table. Adding a simple filter that enables the use of the full composite primary key or applying a hash‑join hint can dramatically improve performance, reducing execution time from several minutes to a few seconds.