My Oracle Support Banner

Wrong Results with subquery coalescing (Doc ID 2860380.1)

Last updated on APRIL 02, 2022

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information 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


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.