hierarchyid 데이터 형식 사용(데이터베이스 엔진)

부모/자식 방법을 사용하면 각 행에 부모에 대한 참조가 포함됩니다. 다음 테이블에서는 하나의 부모/자식 관계에서 부모 및 자식 행을 포함하는 데 사용되는 일반적인 테이블을 정의합니다.

USE AdventureWorks ;
GO

CREATE TABLE ParentChildOrg
   (
    EmployeeID int PRIMARY KEY,
    ManagerId int REFERENCES ParentChildOrg(EmployeeID),
    EmployeeName nvarchar(50) 
   ) ;
GO

일반 작업에 대한 부모/자식과 hierarchyid 비교

• hierarchyid를 사용하면 하위 트리 쿼리가 훨씬 더 빨라집니다.

• hierarchyid를 사용하면 직계 하위 항목 쿼리가 약간 더 느려집니다.

• hierarchyid를 사용하면 리프가 아닌 노드 이동이 더 느려집니다. hierarchyid를 사용할 경우 리프가 아닌 노드 삽입과 리프 노드의 삽입 및 이동의 복잡성은 동일합니다.

다음 경우 부모/자식이 더 우수할 수 있습니다.

• 키의 크기가 매우 중요한 경우. 노드 수가 같은 경우 hierarchyid 값은 정수 패밀리(smallint, int, bigint) 값보다 크거나 같습니다. hierarchyid에서는 부모/자식 구조를 사용할 때 필요한 공통 테이블 식보다 I/O 및 CPU 복잡성의 효율이 훨씬 증가하므로 이 경우에 한해 부모/자식을 많이 사용합니다.

• 계층의 섹션 간 쿼리를 거의 하지 않는 쿼리의 경우. 즉, 쿼리가 일반적으로 계층의 단일 지점만 다루는 경우입니다. 이러한 경우 같은 위치에 배치하는 작업이 중요하지 않습니다. 예를 들어 조직 테이블이 개별 직원에 대한 급여를 실행하는 데에만 사용되는 경우 부모/자식이 더 우수합니다.

• 리프가 아닌 하위 트리가 자주 이동하며 성능이 매우 중요한 경우. 부모/자식 표현의 경우 한 계층에서 한 행의 위치를 변경하면 단일 행에 영향을 줍니다. 그러나 hierarchyid 사용 시 한 행의 위치를 변경하면 n개의 행에 영향을 줍니다. 여기서 n은 이동하는 하위 트리의 노드 수입니다.

  리프가 아닌 하위 트리가 자주 이동하며 성능이 매우 중요하지만 대부분의 이동이 잘 정의된 계층 수준에서 발생하는 경우에는 상위 수준과 하위 수준을 두 개의 계층으로 분할하십시오. 이렇게 하면 모든 트리가 상위 계층의 리프 수준으로 이동됩니다. 예를 들어 서비스에서 호스팅하는 웹 사이트 계층의 경우 사이트에는 계층으로 정렬된 많은 페이지가 있습니다. 호스팅된 사이트는 사이트 계층의 다른 위치로 이동될 수 있지만 하위 페이지는 거의 다시 정렬되지 않습니다. 이는 다음을 통해 나타낼 수 있습니다.

  CREATE TABLE HostedSites 
     (
      SiteId hierarchyid, PageId hierarchyid
     ) ;
  GO
  

XML 문서는 트리이므로 단일 XML 데이터 형식 인스턴스가 전체 계층을 나타낼 수 있습니다. SQL Server에서 XML 인덱스가 생성될 때 hierarchyid 값이 내부적으로 사용되어 계층의 위치를 나타냅니다. 

다음 경우 XML 데이터 형식이 더 우수할 수 있습니다.

• 전체 계층이 항상 저장되고 검색되는 경우

• 응용 프로그램에서 데이터를 XML 형식으로 사용하는 경우

• 조건자 검색이 매우 제한되어 있으며 성능이 중요하지 않은 경우

예를 들어 응용 프로그램에서 여러 조직을 추적하고 전체 조직 계층을 항상 저장 및 검색하며 단일 조직만 쿼리하지 않는 경우 다음 형식의 테이블이 적합할 수 있습니다.

CREATE TABLE XMLOrg 
    (
    Orgid int,
    Orgdata xml
    ) ;
GO

E가 열이나 변수로 지정되면 E.IsDescendantOf(c)가 범위 검색으로 변환됩니다. 이렇게 하면 하위 항목 검색 비용이 상당히 줄어듭니다. E에 너비 우선 인덱스가 있는 경우 E의 모든 하위 항목이 같은 위치에 배치되므로 이 변환이 도움이 됩니다. 예를 들어 코드 조각 EmployeeId.IsDescendantOf(@value)는 EmployeeId >= @Value AND EmployeeId <= @Value.DescendantLimit()으로 실행됩니다. DescendantLimit은 노드의 모든 가능한 하위 항목에 대한 최소 상한을 결정하는 내부 메서드입니다. 여기서 @value가 매개 변수일 필요는 없으며 조인 조건의 열일 수도 있습니다.

**GetAncestor(n)**는 노드의 n번째 상위 항목을 제공합니다. 이는 보다 일반적인 IsDescendantOf와 달리 두 노드 간에 정확한 관계(부모, 자식, 최상위 항목 등)가 필요한 경우 유용합니다.

예를 들어 직속 관리자가 @value인 직원을 모두 찾으려면 다음 쿼리를 실행합니다.

SELECT * FROM Employees WHERE EmployeeId.GetAncestor(1) = @value

이는 원래 조건자를 잔여 조건자로 사용하여 @value의 하위 항목에 대한 범위 검색으로 변환됩니다. 코드는 다음과 같이 변환됩니다.

SELECT * FROM Employees 
WHERE 
   EmployeeId >= @Value AND EmployeeId <= @value.DescendantLimit() 
   AND EmployeeId.GetAncestor(1) = @value

따라서 검색이 @value의 하위 트리로 제한됩니다.

위의 쿼리에서 @value가 트리의 상위 수준에 있는 경우 위의 최적화 작업으로 검색 행 수를 현저하게 줄일 수 없습니다. n번째 상위 항목에 대한 질문이 일반적인 경우 이전에 설명한 대로 응용 프로그램에서 너비 우선 인덱스를 만들어야 합니다.

너비 우선 인덱스가 있는 경우 위의 쿼리는 다음과 같이 추가로 변환됩니다.

SELECT * FROM Employees 
WHERE 
   EmployeeId >=@value AND EmployeeId <= @Value.DescendantLimit() 
   AND @value.GetLevel()+1 = EmployeeId.GetLevel()

GetLevel 메서드를 포함하는 마지막 줄은 너비 우선 인덱스에서 인덱스 조회가 됩니다. EmployeeId가 클러스터링 키인 경우 이는 너비 우선 인덱스의 두 번째 열이 되며 두 개의 조건자는 같은 위치에 배치된 @value에 대한 정확히 n개의 직접 보고를 지정하는 인덱스 조회가 됩니다.

GetAncestor 변환은 직계 부모에 대한 쿼리로 제한되지 않습니다. GetAncestor에 대한 인수는 모든 변수 또는 상수일 수 있습니다.