记录一次OceanBase中ORA01476: divisor is equal to zero的报错验证

背景：

数据库是3.2.x某版本。业务反馈有条sql报错ORA-01476: divisor is equal to zero。

sql语句案例类似如下sql。

select   count(b)  from  zry where  b in ('cs1','cs2') and  a/c=0.5  ;

但是验证b in ('cs1','cs2')条件下c并没有0或者null的值。

直接抛出原因，"where后过滤条件不能保证执行的顺序 where 条件1 and 条件2  执行顺序 不能保证是 先条件1 后条件2，而是有可能先去执行条件2"。跟山文老师求证了下，"这个顺序看的就是按照过滤性来排序。优化器估算的过滤性好的，放前面"，"最新 4x 又改了一下，会额外引入一些系数综合考虑”譬如，有可能异常报错的，再额外加一些代价系数，让他的执行更靠后一些",''或者，带了  UDF 之类的计算代价比较高的表达式，也会额外加一些代价系数，让他的计算靠后"。

我在测试环境下复现下该问题，更直观的看下这个现象。

复现过程：

创建测试表和插入测试数据，该数据b有十个值，cs1-6中c都是没有空值和0的。

obclient [SYS]> create table  zry(a int,b varchar2(20),c int);
Query OK, 0 rows affected (0.968 sec)

obclient [SYS]>declare jid number :=0; begin loop       jid := jid+1;     insert into zry select  round(dbms_random.value(0,10),0),'cs10',round(dbms_random.value(0,10),0) from dual;    if  jid >10000 then    exit;    end if;   end loop; commit;   end;/
Query OK, 1 row affected (2.621 sec)

obclient [SYS]> declare jid number :=0; begin loop       jid := jid+1;     insert into zry select  round(dbms_random.value(0,10),0),'cs9',round(dbms_random.value(0,10),0) from dual;    if  jid >10000 then    exit;    end if;   end loop; commit;   end;/
Query OK, 1 row affected (3.090 sec)

