I. SQL 성능 향상 가이드.

Presentation on theme: "I. SQL 성능 향상 가이드."— Presentation transcript:

1 I. SQL 성능 향상 가이드

2 1. OR OR : Index를 사용하지 못하는 경우가 있음 S Q L 개선안
SELECT a,거래분류코드, a.sum(전표매수), a.sum(건수), a.sum(금액) FROM 조작자일별상세원장 a WHERE a.국기호 = :입력국기호 AND a.거래일자 = :입력거래일자 AND a.조작자번호 = :입력조작자번호 AND ( a.과목코드 = ‘1’ or a.과목코드 = ‘2’) GROUP BY a.거래분류코드; 개선안 SELECT a,거래분류코드, a.sum(전표매수), a.sum(건수), a.sum(금액) FROM 조작자일별상세원장 a WHERE a.국기호 = :입력국기호 AND a.거래일자 = :입력거래일자 AND a.조작자번호 = :입력조작자번호 AND a.과목코드 IN (‘1’, ’2’) AND GROUP BY a.거래분류코드; SELECT a,거래분류코드, a.sum(전표매수), a.sum(건수), a.sum(금액) FROM 조작자일별상세원장 a WHERE a.국기호 = :입력국기호 AND a.거래일자 = :입력거래일자 AND a.조작자번호 = :입력조작자번호 AND a.과목코드 = ‘1’ GROUP BY a.거래분류코드; UNION ALL AND a.과목코드 = ’2’ SELECT /*+ Use_Concat */ a,거래분류코드, a.sum(전표매수), a.sum(건수), a.sum(금액) FROM 조작자일별상세원장 a WHERE a.국기호 = :입력국기호 AND a.거래일자 = :입력거래일자 AND a.조작자번호 = :입력조작자번호 AND ( a.과목코드 = ‘1’ or a.과목코드 = ‘2’) GROUP BY a.거래분류코드;

3 2. BETWEEN AND 범위 검색 : 인덱스의 효율적인 사용을 위해 ‘between and’ 연산 사용
S Q L SELECT a,거래분류코드, a.sum(전표매수), a.sum(건수), a.sum(금액) FROM 조작자일별상세원장 a WHERE a.국기호 = :입력국기호 AND a.거래일자 >= :입력거래일자1 AND a.거래일자 <= :입력거래일자2 AND a.조작자번호 = :입력조작자번호 AND a.과목코드 IN (‘1’, ’2’) GROUP BY a.거래분류코드; 개선안 SELECT a,거래분류코드, a.sum(전표매수), a.sum(건수), a.sum(금액) FROM 조작자일별상세원장 a WHERE a.국기호 = :입력국기호 AND a.거래일자 BETWEEN :입력거래일자1 AND :입력거래일자2 AND a.조작자번호 = :입력조작자번호 AND a.과목코드 IN (‘1’, ’2’) GROUP BY a.거래분류코드;

4 3. LIKE %를 문자 앞에 사용하면 인덱스를 사용하지 못 함 SELECT a.국명, a.국기호 FROM 관서 a
Q L SELECT a.국명, a.국기호 FROM 관서 a WHERE a.국명 LIKE ‘%국’; 개선안 SELECT a.국명, a.국기호 FROM 관서 a WHERE a.국명 LIKE ‘서울%’;

5 4. JOIN 테이블 조인시 중복되는 조건이 추가되면 성능을 저하시킴 S Q L 개선안
SELECT a,거래분류코드, a.sum(전표매수), a.sum(건수), a.sum(금액) FROM 조작자일별상세원장 a, 관서 b WHERE a.국기호 = :입력국기호 AND b.국기호 = :입력국기호 AND a.국기호 = b.국기호 AND a.거래일자 BETWEEN :입력거래일자1 AND :입력거래일자2 AND a.조작자번호 = :입력조작자번호 AND a.과목코드 IN (‘1’, ’2’) GROUP BY a.거래분류코드; 개선안 SELECT a,거래분류코드, a.sum(전표매수), a.sum(건수), a.sum(금액) FROM 조작자일별상세원장 a, 관서 b WHERE a.국기호 = :입력국기호 AND a.국기호 = b.국기호 AND a.거래일자 BETWEEN :입력거래일자1 AND :입력거래일자2 AND a.조작자번호 = :입력조작자번호 AND a.과목코드 IN (‘1’, ’2’) GROUP BY a.거래분류코드;

