Background Image
질의작성
2017.02.28 02:31

CONNECT BY 절 포함 질의 튜닝 예제

조회 수 9693 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄


CONNECT BY 성능 향상

CONNECT BY 절은 계층적 질의로서 일반적으로 메뉴 구성과 같은 TREE구조를 가지는 데이터 조회에 많이 사용된다. 계층형 질의는 일반 질의와는 다른 특성이 있는데 특성을 이해하고 질의 성능을 향상하기 위한 방법을 알아보자.

CONNECT BY 수행 CUBRID 제일 먼저 WHERE 절의 조인 조건을 적용하여 테이블 조인 연산을 수행한 CONNECT BY 절의 조건식을 적용하고, 마지막으로 WHERE  내의 나머지 검색 조건식을 적용하여 연산을 처리한다. 해당 순서로 분리되어 수행된다는 것은 CONNECT BY절의 PRIOR, 조인조건과 검색조건에 대한 복합인덱스는 효과적이지 못하며 조인이 포함된 쿼리의 경우 PRIOR 조건의 인덱스를 사용하지 못한다는 것을 의미한다. 아래 예제를 통해 자세히 알아보자

PRIOR 조건 인덱스 추가

CONNECT BY 수행 CUBRID 하나의 루트 행에 대하여 모든 자식 행을 검색한 , 자식 하위에 속하는 모든 자식 행을 재귀적으로 검색한다. 이러한 검색수행 방법 때문에 PRIOR 조건 식의 자식 행이 되는 컬럼에 인덱스 추가하여 성능 향상을 시킬 있다.

SET TRACE on;

 

SELECT id, mgrid, name

FROM tree

CONNECT BY PRIOR id=mgrid

ORDER SIBLINGS BY  id;

 

SHOW trace;

 

Trace Statistics:

SELECT (time: 845, fetch: 240827, ioread: 0)

    SCAN (table: tree), (heap time: 0, fetch: 40, ioread: 0, readrows: 112, rows: 112)

    CONNECTBY (time: 844, fetch: 240784, ioread: 0)

      SCAN (table: tree), (heap time: 692, fetch: 219520, ioread: 0, readrows: 614656, rows: 5376)

      ORDERBY (time: 35, sort: true, page: 0, ioread: 0)

 

-- 인덱스 추가 후 질의 수행 계획

CREATE INDEX ix_tree ON tree(mgrid);

 

Trace Statistics:

  SELECT (time: 58, fetch: 32586, ioread: 0)

    SCAN (table: tree), (heap time: 0, fetch: 40, ioread: 0, readrows: 112, rows: 112)

    CONNECTBY (time: 58, fetch: 32544, ioread: 0)

      SCAN (index: tree.ix_tree), (btree time: 10, fetch: 11280, ioread: 0, readkeys: 304, filteredkeys: 304, rows: 5376) (lookup time: 2, rows: 5376)

      ORDERBY (time: 13, sort: true, page: 0, ioread: 0)

JOIN 질의 성능 향상

CONNECT BY 절이 포함된 JOIN 질의의 경우 JOIN 먼저 수행되기 때문에 해당 결과가 TEMP  저장되고 이후에 CONNECT BY 절이 수행되어서 PRIOR 조건에 대해 인덱스 스캔을 하지 못한다. JOIN되는 테이블과 CONNECT BY절이 수행되는 테이블이 1:1관계 혹은 1:M관계를 가졌을 경우 IN-LINE VIEW 통해 강제적으로 CONNECT BY절을 우선 수행하여 성능 향상을 시킬 있다. M:M 관계인 경우 다른 결과를 가져올 있으므로 유의해야한다.

예를 들어 메뉴관련 테이블의 경우 메뉴 마스터 테이블과, 메뉴 맵핑 테이블은 서로 외래키 관계를 가지며 1:1 혹은 1:M 관계를 가지게 된다. 이런 경우에는 아래 예시 처럼 변경이 가능하다.

PRIOR 조건식이 JOIN되는 테이블을 참조하는 경우는 인덱스 스캔은 불가하며, 여러 상황을 고려하여 설계상 테이블을 합치는 반정규화를 고려해 보아야 한다.

SET TRACE ON;

 

SELECT t.id,t.name,t2.job,level

FROM tree t INNER JOIN tree2 t2 ON t.id=t2.treeid

START WITH t.mgrid is null

CONNECT BY prior t.id=t.mgrid

ORDER SIBLINGS BY  T.id;

 

SHOW trace;

 

-- 같은 데이터를 재귀 조회 하므로 connect by의 패치량은 작을 수 있지만 재귀적으로 full scan하면서 발생되는 시간에 유의한다.

Trace Statistics:

  SELECT (time: 22200, fetch: 892928, ioread: 0)

    SCAN (table: tree), (heap time: 0, fetch: 488, ioread: 0, readrows: 448, rows: 448)

      SCAN (index: tree2.ix_tree), (btree time: 1, fetch: 1344, ioread: 0, readkeys: 448, filteredkeys: 448, rows: 448) (lookup time: 0, rows: 448)

    CONNECTBY (time: 22198, fetch: 891094, ioread: 0)

      ORDERBY (time: 820, sort: true, page: 1561, ioread: 0)

 

-- IN-LINE VIEW 사용하여 쿼리 변경

SELECT t.id,t.name,t2.job,t.t_level

FROM (

SELECT t.id,t.name,level AS t_level

FROM tree t

START WITH t.mgrid is null

CONNECT BY prior t.id=t.mgrid

ORDER SIBLINGS BY  T.id) t, tree2 t2

