▷ 정렬이 이미 되어 있는 상태이므로 데이터를 찾아내서 이들을 SORT하는 과정을 생략할 수 있음
▷ SORT가 없으면 0초에 가까운 성능을 보여줌
▶ 데이터 양이 많고 정렬이 필요한 상황이라면 우선적으로 생각하는 것이 '인덱스' 작성하는 것
(1) 인덱스와 오라클 힌트(hint)
▷ 웹페이지 목록은 주로 시간의 역순으로 정렬된 결과를 보여줌(최신 데이터가 가장 중요하기 때문)
▷ 오라클의 select문 전달시 'hint' 사용 가능 : 내가 전달한 select 문을 이렇게 실행해 주면 좋겠다 라는 힌트
▶ select문을 어떻게 처리할지에 대한 얘기일 뿐이므로 힌트 구문에서 에러가 나도 전혀 SQL 실행에 지장을 주지 않음
▷ 힌트를 이용한 select문 작성후 실행 계획을 통해 개발자가 원하는 대로 SQL이 실행되는지 확인하는 습관이 필요
▷ 하지만 개발자가 데이터베이스에 어떤 방식으로 실행해 줘야 하는지 명시하기 때문에 조금 강제성이 부여됨
◎ 아래의 SQL 구문 두 개는 동일한 결과를 나타냄
select * from tbl_board order by bno desc;
select /*+ index_desc(tbl_board pk_board) */ * from tbl_board where bno > 0;
▷ 아래의 select문은 order by 조건이 없어도 동일한 결과가 나타납니다. ▷select문에 힌트를 부여한 것이고 힌트의 내용이 'tbl_board 인덱스를 역순으로 이용해 줄 것'이므로 실행 계획에서 이를 활용하고 있는 것을 확인할 수 있습니다.
(2) 힌트 사용 문법
▷ select문을 작성할 때 힌트는 잘못 작성되어도 실행할 때는 무시되기만 하고 별도의 에러가 발생하지 않음
▷ 힌트 사용 문법
SELECT /*+ Hint name (param...) */ colunm name, ... FROM table name ......
힌트 구문은 '/*+'로 시작하고 '*/'로 마무리 됩니다. 힌트 자체는 SQL로 처리되지 않으므로 칼럼명이 나오더라도 별도의 ','로 처리되지 않습니다.
(3) FULL 힌트
▷ 해당 select문을 실행할 때 테이블 전체를 스캔할 것으로 명시하는 FULL 힌트
▷ 모든 데이터를 스캔하기 떄문에 데이터가 많을 때는 상당히 느리게 실행됨
▷ tbl_board 테이블을 FULL 스캔하고 정렬하려면 아래와 같은 구문 작성 가능
select /*+ full(tbl_board) */ * from tbl_board order by bno desc;
SORT가 적용이 되었기 떄문에 정렬하는데 시간이 오래걸립니다. ▷ 0.204초 소요됨
(4) INDEX_ASC, INDEX_DESC 힌트
▷ 목록 페이지에서 가장 많이 사용하는 힌트이며 인덱스를 순서대로 또는 역순으로 이용할 것인지 지정하는 것
▷ INDEX_ASC/DESC 힌트는 주로 'order by'를 위해 사용함
▷ 인덱스 자체가 정렬을 해 둔 상태이므로 이를 통해 sort 과정을 생략하기 위한 용도
▷ INDEX_ASC/DESC 힌트는 테이블 이름과 인덱스 이름을 같이 파라미터로 사용
select /*+ index_asc(tbl_board pk_board) */ * from tbl_board where bno > 0;
◎ 검색 시간은 1/20 정도 줄었으며 sort는 없습니다.
▷INDEX_ASC/DESC를 이용하는 경우 동일한 조건의 order by 구문을 작성하지 않아도 됨 ▷order by 조건 없어도 bno의 순번을 통해서 접근하기 때문에 'order by bno asc' 구문은 필요 없음
4. rownum과 인라인뷰
▷ 역순으로 게시물 목록 조회 가능했다면 전체가 아닌 필요한 만큼의 데이터를 가져오는 방식 학습
▷ 오라클은 rownum 키워드를 통해 데이터에 순번을 붙여 페이지 처리를 함
▷ SQL이 실행된 결과에 넘버링 해주는 것, SELECT문으로 해당 데이터가 몇 번째로 나오는지 알아낼 수 있음
▷ 테이블에서 데이터를 추출한 후 처리되는 변수이므로 상황에 따라 값이 매번 달라짐
◎ rownum을 이용한 구문
select rownum rn, bno, title from tbl_board;
▷sql에 아무 조건이 없으므로 테이블에 섞여있는 상태로 그대로 나오게 됨(테이블을 FULL 스캔한 것과 동일)
◎ 소요 시간 : 0.002초
rownum은 테이블에는 존재하지 않고, 테이블에서 가장 먼저 가져올 수 있는 데이터들을 꺼내서 번호를 붙임
◎ rownum과 hint를 이용한 구문
select /*+FULL(tbl_board)*/ rownum rn, bno, title from tbl_board where bno>0 order by bno;
▷위의 구문은 FULL 힌트를 이용해 전체 데이터를 조회하고 다시 정렬한 방식 ▷ROWNUM은 데이터를 가져올 때 적용되는 것, 이 후 정렬되는 과정에서 ROWNUM은 변경되지 않음 ◎소요 시간 : 0.153초
(1) 인덱스를 이용한 접근 시 ROWNUM
▷ PK_BOARD 인덱스를 통해 접근한다면 아래와 같은 과정으로 접근
▶ PK_BOARD 인덱스를 통해 테이블에 접근
▶ 접근한 데이터에 ROWNUM 부여
▷ 위의 과정에서 이미 정렬 되어있으므로 218번의 접근 순서는 3번째가 아니라 한참 뒤임
◎ 인덱스 사용 예제
select /*+INDEX_ASC(tbl_board pk_board) */ rownum rn, bno, title from tbl_board;
▷SQL은 인덱스를 찾는 순서가 다름 ▷가장 먼저 찾은 데이터부터 ROWNUM이 1부터 시작
▷힌트를 이용해 tbl_board 테이블을 pk_board 순번으로 접근하면 ROWNUM의 값이 순서대로 나오도록 달라집니다.
◎ 역순으로 검색
select /*+INDEX_DESC(tbl_board pk_board) */ rownum rn, bno, title,content from tbl_board where bno>0;
▷bno의 역순으로 접근했기 때문에 bno 값이 가장 큰 데이터가 ROWNUM 값이 1이 됩니다.
(2) 페이지 번호 1, 2의 데이터
▷ 한 페이지당 10개의 데이터를 출력한다고 가정하면 ROWNUM 조건을 WHERE 구문에 추가해서 아래와 같이 작성 가능
select /*+INDEX_DESC(tbl_board pk_board) */ rownum rn, bno, title,content from tbl_board where rownum<=10;
◎ 출력 화면
▷ 가장 높은 번호 게시물 10개만 출력됩니다. ▷PK_BOARD 인덱스 역순으로 접근하며 where 조건에서 rownum 조건은 테이블 접근시 필터링 적용
◎ 2 페이지 데이터 구하기
select /*+INDEX_DESC(tbl_board pk_board) */
rownum rn, bno, title,content from tbl_board where rownum > 10 and rownum <= 20;
◎ 출력 화면 ▷ 아무 결과가 나오지 않음 ▷ 그 이유는 ROWNUM > 10인 값들을 찾을 때 ROWNUM 1의 값은 where 조건에 의해 무효화 되고 다시 다른 데이터를 가져오면 새로운 데이터가 첫 번째 데이터가 되므로 다시 ROWNUM은 1이 됩니다. ▷ 똑같은 과정이 반복되면 아무것도 나오지 않음, 따라서 SQL 작성시 ROWNUM 조건은 반드시 1포함
◎ 1을 포함하여 20까지 출력하는 코드
select /*+INDEX_DESC(tbl_board pk_board) */
rownum rn, bno, title,content from tbl_board where rownum <= 20;
(3) 인라인뷰(in-line View) 처리
▷ 10개씩 출력시 2페이지 20개 데이터 가져올 수 있지만, 1페이지 내용이 같이 나오는 문제 발생
▷ select문 안쪽 from에 select문 사용
▷ 뷰란 '창문'같은 개념으로 복잡한 SELECT 처리를 하나의 뷰로 생성하고 사용자들은 뷰를 통해 나온 결과를
하나의 테이블처럼 쉽게 조회함
select bno, title,content from(select /*+INDEX_DESC(tbl_board pk_board) */
rownum rn, bno, title,content from tbl_board where rownum <= 20) where rn > 10;
20개의 데이터를 가져온 후 2페이지에 해당하는 10개만을 추출해 출력합니다.
◎ 실행 순서 ▷ 필요한 순서로 정렬된 데이터에 ROWNUM을 붙임 ▷ 처음부터 해당 페이지의 데이터를 'ROWNUM <= 30'과 같은 조건을 이용해서 구함 ▷ 구해놓은 데이터를 하나의 테이블처럼 간주하고 인라인뷰로 처리 ▷ 인라인뷰에서 필요한 데이터만 남김
오라클의 데이터베이스 페이징 처리를 위해 힌트와 ROWNUM을 사용하는 방법에 대해 알아보았습니다.
힌트를 사용하면 강제로 해당 구문을 수행해라고 지시할 수 있으며 Rownum을 이용하면 테이블의 한 행마다 번호가 부착되며 원하는 페이지를 출력할 수 있습니다.