6 5. HAVING WHERE절에 표현할 수 있는 부분을 HAVING절에 표현을 하면 인덱스를 사용할 수 없음
S Q L SELECT a.계정코드, COUNT(*) FROM 일계원장 a WHERE a.국기호 = :입력국기호 GROUP BY a.계정코드 HAVING a.계정코드 > ‘AB100’; 개선안 SELECT a.계정코드, COUNT(*) FROM 일계원장 a WHERE a.국기호 = :입력국기호 AND a.계정코드 > ‘AB100’ GROUP BY a.계정코드 ; Example

7 6. EXISTS 유무의 체크는 EXISTS 구문 사용 SELECT a.조작자번호, a.국기호 FROM 조작자정보 a
Q L SELECT a.조작자번호, a.국기호 FROM 조작자정보 a WHERE a.조작자번호 IN (SELECT b.조작자번호 FROM 조작자일별상세원장 b) ; 개선안 SELECT a.조작자번호, a.국기호 FROM 조작자정보 a WHERE EXISTS (SELECT ‘X’ FROM 조작자일별상세원장 b WHERE b.조작자번호 = a.조작자번호) ;

8 6. EXISTS(Cont.) and일 경우엔 Where 구문 다음에, or일 경우엔 Where 구문 마지막에 사용 S Q L
Select … From dept D, Emp E Where E.deptno = D.deptno And E.emp_type = ‘MANAGER’ And D.dept_cat = ‘A’; Select … From dept D, Emp E Where E.deptno = D.deptno And E.emp_type = ‘MANAGER’ Or D.dept_cat = ‘A’; 개선안 Select … From emp E Where exist ( Select ‘x’ From dept Where deptno = E.deptno And dept_cat = ‘A’) And E.emp_type = ‘MANAGER’; Select … From emp E Where E.emp_type = ‘MANAGER’ Or exist ( Select ‘x’ From dept Where deptno = E.deptno And dept_cat = ‘A’);

9 6. EXISTS(Cont.) Distinct는 내부 sorting이 일어나 성능의 저하를 가져옴
Q L Select distinct deptno, deptname From dept d, emp e Where d.deptno = e.deptno 개선안 Select deptno, deptname From dept D Where exists (select ‘x’ from emp E where E.deptno = D.deptno);

10 6. EXISTS(Cont.) Not in 대신에 Not exists를 사용 select … from emp
Q L select … from emp where deptno not in (select deptno from dept where dept_cat = ‘A’); 개선안 select … from emp where not exists (select ‘x’ from dept where dept_cat = ‘A’);

11 7. DRIVING TABLE 두 개 이상의 테이블을 조인하는 경우, 반드시 중심테이블(driving table)이 있어야 함. S Q L Select count(*) From Emp E, Dept D, Location L Where D.deptno = E.deptno And L.locano = E.locano And E.empno Between and 15000; 개선안 Select count(*) From Emp E, Dept D, Location L Where E.empno Between and 15000 And D.deptno = E.deptno And L.locano = E.locano;

12 8. 조건구문의 순서 WHERE절 구문의 순서가 SQL 성능 향상에 영향을 미침 Select E.ename /* 15초 */
From emp E Where E.sal > 50000 And E.emp_type = ‘MANAGER’ And 25 < (Select count(*) from emp where emp_mgr = E.empno); 개선안 Select E.ename /* 1초 */ From emp E Where 25 < (Select count(*) from emp where emp_mgr = E.empno); And E.sal > 50000 And E.emp_type = ‘MANAGER’;

