Wrong Results with subquery coalescing
(Doc ID 2860380.1)
Last updated on APRIL 17, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterInformation in this document applies to any platform.
Symptoms
Select statement results are wrong.
Wrong results shows this with 11 rows:
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
11 11 11 SORT ORDER BY (cr=245 pr=0 pw=0 time=2470 us cost=119 size=3658 card=59)
11 11 11 NESTED LOOPS (cr=245 pr=0 pw=0 time=3374 us cost=118 size=3658 card=59)
11 11 11 HASH JOIN (cr=232 pr=0 pw=0 time=3146 us cost=118 size=2950 card=59)
11 11 11 NESTED LOOPS (cr=232 pr=0 pw=0 time=3126 us cost=118 size=2950 card=59)
90 90 90 STATISTICS COLLECTOR (cr=90 pr=0 pw=0 time=1476 us)
90 90 90 NESTED LOOPS (cr=90 pr=0 pw=0 time=5993 us cost=5 size=64 card=2)
91 91 91 SORT UNIQUE (cr=4 pr=0 pw=0 time=150 us cost=3 size=38 card=2)
91 91 91 INLIST ITERATOR (cr=4 pr=0 pw=0 time=1231 us)
91 91 91 INDEX RANGE SCAN FND_REQUEST_GROUP_UNITS_U1 (cr=4 pr=0 pw=0 time=53 us cost=3 size=38 card=2)(object id 34597)
90 90 90 TABLE ACCESS BY INDEX ROWID FND_CONCURRENT_PROGRAMS (cr=86 pr=0 pw=0 time=1072 us cost=1 size=13 card=1)
90 90 90 INDEX UNIQUE SCAN FND_CONCURRENT_PROGRAMS_U1 (cr=39 pr=0 pw=0 time=340 us cost=0 size=0 card=1)(object id 34377)
11 11 11 TABLE ACCESS BY INDEX ROWID BATCHED FND_CONCURRENT_REQUESTS (cr=142 pr=0 pw=0 time=885 us cost=111 size=522 card=29)
11 11 11 INDEX RANGE SCAN FND_CONCURRENT_REQUESTS_N6 (cr=133 pr=0 pw=0 time=634 us cost=2 size=0 card=226)(object id 34568)
0 0 0 TABLE ACCESS FULL FND_CONCURRENT_REQUESTS (cr=0 pr=0 pw=0 time=0 us cost=111 size=522 card=29)
11 11 11 INDEX UNIQUE SCAN FND_CONCURRENT_PROGRAMS_TL_U1 (cr=13 pr=0 pw=0 time=101 us cost=0 size=12 card=1)(object id 34471)
Correct results should show this with 36 rows:
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
36 36 36 SORT ORDER BY (cr=22450 pr=0 pw=0 time=188369 us cost=8357 size=301 card=7)
36 36 36 FILTER (cr=22450 pr=0 pw=0 time=16253 us)
16082 16082 16082 NESTED LOOPS (cr=22186 pr=0 pw=0 time=193532 us cost=2037 size=2709774 card=63018)
16082 16082 16082 HASH JOIN (cr=7360 pr=0 pw=0 time=131811 us cost=2029 size=1953558 card=63018)
6892 6892 6892 NESTED LOOPS (cr=185 pr=0 pw=0 time=11127 us cost=2029 size=1953558 card=63018)
6892 6892 6892 STATISTICS COLLECTOR (cr=185 pr=0 pw=0 time=9209 us)
6892 6892 6892 TABLE ACCESS FULL FND_CONCURRENT_PROGRAMS (cr=185 pr=0 pw=0 time=7148 us cost=53 size=27820 card=2140)
0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED FND_CONCURRENT_REQUESTS (cr=0 pr=0 pw=0 time=0 us cost=1975 size=522 card=29)
0 0 0 INDEX RANGE SCAN FND_CONCURRENT_REQUESTS_N6 (cr=0 pr=0 pw=0 time=0 us)(object id 34568)
18242 18242 18242 TABLE ACCESS FULL FND_CONCURRENT_REQUESTS (cr=7175 pr=0 pw=0 time=90528 us cost=1975 size=1134594 card=63033)
16082 16082 16082 INDEX UNIQUE SCAN FND_CONCURRENT_PROGRAMS_TL_U1 (cr=14826 pr=0 pw=0 time=40306 us cost=0 size=12 card=1)(object id 34471)
1 1 1 INDEX RANGE SCAN FND_REQUEST_GROUP_UNITS_U1 (cr=22 pr=0 pw=0 time=249 us cost=2 size=14 card=1)(object id 34597)
4 4 4 INDEX UNIQUE SCAN FND_REQUEST_GROUP_UNITS_U1 (cr=242 pr=0 pw=0 time=930 us cost=1 size=19 card=1)(object id 34597)
Changes
Upgraded from 11g
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 |
| Changes |
| Cause |
| Solution |
| References |