CODEWITHSUNDEEP
oracle-database
Soon
Soon

Reputation: 501

(Oracle)Skip scanning table conditionally

My goal is to get average of val column in table HISTORY only when average of val columns in MYREF is NULL. Below is my code. Is this correct way to do this? Is there an other efficient way? Because, actual data in HISOTY are massive and I want to sure that the scanning the HISTROY will never occur if MYREF has value.

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

Upvotes: 0

Views: 42

Answers (1)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

Just use coalesce instead, because Oracle uses short-circuit evaluation for 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;

Simple example:

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

Upvotes: 2

Related Questions