Wrong Result on Hash join+table()+nvl() with Adaptive Plan Enabled in 12C
(Doc ID 2583738.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterInformation in this document applies to any platform.
Symptoms
- On version 12.1.0.2
- Wrong Result on Hash join+table()+nvl() with adaptive plan enabled In 12C
- Removing table function to actual value or remove nvl() function can produce
correct result:select ...
where
...nvl(c.col1, b.col2) in select a.* from table(v_tab) a)... - Wrong results in 12c shows hash join plan:
Correct result on 11G
---------------
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL TAB(cr=324179 pr=49882 pw=0 time=7761307 us)
444 444 444 UNION-ALL (cr=324150 pr=49855 pw=0 time=7747722 us) <==============================================444 rows
0 0 0 FILTER (cr=11 pr=3 pw=0 time=674 us)
0 0 0 NESTED LOOPS (cr=11 pr=3 pw=0 time=663 us)<======NESTED LOOPS
T SUM
- ----------
a 7.51Wrong result on 12c
-------------------------Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL TPREMVOU (cr=324425 pr=50560 pw=0 time=7088898 us)
120 120 120 UNION-ALL (cr=324419 pr=50549 pw=0 time=7085579 us)<=================================120 rows
0 0 0 FILTER (cr=11 pr=3 pw=0 time=712 us)
0 0 0 HASH JOIN (cr=11 pr=3 pw=0 time=699 us cost=86 size=151 card=1)<========HASH JOIN
...
T SUM
- ----------
a 《===wrong results
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |