테이블에 종속된 보기에 테이블을 결합할 때 Oracle의 버그입니까?

테이블에 종속된 보기에 테이블을 결합할 때 Oracle의 버그입니까?

Oracle에서 버그로 생각되는 내용을 찾았는데 누락된 문서가 있는지 궁금합니다.

피들:Oracle: http://sqlfiddle.com/ #!4/43c19/2 SQL Server: http://sqlfiddle.com/ #!3/ddc49/1 MySql: http://sqlfiddle.com/ #!2/43c19/1

기본적으로 저는 보조 테이블에 조인을 남겨둔 메인 테이블을 가지고 있습니다.그리고 나서 저는 뷰에 조인을 남겼습니다.보조 테이블의 열이 null이 아닐 때만 조인할 뷰에 조인을 지정하면 예기치 않은 결과가 나타납니다.이는 쿼리를 표시하는 것으로 가장 잘 설명할 수 있습니다.

SELECT
  1,
  MainTable.*
FROM
  MainTable
  LEFT JOIN SecondaryTable ON MainTable.KeyColumn = SecondaryTable.KeyColumn
  LEFT JOIN ViewWithoutSecondary ON ((SecondaryTable.KeyColumn IS NOT NULL) AND SecondaryTable.KeyColumn = ViewWithoutSecondary.KeyColumn)
UNION ALL
SELECT
  2,
  MainTable.*
FROM
  MainTable
  LEFT JOIN SecondaryTable ON MainTable.KeyColumn = SecondaryTable.KeyColumn
  LEFT JOIN ViewWithSecondary ON ((SecondaryTable.KeyColumn IS NOT NULL) AND SecondaryTable.KeyColumn = ViewWithSecondary.KeyColumn)

직접 테스트할 생성 스크립트는 아래를 참조하십시오.SQL Server와 MySql에서는 동일한 결과를 얻지만 Oracle은 다릅니다.스키마에는 세 개의 테이블과 두 개의 뷰가 있습니다.보기는 다음과 같이 정의됩니다.

CREATE VIEW ViewWithoutSecondary
AS
SELECT
  TertiaryTable.KeyColumn,
  TertiaryValue + 1 ViewValue
FROM
  TertiaryTable

CREATE VIEW ViewWithSecondary
AS
SELECT
  SecondaryTable.KeyColumn,
  TertiaryValue + 1 ViewValue
FROM
  SecondaryTable
  LEFT JOIN TertiaryTable ON SecondaryTable.KeyColumn = TertiaryTable.KeyColumn;

Oracle에서 보기에 SecondaryTable에 대한 참조가 포함되어 있으면 MainTable에서 SecondaryTable에 일치하는 행만 가져옵니다.제가 보기에는 Oracle이 어떤 식으로든 뷰 코드에 밑줄을 그어서 행 중 하나가 누락된 것 같습니다.

MainTable에 3개의 행이 있는 경우 왼쪽 조인을 두 번 수행하면 최소 3개의 행과 조인 결과를 항상 반환해야 합니다.그러나 주어진 예에서는 그렇지 않습니다.

나는 알고 있습니다.SecondaryTable.KeyValue IS NOT NULL값이 null이면 절의 후반부가 사실이 아니기 때문에 중복되지만, 최적화 도구가 더 나은 계획을 세울 수 있도록 쿼리를 재작업하려고 했습니다.

예제를 실행하기 위한 전체 생성 스크립트는 다음과 같습니다.

CREATE TABLE MainTable
(
  KeyColumn varchar(32),
  ValueColumn varchar(32)
);

INSERT INTO MainTable VALUES ('123', 'abc');
INSERT INTO MainTable VALUES ('456', 'def');
INSERT INTO MainTable VALUES ('789', 'ghi');

CREATE TABLE SecondaryTable
(
  KeyColumn varchar(32),
  SecondaryValue integer  
);

INSERT INTO SecondaryTable VALUES ('123', 1);
INSERT INTO SecondaryTable VALUES ('456', 2);

CREATE TABLE TertiaryTable
(
  KeyColumn varchar(32),
  TertiaryValue integer  
);

INSERT INTO TertiaryTable VALUES ('123', 1);

CREATE VIEW ViewWithoutSecondary
AS
SELECT
  TertiaryTable.KeyColumn,
  TertiaryValue + 1 ViewValue
FROM
  TertiaryTable;

CREATE VIEW ViewWithSecondary
AS
SELECT
  SecondaryTable.KeyColumn,
  TertiaryValue + 1 ViewValue
FROM
  SecondaryTable
  LEFT JOIN TertiaryTable ON SecondaryTable.KeyColumn = TertiaryTable.KeyColumn;

쿼리에 대한 설명 계획을 실행하면 Oracle이 뷰를 인라인화하여 쿼리를 변환하고 있으며, 어떤 이유로 인해 왼쪽 아웃터가 아닌 줄 2에서 내부 조인을 수행하고 있음을 알 수 있습니다.

