Oracle SQL - 게시판용 table 생성 예제 단계별 (3)

--부분범위 처리 단계 1

select 

* 

from

(

select /*+INDEX_DESC(tbl_bbs pk_bbs)*/ 

 rownum rn, bbsno, title, content writer, regdate

from tbl_bbs

where rownum <= 20 and bbsno>0

) bbs

where rn>10;

--부분 범위 처리 2단계, 관련 테이블 조인(인덱스 확인 필수)

create index idx_reply_bbs on tbl_reply(bbsno desc);

drop index idx_reply_bbs;

select

*

from

  (

    select * from

    (

      select /*+INDEX_DESC(tbl_bbs pk_bbs)*/ 

      rownum rn, bbsno, title, content writer, regdate

      from tbl_bbs

      where rownum <= 20 and bbsno>0

    ) bbs

    where rn>10

  ) bbs, tbl_reply reply

where bbs.bbsno = reply.bbsno(+);

--3단계 Group By, (데이터가 적을때는 order by 사용 가능)

select

  bbs.bbsno, min(title), min(content), min(writer), count(reply.bbsno), count(*) over() total

from

  (

    select * from

    (

      select /*+INDEX_DESC(tbl_bbs pk_bbs)*/ 

      rownum rn, bbsno, title, content, writer, regdate

      from tbl_bbs

      where rownum <= 1000 and bbsno>0

    ) bbs

    where rn>990

  ) bbs, tbl_reply reply

where bbs.bbsno = reply.bbsno(+)

group by bbs.bbsno;

--4단계 글 제목 검색 추가(먼저 500,000건을 불러 검색 조건을 적용)

select

  bbs.bbsno, min(title), min(content), min(writer), count(reply.bbsno), count(*) over() total

from

  (

    select * from

    (

      select *

      from (select /*+INDEX_DESC(tbl_bbs pk_bbs)*/ rownum rn, bbsno, title, content, writer, regdate 

            from tbl_bbs where rownum <=500000 and bbsno>0)

      where title like '%23435%' 

    ) bbs

    where rn>50

  ) bbs, tbl_reply reply

where bbs.bbsno = reply.bbsno(+)

group by bbs.bbsno;