서술형문제4 SQL 작성

주문 테이블 구조는 아래와 같으며, 파티셔닝 하지 않았다.

하루 주문 건수는 평균 2만 건이며, 10년치 데이터가 저장돼 있다.

주문 데이터를 조회하는 화면은 아래와 같다. 고객번호는 입력하지 않을 수 있지만, 주문일자는 항상 입력해야 한다. 주문일자로는 보통 3일을 입력하며, 최대 1주일까지 입력할 수 있다.

1.조회버튼을 누를 때 수행할 최적의 SQL을 작성하시오.

개발 정책 상 Dynamic SQL은 사용할 수 없다. 주문일시 기준 역순으로 정렬해야 하며, 부분범위처리는 허용되지 않는다. 즉 조회된 결과 집합 전체를 그리드(Grid)에 출력해야 한다.

2.최적의 인덱스 구성안을 제시하시오.

 

화면

튜닝 Point

1.전체범위처리로 처리해야 함

2. 고객번호는 입력하지 않을 수 있지만 주문일자는 항상 입력해야 한다.

3. 주문일시 기준 역순으로 정렬해야 함

 

질문에서 나온 point들을 정리하면 3가지 정도가 될 것 같습니다.

 

 

정답

오라클은 NVL과 DECODE를 사용할 시 옵티아미저가 UNION ALL 방식으로 자동 변환해줍니다.

즉, NVL과 DECODE를 이용해서 고객번호가 NULL 일경우도 표현할 수 있습니다. 하지만 무조건 쓸 수 있는 것은 아닙니다. 모든 NVL/DECODE를 UNION ALL로 변환해주지 않을 뿐 아니라, 조건절 컬럼이 Nullable 컬럼일 경우 NVL/DECODE를 사용하면 결과집합에 오류가 발생합니다. 위의 조건에서는 조건절 컬럼(고객번호,주문번호) 가 Not NULL 컬럼이고 옵션조건(고객번호가 Null일 수도 있다)이 하나이기 때문에 사용해도 무방할 것 같습니다.

제일 확실한 방법은 NVL/DECODE를 쓰지않고 작성하면 제일 확실할 것 같아 제일 먼저 정답으로 작성해봤습니다.

 

(:a,:b,:c는 각각의 바인드변수임)

1.

select 고객번호,주문일시,주문금액,우편번호,배송지
from 주문
where :a is null
and 주문일자 >= to_date(:b,'YYYYMMDD')
and 주문일자 <= to_date(:c,'YYYYMMDD')
union all
select 고객번호,주문일시,주문금액,우편번호,배송지
from 주문
where :a is not null
and 고객번호 = :a
and 주문일자 >= to_date(:b,'YYYYMMDD')
and 주문일자 <= to_date(:c,'YYYYMMDD')
order by 주문일시 desc

 

다른 정답-1

 

select 고객번호,주문일시,주문금액,우편번호,배송지
from 주문
where 고객번호 = NVL(:a,고객번호)
and 주문일자 >= to_date(:b,'YYYYMMDD')
and 주문일자 <= to_date(:c,'YYYYMMDD')
order by 주문일시 desc

 

다른 정답-2

 

select 고객번호,주문일시,주문금액,우편번호,배송지
from 주문
where 고객번호 = DECODE(:a,NULL,고객번호,:a)
and 주문일자 >= to_date(:b,'YYYYMMDD')
and 주문일자 <= to_date(:c,'YYYYMMDD')
order by 주문일시 desc

 

또한 주문일자를 표현하는 방법에서

다른표현 방법-1

and 주문일자 >= to_date(:b,'YYYYMMDD')
and 주문일자 < to_date(:c,'YYYYMMDD') +1

이나

다른표현 방법-2

and 주문일자 between to_date(:b,'YYYYMMDD') and to_date(:c,'YYYYMMDD')

로도 표현할 수 있을 것같습니다.

 

 

2.
인덱스의 경우에는 where 절의 컬럼을 기준으로 생성하면 될 것 같습니다. 정답의 경우 고객번호,주문일자 두개의 컬럼이 조건절에 있는데요, 그렇다면 고객번호 + 주문일자 인덱스 하나만 생성하면 될까요? (저는 문제를 풀때 하나만 적었었는데 해설을 보니 아니더라구요..)

고객번호가 없을 경우도 있기 때문에 주문일자만 있는 인덱스까지 두개가 필요합니다.

인덱스를 주문일자 + 고객번호로 생성할 경우 하나만 생성하면 되지만! 하루 주문건수가 2만건이고 10년치 데이터가 저장되있는 주문 테이블에서 주문일자 컬럼은 변별력이 크게 없습니다. 따라서 고객번호+주문일자가 있는 인덱스와 고객번호가 null일 경우를 대비해서 주문일자만 있는 인덱스를 생성하면 되겠습니다.

또한 파티셔닝이 되어 있지 않기 때문에 일반인덱스로 생성하면 되겠죠?

 

CREATE INDEX 주문_IDX1 ON 주문(고객번호,주문일자);

CREATE INDEX 주문_IDX2 ON 주문(주문일자);