SQLP 11회

-------------------------------------------------------------------------------------------

Q.1 해당 테이블에 대해 인덱스 추천과 해당 요건에 맞는 SQL 을 작성하라

 

 - 테이블 ERD

 - 현재 인덱스는 없음

 - 프로그램 언어에서 Array 단위 Fetch 기능 활용 가능하게 작성(부분범위처리 가능하게)

 - 출력 건수는 10일 단위 즉, 10건을 유지함 (ex.2013-11-01 ~ 2013-11-10) 

 - Array 는 10 으로 잡혀 있다고 가정

 

 - 출력 양식

 

날짜 | 총구매수량 (인터넷구매수량 + 매장구매수량)  | 인터넷 구매수량  | 매장구매수량

 

2013-11-01 100   50 50

2013-11-02 120   40 80

 

-- 아래 부터는 테스트 용도

-- 테이블 정보 

 

DROP TABLE LSS.SQLP_Q1;

CREATE TABLE LSS.SQLP_Q1 

(

 구분 VARCHAR2(50),

 상품명 VARCHAR2(50),

 상품금액 NUMBER, 

 판매수량 NUMBER,

 판매일 VARCHAR2(8)

);

 

INSERT INTO LSS.SQLP_Q1 VALUES('인터넷','A',100,50,'20140101');

INSERT INTO LSS.SQLP_Q1 VALUES('매장','A',100,100,'20140101');

INSERT INTO LSS.SQLP_Q1 VALUES('인터넷','A',100,10,'20140102');

INSERT INTO LSS.SQLP_Q1 VALUES('매장','A',100,40,'20140102');

INSERT INTO LSS.SQLP_Q1 VALUES('인터넷','A',100,200,'20140103');

INSERT INTO LSS.SQLP_Q1 VALUES('인터넷','A',100,30,'20140104');

INSERT INTO LSS.SQLP_Q1 VALUES('매장','A',100,10,'20140104');

 

COMMIT;

 

 

A.1 추정 답안

 

SELECT 판매일, 상품명, SUM(판매수량) 전체판매수량, 

       SUM(CASE WHEN 구분 ='인터넷' THEN 판매수량

                ELSE 0

           END ) AS 인터넷판매수량,

       SUM(CASE WHEN 구분 ='매장' THEN 판매수량

                ELSE 0

           END ) AS 매장판매수량

FROM LSS.SQLP_Q1

WHERE 상품명 = 'A'

AND 판매일 < TO_CHAR(SYSDATE - ((:page-1)*10),'YYYYMMDD')

AND 판매일 >= TO_CHAR(SYSDATE - (:page *10 ),'YYYYMMDD')

GROUP BY 판매일,상품명;

 

 

-- 부분범위처리는 index scan 후 array size 가 될 경우 그 해당 array size 만큼 fetch 를 한다는 의미임

 

INDEX : 상품명 , 판매일 (단, 상품명이 들어올 경우. 안들어올 경우 판매일 + 상품명으로 구성)

 

CREATE INDEX LSS.IX_SQLP_Q1 ON LSS.SQLP_Q1(상품명,판매일);

 

인덱스 생성 후 PLAN

 

Execution Plan

-----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=4 Bytes=292)

   1    0   SORT (GROUP BY NOSORT) (Cost=2 Card=4 Bytes=292)

   2    1     FILTER

   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'LSS.SQLP_Q1' (TABLE) (Cost=2 Card=4 Bytes=292)

   4    3         INDEX (RANGE SCAN) OF 'LSS.IX_SQLP_Q1' (INDEX) (Cost=1 Card=7)

-----------------------------------------------------------

 

Predicate information (identified by operation id):

-----------------------------------------------------------

   2 - filter(TO_CHAR(SYSDATE@!-(TO_NUMBER(:PAGE)-1)*10,'YYYYMMDD')>TO_CHAR(SYSDATE@!-TO_NUMBER(:PAGE)*10,'YYYYMMDD'))

   4 - access("상품명"='A' AND "판매일">=TO_CHAR(SYSDATE@!-TO_NUMBER(:PAGE)*10,'YYYYMMDD') AND "판매일"<TO_CHAR(SYSDATE@!-(TO_NUMBER(:PAGE)-1)*10,'YYYYMMDD'))

-----------------------------------------------------------

 

 

Q.2 해당 실행계획을 참고하여 성능개선 포인트를 적으시오

 

 - 테이블 ERD

 - 현재 인덱스는 변경 불가 (인덱스 2개)

 

 

SELECT 거래코드,상품명, 상품금액 ,거래처 ,SUM(거래금액) AS 전체금액

FROM 

(

  SELECT ROWNUM ,거래코드,상품명, 상품금액 ,거래처 ,거래금액 

  FROM (

      SELECT A.거래코드 , B.상품명, B.상품금액,C.거래처,

            (SELECT 거래금액 FROM 금액 WHERE A.거래코드 = 거래코드) AS 거래금액

      FROM 거래 A, 주문 B, 상품코드 C

      WHERE A.주문코드 =  B.주문코드

      AND A.상품코드 = C.상품코드

      AND 기간 조건

      AND 기타 조건     

  )

  WHERE 페이징 관련 조건

)

WHERE 페이징 관련 조건

GROUP BY 거래코드, 상품명, 상품금액,거래처

 

 

-- 실행계획

-- ??

 

--> 추정

: 다른분의 이야기를 보면 group by 가 메인쿼리의 가장 바깥에 있었다는 내용도 나오고 하던데..

  페이징 관련 조건도 있고요. 여러가지를 봐야 알겠지만 건수를 안에서 미리 줄일 수 있는지 

  라던지..그런게 아니었을까  싶네요.^^

 

cafe.naver.com/sqlpd/2855