---
title: 包含大 OR 的 SQL，硬解析慢，如何优化-OceanBase数据库使用指南
description: 了解OceanBase数据库在实际应用中关于 包含大 OR 的 SQL，硬解析慢，如何优化相关的常见问题和使用技巧，帮助您快速解决 包含大 OR 的 SQL，硬解析慢，如何优化的难题。
---
切换语言

- 简体中文
- English

划线反馈

# 包含大 OR 的 SQL，硬解析慢，如何优化

更新时间：2026-05-15 09:06

适用版本： V4.0.x、V4.1.x、V4.2.x、V4.3.x、V4.4.x 内容类型：TechNote  

在 SQL 查询中，包含多个 OR 条件的场景非常常见，尤其在需要匹配多个离散值时（如模糊搜索、多分类筛选）。OceanBase 当前存在对包含大 OR SQL 硬解析慢的问题，目前可以将大 OR SQL 改写成大 IN 的形式进行优化。 本文通过实际案例 SQL 对比验证大 OR 硬解析耗时高，以及如果将改写成 SQL。

## 详细说明

原始 SQL 中包含多个条件，并用 OR 连接，注意到执行 SQL 的硬解析耗时 8878us。

```sql
obclient [new]> select execute_time,get_plan_time, query_sql from oceanbase.gv$ob_sql_audit where trace_id='YA6240BEE9193-00063603FD7C99D6-0-0';
+--------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| execute_time | get_plan_time | query_sql                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+--------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|          210 |          8878 | select *
from t1
where (
        c1 = 1
        and c2 = 2
    )
    or (
        c1 = 4
        and c2 = 5
    )
    or (
        c1 = 7
        and c2 = 8
    )
    or (
        c1 = 10
        and c2 = 11
    )
    or (
        c1 = 11
        and c2 = 21
    )
    or (
        c1 = 41
        and c2 = 51
    )
    or (
        c1 = 7
        and c2 = 81
    )
    or (
        c1 = 101
        and c2 = 111
    )
        or (
        c1 = 42
        and c2 = 52
    )
    or (
        c1 = 72
        and c2 = 82
    )
    or (
        c1 = 102
        and c2 = 112
    )
    or (
        c1 = 112
        and c2 = 212
    )
    or (
        c1 = 412
        and c2 = 512
    )
    or (
        c1 = 72
        and c2 = 812
    )
    or (
        c1 = 1012
        and c2 = 1112
    )
    and c3 = 7 |
+--------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.206 sec)

```

SQL 形如 `(c1=xx and c2=yy) or (c1=ww and c2=vv) or (c1=aa and c2=bb) ...`，对于这样的 SQL，可以改写成 `(c1,c2) in ((xx,yy),(ww,vv),(aa,bb)) ...` 改写成 IN 之后，会大大降低 SQL 硬解析的时间， 此外将 OR 转成 IN 之后，还会生成诸如 IN 转 VALUES 的大 IN SQL优化，提高执行效率。

对于上面的 SQL 可以进行如下的转换，转换后硬解析时间降低 2609us。

```sql
obclient [new]> select execute_time,get_plan_time, query_sql from oceanbase.gv$ob_sql_audit where trace_id='YA6240BEE9193-00063603FD7C99D9-0-0';
+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| execute_time | get_plan_time | query_sql                                                                                                                                                                  |
+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|          285 |          2609 | select * from t1 where (c1,c2) in((1,2),(4,5),(7,8),(10,11),(11,21),(41,51),(71,81),(101,111),(42,52),(72,82),(102,112),(112,212),(412,52),(712,812),(1012,1112)) and c3=7 |
+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.203 sec)

```

## 适用版本

OceanBase 数据库 V4.x 版本。

Previous

[gv$plan_cache_plan_stat 中命中次数大于执行次数原因](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000234434)

Next

[使用 ADAPTIVE JOIN 导致结果少行](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002762602) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线