WHERE t.id=t2.treeid

SHOW trace;

 

Trace Statistics:

  SELECT (time: 3905, fetch: 2565683, ioread: 0)

    SCAN (temp time: 57, fetch: 954, ioread: 0, readrows: 278656, rows: 278656)

      SCAN (index: tree2.ix_tree), (btree time: 509, fetch: 835968, ioread: 0, readkeys: 278656, filteredkeys: 278656, rows: 278656) (lookup time: 134, rows: 278656)

    SUBQUERY (uncorrelated)

      SELECT (time: 3115, fetch: 1726340, ioread: 0)

        SCAN (table: tree), (heap time: 0, fetch: 40, ioread: 0, readrows: 448, rows: 128)

        CONNECTBY (time: 3115, fetch: 1726299, ioread: 0)

          SCAN (index: tree.ix_tree), (btree time: 527, fetch: 565760, ioread: 0, readkeys: 4224, filteredkeys: 4224, rows: 278528) (lookup time: 112, rows: 278528)

         ORDERBY (time: 667, sort: true, page: 1561, ioread: 0)

 

질의 최적화 관련 유의사항

CONNECT BY 절이 포함된 질의가 JOIN, CONNECT BY, WHERE 절의 조건식 순으로 수행되는 것을 생각했을 테이블간 JOIN 성능과 밀접하게 관련이 있다. IN-LINE VIEW 사용하여 직접적으로 조인 순서를 변경할 경우 CONNECT BY절이 포함된 테이블이 다른 JOIN 테이블에 의해 결과 셋이 변경되지 않는 1:1 혹은 M:1 관계인지 확인하여야 한다. 또한 해당 관계가 외래키와 주키 등록되어 있다면 이후 DML시에도 데이터 적합성을 유지할 있을 것이다.

WHERE 절의 조건식에 의해 상당한 부분의 데이터가 필터 되어 CONNECT BY 수행을 FULL SCAN 하더라도 유리한 경우라면 IN-VEIW 사용하여 부질의에서 WHERE절의 조건을 먼저 수행하고 주질의에서 CONNECT BY절을 수행하는 것도 생각해 있다. 하지만 수행순서 변경에 따라서 결과값이 달라 있는데 업무적으로 해당 결과가 의미가 있고 필요한 경우에만 사용이 가능하니 주의가 필요하다.


List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
159 운영관리 insert 질의로 16K 이상 데이터 입력 시 오류 문제와 해결방법 정만영 2010.07.01 13184
158 CUBRID 매니저 MS949 charset을 CM에서 선택할 수 없는 현상 조치방법 cubebridge 2010.05.17 20543
157 질의작성 모든 필드에 하나 이상 중복 값 찾기 정만영 2010.04.27 21573
156 운영관리 큐브리드 백업파일(볼륨)을 이용한 과거 데이터 복구 시나리오 janus 2010.04.01 21250
155 응용개발 CUBRID에서 euc-kr의 한글 데이터를 utf-8의 한글 데이터로 변경 시 주의할 점 cubebridge 2010.04.01 22380
154 CUBRID 매니저 CUBRID Manager의 host 및 질의편집기 설정 저장 위치 seongjoon 2010.04.01 14705
153 운영관리 CUBRID 데이터베이스 버퍼 캐쉬 파라미터($CUBRID/conf/cubrid.conf) Hint 정만영 2010.03.31 17318
152 운영관리 linux에서 ipcs를 이용한 CUBRID의 Shared Memory 확인하는 방법 seongjoon 2010.03.31 25755
151 운영관리 CUBRID 쿼리 수행 중단 시 주의 사항 cubebridge 2010.03.04 16388
150 운영관리 CUBRID에서의 출력 메시지 한글로 변경 방법. file seongjoon 2010.03.01 12440
149 질의작성 group by 사용 방법 및 여러 필드중 하나의 필드에 대하여만 group by 처리 방법 남재우 2010.02.26 21709
148 응용개발 패키지 형태로 생성된 JAVA class를 JAVA SP에서 사용하기 손승일 2010.02.26 18371
147 응용개발 CUBRID2008 Java Stored Procedure 등록 방법 cubebridge 2010.02.24 19559
146 운영관리 한 호스트내의 DB경로 변경 방법 janus 2010.02.19 12264
145 운영관리 큐브리드 복제 구성 시 마스터 데이터베이스 백업 주의사항 정만영 2010.02.11 12589
144 CUBRID 매니저 CUBRID Manager에서 데이터 검색 결과를 파일로 내려받는 방법. file seongjoon 2010.02.09 15312
143 질의작성 CUBRID에서 대소문자 관리 남재우 2010.01.30 19506
142 CUBRID 매니저 큐브리드 매니저 "선택한 JDBC 드라이버는 CUBRID를 지원하지 않습니다." 해결방법 file 정만영 2010.01.29 22506
141 CUBRID 매니저 CUBRID에서 여러개의 질의 결과 확인하는 방법 file seongjoon 2010.01.02 14439
140 CUBRID 매니저 CUBRID에서 OID 확인하는 방법 file seongjoon 2010.01.02 16298
Board Pagination Prev 1 2 3 4 5 6 7 8 9 10 11 ... 14 Next
/ 14

Contact Cubrid

대표전화 070-4077-2110 / 기술문의 070-4077-2113 / 영업문의 070-4077-2112 / Email. contact_at_cubrid.com
Contact Sales