Different execution plans and joins using synonym or table Edit:

We found the leading cause. If you join tables with the same name but in different schemas you can get this result. Same with joining two tables using the same alias (whyever this works at all).



We have a very strange issue using Oracle 12.1.0.2.

This is the query:

SELECT * FROM OWNER1.TABLE1 JOIN OWNER2.TABLE2 USING (COLUMN1, COLUMN2, COLUMN3, COLUMN4) WHERE COLUMN1=10 AND COLUMN2='string' AND COLUMN3='01.04.2018' ORDER BY COLUMN3;


This results in a cartesian product of the two tables, returning a wrong dataset and thousands of rows.

This is the same query, but this time using the public synonym OWNER1TABLE1 for the table OWNER1.TABLE1:

SELECT * FROM OWNER1TABLE1 JOIN OWNER2.TABLE2 USING (COLUMN1, COLUMN2, COLUMN3, COLUMN4) WHERE COLUMN1=10 AND COLUMN2='string' AND COLUMN3='01.04.2018' ORDER BY COLUMN3;


This results in the correct data being returned (only 16 rows).

Oracle is also using two quite different execution plans:

Using the table name:

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
-------------------------------------------------------------
Plan hash value: 3737086414
-------------------------------------------------------------
| Id  | Operation                           | Name          |
-------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |
|   1 |  MERGE JOIN CARTESIAN               |               |
|   2 |   PARTITION RANGE SINGLE            |               |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| OWNER2.TABLE2 |
|   4 |     INDEX RANGE SCAN                | O2.IDX        |
|   5 |   BUFFER SORT                       |               |
|   6 |    PARTITION RANGE ALL              |               |
|   7 |     TABLE ACCESS FULL               | OWNER1.TABLE1 |
-------------------------------------------------------------


Using the public synonym:

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
---------------------------------------------------------------------
Plan hash value: 1141852644

---------------------------------------------------------------------
| Id  | Operation                                   | Name          |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |               |
|   1 |  HASH JOIN                                  |               |
|   2 |   PARTITION RANGE SINGLE                    |               |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| OWNER1.TABLE1 |
|   4 |     INDEX RANGE SCAN                        | OWNER1.PKY$T1 |
|   5 |   PARTITION RANGE SINGLE                    |               |
|   6 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| OWNER2.TABLE2 |
|   7 |     INDEX RANGE SCAN                        | O2.IDX        |
---------------------------------------------------------------------


The synonym is defined like this:

CREATE public synonym OWNER1TABLE1 FOR OWNER1.TABLE1;


And maybe the strangest thing is, if we define an alias (e.g. T1) for OWNER1.TABLE1 in the query like 

SELECT * FROM OWNER1.TABLE1 T1 JOIN OWNER2.TABLE2 USING (COLUMN1, COLUMN2, COLUMN3, COLUMN4) WHERE COLUMN1=10 AND COLUMN2='string' AND COLUMN3='01.04.2018' ORDER BY COLUMN3;


...it returns the correct data, too.

Anyone knows about a bug or something? Can't really be expected behaviour, can it?
Bug 18856999 - Wrong results (wrong cursor) with schema table masking public synonym (Doc ID 18856999.8)

There is a one-off patch for this with the same number for 12.1.0.2. The fix is in some later PSUs and starting with 12.2.0.1.

As usual, the above requires an active support subscription.