Oracle 계층적 질의문과 import & export

카테고리 없음

Oracle 계층적 질의문과 import & export

JangGeonWu 2022. 8. 17. 12:09

계층적 질의문은 코딩테스트에도 쓰이니 잘 숙지해야 한다.
import랑 export는 글쎄... 그래도 숙지하면 언젠간 쓸 것 같다.

계층적 질의문

관계형 DB에서, 데이터 간 부모 관계를 표현할 수 있는 칼럼을 지정해 계층적인 관계를 표현한다.
셀프 조인과 유사하나 결이 다르다.

계층적 질의문은 SELECT문에서 START WITH와 CONNECT BY 절을 이용한다.

SELECT [LEVEL], COLs... -- LEVEL: 계층별로 레벨 표시, 루트는 1에서 시작해 1씩 증가
FROM table
WHERE condition
START WITH condition -- 계층적 출력 형식 표현
CONNECT BY PRIOR condition; -- 계층 관계의 데이터를 지정

CONNECT BY 절의 'PRIOR col1=col2'를 어떻게 정의하느냐에 따라 형식이 달라진다.

top-down 형식

CONNECT BY PRIOR 자식_키 = 부모_키

2. bottom-up 형식

CONNECT BY PRIOR 부모_키 = 자식_키

다음 예시를 통해 어떻게 쓰이는 지 알아보자.

SELECT  LEVEL, LPAD(' ',(LEVEL - 1) * 2) || dname as "조직도"
FROM    department
START WITH deptno = 10
CONNECT BY PRIOR deptno = college;

deptno는 부서 번호, college는 단과대학(번호)을 의미한다. 단과대학(번호)이 부서번호의 '부모 키'이므로, top-down 형식이라는 점을 알 수 있다.
START WITH deptno = 10은, 루트의 시작점의 조건을 의미한다. 즉, top에 해당하는 deptno = 10이라는 뜻.

계층적 질의문을 DUAL에서 사용하는 방법

SELECT LEVEL-1 AS NUM -- 계층 레벨에 별명을 붙여 표현
FROM DUAL -- 테이블 DUAL에는 start 지점이 없다.
CONNECT BY LEVEL <= 24; -- 계층 관계가 없으므로 PRIOR가 붙지 않음, 조건절만 수행한다.

위에서 중요하게 바라봐야 할 것은, CONNECT BY의 PRIOR는 뒤의 'condition'에 맞춰진다는 점이다.

예를 들면, 아래의 두 계층적 질의문은 결과가 달라진다.

-- 1번
SELECT  level, deptno, dname, college
FROM    department
START WITH deptno = 10
CONNECT BY dname != '정보미디어학부' AND prior deptno = college;

-- 2번
SELECT  level, deptno, dname, college
FROM    department
START WITH deptno = 10
CONNECT BY prior dname != '정보미디어학부' AND deptno = college;

위의 두 질의문 중 정답은 1번이다. 즉, prior는 두 column간의 부모-자식 관계를 구분할 때 쓰인다.

connect_by_root, connect_by_isleaf, connect_by_path

connect_by_root : level이 1인 최상위 로우의 '정보'를 얻어온다.
connect_by_isleaf : 해당 row의 최하위레벨(자식 노드가 없는 경우) 여부(False = 0, True = 1)를 반환한다.
connect_by_path : 로우의 path 정보를 반환한다.

-- connect_by_root: level이 1인 최상위 로우의 정보를 얻어올 수 있다.
SELECT LPAD(' ', 4*(level - 1)) || ename as "사원명",
    empno as "사번",
    CONNECT_BY_ROOT empno "최상위사번",
    LEVEL
FROM emp
START WITH job = UPPER('President')
CONNECT BY PRIOR empno = mgr;

-- connect_by_isleaf: 로우의 최하위레벨 '여부(False/True)'를 반환한다.
SELECT LPAD(' ', 4*(level - 1)) || ename as "사원명",
    empno as "사번",
    CONNECT_BY_ISLEAF Leaf_YN, -- 자신이 단말(leaf)인지 0,1로 표시
    LEVEL
