게시판 페이징 처리 (oracle DB)

1. 게시판 테이블을 생성한다.

2. 게시판 테이블 tbl_board에 pk_board라는 이름으로 bno를 primary key로 하는 제약조건을 만든다.

3. primary key인 bno의 값에 할당될 sequence 객체를 만들어준다.

4. 게시판 테이블 tbl_board에 첫번째 row 데이터를 삽입한다.

이후, 이 삽입된 row를 이용하여 게시판 테이블의 row를 기하급수적으로 삽입한다

5. 이제 페이징 처리를 해주어야 하는데 오라클의 경우는 페이징 처리에 rownum을 활용한다. rownum은 테이블에서 데이터가 출력될 때 붙는 번호라고 이해하면 좋다.

rownum은 출력되면서 붙는 번호이기 때문에 어떤 식으로 SQL문이 실행되는지에 따라서 (실행 계획) 붙는 번호가 달라지게 된다. 아래 두가지 예를 비교해보자.

다음의 쿼리를 실행후 실행계획(단축키 F10)을 보면 index(fast full scan)을 하고 있는것을 확인할 수 있다. fast full scan은 리프블록들을 수평적으로 읽어낸다. 즉 multiple block I/O를 하므로 bno의 순서대로 select되는것이 보장되지 않는다. 다음 질의 결과를 보면 분명 bno값을 넣어 줬을때 1부터 순서대로 넣었는데, 1150부터 엉뚱하게 나온것을 볼 수 있다. 이는 오라클이 읽어낸 첫 블록에 해당하는 row의 bno가 1150번이었으므로 여기에 rownum이 1로 부여됐기 때문이다.

                                       

---

반면에 다음 쿼리 처럼 where 조건절에 bno > 0 을 추가하여 실행시켜보자. 실행계획을 보면 index(range scan)을 하고 있다. 즉, row들을 출력하기전 bno가 0보다 큰 것을 만족하는 것만 select하라고 oracle optimizer에게 알려주는 것이다. 그러면 optimizer가 루트 블록에서 리프 블록까지 수직적으로 탐색후 필요한 부분만 스캔하게 된다. 이 결과  값은 인덱스 컬럼(현재 pk_board) 순서대로 출력된다.(정렬이 된 상태이므로 order by절 없이 정렬된 값을 추출해낸다.) 그러다보니 bno의 순서대로 rownum이 할당되어 출력된 것이다.

                                

6. 이제 게시판 테이블에서 가장 최근에 등록된 글을 10개 단위(첫번째 페이지)로 읽어와보자. 현재 총 4194304개의 게시글 데이터를 넣었다는 가정하에, bno의 범위가 4194304 ~ 4194295에 해당하는 row들만 보여주면된다. 이때 게시판 테이블을 역순으로 스캔을 해야하는 필요성이 생긴다. 이때 오라클에서는 hint라는 것을 사용한다. 

/*+ index_desc(tbl_board pk_board)*/ 는 

tbl_board를 스캔할때 pk_board index를 역으로 스캔하라는 의미이다.

이때 rownum에 대한 한가지 issue가 있다. 만약 게시판의 두번째 페이지를 읽어오기 위해 rownum이 10보다 크고 20보다 작거나같다고 조건을 줘보자. 

결과는 다음과 같이 아무런 row도 출력되지 않는다. 왜 일까?

rownum은 데이터가 나오면서 붙여주는 번호이기 때문에 rownum >10 구문에서 어떤 데이터가 나오면 rownum은 1이 되기 때문에 rownum >10 조건에 맞지 않기 때문에 건너뛰게 된다. 이러한 이유로 rownum은 반드시 1이 포함되어야 한다. 따라서 두번째 페이지를 처리하기 위해서는 in-line view 라는 개념을 알아야 한다. 이는 쉽게 말해서 from 구문에 다른 sql문이 들어간 형태이다. 

두번째 페이지의 데이터 처리는 다음과 같다. 

즉, 우선 게시판테이블에서 rownum이 20개 이하인 데이터를 뽑아낸다음, 이 데이터들 중에서 rownum이 10보다 큰것들을 뽑아내면 rownum이 11~20 즉 두번째 페이지에 해당하는 데이터들을 뽑아올 수 있다.

7. 게시판의 각각의 글들에는 댓글들이 달린다. 원글 하나당 댓글을 여러개가 달릴 수 있으므로 1:n의 관계이다. 댓글 데이터를 처리해줄 테이블 tbl_reply를 생성하고, 댓글번호인 rno을 primary key로 제약조건을 건다. 그리고 이 rno에 할당될 값인 시퀀스를 생성해준다.

이후 생각해야 할 점이 있다. 게시판 원글을 삭제하면 이글에 달린 댓글들도 같이 지워야 하는지, 그리고 만약 원글131이 있다고 했을때, 원글131이 존재해야 여기에 댓글도 달릴 수 있는 것이다. 즉 댓글테이블의 bno는 게시판테이블의 bno를 왜래키로 참조해야하는 관계이다. 따라서 다음과 같이 게시판 테이블이 댓글 테이블을 참조한다는 의미로, foreign key 제약조건을 부여해준다. DML은 autocommit이 되지 않으므로 commit을 꼭 해주자.

8. 댓글데이터도 다음과 같이 많이 넣어준다.

9. 이제 원글이랑 댓글의 관계를 '원글번호-원글제목-원글작성자-이 원글에 달린 댓글 수'  형태로 표현을 해보자.

특정 게시글에 댓글이 6개 달리면 게시글 옆에 [6]처럼 표시되는 것을 구현하고 싶은 것이다. 이는 다음 쿼리와 같다.

그런데 위 쿼리의 실행계획을 확인해보면 full스캔을 하는 것을 알수있다. 즉, 댓글이 어떤 게시글에 해당하는 댓글인지를 확인하기 위해서는 댓글테이블의 bno와 게시판테이블의 bno를 매칭시켜서 확인해야 한다. 이때 댓글 하나하나마다 각각 모든 게시판테이블 데이터들을 full scan하기 때문에 시간이 매우 오래 걸린다. 

이를 해결하기 위해서 index를 사용한다. 댓글테이블의 bno에 index를 지정해주면 댓글이 어떤 게시글에 해당하는지 찾을 때 full scan을 하는것이 아니라 한번에 찾을 수 있는것이다. 

index를 지정해준후 쿼리를 다시 수행하면 속도 성능면에서 매우 빨라지는 것을 확인할 수 있다. 실행계획을 봐도 만든 index를 이용하여 range scan을 하는 것을 확인할 수 있다.

어쨌든 쿼리의 결과는 다음과 같이 확인할 수 있다.