개발은 전투다

시퀀스(Sequence) 와 MAX +1 의 차이

일련번호 같이 기본키를 잡을만한 컬럼의 고유번호를 지정할 때 자동증분을 많이 이용하고는 한다.

보통 시퀀스(Sequence) 또는 MAX + 1 을 사용할텐데, 현업에서는 아직도 MAX + 1을 사용하는 곳이 많다고 한다.

이 둘의 가장 큰 차이점은 무엇일까?

일단 하나 들어보자면 데이터 중복의 허용과 비허용을 말할 수 있다.

예를 들어보자.

한번에 10명의 사용자가 동시접속을 해서 게시글을 작성했다고 생각해보자.

그렇다면 총 10번의 INSERT가 진행될 것이다.

각각의 INSERT에 대해서 일련번호를 MAX + 1, Sequence.NEXTVAL을 사용했다 치면 각각의 값들은 이런식으로 나올 수 있다.

물론 무조건 그렇다는게 아니라 그럴수도 있다는 점을 알아두면 좋겠다.

MAX + 1은 1,1,2,3,4,5,5,6,7,8 이라는 값이 나올 수 있고
Sequence.NEXTVAL 같은 경우에는 1,2,3,4,5,6,7,8,9,10이라는 값으로 나올 것이다.

여기서 말하고자 하는것은 MAX를 사용할 경우에는 중복이 될 수 있고, Sequence 같은 경우에는 중복이 발생할 수 없다는 점이다.

시퀀스는 크리티컬 세션이 보장되기 때문에 절대 데이터의 중복처리가 되질 않는다.

MAX + 1은 현재 가장 큰 값에 + 1을 시켜주는 것이기 때문에 중복이 될 확률이 높다.

일단 차이점에 대해 작성을 해봤는데, 개인적인 생각으로는 MAX + 1을 써야하는 이유가 전혀 없다고 생각된다.

개발이 편해서? 시퀀스도 편하다. 시퀀스를 생성하는게 번거로워서? 그냥 생성문만 실행시켜주면 끝이다.

그리고 성능을 많이 신경써야 하는 부분에 있어서 MAX + 1은 풀스캔을 하기 때문에 성능상 매우 좋지 않다.

되도록 시퀀스를 쓰기를 바란다.

ORACLE LISTAGG 여러 행을 하나의 컬럼으로 가져오기

11g 에서 추가. 10g 이하는 WM_CONCAT 함수 사용

(WM_CONCAT은 페이지 하단 Link 참고)

오라클에서 여러개의 열로 된 값을 한 행의 값으로 가져와야 할 때 LISTAGG 함수를 사용한다.

-  사용방법

SELECT LISTAGG(가져올컬럼, 구분자) WITHIN GROUP (ORDER BY 순서컬럼)
  FROM TABLE_NM

아래의 예를 보자

예)

SELECT DRIVER_NM
  FROM DRIVER_INFO;

DRIVER_INFO 테이블에서 DRIVER_NM 컬럼을 가져왔다.

위와 같은 여러 열의 값을 LISTAGG를 사용하여 하나의 열로 가져와 보자.

SELECT LISTAGG(DRIVER_NM, ',') WITHIN GROUP (ORDER BY DRIVER_NM) AS DRIVER_NM
  FROM DRIVER_INFO

DRIVER_NM 컬럼의 값을 오름차순 그룹으로 묶어 하나의 열로 표현 했다.

그렇다면 LISTAGG의 중복을 제외하기 위해선 어떻게 해야 할까?

아쉽게도 LISTAGG는 중복제외(DISTINCT)를 지원하지 않는다.

SELECT DISTINCT(LISTAGG(DRIVER_NM, ',') WITHIN GROUP (ORDER BY DRIVER_NM)) AS DRIVER_NM
  FROM DRIVER_INFO

위와 같이 DISTINCT를 사용하여도 오류는 나지 않지만

기존에 사용하지 않은 쿼리와 같은 값을 조회한다.

그래서 이를 해결하기 위해

아래와 같이 서브쿼리를 사용하여 중복을 제외한 테이블에서 LISTAGG를 사용한다.

SELECT LISTAGG(DRIVER_NM, ',') WITHIN GROUP (ORDER BY DRIVER_NM) AS DRIVER_NM
  FROM (SELECT DISTINCT DRIVER_NM FROM DRIVER_INFO)

참조)

다른 컬럼과 같이 GROUPING 된 LISTAGG 를 조회 하려면 PARTITION BY를 사용한다.

SELECT DRIVER_ID
     , LISTAGG(DRIVER_NM, ',') WITHIN GROUP (ORDER BY DRIVER_NM)
          OVER(PARTITION BY DRIVER_ID)  AS DRIVER_NM
  FROM DRIVER_INFO

