Execute queries
Oracle takes 0.01s
and 0.03s
to execute these two queries :
-- Oracle applied short-circuit evaluation for AND/OR expression
SELECT CASE WHEN 2<1 AND (SELECT MIN(c0) FROM t0) < 0 THEN 1 ELSE 0 END;
Elapsed: 00:00:00.01
SELECT CASE WHEN 2>1 OR (SELECT MIN(c0) FROM t0) < 0 THEN 1 ELSE 0 END;
Elapsed: 00:00:00.03
Oracle takes 30.12s
and 30.24s
to execute these two queries, which should be executed in 0.0Xs
:
-- negative case
-- Optimizer fails to reorder the AND/OR expressions in a CASE WHEN statement based on their cost, since the "2>1" is cheaper than "(SELECT MIN(c0) FROM t0) < 0"
SELECT CASE WHEN (SELECT MIN(c0) FROM t0) < 0 OR 2>1 THEN 1 ELSE 0 END;
CASEWHEN(SELECTMIN(C0)FROMT0)<0OR2>1THEN1ELSE0END
-------------------------------------------------
1
Elapsed: 00:00:30.12
Execution Plan
----------------------------------------------------------
Plan hash value: 1658584870
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| T0 | 3 | 9 | 2 (0)| 00:00:01 |
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
SELECT CASE WHEN (SELECT MIN(c0) FROM t0) < 0 AND 2<1 THEN 1 ELSE 0 END;
CASEWHEN(SELECTMIN(C0)FROMT0)<0AND2<1THEN1ELSE0END
--------------------------------------------------
0
Elapsed: 00:00:30.24
Execution Plan
----------------------------------------------------------
Plan hash value: 1658584870
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| T0 | 3 | 9 | 2 (0)| 00:00:01 |
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------