Are you aware of any bugs in decode vs case behavior? One of my developer posed this open ended question to me other day. I asked him to send me sql code or test case.
Test Case
BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production SQL> create table table_1 as (select 'a' col1, 1 col2 from dual union select 'b' , 2 from dual ); Table created. SQL> create table table_2 as (select 'a' col1, 1 col2 from dual union select 'b' , null from dual); Table created. SQL> SQL> SQL> select * from table_1; C COL2 - ---------- a 1 b 2 SQL> select * from table_2; C COL2 - ---------- a 1 b SQL> SQL> SELECT a.col1 acol1, 2 a.col2 acol2, 3 b.col1 bcol1, 4 b.col2 bcol2 , 5 CASE 6 WHEN 1 = 1 7 THEN 'case breaks it' 8 END case_breaks_it 9 FROM 10 (SELECT * FROM table_1) a, 11 (SELECT col1, NVL(col2,0) col2 FROM table_2 ) b 12 WHERE a.col1 = b.col1(+) 13 AND a.col2 = b.col2(+); A ACOL2 B BCOL2 CASE_BREAKS_IT - ---------- - ---------- -------------- a 1 a 1 case breaks it b 2 0 case breaks it SQL> SQL> SQL> SQL> SELECT a.col1 acol1, 2 a.col2 acol2, 3 b.col1 bcol1, 4 b.col2 bcol2 , 5 DECODE(1,1,'decode works','this is strange') decode_doesnt 6 FROM 7 (SELECT * FROM table_1) a, 8 (SELECT col1, NVL(col2,0) col2 FROM table_2) b 9 WHERE a.col1 = b.col1(+) 10 AND a.col2 = b.col2(+); A ACOL2 B BCOL2 DECODE_DOESN - ---------- - ---------- ------------ a 1 a 1 decode works b 2 decode works
.
As you can see from above, BCOL2 column shows 0 when using case statement instead of null. Lets quickly look at run time query plan.
Query Plan
SQL> alter session set statistics_level=all; Session altered. SQL> SQL> SELECT a.col1 acol1, 2 a.col2 acol2, 3 b.col1 bcol1, 4 b.col2 bcol2 , 5 CASE 6 WHEN 1 = 1 7 THEN 'case breaks it' 8 END case_breaks_it 9 FROM 10 (SELECT * FROM table_1) a, 11 (SELECT col1, NVL(col2,0) col2 FROM table_2 ) b 12 WHERE a.col1 = b.col1(+) 13 AND a.col2 = b.col2(+); A ACOL2 B BCOL2 CASE_BREAKS_IT - ---------- - ---------- -------------- a 1 a 1 case breaks it b 2 0 case breaks it SQL> @xall PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 66rq1pmg8zyyy, child number 0 ------------------------------------- SELECT a.col1 acol1, a.col2 acol2, b.col1 bcol1, b.col2 bcol2 , CASE WHEN 1 = 1 THEN 'case breaks it' END case_breaks_it FROM (SELECT * FROM table_1) a, (SELECT col1, NVL(col2,0) col2 FROM table_2 ) b WHERE a.col1 = b.col1(+) AND a.col2 = b.col2(+) Plan hash value: 1294080186 --------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | | 0 | SELECT STATEMENT | | 1 | | | 17 (100)| | 2 |00:00:00.01 | 7 | | | | |* 1 | HASH JOIN OUTER | | 1 | 2 | 88 | 17 (6)| 00:00:01 | 2 |00:00:00.01 | 7 | 1156K| 1156K| 535K (0)| | 2 | TABLE ACCESS FULL| TABLE_1 | 1 | 2 | 32 | 8 (0)| 00:00:01 | 2 |00:00:00.01 | 3 | | | | | 3 | TABLE ACCESS FULL| TABLE_2 | 1 | 2 | 56 | 8 (0)| 00:00:01 | 2 |00:00:00.01 | 4 | | | | --------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5428C7F1 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 - SEL$5428C7F1 / TABLE_1@SEL$2 3 - SEL$5428C7F1 / TABLE_2@SEL$3 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.2') DB_VERSION('11.2.0.2') ALL_ROWS OUTLINE_LEAF(@"SEL$5428C7F1") MERGE(@"SEL$2") MERGE(@"SEL$3") OUTLINE(@"SEL$1") OUTLINE(@"SEL$2") OUTLINE(@"SEL$3") FULL(@"SEL$5428C7F1" "TABLE_1"@"SEL$2") FULL(@"SEL$5428C7F1" "TABLE_2"@"SEL$3") LEADING(@"SEL$5428C7F1" "TABLE_1"@"SEL$2" "TABLE_2"@"SEL$3") USE_HASH(@"SEL$5428C7F1" "TABLE_2"@"SEL$3") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("TABLE_1"."COL1"="COL1" AND "TABLE_1"."COL2"=CASE WHEN (ROWID IS NOT NULL) THEN NVL("COL2",0) ELSE NULL END ) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=2) "TABLE_1"."COL1"[CHARACTER,1], "COL1"[CHARACTER,1], "TABLE_1"."COL2"[NUMBER,22], "COL2"[NUMBER,22] 2 - "TABLE_1"."COL1"[CHARACTER,1], "TABLE_1"."COL2"[NUMBER,22] 3 - ROWID[ROWID,10], "COL1"[CHARACTER,1], "COL2"[NUMBER,22] Note ----- - dynamic sampling used for this statement (level=2) 63 rows selected. SQL> SELECT a.col1 acol1, 2 a.col2 acol2, 3 b.col1 bcol1, 4 b.col2 bcol2 , 5 DECODE(1,1,'decode works','this is strange') decode_doesnt 6 FROM 7 (SELECT * FROM table_1) a, 8 (SELECT col1, NVL(col2,0) col2 FROM table_2) b 9 WHERE a.col1 = b.col1(+) 10 AND a.col2 = b.col2(+); A ACOL2 B BCOL2 DECODE_DOESN - ---------- - ---------- ------------ a 1 a 1 decode works b 2 decode works SQL> @xall.sql PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 373cus15943ut, child number 0 ------------------------------------- SELECT a.col1 acol1, a.col2 acol2, b.col1 bcol1, b.col2 bcol2 , DECODE(1,1,'decode works','this is strange') decode_doesnt FROM (SELECT * FROM table_1) a, (SELECT col1, NVL(col2,0) col2 FROM table_2) b WHERE a.col1 = b.col1(+) AND a.col2 = b.col2(+) Plan hash value: 1294080186 --------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | | 0 | SELECT STATEMENT | | 1 | | | 17 (100)| | 2 |00:00:00.01 | 7 | | | | |* 1 | HASH JOIN OUTER | | 1 | 2 | 88 | 17 (6)| 00:00:01 | 2 |00:00:00.01 | 7 | 1156K| 1156K| 510K (0)| | 2 | TABLE ACCESS FULL| TABLE_1 | 1 | 2 | 32 | 8 (0)| 00:00:01 | 2 |00:00:00.01 | 3 | | | | | 3 | TABLE ACCESS FULL| TABLE_2 | 1 | 2 | 56 | 8 (0)| 00:00:01 | 2 |00:00:00.01 | 4 | | | | --------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5428C7F1 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 - SEL$5428C7F1 / TABLE_1@SEL$2 3 - SEL$5428C7F1 / TABLE_2@SEL$3 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.2') DB_VERSION('11.2.0.2') ALL_ROWS OUTLINE_LEAF(@"SEL$5428C7F1") MERGE(@"SEL$2") MERGE(@"SEL$3") OUTLINE(@"SEL$1") OUTLINE(@"SEL$2") OUTLINE(@"SEL$3") FULL(@"SEL$5428C7F1" "TABLE_1"@"SEL$2") FULL(@"SEL$5428C7F1" "TABLE_2"@"SEL$3") LEADING(@"SEL$5428C7F1" "TABLE_1"@"SEL$2" "TABLE_2"@"SEL$3") USE_HASH(@"SEL$5428C7F1" "TABLE_2"@"SEL$3") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("TABLE_1"."COL1"="COL1" AND "TABLE_1"."COL2"=CASE WHEN (ROWID IS NOT NULL) THEN NVL("COL2",0) ELSE NULL END ) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=2) "TABLE_1"."COL1"[CHARACTER,1], "COL1"[CHARACTER,1], "TABLE_1"."COL2"[NUMBER,22], ROWID[ROWID,10], "COL2"[NUMBER,22] 2 - "TABLE_1"."COL1"[CHARACTER,1], "TABLE_1"."COL2"[NUMBER,22] 3 - ROWID[ROWID,10], "COL1"[CHARACTER,1], "COL2"[NUMBER,22] Note ----- - dynamic sampling used for this statement (level=2)
.
Query plans, Outline data and predicate information for both the plans are same but the column projection for query using decode with operation id 1 has additional column ROWID[ROWID,10]. But its not clear at this stage why Oracle needs ROWID from TABLE_2. Lets run 10053 trace to check query transformations.
10053
Final query after transformations:******* UNPARSED QUERY IS ******* SELECT "TABLE_1"."COL1" "ACOL1","TABLE_1"."COL2" "ACOL2","TABLE_2"."COL1" "BCOL1",NVL("TABLE_2"."COL2",0) "BCOL2",'case breaks it' "CASE_BREAKS_IT" FROM "VDESAI"."TABLE_1" "TABLE_1","VDESAI"."TABLE_2" "TABLE_2" WHERE "TABLE_1"."COL1"="TABLE_2"."COL1"(+) AND "TABLE_1"."COL2"=CASE WHEN ("TABLE_2".ROWID(+) IS NOT NULL) THEN NVL("TABLE_2"."COL2"(+),0) ELSE NULL END Final query after transformations:******* UNPARSED QUERY IS ******* SELECT "TABLE_1"."COL1" "ACOL1","TABLE_1"."COL2" "ACOL2","TABLE_2"."COL1" "BCOL1",CASE WHEN "TABLE_2".ROWID IS NOT NULL THEN NVL("TABLE_2"."COL2",0) ELSE NULL END "BCOL2",'decode works' "DECODE_DOESNT" FROM "VDESAI"."TABLE_1" "TABLE_1","VDESAI"."TABLE_2" "TABLE_2" WHERE "TABLE_1"."COL1"="TABLE_2"."COL1"(+) AND "TABLE_1"."COL2"=CASE WHEN ("TABLE_2".ROWID(+) IS NOT NULL) THEN NVL("TABLE_2"."COL2"(+),0) ELSE NULL END
.
From above its clear that Oracle optimizer is rewriting both queries little differently using query transformation. You cannot run this queries directly as there are some other variables applied to it internally during runtime. So running transformed query directly from sqlplus may not produce same result as the original query does.
To prevent query transformation, I tried to run query with case statement with NO_MERGE hint and it produced expected output. If you cannot change code set “_simple_view_merging” to false and you will get correct output.
NO_MERGE
VDESAI@csprod2 > select /*+ NO_MERGE(@"SEL$2") NO_MERGE(@"SEL$3") */ a.col1 acol1, a.col2 acol2, b.col1 bcol1, b.col2 bcol2 2 ,case when 1 = 1 then 'case breaks it' end case_breaks_it 3 from (select * from table_1) a, 4 (select col1, nvl(col2,0) col2 from table_2) b 5 where a.col1 = b.col1(+) 6 and a.col2 = b.col2(+); A ACOL2 B BCOL2 CASE_BREAKS_IT - ---------- - ---------- -------------- a 1 a 1 case breaks it b 2 case breaks it Final query after transformations:******* UNPARSED QUERY IS ******* SELECT "A"."COL1" "ACOL1","A"."COL2" "ACOL2","B"."COL1" "BCOL1","B"."COL2" "BCOL2",'case breaks it' "CASE_BREAKS_IT" FROM (SELECT /*+ NO_MERGE */ "TABLE_1"." COL1" "COL1","TABLE_1"."COL2" "COL2" FROM "VDESAI"."TABLE_1" "TABLE_1") "A", (SELECT /*+ NO_MERGE */ "TABLE_2"."COL1" "COL1",NVL("TABLE_2"."COL2",0) "COL2" FR OM "VDESAI"."TABLE_2" "TABLE_2") "B" WHERE "A"."COL1"="B"."COL1"(+) AND "A"."COL2"="B"."COL2"(+) ---------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | ---------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 17 | | | 1 | HASH JOIN OUTER | | 2 | 64 | 17 | 00:00:01 | | 2 | VIEW | | 2 | 32 | 8 | 00:00:01 | | 3 | TABLE ACCESS FULL | TABLE_1 | 2 | 32 | 8 | 00:00:01 | | 4 | VIEW | | 2 | 32 | 8 | 00:00:01 | | 5 | TABLE ACCESS FULL | TABLE_2 | 2 | 32 | 8 | 00:00:01 | ---------------------------------------+-----------------------------------+ Predicate Information: ---------------------- 1 - access("A"."COL1"="B"."COL1" AND "A"."COL2"="B"."COL2") /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.2') DB_VERSION('11.2.0.2') OPT_PARAM('_unnest_subquery' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$3") OUTLINE_LEAF(@"SEL$1") NO_ACCESS(@"SEL$1" "A"@"SEL$1") NO_ACCESS(@"SEL$1" "B"@"SEL$1") LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1") USE_HASH(@"SEL$1" "B"@"SEL$1") FULL(@"SEL$2" "TABLE_1"@"SEL$2") FULL(@"SEL$3" "TABLE_2"@"SEL$3") END_OUTLINE_DATA */