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

hierarchyid 데이터 형식은 시스템에서 제공됩니다. hierarchyid를 데이터 형식으로 사용하여 계층 구조가 있는 테이블을 만들거나 다른 위치에 있는 데이터의 계층 구조를 참조할 수 있습니다. hierarchyid 함수를 사용하여 Transact-SQL을 통해 계층적 데이터로 쿼리하고 작업할 수 있습니다.

계층적 데이터는 계층 관계를 통해 서로 관련된 데이터 항목 집합으로 정의됩니다. 계층 관계는 데이터의 한 항목이 다른 항목의 부모인 관계입니다. 계층적 데이터는 데이터베이스에서 일반적이며 이러한 데이터의 예는 다음과 같습니다.

• 조직 구조

• 파일 시스템

• 프로젝트의 태스크 집합

• 언어 용어의 분류

• 웹 페이지 간 링크의 그래프

SQL Server 2008에서 새로 도입된 hierarchyid 형식을 사용하면 계층적 데이터를 보다 쉽게 저장하고 쿼리할 수 있습니다. hierarchyid는 계층적 데이터의 가장 일반적인 형식으로, 트리를 나타내기 위해 최적화되었습니다.

hierarchyid의 주요 속성

hierarchyid 데이터 형식의 값은 트리 계층의 위치를 나타냅니다. hierarchyid 값의 속성은 다음과 같습니다.

• 높은 압축성

  n개 노드가 포함된 트리에서 노드를 나타내는 데 필요한 평균 비트 수는 평균 fanout(노드의 평균 자식 수)에 따라 달라집니다. 작은 fanout(0-7)의 경우 크기는 약 6*logAn비트입니다. 여기서 A는 평균 fanout입니다. 평균 fanout 수준이 6인 100,000명으로 구성된 조직 계층의 노드는 약 38비트를 사용합니다. 이는 저장을 위해 40비트나 5바이트로 반올림됩니다.

• 깊이 우선 순서로 비교

  두 개의 hierarchyid 값이 a와 b인 경우 a<b는 깊이 우선 트리 탐색에서 a가 b 앞에 온다는 의미입니다. hierarchyid 데이터 형식의 인덱스에는 깊이 우선 순서가 사용되며 깊이 우선 탐색에서 서로 가까이 있는 노드는 서로 가깝게 저장됩니다. 예를 들어 레코드의 자식은 해당 레코드에 인접하게 저장됩니다.

• 임의 삽입 및 삭제 지원

  GetDescendant 메서드를 사용하면 지정한 노드의 오른쪽, 지정한 노드의 왼쪽 또는 두 형제 사이에 형제를 생성할 수 있습니다. 임의 개수의 노드를 계층에서 삽입하거나 삭제할 때 비교 속성이 유지됩니다. 대부분의 삽입 및 삭제 시 압축성 속성이 유지됩니다. 그러나 두 노드 간 삽입 시에는 약간 덜 압축된 표현으로 hierarchyid 값이 생성됩니다.

hierarchyid의 제한 사항

hierarchyid 데이터 형식의 제한 사항은 다음과 같습니다.

• hierarchyid 형식의 열은 자동으로 트리를 나타내지 않습니다. 응용 프로그램에 따라 원하는 행 간 관계가 값에 반영되도록 hierarchyid 값이 생성되어 할당됩니다. 일부 응용 프로그램에서는 hierarchyid 형식의 열이 트리를 표현하지 않을 수도 있습니다. 이 경우 해당 값은 다른 테이블에 정의된 계층의 위치에 대한 참조일 수 있습니다.

• 응용 프로그램에 따라 hierarchyid 값이 생성되어 할당되는 작업에 대한 동시성이 달리 관리됩니다. 응용 프로그램에서 UNIQUE KEY 제약 조건을 사용하거나 자체 논리를 통해 자체적으로 고유성을 적용하지 않는 한 열의 hierarchyid 값에 대한 고유성이 보장되지 않습니다.

• hierarchyid 값이 나타내는 계층 관계는 외래 키 관계와 같은 방식으로 적용되지 않습니다. A에 자식 B가 있는 상태에서 A를 삭제하여 B에 존재하지 않는 레코드에 대한 관계가 남게 되는 계층 관계를 만들 수 있으며 이러한 관계가 적절한 경우도 있습니다. 이 동작이 허용되지 않는 경우에는 응용 프로그램에서 부모를 삭제하기 전에 하위 항목에 대해 쿼리해야 합니다.

인덱싱 방법

계층적 데이터의 인덱싱 방법에는 두 가지가 있습니다.