explain plan 
SET statement_id = 'no-hint' FOR  
SELECT 
  MainTable.*
FROM
  MainTable
  LEFT JOIN SecondaryTable ON MainTable.KeyColumn = SecondaryTable.KeyColumn
  LEFT JOIN ViewWithSecondary ON ((SecondaryTable.KeyColumn IS NOT NULL) AND SecondaryTable.KeyColumn = ViewWithSecondary.KeyColumn);

SELECT PLAN_TABLE_OUTPUT 
  FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'no-hint','TYPICAL'));


----------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |     2 |   108 |    20  (10)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER   |                |     2 |   108 |    20  (10)| 00:00:01 |
|*  2 |   HASH JOIN           |                |     2 |   108 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | SECONDARYTABLE |     2 |    36 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | MAINTABLE      |     3 |   108 |     3   (0)| 00:00:01 |
|   5 |   VIEW                |                |     1 |       |     7  (15)| 00:00:01 |
|*  6 |    FILTER             |                |       |       |            |          |
|*  7 |     HASH JOIN OUTER   |                |     1 |    36 |     7  (15)| 00:00:01 |
|*  8 |      TABLE ACCESS FULL| SECONDARYTABLE |     1 |    18 |     3   (0)| 00:00:01 |
|   9 |      TABLE ACCESS FULL| TERTIARYTABLE  |     1 |    18 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("MAINTABLE"."KEYCOLUMN"="SECONDARYTABLE"."KEYCOLUMN")
   6 - filter("SECONDARYTABLE"."KEYCOLUMN" IS NOT NULL)
   7 - access("SECONDARYTABLE"."KEYCOLUMN"="TERTIARYTABLE"."KEYCOLUMN"(+))
   8 - filter("SECONDARYTABLE"."KEYCOLUMN"="SECONDARYTABLE"."KEYCOLUMN")

이 문제에 대한 해결 방법은 NO_MERGE 힌트를 사용하는 것입니다.

SELECT /*+ NO_MERGE(ViewWithSecondary) */
  MainTable.*
FROM
  MainTable
  LEFT JOIN SecondaryTable ON MainTable.KeyColumn = SecondaryTable.KeyColumn
  LEFT JOIN ViewWithSecondary ON ((SecondaryTable.KeyColumn IS NOT NULL) AND SecondaryTable.KeyColumn = ViewWithSecondary.KeyColumn);

이렇게 하면 다음과 같은 예상 결과가 생성됩니다.

KEYCOLUMN                        VALUECOLUMN                    
-------------------------------- --------------------------------
123                              abc                              
456                              def                              
789                              ghi  

암시된 쿼리에 대한 쿼리 계획을 비교합니다.여기 2호선에서 왼쪽 바깥쪽 조인이 보입니다.

explain plan
SET statement_id = 'with-hint' FOR
SELECT /*+ NO_MERGE(ViewWithSecondary) */
  MainTable.*
FROM
  MainTable
  LEFT JOIN SecondaryTable ON MainTable.KeyColumn = SecondaryTable.KeyColumn
  LEFT JOIN ViewWithSecondary ON ((SecondaryTable.KeyColumn IS NOT NULL) AND SecondaryTable.KeyColumn = ViewWithSecondary.KeyColumn);

SELECT PLAN_TABLE_OUTPUT 
  FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'with-hint','TYPICAL'));  

--------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |     6 |   324 |    26   (8)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER    |                   |     6 |   324 |    26   (8)| 00:00:01 |
|*  2 |   HASH JOIN OUTER      |                   |     3 |   162 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL   | MAINTABLE         |     3 |   108 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | SECONDARYTABLE    |     2 |    36 |     3   (0)| 00:00:01 |
|   5 |   VIEW                 |                   |     2 |       |     7  (15)| 00:00:01 |
|*  6 |    FILTER              |                   |       |       |            |          |
|*  7 |     VIEW               | VIEWWITHSECONDARY |     2 |    36 |     7  (15)| 00:00:01 |
|*  8 |      HASH JOIN OUTER   |                   |     2 |    72 |     7  (15)| 00:00:01 |
|   9 |       TABLE ACCESS FULL| SECONDARYTABLE    |     2 |    36 |     3   (0)| 00:00:01 |
|  10 |       TABLE ACCESS FULL| TERTIARYTABLE     |     1 |    18 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("MAINTABLE"."KEYCOLUMN"="SECONDARYTABLE"."KEYCOLUMN"(+))
   6 - filter("SECONDARYTABLE"."KEYCOLUMN" IS NOT NULL)
   7 - filter("SECONDARYTABLE"."KEYCOLUMN"="VIEWWITHSECONDARY"."KEYCOLUMN")
   8 - access("SECONDARYTABLE"."KEYCOLUMN"="TERTIARYTABLE"."KEYCOLUMN"(+))

언급URL : https://stackoverflow.com/questions/27903303/is-this-a-bug-in-oracle-when-joining-a-table-to-a-view-that-depends-on-the-table