Wrong Results From Query Using INDEX JOIN (Doc ID 1050644.1)

Last updated on SEPTEMBER 15, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.7 to 11.2.0.1.0 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Symptoms

A Query returns Wrong Results.
The problem specifically arises post a column is added using  "ALTER TABLE ADD column".
Explain plan of the Query shows we are doing an Index Join.

Example :
~~~~~~~
 Column MISCSEC shows value (0) instead of correct value :

Testcase

we get wrong results when

SELECT SCDEFINITIONS.MISCSEC,SCDEFINITIONS.SCIK FROM SCDEFINITIONS WHERE
SCDEFINITIONS.MISCSEC != 0;

or

SELECT SCDEFINITIONS.MISCSEC,SCDEFINITIONS.SCIK FROM SCDEFINITIONS WHERE
SCDEFINITIONS.MISCSEC <> 0;

DIAGNOSTIC ANALYSIS:
--------------------
Expected results
----------------
MISCSEC SCIK
---------- ----------
65156 48
78769 39
440922 47

Wrong Results
-------------
MISCSEC SCIK
---------- ----------
0 48
0 39
0 47

We see index join when both columns in select

If we query
SELECT SCDEFINITIONS.MISCSEC,SCDEFINITIONS.SCIK FROM SCDEFINITIONS WHERE
SCDEFINITIONS.MISCSEC > 0;
Results correct

If we query
SELECT SCDEFINITIONS.MISCSEC FROM SCDEFINITIONS WHERE SCDEFINITIONS.MISCSEC
!= 0;
Results correct

P.N: Both the above do not join indexes



Execution plan for wrong results


| Id | Operation | Name | Rows | Bytes |Cost | Time |
-------------------------------------------------------------+
| 0 | SELECT STATEMENT | | | | 3 | |
| 1 | VIEW | index$_join$_001 | 3 | 18 | 3 | 00:00:01 |
| 2 | HASH JOIN | | | | | |
| 3 | INDEX FAST FULL SCAN | P_SCDEFINITIONS | 3 | 18 | 1 | 00:00:01 |
| 4 | INDEX FAST FULL SCAN | R_SCDEFINITIONS_MISCSEC| 3 | 18 | 1 | 00:00:01 |
---------------------------------------------------------
Predicate Information:
----------------------
2 - access(ROWID=ROWID)
4 - filter(NVL("SCDEFINITIONS"."MISCSEC",0)<>0)

Changes

Column was added using ALTER TABLE ADD earlier.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms