写Oracle SQL的时间长了你会发现一个规律很多莫名其妙的数据翻倍、结果集为空、报表对不上数最后追到根上往往不是多表关联写错了而是OR这个看起来最简单的操作符出了问题。比如某开发者在报表里想筛“flag为1或者flag为2并且type是A”的数据随手写了一句WHERE flag1 OR flag2 AND typeA表面读起来一点毛病都没有结果跑出来却带着一堆type完全不是A的记录。这不是OR本身不好用而是Oracle的运算符优先级在三值逻辑下藏着不少容易踩的坑。这篇文章不是OR的语法说明书而是把OR在实际SQL里最容易出问题的几个场景——优先级、NULL、索引、JOIN、UNION ALL改写——串起来讲一遍。适合天天写Oracle SQL的开发、做数据分析和报表的同学也适合被“OR导致索引失效”“OR要拆成UNION ALL”这类结论困扰的DBA。看完之后你再遇到含OR的SQL至少知道该检查哪里、该用什么证据判断性能而不是凭感觉。1. 一个优先级问题和一个NULL问题先给OR打上括号1.1 三值逻辑下OR到底怎么算Oracle的判断结果不是二值逻辑而是三值逻辑真、假、空。空就是NULL参与判断时的结果不是“没有答案”而是“不知道答案”。WHERE子句只保留结果为真的行结果为假或者为空的记录一概不返回。OR在这种体系下的规则其实很简单左侧右侧OR结果TRUETRUETRUETRUEFALSETRUETRUENULLTRUEFALSEFALSEFALSEFALSENULLNULLNULLNULLNULL注意那个“TRUE OR NULL TRUE”。换句话说只要OR两边有一个条件明确满足另一个条件即使遇到NULL数据整行也会被保留。这跟AND完全不同AND遇到NULL除非另一边明确为TRUE否则整组条件基本就废了。典型例子是查金额为0或者金额缺失的数据SELECT * FROM t_order WHERE amount 0 OR amount IS NULL;如果只写WHERE amount 0amount为NULL的行会被过滤掉这是大多数人都知道的。但反过来如果把amount IS NULL写进OR里逻辑上就要求先想清楚amount为NULL时第一个条件amount 0结果是NULL第二个条件为TRUEOR下来是TRUE所以行会被选中。这个组合没有写错但你在设计查询时一定要意识到OR自带“容错”能力目标就是要把两类数据都捞出来。1.2 AND比OR优先级高括号不能省Oracle的运算符优先级里比较运算符、IS NULL、LIKE这些最高接下来是NOT再往下是ANDOR的优先级最低。所以WHERE a 1 OR b 1 AND c 1会被解析成WHERE a 1 OR (b 1 AND c 1)而不是很多人脑子里想的WHERE (a 1 OR b 1) AND c 1开头那个报表事故就是这么来的。开发想表达“flag1或者flag2并且typeA”但因为没加括号SQL实际变成了“flag1” 这一整块独立成立后面那一坨只是给flag2的补充条件。结果typeA根本没限制住flag1的记录数据量一下就偏了。我的习惯是只要一条SQL里OR和AND同时出现就给OR整体套上括号哪怕语义上不会产生歧义也套。这样做有两个好处。第一别人review代码时不用再心算一遍优先级第二以后有人在条件前面再加一个AND时不会因为优先级继承问题把整个条件组改坏。这事在CASE WHEN里同样成立CASE WHEN a1 OR b1 AND c1 THEN ...这种写法只要没括号讨论语义都容易吵架。2. 等值条件里OR的等价写法IN、多列IN、NOT IN的陷阱2.1 单列等值OR几乎就是IN如果OR的两边是对同一个列的等值判断比如WHERE status P OR status C我一般会直接改写成WHERE status IN (P, C)这不是为了“看起来简洁”。Oracle优化器通常会把IN识别成INLIST迭代器执行计划里会出现INLIST ITERATOR配合索引扫描时可以把集合里的值逐个拿出来访问索引。OR虽然也可能被改成类似逻辑但IN的可读性和后续维护性明显更好特别是当你要追加第三个、第四个值的时候IN只需要扩列表OR则要一路往下加条件。有一点要记住IN和等值OR都解决不了NULL匹配问题。status IN (P, C)不会因为status是NULL就返回这行因为NULL既不等同于P也不等同于C。如果你确实要把NULL也筛出来必须单独加OR status IS NULL。2.2 多列组合OR用行值IN还有一种常见形态是多个列组合出来的ORWHERE (cust_type A AND channel 01) OR (cust_type B AND channel 02)这种条件如果分支很多整个WHERE会变得又长又难读。Oracle支持行值比较上面这组条件可以写成WHERE (cust_type, channel) IN ((A, 01), (B, 02))执行起来优化器会把它拆回类似OR的计划但SQL本身简洁得多。注意列的顺序、数量、类型必须一一对应NULL依然不会参与相等匹配。如果业务里这两个列确实允许NULL并且NULL本身就是你需要匹配的值那还是要单独再写NULL分支没有捷径。2.3 NOT IN与NULL撞车IN等价于ORNOT IN就等价于对OR结果取反也就是对多个等值条件做AND取反。这条链路一旦遇到NULL会出现一个非常经典的生产事故SELECT * FROM t_customer WHERE id NOT IN (SELECT id FROM t_blacklist);直觉上“不在黑名单里”应该把正常客户都查出来。但子查询返回的黑名单里只要有一条记录的id是NULL整个查询结果就是空。原因很简单NOT IN展开后等价于id 值1 AND id 值2 AND ... AND id NULLid NULL的结果是NULL而整条AND链只要有一个NULL就变成NULLWHERE只保留TRUE所以全部行都被滤掉。要规避这个坑优先改成NOT EXISTSSELECT * FROM t_customer c WHERE NOT EXISTS ( SELECT 1 FROM t_blacklist b WHERE b.id c.id );或者先在子查询里把NULL过滤掉再用NOT IN。这个坑和OR的关系在于当你的直觉把“不等于一组值”翻译成“不是这个也不是那个”时一旦里面混进NULL整个逻辑就静悄悄失效了。3. 执行计划视角OR不是必然不走索引但每个分支都得出得去3.1 “OR一定不走索引”是错误的网上有很多结论说“SQL里出现OR就会导致索引失效”这个说法过于绝对。Oracle不是不能用索引而是要看你OR的每个分支能不能独立访问索引。举个模拟场景。有一张订单表CREATE TABLE t_order ( id NUMBER PRIMARY KEY, status VARCHAR2(10), pay_type VARCHAR2(10), amount NUMBER ); CREATE INDEX idx_order_status ON t_order(status); CREATE INDEX idx_order_pay ON t_order(pay_type);查询条件是SELECT * FROM t_order WHERE status P OR pay_type C;status和pay_type各自有索引两个条件又都能在索引里快速定位。优化器完全可以选择BITMAP OR的方式把两个索引的命中结果做一个按位或合并再回表取数据。简化后的执行计划大概会是这样| Id | Operation | Name | | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID | T_ORDER | | 2 | BITMAP CONVERSION TO ROWIDS | | | 3 | BITMAP OR | | | 4 | BITMAP INDEX SINGLE VALUE | IDX_ORDER_STATUS | | 5 | BITMAP INDEX SINGLE VALUE | IDX_ORDER_PAY |这其实是Oracle把普通的B树索引在内部转换成位图结构做合并并不需要你提前建真正意义上的位图索引。所以遇到OR先别急着下结论“没救”得先看两个分支各自是什么谓词、有没有索引可走。3.2 分支谓词里一旦出现“不干净”的东西计划就会走向全表扫描OR执行计划变差的根本原因是某一个分支“走不出去”。比如下面这种WHERE status P OR UPPER(pay_type) Cpay_type列上套了UPPER函数正常情况下没法直接对pay_type索引做等值匹配。这个时候优化器如果把OR整体当作一个filter来看就很可能会选择全表扫描。又比如WHERE amount 1000 OR status Pamount上的范围条件、status上的等值条件能否一起利用索引要看统计信息、数据分布和优化器对代价的判断。如果两分支选择性都很差全表扫描反而是最理性的选择。此时不是索引“失效”而是索引方案本身不划算。隐式转换也是同一个道理。字符串和数字比较时如果列类型和常量类型不一致Oracle可能对列做隐式函数转换索引一样用不上。写OR条件时尽量保证每个分支的列都是裸列函数、拼接、隐式转换统统外包成基于函数的索引或者先处理好再进SQL。3.3 用DBMS_XPLAN判断OR被拆没拆纸上谈兵没意义遇事不决查计划。Oracle最常用的方式是EXPLAIN PLAN FOR SELECT * FROM t_order WHERE status P OR pay_type C; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);如果你还需要真实运行时的行数和耗时可以这样跑一遍SELECT /* GATHER_PLAN_STATISTICS */ id, status, pay_type FROM t_order WHERE status P OR pay_type C; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT ALLSTATS LAST));重点看Predicate Information这一段。如果OR条件出现在同一行的filter里比如filter(STATUSP OR PAY_TYPEC)说明大概率是先扫表或索引再逐行做OR过滤。如果OR被优化器展开成了多个分支每个分支里看到的是独立的access条件说明发生了OR扩展各分支分别走索引。想验证拆开的效果还可以用优化器hint/* USE_CONCAT */强制做OR展开对照一下执行计划和运行时间。这个hint不能顺手加到生产SQL里但临时测试非常有价值能告诉你在当前数据分布下拆和不拆的差距到底有多大。4. JOIN和CASE中的OR外连接重复行与NULL分支4.1 ON子句OR会让外连接结果变得不可预测WHERE里的OR至少只是过滤问题JOIN的ON条件里写OR麻烦会更大。看这个写法SELECT c.id, e.id FROM t_customer c LEFT JOIN t_order e ON e.customer_id c.id OR e.trace_no c.trace_no;业务想说只要客户的id能对上订单的customer_id或者对得上trace_no就关联出来。这个语义听起来没错但实际执行时关联方式变得像“拿一个客户去和订单表做遍历匹配”订单表里如果有两行分别满足了两个OR分支客户就会被关联出两行来。原本你可能只想要“这个客户匹配到的所有订单”但结果里同一笔订单如果同时满足两个条件也会因为两条路径都命中而出现重复。最要命的是外连接中ON子句带OR时Oracle对未匹配行的保留逻辑很容易和直觉冲突一不留神就把本该保留的NULL行丢掉了。我在项目里基本有一条铁律ON子句里不要写OR。遇到这种多键关联需求先停下来重新梳理业务看看能不能把关联拆成独立的字段键必要时用UNION ALL分两段查。4.2 CASE WHEN中的OR同样受三值逻辑支配CASE WHEN里的条件判断和WHERE遵循同一套三值逻辑SELECT id, CASE WHEN status P OR pay_type C THEN 命中 ELSE 未命中 END AS flag FROM t_order;这里OR的优先级问题依然存在。如果条件里既有AND又有OR不写括号就会得到和你预期完全不同的结果CASE WHEN type A OR status P AND channel X ...会被理解成CASE WHEN type A OR (status P AND channel X) ...如果业务本来想要的是(type A OR status P) AND channel X那结果自然不对。还有一个隐藏点WHEN ... OR NULL这个写法永远不成立。因为任何表达式OR上NULL只有另一端为TRUE时结果才是TRUE如果另一端不是TRUE整个结果就是NULLCASE会继续往下走不会进入这个分支。所以不要用“OR NULL”来占位该判断IS NULL就写IS NULL。4.3 实在要在JOIN里用OR时怎么兜底如果业务实在绕不开多键关联我的做法是拆成两条JOIN再按业务需要合并。比如SELECT c.id, e.id FROM t_customer c LEFT JOIN t_order e ON e.customer_id c.id UNION ALL SELECT c.id, e.id FROM t_customer c LEFT JOIN t_order e ON e.trace_no c.trace_no;拆完之后必须验证重复行。因为同一个客户可能通过customer_id匹配到订单A又通过trace_no匹配到订单A本身这样UNION ALL会把这笔订单输出两次。要不要去重、怎么去重得由业务语义说了算。如果容忍重复那就用UNION ALL如果结果集必须唯一需要用ROW_NUMBER或者业务上能保证互斥的分支条件。这里没有一把梭的万能写法最怕的是直接照搬别人改过的JOIN SQL而不验证行数。5. 手工拆OR成UNION ALL的正确姿势先保证分支互斥5.1 直接两个UNION ALL重复行会翻倍优化器自己做的OR展开和你在SQL里手动把WHERE拆成两段UNION ALL并不完全是同一件事。看这个原始条件SELECT * FROM t_order WHERE status P OR pay_type C;假设有一笔订单同时满足statusP而且pay_typeC原始SQL只会返回这一行。如果你图省事写成SELECT * FROM t_order WHERE status P UNION ALL SELECT * FROM t_order WHERE pay_type C;这笔订单会出现两次。这就是我常说的“拆OR必须先做互斥分支”的原因。打个比方规则是“门牌101或102都可以进入”有间房恰好两个门牌都挂你不会希望在访客名单上登记两遍。Oracle自己做OR展开时很多时候会在分支里偷偷加上LNNVL这类互斥条件避免两个分支重复取到同一行。手动拆的时候这个互斥条件也得自己补上否则结果集就是错的。5.2 用LNNVL构造互斥分支Oracle提供了LNNVL函数意思是“判断条件不为真”。它和普通的取反不一样LNNVL不但会在条件结果为FALSE时返回TRUE在条件结果为NULL时也返回TRUE。刚好适合用来构造OR分支的互斥。假设原始条件是WHERE a 1 OR b 1手工拆成UNION ALL时可以这样写SELECT * FROM t WHERE a 1 AND LNNVL(b 1) UNION ALL SELECT * FROM t WHERE b 1;第一分支负责“a1并且b不是1”第二分支负责“b1”。这样当一行同时满足a1和b1时第一分支的LNNVL(b1)为FALSE把它拦下来只有第二分支输出它行数和原始SQL保持一致。这个写法在a、b存在NULL时也基本贴近原始语义因为LNNVL会把NULL当作“不为真”处理正好和OR的TRUE OR NULL逻辑对齐。代价是LNNVL不是一个特别适合走索引的写法它通常会变成过滤条件。所以这个方案是用来保住正确性的不一定能保住性能。想同时保住性能就要回到第3章说的先看两个分支各自能不能独立走索引。5.3 不值得拆OR的情况不是所有OR都要拆。我见过有人为了“优化”一个只有几万行、查询毫秒级的表硬把OR拆成三个UNION ALL分支结果SQL长了、代码难看了、计划也没快多少。拆OR的价值只在一个前提下成立每个分支都能借助索引或分区裁剪显著减少数据量而且合并这些结果的开销小于全表扫描的开销。如果两个OR条件都是低选择性字段比如statusP命中40%数据pay_typeC又命中30%拆成两个索引扫描再回表合并代价很可能比一次全表扫描还要高。此时优化器选择全表扫描不是错误而是它在告诉你这条路比拆开走更便宜。不要为了“看起来应该走索引”而强行拆SQL。我在测试时通常先加/* USE_CONCAT */hint让优化器试跑一次OR展开对比原始计划的A-Time和逻辑读然后再决定要不要手工改写。这个习惯帮我避免了很多“优化了个寂寞”的情况。6. 我排查OR相关SQL时固定按这个顺序检查6.1 一张可以贴在工位上的检查表这些年被OR坑过太多次我现在遇到任何含OR的SQL都按固定顺序过一遍检查项常见现象建议括号OR和AND混写没加括号给OR整体套括号优先级开发以为是A或B且C实际是A或(B且C)语义确认后改写条件组NULL用等于条件匹配NULL列补IS NULL分支NOT IN子查询里有NULL导致结果集全空改NOT EXISTSJOIN ON外连接OR导致重复行或丢NULL行拆连接验证行数执行计划一个大filter挂着整串ORDBMS_XPLAN看是否OR扩展手工改写直接两个UNION ALL重复行翻倍用LNNVL做互斥分支这七项检查完90%的OR问题都能定位到根因。剩下的10%基本上是数据分布突然变化、统计信息过期、或者隐含参数影响了优化器对OR扩展的判断这时候回到执行计划去对比代价即可。6.2 回归验证改了SQL不等于换了对结果OR相关的改动最忌讳只跑一条数据就交付。同一套查询条件在数据分布不同的环境里计划可能完全不一样。我的做法是改完SQL后至少拿三组数据验证——不命中任何分支的记录、命中一个分支的记录、命中多个分支的记录分别统计行数和原始SQL的结果做对比。如果结果集有差异先不要讨论性能先把SQL逻辑改到完全一致再谈优化。另外执行计划里出现OR展开时不要只看有没有索引还要关注每行估算的Rows和实际A-Rows差多少。估算差得远说明统计信息不准即便走了索引也未必快。这种情况下先刷新统计信息再重新看计划往往比反复调SQL更有效。最后说一个体会OR本身不难写难的是每次写之前都默认Oracle有一套独立的优先级和三值逻辑而不是按自然语言的“或者”去理解。很多线上事故从根上看都是把一个表达式当作“人话”翻译成了SQL却没有回看SQL翻译完到底在说什么。保持对OR条件的那种“不轻信”态度比记住再多技巧都管用。