Hi, Oracle developers, please consider such a query:
SELECT * FROM t0 WHERE c0>1 AND (SELECT COUNT(c0) FROM t0, t1, t2)>0;
There are two conditions in the WHERE 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. However, Oracle does not short-circuit to optimize such queries. Specially, 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
-- false positive (lucky case)
SELECT * FROM t0 WHERE c0>1 AND (SELECT COUNT(*) FROM t0, t1, t2)>0;
no rows selected
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3753548476
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 8 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL | T0 | 1 | 3 | 2 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | | 6 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | | 4 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T0 | 1 | | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL| T2 | 1 | | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T1 | 10000 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "T2" "T2","T1" "T1","T0" "T0"))
2 - filter("C0">1)
-- negative case: expect 00:00:00.00 but consume 00:00:31.44
-- change "SELECT COUNT(*)" to "SELECT COUNT(c0)"
SELECT * FROM t0 WHERE c0>1 AND (SELECT COUNT(c0) FROM t0, t1, t2)>0;
no rows selected
Elapsed: 00:00:31.44
Execution Plan
----------------------------------------------------------
Plan hash value: 1536065674
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1244K (1)| 00:00:49 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL | T0 | 1 | 3 | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 3 | | |
| 4 | MERGE JOIN CARTESIAN | | 3000M| 8583M| 1244K (1)| 00:00:49 |
| 5 | MERGE JOIN CARTESIAN| | 30000 | 90000 | 20 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T0 | 3 | 9 | 2 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 10000 | | 18 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | T1 | 10000 | | 6 (0)| 00:00:01 |
| 9 | BUFFER SORT | | 100K| | 1244K (1)| 00:00:49 |
| 10 | 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")>0)
2 - filter("C0">1)
-- negative case: expect 00:00:00.00 but consume 00:00:25.35
-- change SELECT ">0" to ">1"
SELECT * FROM t0 WHERE c0>1 AND (SELECT COUNT(*) FROM t0, t1, t2)>1;
no rows selected
Elapsed: 00:00:25.35
Execution Plan
----------------------------------------------------------
Plan hash value: 1536065674
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1244K (1)| 00:00:49 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL | T0 | 1 | 3 | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | | | |
| 4 | MERGE JOIN CARTESIAN | | 3000M| | 1244K (1)| 00:00:49 |
| 5 | MERGE JOIN CARTESIAN| | 30000 | | 20 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T0 | 3 | | 2 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 10000 | | 18 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | T1 | 10000 | | 6 (0)| 00:00:01 |
| 9 | BUFFER SORT | | 100K| | 1244K (1)| 00:00:49 |
| 10 | TABLE ACCESS FULL | T2 | 100K| | 41 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( (SELECT COUNT(*) FROM "T2" "T2","T1" "T1","T0" "T0")>1)
2 - filter("C0">1)
COUNT (*)>0 can be optimized for existence checking, so the first query above is a lucky case rather than applying short-circuit optimization. COUNT (*)>1 needs to calculate the number of rows and COUNT (column)>0 needs to check if it is NULL, so the second and third queries consume much time.
All of the above three queries use two filters separately which make cannot short-circuit ( false AND anyother is false). The follows is a positive case where only one filter is used, which can short-circuit( true OR anyother is true)：
-- positive case: it only uses one filter and makes "c0=1" fronted
SELECT * FROM t0 WHERE c0=1 OR (SELECT COUNT(c0) FROM t0, t1, t2)>1;
C0
----------
1
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 873585208
--------------------------------------------------------------------------------
| 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)
Thank you for your time. I look forward to your reply.
Best regards,
Jinhui Lai
Nanchang University