Oracle SQL SELECT unexpected behaviour

Question

In legacy code I have found some strange SQL SELECT query which caused bug in our application. My Oracle version is 11.2.0.1.0. Below (simplified) code:

DDL:

DROP TABLE A;
DROP TABLE B;
DROP TABLE C;

CREATE TABLE "A"
  (
    "ID_B"              NUMBER NOT NULL
  );

CREATE INDEX "A_INDEX" ON "A"("ID_B");

CREATE TABLE "B"
  (
    "ID"                NUMBER NOT NULL,
    "NAME"              VARCHAR2(50 BYTE),
    "SURNAME"           VARCHAR2(50 BYTE),
    CONSTRAINT "PK_B" PRIMARY KEY ("ID")
  );

CREATE TABLE "C"
  (
    "ID"                NUMBER NOT NULL
  );


INSERT INTO A(ID_B) VALUES (10);
INSERT INTO B(ID, SURNAME, NAME) VALUES(10, 'LUCKY', 'LUKE');
COMMIT;

SELECT:

SELECT COUNT(*)
FROM
  (
    SELECT 
      B.ID,
      B.SURNAME,
      B.NAME
    FROM A
      LEFT JOIN B ON B.ID = A.ID_B
      LEFT OUTER JOIN (SELECT * FROM C WHERE ID = 10) C ON  1 = 1
    WHERE A.ID_B = 10
  );

PROBLEM: Could You help me understand why select count(*) returns 0 as result by sub-query returns 1 result row? When I drop 'A_INDEX' both select works fine (count(*) returns 1).

Marmite Bomber · Accepted Answer

Without the index the query with count(*) hat this execution plan in Linux: Version 11.2.0.3.0

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    15 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |      |     1 |    15 |            |          |
|   2 |   MERGE JOIN OUTER    |      |     1 |    15 |     5   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL  | A    |     1 |    13 |     3   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |      |     1 |     2 |     2   (0)| 00:00:01 |
|   5 |     VIEW              |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS FULL| C    |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("A"."ID_B"=10)
   6 - filter("ID"=10)

Note that Oracle doesn't access table B and performs outer join on A and C, which leads to result 1 - correct

With the index on A - Oracle open CARTESIAN JOIN which leads to result 1 * 0 = 0 (this join is not OUTER)

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |    15 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |         |     1 |    15 |            |          |
|   2 |   MERGE JOIN CARTESIAN|         |     1 |    15 |     2   (0)| 00:00:01 |
|   3 |    VIEW               |         |     1 |     2 |     2   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL | C       |     1 |    13 |     2   (0)| 00:00:01 |
|   5 |    BUFFER SORT        |         |     1 |    13 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN  | A_INDEX |     1 |    13 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("ID"=10)
   6 - access("A"."ID_B"=10)

Sorry for the bad news, IMO this is a wrong optimization in Oracle, the only chance is to open SR (or to workaround it with drop index or reformulate the query).

For completness, the plan for the query with the hint /*+ NO_QUERY_TRANSFORMATION */ The table B is now accessed and both joins are outer, so it works fine.

------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     1 |    80 |     4   (0)| 00:00:01 |
|   1 |  MERGE JOIN OUTER              |         |     1 |    80 |     4   (0)| 00:00:01 |
|   2 |   MERGE JOIN OUTER             |         |     1 |    80 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN            | A_INDEX |     1 |    13 |     1   (0)| 00:00:01 |
|   4 |    BUFFER SORT                 |         |     1 |    67 |     1   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID| B       |     1 |    67 |     1   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN         | PK_B    |     1 |       |     0   (0)| 00:00:01 |
|   7 |   BUFFER SORT                  |         |     1 |       |     3   (0)| 00:00:01 |
|   8 |    VIEW                        |         |     1 |       |     2   (0)| 00:00:01 |
|*  9 |     TABLE ACCESS FULL          | C       |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."ID_B"=10)
   6 - access("B"."ID"(+)=10)
   9 - filter("ID"=10)

Oracle SQL SELECT unexpected behaviour

Answers (2)

Related Questions