FROM emp
START WITH job = UPPER('President')
CONNECT BY PRIOR empno = mgr;

-- SYS_CONNECT_BY_PATH: 로우의 path 정보를 반환한다.
SELECT LPAD(' ', 4*(level - 1)) || ename as "사원명",
    empno as "사번",
    SYS_CONNECT_BY_PATH(ename,'/') e_path, -- sys_connect_by_path는 함수이며, 경로를 칼럼과 입력한 특수문자로 표시한다,
    LEVEL
FROM emp
START WITH job = UPPER('President')
CONNECT BY PRIOR empno = mgr;

해당 변수는 조건절에 사용이 가능하다.

SELECT LPAD(' ', 4*(level - 1)) || ename as "사원명",
    empno as "사번",
    CONNECT_BY_ISLEAF Leaf_YN, -- 자신이 단말(leaf)인지 0,1로 표시
    LEVEL
FROM emp
WHERE connect_by_isleaf = 1 -- 단말인 row만 표시한다.
START WITH job = UPPER('President')
CONNECT BY PRIOR empno = mgr;

ORDER 'SIBLINGS' BY

트리 구조는 그대로 두고, sibling 관계 내에서 정렬한다.

해석하자면, 다음 방식으로 order를 진행하면 트리 구조가 깨진다.

SELECT LPAD(' ', 4*(level - 1)) || ename as "사원명",
    empno as "사번",
    CONNECT_BY_ISLEAF Leaf_YN, -- 자신이 단말(leaf)인지 0,1로 표시
    LEVEL 
FROM emp
START WITH job = UPPER('President')
CONNECT BY PRIOR empno = mgr
ORDER BY ename; -- 트리 파괴

다음 방식으로 order를 진행해야 트리 구조가 유지된다.

SELECT LPAD(' ', 4*(level - 1)) || ename as "사원명",
    empno as "사번",
    CONNECT_BY_ISLEAF Leaf_YN, -- 자신이 단말(leaf)인지 0,1로 표시
    LEVEL
FROM emp
START WITH job = UPPER('President')
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename; -- 트리 유지

CYCLE 조건

connect_by_iscycle: 부모-자식간의 관계가 cycle을 만들면 1, 아니면 0을 반환한다.
CONNECT BY 절에 "NOCYCLE PRIOR"을 조건으로 '루프'를 무한하게 출력하는 상황을 방지할 수도 있다. 아래와 같이 말이다.

SELECT LPAD(' ', 4*(level - 1)) || ename as "사원명",
    empno as "사번",
    CONNECT_BY_ISLEAF Leaf_YN, -- 자신이 단말(leaf)인지 0,1로 표시
    LEVEL
FROM emp
START WITH job = UPPER('President')
CONNECT BY NOCYCLE PRIOR empno = mgr
ORDER SIBLINGS BY ename; -- 트리 유지

EXPORT and IMPORT

데이터베이스 간 데이터 정보를 쉽게 이동시킬 수 있는 간단한 방법을 제공한다.

주로 다음과 같은 상황에서 쓰인다.

테이블 재구성 할 때
Row Migration이 많이 발생한 경우
빈 Block이 많은 경우
Fragmentation이 많이 발생한 경우
경합을 최소화하기 위해서
사용자간에 데이터를 이동하기 위해(사용자 계정을 제거하거나 정보 분배할 때)
데이터베이스 간에 데이터를 이동하기 위해
개발 데이터베이스에서 실제 서비스가 이루어지는 데이터베이스로 데이터를 이전할 때
다른 플랫폼으로 데이터를 이전할 때
데이터베이스 버전 변경을 위해서 '백업'할 때 등에 사용

어... 위에서 Row Migration이랑 Fragmentation, 경합의 최소화는 짧게 짚고 넘어가보자.

