#1 고객명이 입력되었을 때 주문일자 최근 순으로 10건씩 조회 할 때 SQL 작성 및 인덱스 만들기
주문 6천만건
고객 100만건
고객 1명당 평균 60건의 주문
====================================================
이렇게 rownum을 맨 안의 rownum에 붙이게되면 원하는 결과가 나오지 않는다.
위의 sql과 결과를 보면 알듯이 rownum이 정렬된 순서에 붙는게 아니라
조회되는 순서에 붙기때문에 rownum을 쓸때는 select 밖의 테이블에서 붙여야된다.
이런식으로 밖의 테이블에서 rownum을 활용하면 된다.
지금은 최초 10건을 활용하고 있지만 11~20건을 조회하고 싶다면 아래와 같이 sql을 작성하면된다.
아래와 같이 sql을 작성해도 같은 결과가 나오지만 권장하지 않는다.
그 이유는 다음 위의 두 SQL의 실행계획을 TKPROF로 분석한 것을 확인해보자
위의 SQL이 권장하는 방식이고 아래는 권장하지 않는 SQL 방식이다.
둘의 차이는
위는 COUNT STOPKEY , SORT ORDER BY STOPKEY
아래는 COUNT, SORT ODER BY 가 나와있다.
왼쪽의 ROWS를 확인하면 아래는 59 위는 20으로 차이가 난다.
위는 20개의 ROW를 가지고 정렬을 수행하고 아래는 59개의 ROW를 가지고 ORDER를 수행한다는 점이다. 59개의 ROW를 가지고 수행한다는 것은 읽을 수 있는 모든 ROW를 읽어와서 수행하기 때문에 건수가 많아질수록 이 방식은 최적화 대신 과부하를 선사할 수 있다.
이러한 결과는 COUNT STOP KEY라는 실행계획에서 차이가 발생한다. top n 쿼리를 통해 상위 N개를 가지고 소팅을 진행하기 때문이다. 그렇기에 권장하는 방식을 따르는것이 좋다.
이제 쿼리를 가지고 인덱스 구성을 실행한다.
고객명을 가지고 고객에 접근하고 있고
고객번호를 가지고 주문에 접근하고 있다.
그리고 주문일자를 통해서 정렬이 필요로하고있기때문에 세개의 컬럼을 위주로 살펴보겠다.
100만명 중에 1명을 통해서 접근하기때문에 인덱스를 구성해주면 인덱스 효율이 좋을 수 있다.
그리고 고객번호를 통해 조인을 하고 고객번호당 60건 정도의 건수가 있기에 선택도가 낮아서 인덱스를 구성해 주면 좋다.
그리고 인덱스를 통해서 주문일자를 미리 정렬해주고 원하는 구간의 10건정도를 미리 얻을 수 있으면 디스크IO를 줄일수 있어서 좋지만 안해줘도 충분히 괜찮다고 생각한다. 만약 반응속도가 빨라야 하는 OLTP환경일 경우는 인덱스 구성하고 NL조인을 통해서 구성하면 좋다.
그렇기에
create index custmoer_IDX_01 on 고객(고객명);
create index order_IDX_01 on 주문(고객번호, 주문일자);를 만들고 실행계획을 살펴보겠다.
만든 인덱스를 통하여 접근하였고 87000이상이였던 cost가 68로 줄었다.
그리고 cr(consistent read)도 31만에서 66으로 줄었다.
INDEX RANGE SCAN에서도 3건으로 ORDER_IDX_01을 통해 59개의 ROW을 읽었기때문에 비효율은 없다. 그리고 COUNT STOPKEY를 통해서도 비효율이 없으므로 이정도로 마무리 해도 괜찮을 것같다.
create index custmoer_IDX_01 on 고객(고객명);
create index order_IDX_01 on 주문(고객번호, 주문일자);
select *
from(
select rownum as rnm , t.*
from(
select c.고객명, j.주문일자,j.주문금액,j.배송지
from 고객 c, 주문 j
where 1=1
and c.고객명 = 'LMVPJ'
and j.고객번호 = c.고객번호
order by j.주문일자 desc) t
where rownum <=20
) t2
where 1=1
and t2.rnm >10
;