[Oracle] INNER JOIN이란? 

INNER(내부) JOIN 이란, 두 테이블간 조인 조건을 만족하는 행을 반환할 때 사용하는 구문이다.

쉽게 조인하려는 두 테이블의 교집합이라고 생각하면 될 듯 하다.

사용 방법

SELECT *                                    조회할 컬럼을 선택한다 (모든 컬럼 조회)
  FROM TABLE1  T1                             
  INNER JOIN TABLE2  T2              INNER 는 생략가능
  ON (T1.KEY = T2.KEY)                두 테이블을 연결할 조건을 명시한다

실제 어떻게 사용되는지 살펴보자

KEY 컬럼이 TIMESTAMP와 NUM인 같은 구조의 테이블 A와 B가 있다.

두 테이블의 TIMESTAMP와 NUM의 값이 같은 데이터를 조회하고 싶다.

여기서 INNER JOIN의 INNER는 생략 가능하다.

SELECT A.TIMESTAMP
     , A.NUM
     , A.VERSION
     , A.MSG_ID
     , A.GRADE
     , B.TIMESTAMP
     , B.NUM
     , B.VERSION
     , B.MSG_ID
     , B.GRADE 
  FROM A
  INNER JOIN B ON(B.TIMESTAMP = A.TIMESTAMP AND B.NUM = A.NUM)

출처: https://haenny.tistory.com/35 [Haenny]

조인 (JOIN)

 - JOIN은 각 테이블간에 공통된 걸럼(조건)으로 데이터를 합쳐 표현하는 것이다.

 - JOIN에는 크게 INNER JOIN, OUTER JOIN이 있다.

▶ 예제 테이블을 통해 이해해보자.

TABLE 1) MEM : 회원테이블 (회원번호, 이름, 이메일, 부서번호)

TABLE 2) DEPART : 부서테이블 (부서번호, 부서명)

▶ 1. SELECT FROM을 이용한 조인

1.1) 회원 테이블과 부서 테이블의 조인

 => DEPART_ID가 공통 컬럼

 => MEM테이블의 DEPART_ID와 DEPART테이블의 DEPART_ID를 연결하여 준다.

ex)

SELECT  MEM.MEM_ID, MEM.NAME, MEM.DEPART_ID, DEPART.DEPART_NAME

FROM     MEM

               , DEPART

WHERE MEM.DEPART_ID = DEPART.DEPART_ID;

1.2) 이때 테이블명이 긴 경우 일일이 그 테이블 명을 다 써주지 않고 별칭을 쓸 수 있다.

ex)

SELECT A.MEM_ID, A.NAME, A.DEPART_ID, B.DEPART_NAME

FROM   MEM A

             , DEPART B

WHERE A.DEPART_ID = B.DEPART_ID;

 결과는 동일

▶ 2. ANSI 표준 조인 (JOIN절을 이용한 명시적 조인)

문법 =>

JOIN절을 명시적으로 선언하여 질의문을 작성할 수 도 있다.

SELECT 컬럼이름1, 컬럼이름2, ㆍㆍㆍ

FROM    테이블명1

JOIN    테이블명2

ON        테이블명1.컬럼명 = 테이블명2.컬럼명;

위의 쿼리를 이렇게 바꿔 작성할 수 있다.

SELECT A.MEM_ID, A.NAME, A.DEPART_ID, B.DEPART_NAME

FROM    MEM A

JOIN    DEPART B

ON     A.DEPART_ID = B.DEPART_ID

▶ 3. 질의문 사용한 JOIN 

3.1) 회원 테이블과 부서 테이블의 조인 (특정 회원 조회 EX) NAME이 ‘갓동수’ 인 회원)

3.1.1) SELECT FROM을 이용한 조인에 AND로 조건을 추가하여 조회 가능하다.

ex)

SELECT A.MEM_ID, A.NAME, A.DEPART_ID, B.DEPART_NAME

FROM    MEM A

             , DEPART B

WHERE A.DEPART_ID = B.DEPART_ID

AND A.NAME = '갓동수'

3.1.2) ANSI 표준 쿼리에서는 WHERE 문을 추가하여 조건 조회가 가능하다.

SELECT A.MEM_ID, A.NAME, A.DEPART_ID, B.DEPART_NAME

FROM MEM A

JOIN DEPART B

ON A.DEPART_ID = B.DEPART_ID

WHERE A.NAME = '갓동수';

▶ 4. 3개 이상(다중) 테이블 조인

ex TABLE Name  =  MOD_MEM_LOG : 회원정보 변경로그 (회원번호, 변경시간)

4.1) 회원 테이블과 부서 테이블의 조인 후 그 회원의 회원 수정 기록을 알고 싶다.

