계층형 쿼리 (Hierarchical Query)
관계형 데이터베이스는 2차원 테이블구조. 실무에선 계층적 데이터구조를 많이 활용함.
특정 컬럼을 통해 데이터간의 부모-자식 관계를 표현하는 관계를 순환관계(Recursive Relationship)라 함.
ex) 군부대 관련 데이터베이스에서 상위부대번호(PK)를 부대의 부모키로 지정하여 부대간 계층 관계 표현
Hierarchyid
- MS-SQL에서는 계층적 데이터구조의 구현에 Hierarchyid라는 데이터타입을 지원함. (부모키 방식은 대체적으로 활용)
- Hierarchyid는 계층적 데이터의 가장 일반적인 형식으로 트리 계층에서 노드의 위치를 나타냄.
- Hierarchyid 데이터타입의 인덱스는 데이터 저장시 깊이 우선(Depth First) 저장을 지원함.
ex) A와 B가 특정 노드의 hierarchyid값일 때 A<B는 깊이 우선 트리구조에서 A가 B의 상위 노드로 위치한다는 것을 의미함.
이때 hierarchyid 인덱스를 적용할 경우 B는 A의 위치에 인접하게 저장됨.
Hierarchyid의 제한사항 (이해한 것만 기록함;)
- UNIQUE 제약조건을 사용하거나 자체 논리를 통해 고유성을 적용하지 않는 한 기본 고유성 보장X.
- 외래키(Foreign Key)와 적용방식이 다름. A에 하위노드 B가 존재하는 상태에서 A를 삭제 가능.
Hierarchyid를 활용한 계층적 데이터구조의 인덱싱 방법
1) 깊이 우선 인덱스 (Depth-First Index) CREATE CLUSTERED INDEX Index_Org_DepthFirst ON dbo.EmployeeOrg(hierarchyid);  - Hierarchyid 자체에 인덱스를 적용.
- 하위 트리의 행이 서로 가깝게 저장됨. ex) 같은 관리자에게 직접 보고하는 직원의 레코드는 가깝게 저장 |
2) 너비 우선 인덱스 (Breadth-First Index) CREATE CLUSTERED INDEX Index_Org_BreadthFirst ON dbo.EmployeeOrg(hierarchyid);  - 노드의 Level과 Hierarchyid에 인덱스를 적용. - 계층의 각 수준에 행을 함께 저장함. ex) 계층 구조상 동일한 레벨의 직원의 레코드는 가깝게 저장 . |
계층 메서드의 활용 (링크 : MS-SQL Server TechNote)
테이블의 생성
- OrgNode는 계층관계를 저장하는 hierarchyid열이다.
- OrgLevel은 너비 우선 인덱스를 사용하기 위한 계산열로, OrgNode를 기반으로 한다.
- 테이블 상 EmployeeID가 존재하나, hierarchyid를 PK로 사용 가능하므로 원칙적으로는 필요하지 않다.
- 테이블에는 OrgNode, OrgLevel, EmployeeID, EmpName(사원명), Title(직책)이 포함된다.
CREATE TABLE EmployeeOrg
(
OrgNode hierarchyid PRIMARY KEY CLUSTERED,
OrgLevel AS OrgNode.GetLevel(),
EmployeeID int UNIQUE NOT NULL,
EmpName varchar(20) NOT NULL,
Title varchar(20)
);
GO
효율적인 너비 우선 검색을 위한 복합 인덱스 생성
CREATE UNIQUE INDEX Index_Org_BreadthFirst
ON dbo.EmployeeOrg(OrgLevel, OrgNode)l
GO
GetRoot()메서드를 활용을 통한 루트 노드 삽입 (링크 : GetRoot())
- 데이터 구조상 가장 최상위인 루트에 총 마케팅 매니저인 David의 데이터를 입력한다.
- OrgLevel 항목은 계산 열이므로 INSERT문에서 제외.
- OrgNode 항목은 GetRoot()메서드를 사용하여 계층의 루트값을 입력한다.
INSERT dbo.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager');
GO
ToString()메서드를 활용한 노드의 계층 구조 확인 (링크 : ToString()메서드)
- ToString()메서드를 적용해 계층 구조를 문자열로 풀어 확인해본다. 질의문과 결과집합은 다음과 같다.
SELECT OrgNode.ToString() AS Text_OrgNode, OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM dbo.EmployeeOrg;
GO
Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
------------- -------- -------- ----------- ---------- -------------------
/ Ox 0 6 David Marketing Manager
자신의 hierarchyid를 기준으로 하위 계층의 hierarchyid를 알려주는 GetDescendant()메서드 (링크 : GetDescendant())
- GetDescendant(hid1, hid2)메서드는 인자로 두 개의 hierarchyid를 받으며, 투입된 인자에 따른 결과는 다음과 같다.
(1) 부모(this)가 NULL : NULL 리턴
(2) 부모가 NULL이 아니고 hid1과 hid2가 모두 NULL : 부모의 하위 계층의 hid 리턴
(3) 부모와 hid1이 NULL이 아니고 hid2가 NULL : 부모의 하위 계층이며 hid1보다 큰 hid 리턴
(4) 부모와 hid2가 NULL이 아니고 hid1이 NULL : 부모의 하위 계층이며 hid2보다 작은 hid 리턴
(5) 부모와 hid1, hid2가 모두 NULL이 아님 : 부모의 하위 계층이며 hid1보단 크고 hid2보단 작은 hid 리턴
최초의 루트 하위 노드 생성
- 루트를 제외한 행이 아직 없으므로 GetDescendant(NULL, NULL)을 적용
- David에게 보고하는 마케팅전문가 Sariya의 데이터 삽입
DECLARE @Manager hierarchyid
SELECT @Manager = hierarchyid::GetRoot() - GetRoot()메서드를 통해 루트hid 할당
FROM dbo.EmployeeOrg;
INSERT dbo.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES (@Manager.GetDescendant(NULL, NULL), 46, 'Sariya', 'Marketing Specialist'); - GetDescendant(NULL, NULL)
GO hid1과 hid2가 모두 NULL이므로 루트의
바로 하위 계층인 hid를 리턴한다.
결과
새로운 직원 데이터 입력을 위한 일련의 코드를 GetAncestor()메서드를 활용한 저장 프로시저로 정의 (링크 : GetAncestor())
- 새 직원의 관리자에게 지정된 EmployeeID, 새 직원의 EmployeeID, 해당 직원의 이름과 직책만 입력받는다.
CREATE PROC AddEmp (@mgrid int, @empid int, @empName varchar(20), @title varchar(20))
AS
BEGIN
DECLARE @mOrgNode hierarchyid, @lc hierarchyid
SELECT @mOrgNode = OrgNode
FROM dbo.EmployeeOrg
WHERE EmployeeID = @mgrid
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @lc = max(OrgNode)
FROM dbo.EmployeeOrg
WHERE OrgNode.GetAncestor(1) = @mOrgNode;
INSERT dbo.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES (@mOrgNode.GetDescendant(@lc, NULL), @empid, @empName, @title)
COMMIT
END;
GO
생성한 저장 프로시저를 실행하여 David에게 직접 또는 간접적으로 보고하는 직원 4명을 추가
EXEC AddEmp 6, 271, 'John', 'Marketing Specialist';
EXEC AddEmp 6, 119, 'Jill', 'Marketing Specialist';
EXEC AddEmp 46, 269, 'Wanida', 'Marketing Assistant';
EXEC AddEmp 271, 272, 'Mary', 'Marketing Assistant';
결과
