Oracle Database Free

Pull the latest container of Oracle

docker run -d -p 1521:1521 -e ORACLE_PWD=1234 --name oracle container-registry.oracle.com/database/free:latest
docker exec -it oracle /bin/bash
sqlplus / as sysdba 

Create table t0 and insert data

SET TIMING ON;
SET AUTOTRACE ON EXPLAIN;
-- insert into t0 with 1 billion rows
-- Since inserting 1 billion rows at once may fail, you can insert 0.1 billion rows 10 times
CREATE TABLE t0(c0 NUMBER(10));
INSERT INTO t0 (c0) SELECT LEVEL FROM dual CONNECT BY LEVEL <= 1000000000;

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 |
---------------------------------------------------------------------------