MEM_MOD_LOG 테이블을 추가하고, 공통 컬럼을 연결 시켜준다.

첫번째 방법) SELECT, FROM 절

SELECT  A.MEM_ID, A.NAME, A.DEPART_ID, B.DEPART_NAME, C.LOG_TIME

FROM     MEM A

               , DEPART B

          , MEM_MOD_LOG C

WHERE   A.DEPART_ID = B.DEPART_ID

AND     A.MEM_ID = C.MEM_ID

AND        A.NAME = '갓동수';

두번째 방법) ANSI 표준쿼리

SELECT  A.MEM_ID, A.NAME, A.DEPART_ID, B.DEPART_NAME, C.LOG_TIME

FROM     MEM A

JOIN     DEPART B

ON         A.DEPART_ID = B.DEPART_ID

JOIN   MEM_MOD_LOG C

ON      A.MEM_ID = C.MEM_ID

WHERE  A.NAME = '갓동수';

▶ 5. 아우터 조인 (LEFT, RIGHT, FULL OUTER JOIN)

EQUI JOIN 문장들의 한 가지 제약점은 그것들이 조인을 생성하려 하는 두개의 테이블의 

두개 컬럼에서 공통된 값이 없다면 테이블로부터 데이터를 반환하지 못한다는 것이다.

- 정상적으로 저인 조건을 만족하지 못하는 행들을 보기위해 OUTER JOIN을 사용한다. 

OUTER JOIN 연산자는 "(+)"이다.

- 조인시킬 값이 없는 조인측에 "(+)"를 위치 시킨다.

- OUTER JOIN 연산자는 표현식의 한 편에만 올 수 있다.

EX1 ) ‘사자’님의 회원 정보를 조회해보겠다. (부서명 까지 조인)

SELECT  A.MEM_ID, A.NAME, A.DEPART_ID, B.DEPART_NAME

FROM     MEM A

               , DEPART B

WHER    A.DEPART_ID = B.DEPART_ID

AND       A.NAME = '사자';

위와 같은 정보가 나오는 것을 알 수 있다.

EX2) 그럼 '사자'님의 회원정보 수정 기록을 알기 위해 조인을 해보자.

SELECT  A.MEM_ID, A.NAME, A.DEPART_ID, B.DEPART_NAME, C.LOG_TIME

FROM    MEM A

              , DEPART B

         , MEM_MOD_LOG C

WHERE A.DEPART_ID = B.DEPART_ID

AND    A.MEM_ID = C.MEM_ID

AND      A.NAME = '사자';

아무런 결과가 나오지 않는다.

그 이유를 살펴보면 ‘사자’님은 한번도 회원정보를 수정한 적이 없기 때문이다.

회원 정보 수정 로그 (사자 MEM_ID : 0000000003)

회원 정보 수정 로그가 없더라도 회원 기본 정보를 보고 싶다면 이때 OUTER 조인을 사용하면 된다.

▶ 5.1) ORACLE에서의 OUTER조인

간단하게 말하여 데이터가 없을 수도 있는 쪽 JOIN 컬럼에 (+)를 추가하여 OUTER JOIN이 가능하다.

SELECT  A.MEM_ID, A.NAME, A.DEPART_ID, B.DEPART_NAME, C.LOG_TIME

FROM     MEM A

               , DEPART B

          , MEM_MOD_LOG C

WHERE   A.DEPART_ID = B.DEPART_ID

AND     A.MEM_ID = C.MEM_ID (+)

AND       A.NAME = '사자';

위와 같이 회원정보 변경 이력이 없더라도 나머지 조인에 의한 결과행은 보여지도록 된다.

▶ 5.2) ANSI 표준 쿼리의 OUTER JOIN

 - ANSI 표준에서의 OUTER JOIN은 LEFT, RIGHT OUTER 조인 2가지가 있다.

 5.2.1) LEFT OUTER JOIN

이전의 JOIN ON 절에서 JOIN 앞에 LEFT OUTER를 붙여주면 된다.

ex) 

SELECT  A.MEM_ID, A.NAME, A.DEPART_ID, B.DEPART_NAME, C.LOG_TIME

FROM     MEM A

JOIN     DEPART B

ON         A.DEPART_ID = B.DEPART_ID

LEFT    OUTER JOIN MEM_MOD_LOG C

ON         A.MEM_ID = C.MEM_ID

WHERE  A.NAME = '사자';

▶ 5.3) FULL OUTER JOIN

양쪽 테이블에 다 Outer Join을 거는 것을 TWO-WAY OUTER JOIN 또는 FULL OUTER JOIN 이라 한다.

출처: https://goddaehee.tistory.com/62 [갓대희의 작은공간]