Spring 게시판 만들기(16)

게시판의 페이징, 검색기능(SQL 부분) 짚고가기

페이징 기능

• 페이징쿼리

• 오라클 : 인덱스 힌트

  • 인덱스를 사용하여, 성능목적

  • ORDER BY를 사용하지 않아도, 인덱스가 적용된 컬럼순서에 따라 조회됨
  • INDEX = INDEX_ASC : 오름차순정렬, INDEX_DESC: 내림차순 정렬

ORA_USER 계정

-- 사원번호 컬럼을 오름차순으로 정렬 조회
SELECT EMPLOYEE_ID, EMP_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, SALARY, MANAGER_ID,
       COMMISSION_PCT, RETIRE_DATE, DEPARTMENT_ID, JOB_ID, CREATE_DATE, UPDATE_DATE
FROM EMPLOYEES
ORDER BY EMPLOYEE_ID ASC;

-- 인덱스 힌트를 사용하여, 데이터를 정렬 조회
-- 인덱스명 : PK_EMPLOYEES
-- EMPLOYEE_ID 컬럼으로 정렬된 출력형태가 아닌 경우 인덱스명을 확인할 것
-- 인덱스명이 없다면 알아서 주석처리 효과를 받음
SELECT /*+ INDEX(E PK_EMPLOYEES) */
       EMPLOYEE_ID, EMP_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, SALARY, MANAGER_ID,
       COMMISSION_PCT, RETIRE_DATE, DEPARTMENT_ID, JOB_ID, CREATE_DATE, UPDATE_DATE
FROM EMPLOYEES E;

SELECT /*+ INDEX_DESC(E PK_EMPLOYEES) */
       EMPLOYEE_ID, EMP_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, SALARY, MANAGER_ID,
       COMMISSION_PCT, RETIRE_DATE, DEPARTMENT_ID, JOB_ID, CREATE_DATE, UPDATE_DATE
FROM EMPLOYEES E;

ezen 계정에 TBL_BOARD 테이블로 이동

DROP SEQUENCE SEQ_BOARD;

CREATE SEQUENCE SEQ_BOARD;

DELETE FROM TBL_BOARD;

-- 중간중간에 커밋하기
COMMIT;

SELECT * FROM TBL_BOARD;

-- 페이징쿼리를 위한 더미데이터 작업
INSERT INTO TBL_BOARD(bno, title, content, writer)
VALUES (SEQ_BOARD.NEXTVAL, '테스트 게시물 데이터', '테스트 게시물 데이터', 'USER01');
INSERT INTO TBL_BOARD(bno, title, content, writer)
VALUES (SEQ_BOARD.NEXTVAL, '테스트 게시물 데이터', '테스트 게시물 데이터', 'USER02');
INSERT INTO TBL_BOARD(bno, title, content, writer)
VALUES (SEQ_BOARD.NEXTVAL, '테스트 게시물 데이터', '테스트 게시물 데이터', 'USER03');
INSERT INTO TBL_BOARD(bno, title, content, writer)
VALUES (SEQ_BOARD.NEXTVAL, '테스트 게시물 데이터', '테스트 게시물 데이터', 'USER04');
INSERT INTO TBL_BOARD(bno, title, content, writer)
VALUES (SEQ_BOARD.NEXTVAL, '테스트 게시물 데이터', '테스트 게시물 데이터', 'USER05');

-- 여러개 데이터
-- 같은 데이터 5개를 계속 현재 있는 행만큼 집어넣는다
INSERT INTO TBL_BOARD(bno, title, content, writer)
SELECT SEQ_BOARD.NEXTVAL, title, content, writer FROM TBL_BOARD;

-- page 별 데이터 10개 출력
-- ROWNUM : 출력시 데이터행에 일련번호를 부여하는 기능
SELECT ROWNUM, bno, title, content, writer FROM TBL_BOARD WHERE ROWNUM < 11 ORDER BY BNO DESC;