Row Migration: update로 인해 행의 길이가 증가했을 때, 저장 공간이 부족한 경우에 원래 정보(주소)를 기존 블록에 남겨두고 실제 데이터를 다른 블록에 저장하는 경우를 의미.
Fragmentation(단편화): insert와 update를 하다보면 테이블 스페이스에 '빈 공간'이 조금씩 생기는데, 이 '빈 공간'이 쌓이는 걸 단편화라고 한다.
경합의 최소화: 트랜잭션끼리 서로 충돌하는 걸 방지하기 위해 'lock(잠금)'이라는 걸 설정하는데, 이 'lock' 때문에 발생하는 문제를 '경합'이라 한다.

Export-Import 할 때 알아두어야 할 점이다.

catexp.sql은 Export-Import를 하기 위한 뷰를 생성한다. 이는 일반적으로 database를 생성할 때 catalog.sql이라는 게 만들어지는데, 이 안에 포함되어있다.
공통적으로 사용되는 '파라미터'는 Parameter 파일에 지정한다. 파라미터는 따로 지정 안하면 나중에 문제 생길수도 있다.
Export 하기 전, Disk 공간이 충분한 지 확인해야 한다. Export로 나오는 dmp 파일이 생각보다 크다는 걸 명심하자.

Export

일단, Export하기 전에 SQL에서 'HOST'를 입력한 뒤에 다음을 실행해야 한다. 이때,

cd C:\Users\user

으로 파일 생성하기를 원하는 곳으로 이동할 수 있다.
뭐 필수는 아닌데, 내 파일이 어딨는지는 알아야 하니까 :)

그러고서 다음 명령어를 입력하면 된다.

-- 1: system이 hr의 테이블 export
C:\Users\user>exp system/manager tables=(hr.emp, hr.dept) grants=y indexes=y

-- 2: hr 본인이 본인 테이블 export
C:\Users\user>exp hr/hr file=hr.dmp tables=(emp,dept) rows=y compress=y

-- 3: hr 본인이 본인 '데이터베이스' 전체를 export
C:\Users\user>exp hr/hr file=hr_all.dmp owner=hr grants=y rows=y compress=y

-- 4: system이 hr의 '데이터베이스' 전체를 export
C:\Users\user>exp system/manager full=y file=dba1.dmp grants=y rows=y

아래는 위의 파라미터들을 정리한 표이다.

파라미터	의미
exp	export
aa/bb	유저/유저패스워드
file=aaa.dmp	저장할 dump 파일 이름
grants	객체에 대한 권한을 export 여부
indexes	인덱스 export 여부
log	모든 export 메시지를 기록할 파일 이름으로, 기록 안하면 log 생성 안함
direct	직접 경로로 export
rows	행(데이터)들이 export 될 것인지 여부
compress	fragmentation된 세그먼트들을 압축할 지 여부
owner	사용자 객체와 데이터, owner의 모든 권한과 인덱스의 export 여부
full	전체 데이터베이스를 export할 지 여부

import

import도 export랑 비슷하다.

다음 명령어를 수행하면 된다.

-- 1: system이 hr의 테이블 import
C:\Users\user>imp system/manager file=EXPDAT.dmp fromuser=hr tables=(hr.emp, hr.dept)

-- 2: system이 hr의 테이블을 tiger에게 import
C:\Users\user>imp system/manager file=hr.dmp fromuser=hr touser=tiger tables=(emp,dept)

-- 3: system이 hr의 '데이터베이스' 전체를 tiger에게 import
C:\Users\user>imp system/manager file=hr_all.dmp fromuser=hr touser=tiger

아래는 위의 파라미터들을 정리한 표이다.

파라미터	의미
imp	import
aa/bb	유저/유저패스워드
file=aaa.dmp	import할 dump 파일 이름
show	import 실행하는 대신 화면에 표시할 지 여부
ignore	import하면서 발생하는 에러를 무시할 지 여부
grants	권한을 import할 지 여부
fromuser	읽을 객체들을 가지고 있는 사용자 목록
touser	import를 적용할 대상(유저)
tables	import할 테이블 목록

이것으로 계층적 질의문과 import, export에 대한 내용을 정리하였다.