태그 보관물: sql-server-2014

sql-server-2014

기본 키의 자체 조인 = tN.Id N 개의 클러스터 된

N자체 조인으로 구성된이 쿼리를 고려하십시오 .

select
    t1.*
from [Table] as t1
join [Table] as t2 on
    t1.Id = t2.Id
-- ...
join [Table] as tN on
    t1.Id = tN.Id

N 개의 클러스터 된 인덱스 스캔 및 N-1 병합 조인으로 실행 계획을 생성합니다.

솔직히 모든 조인을 최적화하지 않고 클러스터 된 인덱스 스캔을 하나만 수행 해야하는 이유는 없습니다. 즉 원래 쿼리를 다음과 같이 최적화하십시오.

select
    t1.*
from [Table] as t1

질문

  • 조인이 최적화되지 않은 이유는 무엇입니까?
  • 모든 조인이 결과 집합을 변경하지 않는다고 말하는 것이 수학적으로 올바르지 않습니까?

에 테스트 :

  • 원본 서버 버전 : SQL Server 2014 (12.0.4213)
  • 소스 데이터베이스 엔진 에디션 : Microsoft SQL Server Standard Edition
  • 소스 데이터베이스 엔진 유형 : 독립형 SQL Server
  • 호환성 수준 : SQL Server 2008 (100)

쿼리는 의미가 없습니다. 그것은 단지 내 마음에 왔고 지금 그것에 대해 궁금합니다.

여기의 바이올린 테이블 작성 3 개 쿼리와 함께 다음과 함께 inner join의와 left join의 혼합. 실행 계획도 볼 수 있습니다.

것 같다 left join동안의이 결과 실행 계획에 제거 inner join의이 없습니다. 그래도 왜 그런지 모르겠다 .



답변

먼저 (id)테이블의 기본 키 라고 가정 합니다. 이 경우, 조인은 중복 (증명 될 수 있음)되어 제거 될 수 있습니다.

이제 그것은 단지 이론 또는 수학입니다. 옵티마이 저가 실제 제거를 수행하려면 이론이 코드로 변환되어 옵티마이 저의 최적화 / 재 작성 / 제거 제품군에 추가되어야합니다. 이를 위해서는 (DBMS) 개발자는 효율성에 좋은 이점이 있으며 일반적인 경우라고 생각해야합니다.

개인적으로는 하나처럼 들리지 않습니다 (충분히 흔함). 당신이 인정하는 것처럼, 쿼리는 어리석은 것처럼 보이고 검토자는 개선되고 중복 조인이 제거되지 않는 한 검토자가 검토를 통과해서는 안됩니다.

즉, 제거가 수행되는 유사한 쿼리가 있습니다. Rob Farley의 매우 멋진 관련 블로그 게시물이 있습니다 : SQL Server의 JOIN 단순화 .

이 경우 조인을 LEFT조인으로 변경하기 위해해야 ​​할 모든 것 입니다. dbfiddle.uk를 참조하십시오 . 이 경우 옵티마이 저는 결과를 변경하지 않고도 조인을 안전하게 제거 할 수 있음을 알고 있습니다. 단순화 논리는 매우 일반적이며 자체 조인에는 적합하지 않습니다.

물론 원래 쿼리에서 INNER조인을 제거해도 결과를 변경할 수 없습니다. 그러나 기본 키에서 자체 조인하는 것은 일반적이지 않으므로 옵티 마이저에는이 사례가 구현되어 있지 않습니다. 그러나 조인 된 열이 테이블 중 하나의 기본 키인 경우 조인 (또는 왼쪽 조인)하는 것이 일반적입니다 (그리고 종종 외래 키 제약 조건이 있음). 조인을 제거하는 두 번째 옵션이 있습니다. (자체 참조!) 외래 키 제약 조건을 추가합니다.

ALTER TABLE "Table"
    ADD FOREIGN KEY (id) REFERENCES "Table" (id) ;

그리고 짜잔, 조인이 제거됩니다! (같은 바이올린에서 테스트) : 여기