obclient [SYS]> declare jid number :=0; begin loop       jid := jid+1;     insert into zry select  round(dbms_random.value(0,10),0),'cs8',round(dbms_random.value(0,10),0) from dual;    if  jid >10000 then    exit;    end if;   end loop; commit;   end;/
^[[AQuery OK, 1 row affected (3.182 sec)

obclient [SYS]> declare jid number :=0; begin loop       jid := jid+1;     insert into zry select  round(dbms_random.value(0,10),0),'cs7',round(dbms_random.value(0,10),0) from dual;    if  jid >10000 then    exit;    end if;   end loop; commit;   end;/
Query OK, 1 row affected (3.134 sec)

obclient [SYS]> declare jid number :=0; begin loop       jid := jid+1;     insert into zry select  round(dbms_random.value(0,10),0),'cs6',round(dbms_random.value(0,10),0) from dual;    if  jid >10000 then    exit;    end if;   end loop; commit;   end;/
Query OK, 1 row affected (2.851 sec)

obclient [SYS]> declare jid number :=0; begin loop       jid := jid+1;     insert into zry select  round(dbms_random.value(0,10),0),'cs5',round(dbms_random.value(1,10),0) from dual;    if  jid >10000 then    exit;    end if;   end loop; commit;   end;/
Query OK, 1 row affected (2.927 sec)

obclient [SYS]> declare jid number :=0; begin loop       jid := jid+1;     insert into zry select  round(dbms_random.value(0,10),0),'cs4',round(dbms_random.value(1,10),0) from dual;    if  jid >10000 then    exit;    end if;   end loop; commit;   end;/
Query OK, 1 row affected (2.965 sec)

obclient [SYS]> declare jid number :=0; begin loop       jid := jid+1;     insert into zry select  round(dbms_random.value(0,10),0),'cs3',round(dbms_random.value(1,10),0) from dual;    if  jid >10000 then    exit;    end if;   end loop; commit;   end;/
Query OK, 1 row affected (3.018 sec)

obclient [SYS]> declare jid number :=0; begin loop       jid := jid+1;     insert into zry select  round(dbms_random.value(0,10),0),'cs2',round(dbms_random.value(1,10),0) from dual;    if  jid >10000 then    exit;    end if;   end loop; commit;   end;/
Query OK, 1 row affected (2.901 sec)

obclient [SYS]> declare jid number :=0; begin loop       jid := jid+1;     insert into zry select  round(dbms_random.value(0,10),0),'cs1',round(dbms_random.value(1,10),0) from dual;    if  jid >10000 then    exit;    end if;   end loop; commit;   end;/
Query OK, 1 row affected (3.001 sec)

obclient [SYS]> commit;
Query OK, 0 rows affected (0.001 sec)

为了看出优化器的代价选择，咱们不对这个表进行统计信息收集，看下优化器判断的两个条件的选择率。

obclient [SYS]> desc select  *  from  zry where a/c in (0.5,1,2);
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                                                                                                                                                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ====================================
|ID|OPERATOR  |NAME|EST. ROWS|COST |
------------------------------------
|0 |TABLE SCAN|ZRY |1501     |39959|           #####因为a/c=0.5的优化器预估行数比较少，所以我加了两个值，原理一样。
====================================

Outputs & filters: 
-------------------------------------
  0 - output([ZRY.A], [ZRY.B], [ZRY.C]), filter([ZRY.A / ZRY.C IN (.5, 1, 2)]), 
      access([ZRY.A], [ZRY.C], [ZRY.B]), partitions(p0)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.003 sec)

obclient [SYS]> desc select  *  from  zry where  b in ('cs1','cs2');
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                                                                                                                                                                                                                        |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ====================================
|ID|OPERATOR  |NAME|EST. ROWS|COST |
------------------------------------
|0 |TABLE SCAN|ZRY |1981     |39959|      #####可以看出来b in ('cs1','cs2')的预估行数要比a/c in (0.5,1,2)的行数多
====================================

Outputs & filters: 
-------------------------------------
  0 - output([ZRY.A], [ZRY.B], [ZRY.C]), filter([ZRY.B IN (?, ?)]), 
      access([ZRY.B], [ZRY.A], [ZRY.C]), partitions(p0)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.003 sec)


obclient [SYS]> desc  select  /*+query_timeout(720000000)*/ count(b)  from  zry where  b in ('cs1','cs2') and   a/c in (0.5,1,2)  ;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =========================================
|ID|OPERATOR       |NAME|EST. ROWS|COST |
-----------------------------------------
|0 |SCALAR GROUP BY|    |1        |40088|
|1 | TABLE SCAN    |ZRY |30       |40087|
=========================================

Outputs & filters: 
-------------------------------------
  0 - output([T_FUN_COUNT(ZRY.B)]), filter(nil), 
      group(nil), agg_func([T_FUN_COUNT(ZRY.B)])
  1 - output([ZRY.B]), filter([ZRY.A / ZRY.C IN (.5, 1, 2)], [ZRY.B IN (?, ?)]),     ###########整体的计划中，filter是先过滤a/c后过滤b
      access([ZRY.B], [ZRY.A], [ZRY.C]), partitions(p0)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.003 sec)

obclient [SYS]> select  /*+query_timeout(720000000)*/ count(b)  from  zry where  b in ('cs1','cs2') and   a/c in (0.5,1,2)  ;
ORA-01476: divisor is equal to zero        ##########很明显，先过滤a/c，因为全表中c是有空值的所以报错了

我们缩小下b的范围，尝试让b条件的优化器代价更小。

obclient [SYS]> desc select  *  from  zry where  b in ('cs1');
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                                                                                                                                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ====================================
|ID|OPERATOR  |NAME|EST. ROWS|COST |
------------------------------------
|0 |TABLE SCAN|ZRY |991      |39291|                       ###############我们减少了b的一个值，预估行数比a/c条件要小了
====================================