-- page별 데이터 출력건수 3개
SELECT ROWNUM, TITLE, CONTENT, WRITER FROM TBL_BOARD WHERE ROWNUM >=1 AND ROWNUM <=3;
-- 조건식 사용시 중간범위를 가져올 수 없다
SELECT ROWNUM, TITLE, CONTENT, WRITER FROM TBL_BOARD WHERE ROWNUM >=4 AND ROWNUM <=6; -- 못가져옴
SELECT ROWNUM, TITLE, CONTENT, WRITER FROM TBL_BOARD WHERE ROWNUM >=7 AND ROWNUM <=9; -- 못가져옴

-- 페이징쿼리 : ROWNUM과 인라인뷰 이용
-- 인라인 뷰 : FROM절에서 사용하는 서브쿼리(SELECT문) 형태
SELECT RN, BNO, TITLE, CONTENT, WRITER, REGDATE
FROM ( SELECT ROWNUM RN, bno, title, content, writer, regdate FROM TBL_BOARD)
WHERE RN >= 1 AND RN <= 3;

SELECT RN, BNO, TITLE, CONTENT, WRITER, REGDATE
FROM ( SELECT ROWNUM RN, bno, title, content, writer, regdate FROM TBL_BOARD)
WHERE RN >= 4 AND RN <= 6;

SELECT RN, BNO, TITLE, CONTENT, WRITER, REGDATE
FROM ( SELECT ROWNUM RN, bno, title, content, writer, regdate FROM TBL_BOARD)
WHERE RN >= 7 AND RN <= 9;

페이징쿼리 (완성)

• 인덱스 넣는 부분 : + INDEX_DESC는 역순이니 최근 등록순으로 출력 (테이블이름 시퀀스이름)으로 입력. 양쪽 주석으로 닫기

-- 페이징쿼리(완성) : ROWNUM과 인라인뷰를 이용, 인덱스 힌트 적용
-- 파라미터 : pagenum, amount
-- pagenum : 페이지 번호. 예) 1, 2, 3, 4, 5
-- amount : 페이지마다 데이터 출력 건수. 예) 10개, 20개, 30개
SELECT RN, BNO, TITLE, CONTENT, WRITER, REGDATE
FROM ( SELECT /*+ INDEX_DESC(TBL_BOARD PK_BOARD) */ ROWNUM RN, BNO, TITLE, CONTENT, WRITER, REGDATE
       FROM TBL_BOARD
       WHERE ROWNUM <= #{pageNum} * #{amount}
WHERE RN > (#{pageNum}-1) * #{amount};

-- pageNum = 1, amount = 10
SELECT RN, BNO, TITLE, CONTENT, WRITER, REGDATE
FROM ( SELECT /*+ INDEX_DESC(TBL_BOARD PK_BOARD) */ ROWNUM RN, BNO, TITLE, CONTENT, WRITER, REGDATE
       FROM TBL_BOARD
       WHERE ROWNUM <= (1 * 10))
WHERE RN > ((1-1) * 10);

-- pageNum = 2, amount = 10
SELECT RN, BNO, TITLE, CONTENT, WRITER, REGDATE
FROM ( SELECT /*+ INDEX_DESC(TBL_BOARD PK_BOARD) */ ROWNUM RN, BNO, TITLE, CONTENT, WRITER, REGDATE
       FROM TBL_BOARD
       WHERE ROWNUM <= (2 * 10))
WHERE RN > ((2-1) * 10);

-- pageNum = 3, amount = 10
SELECT RN, BNO, TITLE, CONTENT, WRITER, REGDATE
FROM ( SELECT /*+ INDEX_DESC(TBL_BOARD PK_BOARD) */ ROWNUM RN, BNO, TITLE, CONTENT, WRITER, REGDATE
       FROM TBL_BOARD
       WHERE ROWNUM <= (3 * 10))
WHERE RN > ((3-1) * 10);

이전페이지들에서 보여준 데이터는 제외하고, 남은 데이터를 현재페이지에서 보여준다