create table docs
(id int identity primary key,
 doc varchar(64)
) ;
GO
insert
into docs (doc)
values ('Enter one batch per field, don''t use ''GO''')
     , ('Fields grow as you type')
     , ('Use the [+] buttons to add more')
     , ('See examples below for advanced usage')
  ;
GO
4 행 영향을 받음
--------------------------------------------------------------------------------
-- Or use XML to see the visual representation, thanks to Justin Pealing and
-- his library: https://github.com/JustinPealing/html-query-plan
--------------------------------------------------------------------------------
set statistics xml on;
select d1.* from docs d1 
    join docs d2 on d2.id=d1.id
    join docs d3 on d3.id=d1.id
    join docs d4 on d4.id=d1.id;
set statistics xml off;
GO
아이디 | 문서
-: | : ----------------------------------------
 1 | 필드 당 하나의 배치를 입력하고 'GO'를 사용하지 마십시오
 2 | 입력 할 때 필드가 커짐
 3 | 더 추가하려면 [+] 버튼을 사용하십시오
 4 | 고급 사용법은 아래 예를 참조하십시오.

--------------------------------------------------------------------------------
-- Or use XML to see the visual representation, thanks to Justin Pealing and
-- his library: https://github.com/JustinPealing/html-query-plan
--------------------------------------------------------------------------------
set statistics xml on;
select d1.* from docs d1 
    left join docs d2 on d2.id=d1.id
    left join docs d3 on d3.id=d1.id
    left join docs d4 on d4.id=d1.id;
set statistics xml off;
GO
아이디 | 문서
-: | : ----------------------------------------
 1 | 필드 당 하나의 배치를 입력하고 'GO'를 사용하지 마십시오
 2 | 입력 할 때 필드가 커짐
 3 | 더 추가하려면 [+] 버튼을 사용하십시오
 4 | 고급 사용법은 아래 예를 참조하십시오.

alter table docs
  add foreign key (id) references docs (id) ;
GO
--------------------------------------------------------------------------------
-- Or use XML to see the visual representation, thanks to Justin Pealing and
-- his library: https://github.com/JustinPealing/html-query-plan
--------------------------------------------------------------------------------
set statistics xml on;
select d1.* from docs d1 
    join docs d2 on d2.id=d1.id
    join docs d3 on d3.id=d1.id
    join docs d4 on d4.id=d1.id;
set statistics xml off;
GO
아이디 | 문서
-: | : ----------------------------------------
 1 | 필드 당 하나의 배치를 입력하고 'GO'를 사용하지 마십시오
 2 | 입력 할 때 필드가 커짐
 3 | 더 추가하려면 [+] 버튼을 사용하십시오
 4 | 고급 사용법은 아래 예를 참조하십시오.


답변

관계형 용어로 속성 이름 바꾸기가없는 자체 조인은 아무 문제가 없으며 실행 계획에서 안전하게 제거 할 수 있습니다. 불행히도 SQL은 관계가 없으며 옵티마이 저가 자체 조인을 제거 할 수있는 상황은 소수의 경우로 제한됩니다.

SQL의 SELECT 구문은 프로젝션보다 조인 논리적 우선 순위를 제공합니다. 열 이름에 대한 SQL의 범위 지정 규칙과 중복 열 이름과 명명되지 않은 열이 허용된다는 사실은 관계형 대수의 최적화보다 SQL 쿼리 최적화를 상당히 어렵게 만듭니다. SQL DBMS 공급 업체는 한정된 리소스를 보유하고 있으며 지원하려는 최적화 종류를 선택해야합니다.


답변

기본 키는 항상 고유하며 널 (NULL) 값은 허용되지 않으므로 자체 참조 보조 키가없고 where 문이없는 기본 키에서 테이블 자체를 조인하면 원래 테이블과 동일한 수의 행이 생성됩니다.

그들이 그것을 최적화하지 않는 이유에 관해서는 그들이 계획하지 않았거나 사람들이하지 않을 것이라고 가정 한 경우라고 말합니다. 보장 된 고유 기본 키에서 테이블 자체를 조인하는 것은 목적을 달성하지 못합니다.


답변