Outputs & filters: 
-------------------------------------
  0 - output([ZRY.A], [ZRY.B], [ZRY.C]), filter([ZRY.B = ?]), 
      access([ZRY.B], [ZRY.A], [ZRY.C]), partitions(p0)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.006 sec)


obclient [SYS]> desc select  /*+query_timeout(720000000)*/ count(b)  from  zry where  b in ('cs1') and   a/c in (0.5,1,2)  ;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =========================================
|ID|OPERATOR       |NAME|EST. ROWS|COST |
-----------------------------------------
|0 |SCALAR GROUP BY|    |1        |39419|
|1 | TABLE SCAN    |ZRY |15       |39419|
=========================================

Outputs & filters: 
-------------------------------------
  0 - output([T_FUN_COUNT(ZRY.B)]), filter(nil), 
      group(nil), agg_func([T_FUN_COUNT(ZRY.B)])
  1 - output([ZRY.B]), filter([ZRY.B = ?], [ZRY.A / ZRY.C IN (.5, 1, 2)]),                ################看下总体的计划中filter的顺序是先过滤的B又过滤的a/c
      access([ZRY.B], [ZRY.A], [ZRY.C]), partitions(p0)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.003 sec)
select  /*+query_timeout(720000000)*/ count(b)  from  zry where  b in ('cs1') and   a/c in (0.5,1,2)  ;

obclient [SYS]> select  /*+query_timeout(720000000)*/ count(b)  from  zry where  b in ('cs1') and   a/c in (0.5,1,2)  ;    ##########可以看到sql可以正常执行了
+----------+
| COUNT(B) |
+----------+
|     1906 |
+----------+
1 row in set (0.098 sec)

obclient [SYS]> select  count(*) from  zry where b in ('cs1','cs2') and (c=0 or c is null);     ############通过该sql可以看出来b in ('cs1','cs2')的条件下c是没有空值和0的数据的
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.066 sec)

结论：

通过这个实验很直观可以看出来，where后条件按照过滤性来排序。优化器估算的过滤性好的，会优先执行。

OB在最新 4.x ，会额外引入一些系数综合考虑，一些异常报错的，会再额外加一些代价系数，让他的执行更靠后一些，或者，带了  UDF 之类的计算代价比较高的表达式，也会额外加一些代价系数，让他的计算靠后。

那现在这种情况在3.x等前期版本怎么解决和规避那。“有一些谓词是可以去做 query range 抽取的，query range 抽取的谓词一定是最先做的”。

1.修改sql，使用case或者decode之类的方式将0的情况在a/c条件中排除掉。

2.创建b列相关索引，绑定执行计划，让他先走b条件。效果如下案例。

create index idx_zry_01 on zry(b,a);

call dbms_stats.gather_table_stats('SYS','ZRY',granularity=>'ALL',method_opt=>'FOR ALL COLUMNS SIZE 128');


obclient [SYS]> desc select  /*+query_timeout(720000000)*/ count(b)  from  zry where  b in ('cs1') and   a/c in (0.5,1,2)  ;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ====================================================
|ID|OPERATOR       |NAME           |EST. ROWS|COST |
----------------------------------------------------
|0 |SCALAR GROUP BY|               |1        |36684|
|1 | TABLE SCAN    |ZRY(IDX_ZRY_01)|141      |36678|
====================================================

Outputs & filters: 
-------------------------------------
  0 - output([T_FUN_COUNT(ZRY.B)]), filter(nil), 
      group(nil), agg_func([T_FUN_COUNT(ZRY.B)])
  1 - output([ZRY.B]), filter([ZRY.A / ZRY.C IN (.5, 1, 2)]), 
      access([ZRY.B], [ZRY.A], [ZRY.C]), partitions(p0)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.015 sec)

obclient [SYS]> select  /*+query_timeout(720000000)*/ count(b)  from  zry where  b in ('cs1') and   a/c in (0.5,1,2)  ;
+----------+
| COUNT(B) |
+----------+
|     1870 |
+----------+
1 row in set (0.023 sec)

行之所向，莫问远方。