(Oracle)有条件地跳过扫描表

Question

我的目标是只有当MYREF中val列的平均值为NULL时，才能获得HISTORY表中val列平均值。下面是我的代码。这样做正确吗？还有其他有效的方法吗？因为，HISTOTY中的实际数据是巨大的，我想确保如果MYREF有值，扫描HISTORY将永远不会发生。

CREATE TABLE HISTORY (THEDATE VARCHAR(20),VAL NUMBER);
INSERT INTO HISTORY VALUES('20170101', 3);
INSERT INTO HISTORY VALUES('20200923', 4);
CREATE TABLE MYREF (VAL NUMBER);
INSERT INTO  MYREF VALUES( NULL);
WITH MYREF_VAL AS( SELECT AVG(VAL) FROM MYREF)
,HISTORY_CAL AS (SELECT AVG(VAL) FROM HISTORY)
SELECT NVL((SELECT AVG(VAL) FROM MYREF), (SELECT AVG(VAL) FROM HISTORY)) VAL FROM DUAL
--Expected result 3.5 which is correct

Answer 1

只需使用coalize，因为Oracle将short-circuit evaluation用于coalesce：https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions023.htm

WITH MYREF_VAL AS( SELECT AVG(VAL) FROM MYREF)
,HISTORY_CAL AS (SELECT AVG(VAL) FROM HISTORY)
SELECT 
COALESCE((SELECT AVG(VAL) FROM MYREF), (SELECT AVG(VAL) FROM HISTORY)) VAL 
FROM DUAL;

简单示例：

SQL> select nvl(1, 1/0) a from dual;
select nvl(1, 1/0) a from dual
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL> select coalesce(1, 1/0) a from dual;
A
----------
1