Hi, Oracle developers, please consider such queries:
q1: SELECT c0, COUNT(c0) FROM t0 GROUP BY c0 HAVING (SELECT COUNT(c0) FROM t0,t1,t2)>1 AND c0>1;
q2: SELECT c0, COUNT(c0) FROM t0 GROUP BY c0 HAVING (SELECT COUNT(c0) FROM t0,t1,t2)>1 OR c0=1;
As for q1, there are two conditions in the HAVING clause, connected by AND, which means that if any one of the conditions is not satisfied, it is false. Therefore, when we calculate two conditions, if we calculate one of the conditions to be false, we can short-circuit, which can avoid unnecessary calculations.
As for q2, there are two conditions in the HAVING clause, connected by OR, which means that if any one of the conditions is met, it is true. Therefore, when we calculate two conditions, if we calculate one of the conditions to be true, we can short-circuit, which can avoid unnecessary calculations.
However, Oracle does not short-circuit to optimize such queries. Specifically, short-circuiting is necessary when the computation of the condition is expensive. You can reproduce it as follows:
1.Start oralce service
docker run -d -p 1521:1521 -e ORACLE_PWD=1213 --name oracle container-registry.oracle.com/database/free:latest
docker exec -it oracle /bin/bash
sqlplus / as sysdba
2. Create table and insert data.
CREATE TABLE t0(c0 NUMBER(10));
CREATE TABLE t1(c1 NUMBER(10));
CREATE TABLE t2(c2 NUMBER(10));
INSERT INTO t0 VALUES(1);
INSERT INTO t1 SELECT LEVEL FROM dual CONNECT BY LEVEL <= 10000;
INSERT INTO t2 SELECT LEVEL FROM dual CONNECT BY LEVEL <= 100000;
3. Execute queries
First, there is positive case: Oracle uses one filter and makes "c0=1" fronted for WHERE clause, which helps short-circuit.
-- According to predicate information, it uses one filter and makes "c0=1" fronted
SELECT * FROM t0 WHERE (SELECT COUNT(c0) FROM t0,t1,t2)>1 OR c0=1;
C0
----------
1
Elapsed: 00:00:00.01
Execution Plan
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 9 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | T0 | 3 | 9 | 2 (0)| 00:00:01 |
| 2 | SORT AGGREGATE | | 1 | 3 | | |
| 3 | MERGE JOIN CARTESIAN | | 3000M| 8583M| 1244K (1)| 00:00:49 |
| 4 | MERGE JOIN CARTESIAN| | 30000 | 90000 | 20 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T0 | 3 | 9 | 2 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 10000 | | 18 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T1 | 10000 | | 6 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 100K| | 1244K (1)| 00:00:49 |
| 9 | TABLE ACCESS FULL | T2 | 100K| | 41 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C0"=1 OR (SELECT COUNT("C0") FROM "T2" "T2","T1"
"T1","T0" "T0")>1)
Second, there are two negative cases: Oracle uses one filter and does not make "c0=1" fronted for HAVING clause, which fails to short-cirfuit.
-- According to predicate information, it use one filter and makes "SELECT COUNT(c0) FROM t0,t1,t2" fronted
-- HAVING clause with mutiple conditions connected by OR
SELECT c0, COUNT(c0) FROM t0 GROUP BY c0 HAVING (SELECT COUNT(c0) FROM t0,t1,t2)>1 OR c0=1;
C0 COUNT(C0)
---------- ----------
1 1
Elapsed: 00:01:01.83
Execution Plan
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (34)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 3 | 3 (34)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T0 | 3 | 9 | 2 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 3 | | |
| 5 | MERGE JOIN CARTESIAN | | 3000M| 8583M| 1244K (1)| 00:00:49 |
| 6 | MERGE JOIN CARTESIAN| | 30000 | 90000 | 20 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T0 | 3 | 9 | 2 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 10000 | | 18 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | T1 | 10000 | | 6 (0)| 00:00:01 |
| 10 | BUFFER SORT | | 100K| | 1244K (1)| 00:00:49 |
| 11 | TABLE ACCESS FULL | T2 | 100K| | 41 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( (SELECT COUNT("C0") FROM "T2" "T2","T1" "T1","T0"
"T0")>1 OR "C0"=1)
-- According to predicate information, it uses one filter and makes "SELECT COUNT(c0) FROM t0,t1,t2" fronted
-- HAVING clause with mutiple conditions connected by AND
SELECT c0, COUNT(c0) FROM t0 GROUP BY c0 HAVING (SELECT COUNT(c0) FROM t0, t1,t2)>1 AND c0>1;
no rows selected
Elapsed: 00:01:01.58
Execution Plan
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (34)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 3 | 3 (34)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T0 | 3 | 9 | 2 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 3 | | |
| 5 | MERGE JOIN CARTESIAN | | 3000M| 8583M| 1244K (1)| 00:00:49 |
| 6 | MERGE JOIN CARTESIAN| | 30000 | 90000 | 20 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T0 | 3 | 9 | 2 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 10000 | | 18 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | T1 | 10000 | | 6 (0)| 00:00:01 |
| 10 | BUFFER SORT | | 100K| | 1244K (1)| 00:00:49 |
| 11 | TABLE ACCESS FULL | T2 | 100K| | 41 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( (SELECT COUNT("C0") FROM "T2" "T2","T1" "T1","T0"
"T0")>1 AND "C0">1)