• 깊이 우선

  깊이 우선 인덱스에서는 하위 트리의 행이 서로 가깝게 저장됩니다. 예를 들어 한 관리자를 통해 보고하는 모든 직원은 해당 관리자의 레코드에 가깝게 저장됩니다.

• 너비 우선

  너비 우선은 계층의 각 수준에 행을 함께 저장합니다. 예를 들어 같은 관리자에게 직접 보고하는 직원의 레코드는 서로 가깝게 저장됩니다.

예

GetLevel() 메서드를 사용하여 너비 우선 순서를 만들 수 있습니다. 다음 예에서는 너비 우선 인덱스와 깊이 우선 인덱스를 모두 만듭니다.

USE AdventureWorks2008R2 ; 
GO

CREATE TABLE Organization
   (
    BusinessEntityID hierarchyid,
    OrgLevel as BusinessEntityID.GetLevel(), 
    EmployeeName nvarchar(50) NOT NULL
   ) ;
GO

깊이 우선 인덱스에서는 노드의 하위 트리에 있는 모든 노드가 같은 위치에 배치됩니다. 따라서 "이 폴더 및 해당 하위 폴더에서 모든 파일 찾기"와 같은 하위 트리에 대한 쿼리에 답하는 데에는 깊이 우선 인덱스가 효율적입니다.

CREATE CLUSTERED INDEX Org_Breadth_First 
ON Organization(OrgLevel,BusinessEntityID) ;
GO

CREATE UNIQUE INDEX Org_Depth_First 
ON Organization(BusinessEntityID) ;
GO

너비 우선 인덱스에서는 노드의 모든 직계 자식이 같은 위치에 배치됩니다. 따라서 "이 관리자에게 직접 보고하는 모든 직원 찾기"와 같은 인접한 자식에 대한 쿼리에 답하는 데에는 너비 우선 인덱스가 효율적입니다.

깊이 우선을 사용할 것인지, 너비 우선을 사용할 것인지 또는 둘 다를 사용할 것인지 여부와 클러스터링 키(있는 경우)를 만들 인덱스는 위의 쿼리 형식에 대한 상대적 중요도와 SELECT 및 DML 작업의 상대적 중요도에 따라 달라집니다. 인덱싱 방법에 대한 자세한 예는 자습서: hierarchyid 데이터 형식 사용을 참조하십시오.

hierarchyid에 대한 대체 방법을 사용하는 경우

계층적 데이터를 나타내는 데 hierarchyid 외에 다음 두 가지 방법을 사용할 수 있습니다.

• 부모/자식

• XML

hierarchyid는 일반적으로 이러한 대체 방법보다 우수합니다. 그러나 대체 방법을 사용하는 것이 더 나은 상황도 있으며 아래에서는 이에 대해 설명합니다.

부모/자식

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

USE AdventureWorks2008R2 ;
GO

CREATE TABLE ParentChildOrg
   (
    BusinessEntityID int PRIMARY KEY,
    ManagerId int REFERENCES ParentChildOrg(BusinessEntityID),
    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 문서는 트리이므로 단일 XML 데이터 형식 인스턴스가 전체 계층을 나타낼 수 있습니다. SQL Server에서 XML 인덱스가 생성될 때 hierarchyid 값이 내부적으로 사용되어 계층의 위치를 나타냅니다.

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

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

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

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

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

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

부모/자식에서 hierarchyid로 마이그레이션

오늘날 대부분의 트리는 부모/자식을 사용하여 표현됩니다. 부모/자식 구조를 hierarchyid를 사용하는 테이블로 마이그레이션하는 가장 쉬운 방법은 임시 열이나 임시 테이블을 사용하여 계층의 각 수준에서 노드 수를 추적하는 것입니다. 부모/자식 테이블 마이그레이션의 예는 자습서: hierarchyid 데이터 형식 사용의 1단원을 참조하십시오.

hierarchyid의 쿼리 변환

계층 쿼리의 성능을 최대화하기 위해 SQL Server에서는 hierarchyid 관련 쿼리의 3가지 변환을 자동으로 수행합니다. 이러한 변환의 결과는 변환된 쿼리의 실행 계획 출력에서 확인할 수 있습니다.

GetAncestor를 범위 검색 및 잔여 조건자로 변환

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

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

DECLARE @value hierarchyid ;
SELECT * FROM AdventureWorks2008R2.HumanResources.Employee 
WHERE  OrganizationNode.IsDescendantOf(@value) = 1;

참고 항목

참조

hierarchyid(Transact-SQL)

개념

hierarchyid 데이터 작업

hierarchyid 데이터 형식 메서드 참조

자습서: hierarchyid 데이터 형식 사용