My Oracle Support Banner

WRONG RESULT WITH OFE 11201/11107 FOR FA QUERY WITH SERIAL EXEC (Doc ID 2430478.1)

Last updated on AUGUST 08, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

Select on Peoplesoft query is giving wrong results(0 rows selected) with HASH Join Plan whereas with /*+ RULE */ hint for the same query in 12.1.0.2 is giving correct results (4,278 rows selected) with a Nested Loop Plan.

12102: (Run WITHOUT a Hint, get a Hash Join):

Select
    total_rows,
    'No Hint'  as Type,
    B.Name     as DB_name,
    C.Version
  from
  (select count(*) as total_rows

---------------

0 rows selected.

Plan hash value: 2531761130   [with no hint and with ALL_ROWS hint]
----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name             | Rows  | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                  |     1 |    84 |    46   (0)| 00:00:01 |
|*  1 |  FILTER                               |                  |       |       |            |       |
|*  2 |   HASH JOIN                           |                  |  3894 |   319K|    46   (0)| 00:00:01 |  >>>>>>>>>>>>>>>>>>
|   3 |    TABLE ACCESS FULL                  | PS_XXXX_XXXX_TBL |  3894 |   167K|     9   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN               | PSHXXXXXXXE    | 11605 |   453K|    37   (0)| 00:00:01 |
|   5 |   SORT AGGREGATE                      |                  |     1 |    31 |            |       |
|*  6 |    TABLE ACCESS BY INDEX ROWID BATCHED| PS_XXXX_XXXX_TBL |     1 |    31 |     3   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN                  | PS_XXXX_XXXX_TBL |     1 |       |     2   (0)| 00:00:01 |
|*  8 |   TABLE ACCESS BY INDEX ROWID BATCHED | PS_XXXX_XXXX_TBL |     1 |    31 |     3   (0)| 00:00:01 |
|*  9 |    INDEX RANGE SCAN                   | PS_XXXX_XXXX_TBL |     1 |       |     2   (0)| 00:00:01 |
|  10 |   SORT AGGREGATE                      |                  |     1 |    27 |            |       |
|* 11 |    INDEX RANGE SCAN                   | PS_XXXX_XXXX_TBL |     1 |    27 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Run WITH a RULE Hint, get a Nested Loop:

Select /*+ RULE */
    total_rows,
    'No Hint'  as Type,
    B.Name     as DB_name,
    C.Version
  from
  (select count(*) as total_rows

------------------------

4,278 rows selected

Plan hash value: 2840222651  [RULE hint returns 4,278]
----------------------------------------------------------
| Id  | Operation                     | Name             |
----------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |
|*  1 |  FILTER                       |                  |
|   2 |   NESTED LOOPS                |                  |  >>>>>>>>>>>>>
|   3 |    NESTED LOOPS               |                  |  >>>>>>>>>>>>>
|   4 |     TABLE ACCESS FULL         | PS_XXXX_XXXX_TBL |
|*  5 |     INDEX RANGE SCAN          | PSXXXXXXXXE    |
|*  6 |    TABLE ACCESS BY INDEX ROWID| PXXXXXXDE       |
|   7 |   SORT AGGREGATE              |                  |
|*  8 |    TABLE ACCESS FULL          | PS_XXXXX_XXXX_TBL |
|*  9 |   TABLE ACCESS FULL           | PS_XXXXX_XXX_TBL |
|  10 |   SORT AGGREGATE              |                  |
|* 11 |    TABLE ACCESS FULL          | PS_XXXX_XXX_TBL |
----------------------------------------------------------

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


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