13 9. ROWID Rowid는 가장 빠른 액세스 방법
조회시 For Update 사용 Select rowid, … Into :emp_rowid … From emp Where emp.emp_no = 56789 For Update; Update emp Set emp.ename = ‘xxxxx’ Where rowid = :emp_rowid;

14 10. DATABASE ACCESS 가능한 데이터베이스의 액세스를 줄인다.
즉, 가능한 물리적 I/O를 줄여 한번의 SQL로 처리 S Q L Select ename, sal, grade From emp Where empno = 1234; Where empno = 5678; 개선안 Select a.ename, a.sal, a.grade, b.ename, b.sal, b.grade From emp a, emp b Where a.empno = 1234 and b.empno = 5678;

15 11. DECODE Count, Sum 연산을 할 때, Decode를 사용하면 성능이 향상됨 S Q L 개선안
Select count(*), sum(sal) From emp Where deptno = 1000 And ename like ‘김%’; Where deptno = 3000 개선안 Select count(decode(deptno, 1000, ‘x’, null)) dept1000_count, count(decode(deptno, 3000, ‘x’, null)) dept3000_count, sum(decode(deptno, 1000, sal, null)) dept1000_sal, sum(decode(deptno, 3000, sal, null)) dept3000_sal From emp Where ename like ‘김%’;

16 12. INDEX COLUMN 인덱스 컬럼을 가공하게 되면 인덱스를 사용하지 못 함
S Q L Select account_name, trans_date, amount From Transaction Where substr(account_name,1,7) = ‘CAPITAL’; 개선안 Select account_name, trans_date, amount From Transaction Where account_name like ‘CAPITAL%’;

17 12. INDEX COLUMN(Cont.) Select account_name, trans_date, amount
Q L Select account_name, trans_date, amount From Transaction Where amount != 0; 개선안 Select account_name, trans_date, amount From Transaction Where amount > 0;

18 12. INDEX COLUMN(Cont.) Select account_name, trans_date, amount
Q L Select account_name, trans_date, amount From Transaction Where account_name || account_type = ‘MAXA’; 개선안 Select account_name, trans_date, amount From Transaction Where account_name = ‘MAX’ And account_type = ‘A’;

19 12. INDEX COLUMN(Cont.) Select account_name, trans_date, amount
Q L Select account_name, trans_date, amount From Transaction Where Trunc(trans_date) = Trunc(sysdate); 개선안 Select account_name, trans_date, amount From Transaction Where trans_date Between Trunc(sysdate) and Trunc(sysdate) ;

20 13. IRRELEVANT TABLE SQL성능을 고려하여 관련 없는 테이블도 하나의 SQL로 처리
Select name from emp Where empno = 1000; Select name from dept Where deptno = 200; Select name from item Where itemtype = ‘RAD’; 개선안 Select E.name, D.name, I.name From Emp E, Dept D, Item I, Dual X Where NVL(‘x’,X.dummy) = NVL(‘x’,E.rowid(+)) And NVL(‘x’, X.dummy) = NVL(‘x’,D.rowid(+)) And NVL(‘x’, X.dummy) = NVL(‘x’,I.rowid(+)) And E.empno(+) = 1000 And D.deptno(+) = 200 And I.itemtype(+) = ‘RAD’;

21 14. Sub-query Table 수 서브쿼리에서 사용하는 테이블의 수를 줄인다. Select ename From emp
Where emp_cat = (select max(category) from emp_categories) And sal_range = (select max(sal_range) from emp_categories) And empdept = 2000; 개선안 Select ename From emp Where (emp_cat, sal_range) = (select max(category), max(sal_range) from emp_categories) And empdept = 2000;

22 II. 사례별 SQL 사용법 가이드

