Optimizing OR Conditions in GBase 8a: Filter and Join Rewrites

When a query with OR causes a dramatic performance drop and the default execution plan is poor, you can manually restructure the SQL to guide the optimizer. This article covers two common scenarios — OR in filter predicates and OR in join conditions — with practical rewrite techniques for a gbase database.

Optimizing OR in Filter Predicates

Single Column, Multiple Values (A=XX OR A=YY)

Transform multiple OR checks into a single equality check. For numeric columns, use the DECODE function:

-- Original
SELECT * FROM t3 WHERE id = 1 OR id = 11;

-- Rewrite with DECODE (maps value 11 to 1)
SELECT * FROM t3 WHERE DECODE(id, 1, 1, 11, 1) = 1;

For character columns, use CASE WHEN:

SELECT * FROM t3 WHERE CASE id WHEN 'XX' THEN 'XX' WHEN 'YY' THEN 'XX' END = 'XX';

Both rewrites return the same result as the original OR:

gbase> select * from t3 where id=1 or id=11;
+------+------+
| id   | id2  |
+------+------+
|    1 |    1 |
|   11 |   11 |
+------+------+

gbase> select * from t3 where decode(id,1,1,11,1)=1;
+------+------+
| id   | id2  |
+------+------+
|    1 |    1 |
|   11 |   11 |
+------+------+

Different Columns, Different Values (A=XX OR B=YY)

Concatenate multiple case expressions into a single string and use LIKE to test for a match:

-- Original
SELECT * FROM t3 WHERE id = 111 OR id2 = 9999;

-- Rewrite
SELECT * FROM t3 
WHERE CONCAT(CASE WHEN id = 111 THEN 111 ELSE '' END,
             CASE WHEN id2 = 9999 THEN 111 ELSE '' END) LIKE '111%';

Example output:

gbase> select * from t3 where id=111 or id2=9999;
+------+------+
| id   | id2  |
+------+------+
|  111 | 1111 |
+------+------+

gbase> select * from t3 where concat(case when id=111 then 111 else '' end,case when id2=9999 then 111 else '' end) like '111%';
+------+------+
| id   | id2  |
+------+------+
|  111 | 1111 |
+------+------+

Optimizing OR in Join Conditions

When a LEFT JOIN contains an OR in its ON clause, you can split it into two separate LEFT JOINs and merge the results with COALESCE. This rewrite works only when the right table matches at most one row per left row (a 1:1 relationship after the join). Otherwise, the duplicate rows produced by the original OR would be lost.

Original:

SELECT b.XX FROM a 
LEFT JOIN b ON (a.id = b.id OR a.name = b.name) AND ...other_conditions

Rewrite:

SELECT COALESCE(b.XX, b2.XX) 
FROM a
LEFT JOIN b ON a.id = b.id AND ...other_conditions
LEFT JOIN b b2 ON a.id <> b2.id AND a.name = b2.name AND ...other_conditions

Complete example with tables t1 and t3:

-- Tables
gbase> select * from t1;
+------+------+
| id   | id2  |
+------+------+
|    1 |   66 |
|    2 |   77 |
+------+------+

gbase> select * from t3;
+------+------+
| id   | id2  |
+------+------+
|    1 |   66 |
|    3 |   77 |
|    9 |   99 |
+------+------+

-- Original OR join
gbase> select * from t1 left join t3 on t1.id=t3.id or t1.id2=t3.id2;
+------+------+------+------+
| id   | id2  | id   | id2  |
+------+------+------+------+
|    1 |   66 |    1 |   66 |
|    2 |   77 |    3 |   77 |
+------+------+------+------+

-- Split join with COALESCE
gbase> select t1.id, t1.id2,
              coalesce(t3.id, t3_2.id) id,
              coalesce(t3.id2, t3_2.id2) id2
       from t1 
       left join t3 on t1.id = t3.id 
       left join t3 t3_2 on t1.id <> t3_2.id and t1.id2 = t3_2.id2;
+------+------+------+------+
| id   | id2  | id   | id2  |
+------+------+------+------+
|    2 |   77 |    3 |   77 |
|    1 |   66 |    1 |   66 |
+------+------+------+------+

Important Caveats

• All rewrites are manual execution‑plan hacks; always verify correctness and performance improvement in a test environment.
• The join split method requires that the right table matches exactly one row for each left row across both conditions; otherwise, the result set will be incorrect (missing or duplicated rows).
• In filter rewrites, DECODE and CASE WHEN work well for small numbers of values; for many values consider IN lists or other set‑based approaches.

By applying these patterns, you can often avoid expensive OR scans in your gbase database, turning a slow query into a fast, index‑friendly one. GBASE's MPP engine handles these deterministic rewrites efficiently, making them a valuable tool for performance tuning in GBase 8a.