CODEWITHSUNDEEP
sqloracle-database
realsnad
realsnad

Reputation: 45

Unexpected query results in Oracle db

We have Oracle 12.2.0.1.0 database. We create a simple table like this:

CREATE TABLE TABLE1 (DATE1 TIMESTAMP (6));
INSERT INTO TABLE1 VALUES (TIMESTAMP'2018-05-30 00:00:00');
INSERT INTO TABLE1 VALUES (TIMESTAMP'2018-05-30 00:00:00');

When we query with the following two select statements, we get different results. The first one returns two rows as expected, while the second one doesn't.

SELECT T1.*, NVL(T2.DATE1, TIMESTAMP'1900-01-01 00:00:00')
FROM TABLE1 T1
LEFT JOIN TABLE1 T2
ON 1 = 0
WHERE T1.DATE1 > NVL(T2.DATE1, TIMESTAMP'1900-01-01 00:00:00');

SELECT T1.*, NVL(T2.DATE1, TIMESTAMP'1900-01-01 00:00:00')
FROM TABLE1 T1
LEFT JOIN TABLE1 T2
ON T1.DATE1 || '---' = '-'
WHERE T1.DATE1 > NVL(T2.DATE1, TIMESTAMP'1900-01-01 00:00:00');

T1 and T2 are the same TABLE1. We are joining it on itself.

Please advise why that is so. Thanks.

Upvotes: 2

Views: 99

Answers (1)

user330315
user330315

Reputation:

It seems the optimizer gets confused with so many levels of obfuscating the join condition.

The first query results in the following execution plan: 

SQL_ID  9k6g3m0xs31w7, child number 1
-------------------------------------
select t1.*, nvl(t2.date1, timestamp'1900-01-01 00:00:00') from table1 
t1   left join table1 t2 on 1 = 0 where t1.date1 > nvl(t2.date1, 
timestamp'1900-01-01 00:00:00')

Plan hash value: 963482612

-----------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |      1 |        |       |     3 (100)|      2 |00:00:00.01 |       7 |
|*  1 |  TABLE ACCESS FULL| TABLE1 |      1 |      2 |    26 |     3   (0)|      2 |00:00:00.01 |       7 |
-----------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$F7AF7B7D / T1@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"."DATE1">TIMESTAMP' 1900-01-01 00:00:00.000000000')

So the planner correctly sees that the self join is unnecessary and replaces the NVL() condition on the joined table with a condition on the column itself.

Apparently this "replacing" the condition does not work correctly in 12.2.

The second query results in the following plan:

SQL_ID  3twykk3kcyyxy, child number 1
-------------------------------------
select t1.*, nvl(t2.date1, timestamp'1900-01-01 00:00:00') from table1 
t1 left join table1 t2   on t1.date1 || '---' = '-' where t1.date1 > 
nvl(t2.date1, timestamp'1900-01-01 00:00:00')

Plan hash value: 736255932

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |      1 |        |       |     8 (100)|      0 |00:00:00.01 |       7 |       |       |          |
|*  1 |  FILTER                |                 |      1 |        |       |            |      0 |00:00:00.01 |       7 |       |       |          |
|   2 |   MERGE JOIN OUTER     |                 |      1 |      1 |    26 |     8  (25)|      2 |00:00:00.01 |       7 |       |       |          |
|   3 |    SORT JOIN           |                 |      1 |      2 |    26 |     4  (25)|      2 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
|   4 |     TABLE ACCESS FULL  | TABLE1          |      1 |      2 |    26 |     3   (0)|      2 |00:00:00.01 |       7 |       |       |          |
|*  5 |    SORT JOIN           |                 |      2 |      2 |    26 |     4  (25)|      0 |00:00:00.01 |       0 |  1024 |  1024 |          |
|   6 |     VIEW               | VW_LAT_C83A7ED5 |      2 |      2 |    26 |     3   (0)|      0 |00:00:00.01 |       0 |       |       |          |
|*  7 |      FILTER            |                 |      2 |        |       |            |      0 |00:00:00.01 |       0 |       |       |          |
|   8 |       TABLE ACCESS FULL| TABLE1          |      0 |      2 |    26 |     3   (0)|      0 |00:00:00.01 |       0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$F7AF7B7D
   4 - SEL$F7AF7B7D / T1@SEL$1
   6 - SEL$BCD4421C / VW_LAT_AE9E49E8@SEL$AE9E49E8
   7 - SEL$BCD4421C
   8 - SEL$BCD4421C / T2@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"."DATE1">NVL("ITEM_1",TIMESTAMP' 1900-01-01 00:00:00.000000000'))
   5 - access(INTERNAL_FUNCTION("T1"."DATE1")>NVL("ITEM_1",TIMESTAMP' 1900-01-01 00:00:00.000000000'))
   7 - filter(INTERNAL_FUNCTION("T1"."DATE1")||'---'='-')

So the optimizer replaced the reference to the table column with some ITEM1 placeholder - and the step access(INTERNAL_FUNCTION("T1"."DATE1")>NVL("ITEM_1",TIMESTAMP' 1900-01-01 00:00:00.000000000')) messes things up.

With 12.1 the plan is essentially the same, the only difference is that the access() part is missing in the predicates, so I guess that replacement is somewhat buggy in 12.2 (to be precise my version is: 12.2.0.1.0)

Upvotes: 1

Related Questions