OracleSQL - ROW_NUMBER

게시판을 만든다고 했을때, 게시판을 구분하기위해 ID를 정한다고 가정하자. 이 ID는 1부터 게시물이 생성될때 부여하며 1씩 증가한다. SQL에서 이러한 기능을 제공하는 쿼리문은 대표적으로 SEQUENCE가 있다.

 

SEQUENCE

시퀀스는 자동으로 순차적으로 증가하는 순번을 반환하는 데이터베이스 기능 중 하나이다. 보통 Primary Key (PK, 기본키) 값에 중복값을 방지하기 위해 사용한다. 예를들어 게시판을 추가할 때 가장 큰 id 값에 +1을 해주는 로직을 짤 필요없이 시퀀스를 사용하면 자동으로 +1을 해주기 때문에 매우 편리하다.

 

시퀀스를 사용하기 위해서는 시퀀스를 생성해주어야 한다. 다음과 같은 형식으로 만든다.

 

CREATE SEQUENCE [시퀀스 명]               
START WITH [시작숫자]                        -- 시작숫자의 디폴트값을 증가일때 MINVALUE 감소일때 MAXVALUE
INCREMENT BY [증감숫자]                    -- 증감숫자가 양수이면 증가 음수이면 감소
NOMAXVALUE (OR) MAXVALUE [최대값]  -- 최대값을 설정하거나, 디폴트 값으로 설정
CACHE OR NOCACHE                          -- CACHE 설정시 캐시메모리에 시퀀스값 미리할당

예를들어 다음 쿼리문으로 시퀀스와 테이블을 생성했다고 가정하자.

-- 시퀀스 생성
CREATE SEQUENCE SEQ_BOARD
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOCACHE;

--==>> Sequence SEQ_BOARD이(가) 생성되었습니다.


-- 테이블 생성
CREATE TABLE TBL_BOARD
( NO                    NUMBER
, TITLE                  VARCHAR2(50)
, CONTENTS          VARCHAR2(1000)
, NAME                VARCHAR2(20)
, PW                    VARCHAR2(20)
, CREATED            DATE DEFAULT SYSDATE
);

--==>> Table TBL_BOARD이(가) 생성되었습니다.

이제 테이블에 다음과 같이 데이터를 추가한다. SEQ_BOARD의 NEXTVAL을 사용하여 게시물의 ID를 순서대로 자동적으로 부여한다.

 

INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '아~ 여행가고 싶다', '에펠이 아른거려요', '안혜지', 'java006$', DEFAULT);

INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '아~ 딴거하고싶다', '핸펀이 자꾸 신경쓰여요', '박해진', 'java006$', SYSDATE);

INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '저는요', '자는게 제일 좋아요', '윤홍준', 'java006$', SYSDATE);

INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '날씨가', '많이 서늘해졌네요', '조윤상', 'java006$', SYSDATE);

INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '부끄럽네요', '그래서 저는 항상 눈만 보여요', '조영욱', 'java006$', SYSDATE);

INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '부끄러우면', '저는 가끔 화면에서 사라져요', '조인경', 'java006$', SYSDATE);

INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '어디갔지?', '토르야~ 밥묵어라~~!!', '정의진', 'java006$', SYSDATE);

INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '여기는 숲속', '저는 피톤치드에 중독되었어요', '허수민', 'java006$', SYSDATE);

테이블의 데이터를 확인해보면 다음과 같다.

 

이제 이 상태에서 게시물을 몇 개 삭제해보겠다. 3, 4, 6, 8번 게시물을 삭제한다.

 

DELETE
FROM TBL_BOARD
WHERE NO = 3;

DELETE
FROM TBL_BOARD
WHERE NO = 4;

DELETE
FROM TBL_BOARD
WHERE NO = 6;

DELETE
FROM TBL_BOARD
WHERE NO = 8;

그러면 테이블의 데이터는 다음과 같다.

 

이제 데이터를 한 개 추가해보도록 하겠다.

 

INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '저도 숲속', '수민이랑 같은 숲에 있어요', '김일웅', 'java006$', SYSDATE);

그러면 테이블의 데이터는 다음과 같다.

 

여기서 게시물 ID로 SEQUENCE를 사용하였을때 문제점이 발견된다. 어떤 문제점이 있는 것일까?

 

ROW_NUMBER

게시물 ID를 SEQUENCE로 사용하게 되면 게시물을 삭제했을 경우, 삭제한 게시물 자리에 다음 번호를 가진 게시물이 등록되는 상황이 발생하게 된다. 이는 보안성 측면이나 미관상 바람직하지 않은 상황일 수 있기 때문에 ROW_NUMBER()의 사용을 고려해 볼 수 있다. 

 

관리의 목적으로 사용할 때에는 SEQUENCE나 IDENTITY 기능을 사용하지만, 단순히 게시물을 목록화하여 사용자에게 리스트 형식으로 보여줄 때에는 이러한 기능을 사용하지 않는 것이 바람직하다.

 

ROW_NUMBER의 사용법은 다음과 같다.

 

ROW_NUMBER OVER(PARTITION BY [묶음처리할 기준이 되는 컬럼] ORDER BY [정렬기준이 되는 컬럼]) 

앞서 생성한 테이블 데이터의 게시물을 생성된 순서대로 ROW_NUMBER로 ID를 부여하면 다음과 같다.

 

SELECT ROW_NUMBER() OVER(ORDER BY CREATED) "글번호"
        , TITLE "제목", NAME "작성자", CREATED "작성일"
FROM TBL_BOARD
ORDER BY 4 DESC;