오라클 계층형 쿼리 및 MAX +1 해결

계층형 쿼리란?

한테이블에 레코드들이 계층관계(상위,하위)를 이루며 존재할 때, 이 관계에 따라 레코드를 계층관계(상위,하위) 한 구조로 데이터를 가져올 때 사용되는 SQL문을 의미한다.

쉽게말하자면 계층형 쿼리는 말그대로 계층 관계를 나타내는 쿼리문을 말한다.

예시) 부서, 메뉴, 권한 등 트리 구조의 데이터를 표시해 줄 때 사용

출처

• https://velog.io/@dani0817/SQLORACLE-%EA%B3%84%EC%B8%B5%ED%98%95-%EC%BF%BC%EB%A6%AC
• https://yunamom.tistory.com/247

SELECT 컬럼1, 컬럼2...
FROM 테이블
WHERE 조건
START WITH 최상위 조건
CONNECT BY [NOCYCLE][PRIOR 계층형 구조 조건]

• START WITH

  • 계층형 구조에서 최상위 계층의 ROW 를 식별하는 조건을 명시한다.

• CONNECT BY [NOCYCLE][PRIOR 계층형 구조 조건]

  CONNECT BY PRIOR 부모노드 = 자식노드
  CONNECT BY 자식노드 = PRIOR 부모노드

  • 계층형 구조가 어떤 식으로 연결되는 지를 기술하는 부분이다.

    • NOCYCLE : 데이터를 펼치면서 이미 나타났던 데이터가 다시 나타나는 경우 CYCLE이 형성되었다라고 한다. 이때 오류가 발생하는데 NOCYCLE을 추가하면 사이클이 발생한 이후의 데이터를 출력하지 않는다.

• SELECT 절에서 추가 사용

  • LEVEL

    • 계층 값을 가져옴
      
      

  • CONNECT_BY_ROOT 컬럼값

    • 계층구조 쿼리에서 LEVEL이 0인 최상위 로우의 정보를 얻어 올 수 있습니다.
      
      

  • CONNECT_BY_ISLEAF

    • 계층구조 쿼리에서 로우의 최하위 레벨(Leaf) 여부를 반환합니다. 최하위 레벨이면 1, 아니면 0
      
      

  • SYS_CONNECT_BY_PATH(컬럼값, 구분자)

    • 계층구조 쿼리에서 현재 로우 까지의 PATH 정보를 가져올수 있습니다.

• ORDER BY 절에서 사용

  • ORDER SIBLINGS BY 컬럼값

    • 계층구조 쿼리에서 상관관계를 유지하면서 정렬을 할 수 있게 해줍니다.

Max+1

• Insert할때 PK 값을 max+1로 넣게 되면 동시에 조회 시 중복 에러가 발생한다.

  SELECT NVL(MAX(NUM)+1, 1) FROM DUAL;

• Max+1 대신 시퀀스를 사용하여 중복 사용 에러를 해결하자.

시퀀스

• 생성

  CREATE SEQUENCE SEQ_SEQUENCE -- 시퀀스 명
  INCREMENT BY 1 -- 증가값
  START WITH 1 -- 초기값
  MINVALUE 1 -- 최소값
  MAXVALUE 999999 / NOMAXVALUE -- 최대값 / 무한대
  NOCYCLE / CYCLE -- 사이클 X / MAXVALUE값 이후 다시 MINVALUE 시작
  CACHE 20 / NOCACHE -- 캐시 사용 유무
  ORDER / NOORDER

• CACHE 사용

  • 장점 : 시퀀스 조회 속도 상승
  • 단점 : DB가 종료되거나 비정상적인 오류가 발생한다면 다시 시작할 때 캐시 이후 값으로 시작한다. 왜냐하면 메모리에 저장된 사용하지 않는 값들이 모두 제거됐기 때문이다.
  • CACHE를 미리 값을 할당하지 않을 경우, BUFFERS MEMORY는 절약할 수 있다. 하지만 시퀀스를 사용할 때마다 CPU에서 연산처리해야되기 때문에 빈번한 사용 시 비효율적이다.(CPU에 무리를 줌)

• 사용

  SELECT SEQUENCE.NEXTVAL FROM DUAL;

  SELECT SEQUENCE.CURRVAL FROM DUAL;

• NEXTVAL : 다음번호 조회

• CURRVAL : 현재번호 조회

  정리

  1. MAX+1 : 중복으로 사용가능

  2. 시퀀스(Sequence) : 중복 사용 불가

  출처

  https://jfbta.tistory.com/99