Skip to Main Content

Oracle Database Free

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Optimizer fails to reorder the AND/OR expressions in a CASE WHEN statement based on their cost

jinhui lai2 days ago — edited 25 hours ago
  • 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 |
    ---------------------------------------------------------------------------
    
Comments
Post Details
Added 2 days ago
5 comments
43 views