23 사례 1 – index 사용 기간을 연별로 나누고 월별로 나눈 조건을 주면 인덱스를 사용할 수 있다.
S Q L Select a.yy||a.mm, dept, (sale_qty) From sales_summery a Where a.yy||A.mm Between ‘9410’ and ‘9504’ Group by a.yy||a.mm, dept; 개선안 Select a.yy||a.mm, dept, sum(sale_qty) From sales_summery a Where (a.yy = ‘94’ and a.mm betweem ‘10’ and’12’) or (a.yy =’95’ and a.mm between ‘01’ and ‘04’) Group by a.yy||a.mm, dept;

24 사례 1 (Cont.) in을 union all로 대체 select nvl(code,’’), nvl(desc,’’)
Q L select nvl(code,’’), nvl(desc,’’) from items where code like :b1 and item_type in (‘ca’,’da’) and rownum < 21; 개선안 select nvl(code,’’), nvl(desc,’’) from (select code, desc from items where code like :b1 and item_type = ‘ca’ union all select code, desc from items where code like :b1 and item_type = ‘da’) where rownum < 21;

25 사례 2 - outer join의 이해 코드 테이블과 참조하는 테이블의 조인 S Q L
select x.key, x.fld1, y.key, y.col1, y.col2 from tab1 x, tab2 y where x.key = y.key(+) and x.fld1 > ‘aaa’ and y.col1 = 10; select x.key, x.fld1, y.key, y.col1, y.col2 from tab1 x, tab2 y where x.key = y.key(+) and x.fld1 > ‘aaa’ and (y.col1 = 10 or y.col is null); 개선안 select x.key, x.fld1, y.key, y.col1, y.col2 from tab1 x, tab2 y where x.key = y.key(+) and x.fld1 > ‘aaa’ and y.col1(+) = 10;

26 사례 2 (Cont.) outer join에서 in의 사용
S Q L select x.key, x.fld1, y.key, y.col1, y.col2 from tab1 x, tab2 y where x.key = y.key(+) and x.fld1 > ‘aaa’ and y.col1(+) in (10,30,50); 개선안 select x.key, x.fld1, y.key, y.col1, y.col2 from tab1 x, (select key, col1, col2 from tab2 where col1 in (10,30,50)) y where x.key = y.key(+) and x.fld1 > ‘aaa’;

27 사례 3 - 배타적 관계에서 outer join의 활용
계좌정보 테이블은 개인고객 계좌 및 법인고객 계좌로 구성 S Q L create table 계좌정보 (계좌번호 varchar2(10) primary key, …. 고객번호 varchar2(10) not null, 고객종류 varchar2(1) not null constraint check_type check (고객종류 in (‘1’,’2’)); cursor for select … from ..where fetch … if (:고객종류 = ‘1’) select 성명 into :고객명 from 개인고객정보 where 고객번호 = :고객번호; 개선안 select a.계좌번호, a.개설일, b.개인고객명, c.법인고객명 from 계좌정보 a, 개인고객 b, 법인고객 c where a.개설일 like ‘199510%’ and a.고객번호 = b.고객번호(+) and a.고객번호 = c.고객번호(+) order by a.개설일;

28 사례 4 – 조인하는 테이블의 순서에 따른 차이 조인할 때 where구문의 테이블 순서와 컬럼의 상수값의 위치에 따라 실행계획이 달라질 수 있다. 납품실행계획 a (부서번호#,주문번호#,직원번호#):가장 건수가 많다. 주문 b (주문번호#) 고객 d (고객번호#) 수주진행현황 c (회사번호#,부서번호#,주문번호#,주문일자#,고객번호#)

29 사례 4 (Cont.) S Q L select distinct a.부서번호, a.주문번호, b.주문일자, b.직원번호, b.고객번호, d.출고중지구분 from 납품실행계획a, 주문b, 고객d, 수주진행현황 c where c.부서번호 = b.부서번호 and c.고객번호 = b.고객번호 and c.직원번호 = b.직원번호 and c.주문번호 = b.주문번호 and c.주문일자 = b.주문일자 and a.부서번호 = b.부서번호 and a.주문번호 = b.주문번호 and a.주문일자 = b.주문일자 and a.완료여부 is null and c.고객번호 = d.고객번호 and c.부서번호 = 3000 and c.회사번호 = 3; 개선안 select /*+ordered */ distinct a.부서번호, a.주문번호, b.주문일자, b.직원번호, b.고객번호, d.출고중지구분 from 수주진행현황 c, 납품실행계획 a, 고객 d, 주문 b (c의 범위를 줄여서 드라이빙 테이블로 이용한다.) where c.부서번호 = b.부서번호 and c.고객번호 = b.고객번호 and c.직원번호 = b.직원번호 and c.주문번호 = b.주문번호 and c.주문일자 = b.주문일자 and a.부서번호 = c.부서번호 and a.주문번호 = c.주문번호 and a.주문일자 = c.주문일자 and a.완료여부 is null and c.고객번호 = d.고객번호 and c.부서번호 = 3000 and c.회사번호 = 3;

30 사례 5 – MAX값의 처리 가장 효율적인 MAX값 처리를 위해 index된 컬럼을 이용한다. S Q L 개선안
select stock_q from GFLM600 a where io_date = (select max(io_date) from GFLM600 b where b.car_code = :value1 and b.box_code = :value2 and a.car_code = b.car_code and a.box_code = b.box_code); 개선안 select /*+ index_desc(a GFLM600_PK) :car_code + box_code + io_date */ stock_q from GFLM600 a where car_code = :value1 and box_code = :value2 and rownum = 1;

31 사례 5 (Cont.) max-min에서 sql분리 S Q L 개선안
select (max(eqpmroom) – min(eqpmroom)) + 1 into :nextroom from thiseqpm where eqpmdept = :value1 and eqpmpart = :value2; 개선안 Select /*+ index_desc (thiseqpm thiseqpm_x1) */ Eqmproom into max_room From thiseqpm Where eqpmdept = :value1 And eqpmpart = :value2 And rownum=1; Select /*+ index_asc (thiseqpm thiseqpm_x1) */ Eqmproom into min_room From thiseqpm Where eqpmdept = :value1 And eqpmpart = :value2 And rownum=1;

32 사례 6 – 인덱스 칼럼 분리에 따른 문제해결 In line view를 이용한 group by의 효과적인 처리
Sale Table : Product_no + Saledate Product Table : Product_no에 인덱스가 있다. S Q L Select product_no, sum(sale_amt*unit_price) From product Where product_no between ‘P2150’ and ‘P2160’ And saledate like ‘1997%’ Group by product_no; 개선안 Select a.product_no, sum(a.sale_amt*unit_price) From product a, (select product_no from product where product_no between ‘P2150’ and ‘P2160’) b where a.product_no = b.product_no and a.saledate like ‘1995%’ group by a.product_no;

33 사례 6 (Cont.) Decode와 group by의 효율적인 사용 S Q L 개선안
Select job_class, sex, sum(decode(reg_wd,’200101’,1,0))as 0101월, sum(decode(reg_wd,’ ’,1,0))as 0102월, sum(decode(reg_wd,’ ’,1,0))as 0103월 from qualifier group by job_class, sex; 개선안 Select JC,SX, sum(decode(R_W,’ ’,CNT,0)) as 0101월, sum(decode(R_W,’ ’,CNT,0)) as 0102월, sum(decode(R_W,’ ’,CNT,0)) as 0103월 from (select /*+ full(qualifier) paraller(qualifier,2) */ job_class as JC, sex as SX, reg_wd as R_W, count(*) as CNT from qulifier group by job_class, sex, reg_wd) group by JC, SX