by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

1.2 인덱스의 적용원칙

Version 8 by 김정식
on 5월 15, 2019 12:55.


compared with
Current by 김정식
on 5월 17, 2019 13:04.


 
Key
These lines were removed. This word was removed.
These lines were added. This word was added.

View page history


There are 35 changes. View first change.

 h2. 양호한 엑세스 경로를 보장받기 위한 조건
 - 좋은 액세스 경로가 생성될 수 있도록 테이블을 설계.
 - 적절하고 종합적인 경우를 대비한 인덱스의 지정.
 - 넓은 범위의 처리나 조인의 효율성을 향상하기 위한 클러스터링.
 - 인덱스 적용원칙에 맞는 SQL코딩.
 - 효율적인 SQL구사.
 - 통계 자료의 주기적인 재생성.
 - 힌트나 사용제한(Suppressing) 기능의 활용.
  
 \\
  
 h2. 인덱스를 사용하지 않는 경우
 - 인덱스 컬럼이 비교되기 전에 변형이 일어날 경우.
 {code} SELECT deptno FROM dept WHERE SUBSTR(dname,1,3) = 'ABC'{code}
 - 부정형(NOT, <>)으로 조건을 기술한 경우.
 {code} SELECT ename FROM emp WHERE job <> 'SALES' {code}
 - 인덱스 컬럼이 NULL로 비교되는 경우(NULL, NOT NULL의 사용)
 {code} SELECT * FROM emp WHERE ename IS NOT NULL {code}
 - 옵티마이져가 특정 인덱스의 사용을 취사 선택할 경우 사용되지 않을 수 있음
 - 조건의 순위(Ranking), 사용자의 힌트, 통계정보에 의거하여 산출된 액세스비용 등등..
 {code} SELECT * FROM emp WHERE job LIKE 'AB%' AND empno = '7890 {code}
  
 \\
  
 
 h2. 가. 인덱스컬럼의 변형(Suppressing)
  
  
 h4. 외부적(External) 변형
  
 || 개선 전 || 개선 후 ||
 | SELECT dept, ename
  FROM emp
  WHERE SUBSTR(job, 1, 4)='SALE' | SELECT dept, ename
  FROM emp
  WHERE job LIKE 'SALE%' |
 | SELECT dept, ename
  FROM emp
  WHERE sal*12 = 100000 | SELECT dept, ename
  FROM emp
  WHERE sal = 100000/12 |
 | SELECT dept, ename
  FROM emp
  WHERE NVL(job, 'X') = 'SALE'| SELECT dept, ename
  FROM emp
  WHERE job = 'SALE' |
  
\\
  
 h5. 인덱스의 특징을 역으로 이용하여 수행속도를 향상.
 h6. 'CUSTNO'와 'STATUS'는 각각 인덱스가 생성되어 있고 'STATUS'가 '90'인 경우에는 분포도가 넓다고 가정하면.
 * 개선 전
 {code:SQL}
 SELECT custno, chuldate
  FROM chulgot
  WHERE custno = 'DN01'
  AND status = '90'
 {code}
  
 * 개선 후
 {code:SQL}
 SELECT custno, chuldate
  FROM chulgot
  WHERE custno = 'DN01'
  AND RTRIM(status) = '90'
 {code}
  
 h6. ORD_DATE LIKE '9502%'를 만족하는 로우수가 ORD_DEPT = '12345'를 만족하는 로우 수보다 적다고 가정 했을 경우..
 * 개선 전
 {code:SQL}
 SELECT x.ordno, x.ord_date, y.item, y.ordqty
  FROM ORDER1T x, ORDER2T y
  WHERE x.ordno = y.ordno
  AND x.ord_date LIKE '9502%'
  AND y.orddept = '12345'
  ORDER BY ord_date
 {code}
  
 * 개선 후
 {code:SQL}
 SELECT x.ordno, x.ord_date, y.item, y.ordqty
  FROM ORDER1T x, ORDER2T y
  WHERE x.ordno = y.ordno
  AND x.ord_date LIKE '9502%'
  AND RTRIM(y.orddept = '12345')
 ORDER BY ord_date
 {code}
  
 h6. sale_dept 가 95년도인 데이터가 아주 많고, sale_dept로 생성된 인덱스가 있다고 가정 했을 경우.
 * 개선 전
 {code:SQL}
 SELECT sal_no, sale_date, sale_dept, saleqty
  FROM mechult
  WHERE sale_date LIKE '95%'
  ORDER BY sale_dept
 {code}
  
 * 개선 후
 {code:SQL}
 SELECT sal_no, sale_date, sale_dept, saleqty
  FROM mechult
  WHERE RTRIM(sale_date) LIKE '95%'
  AND sale_dept > ''
 {code}
  
\\
  
 h4. 내부적 변형
  
h5. JOIN시 데이터 타입의 통일 {section}{column:width=48%}
 {code:SQL}
  h5. JOIN시 데이터 타입의 통일
  
 | {code:SQL}
 SELECT chr,num,var,dat
  FROM samplet
  WHERE chr = 10
{code}{column}{column:width=4%}===> {column}{column:width=48%}
 {code:SQL}
  {code} | ===> | {code:SQL}
 SELECT chr,num,var,dat
  FROM samplet
  WHERE to_number(chr) = 10
{code}{column}{section}=> 문자타입을 숫자와 비교: 숫자로 변형.
 => 숫자타입을 문자와 비교: 형변환 없음.(단, like '10%'와 같이 비교하는 경우 문자로 형변환 됨)
  {code} |
  
- 문자타입을 숫자와 비교: 숫자로 변형.
 - 숫자타입을 문자와 비교: 형변환 없음.(단, like '10%'와 같이 비교하는 경우 문자로 형변환 됨)
  
 * 그럼 여기서 질문....(DAT컬럼은 Data type)
  
 {code:SQL}
 SELECT *
 FROM SAMPLET
 WHERE DAT = '01-JAN-94'
 {code}
  
h4. 나. 부정형비교
  h2. 나. 부정형비교
  
* 부정형을 긍정형으로 유도 {section}{column:width=33%}{code:SQL}
  h4. 부정형을 긍정형으로 유도
 * 개선 전
 {code:SQL}
 SELECT 'Not found'
  FROM EMP
  WHERE EMPNO <> 7369
{code}(n)
 \\
 \\ {column}{column:width=33%}{code:SQL}
  {code}
  
 * 개선 후
 {code:SQL}
 SELECT 'NOT FOUND'
 FROM DUAL
 WHERE NOT EXISTS
  ( SELECT 'X' FROM EMP
  WHERE EMPNO = 7369 )
{code}(y)
 \\
 \\ {column}{column:width=33%}{code:SQL}
  {code}
 {code:SQL}
 SELECT 'Not found'
  FROM emp a
  WHERE NOT EXISTS
  (SELECT empno FROM emp b
  WHERE b.empno = 7369
  AND a.empno = b.empno)
{code}(y)
 \\
 \\ {column}{section}
  {code}
  
=> TAB1테이블의 'YYYYMM','COL1'가 각각 인덱스로 생성되어 있고 TAB2의 'YYYYMM','COL2'가 각각 인덱스로 생성되어 있다 {section}{column:width=33%}
  * TAB1테이블의 'YYYYMM','COL1'가 각각 인덱스로 생성되어 있고 TAB2의 'YYYYMM','COL2'가 각각 인덱스로 생성되어 있다
  
* 개선 전
 {code:SQL}
 1)
 SELECT *
  FROM TAB1
  WHERE YYYYMM = '199910'
  AND NOT EXISTS ( SELECT *
  FROM TAB2
  WHERE COL2 = COL1
  AND YYYYMM = '199910')
{code}(n)
 \\
 \\
 \\
 \\ {column}{column:width=33%}
  {code}
 {code:SQL}
 2)
 SELECT *
  FROM TAB1
  WHERE YYYYMM ='199910'
  AND COL1 NOT IN (SELECT COL2
  FROM TAB2
  WHERE YYYYMM = '199910')
{code}(n)
 \\
 \\
 \\
 \\ {column}{column:width=33%}
  {code}
  
 * 개선 후
 {code:SQL}
 3)
 SELECT *
 FROM TAB1
 WHERE (YYYYMM, COL1) IN (SELECT '199910', COL1
  FROM TAB1
  WHERE YYYYMM = '199910'
  MINUS
  SELECT '199910', COL2
  FROM TAB2
  WHERE YYYYMM = '199910')
{code}(y)
  {code}
  
 * 1) 과 2) 에서의 sub쿼리는 나중에 수행되거나 check조건으로 수행된다.
 * 3) 인 경우엔 서브쿼리에서 MINUS결과를 먼저 수행하고, 그 결과를 가지고 메인쿼리를 엑세스 한다.
 * 각각 테이블에 'YYYYMM+COL1','YYYYMM+COL2'로 구성된 인덱스가 있다면 테이블을 엑세스 하지 않고 인덱스만 가지고 sort merge방식으로 서브쿼리를 먼저 수행한 후 처리된 서브쿼리의 결과로 메인쿼리의 조건으로 사용한다.
 \\
 
 \\
\\
 \\ {column}{section}
 1) 과 2) 에서의 sub쿼리는 나중에 수행되거나 check조건으로 수행된다.
 3) 인 경우엔 서브쿼리에서 MINUS결과를 먼저 수행하고, 그 결과를 가지고 메인쿼리를 엑세스 한다.
 각각 테이블에 'YYYYMM+COL1','YYYYMM+COL2'로 구성된 인덱스가 있다면 테이블을 엑세스 하지 않고 인덱스만 가지고 sort merge방식으로 서브쿼리를 먼저 수행한 후 처리된 서브쿼리의 결과로 메인쿼리의 조건으로 사용한다.
 \\
 \\
 \\
 \\
 h4. 다. NULL을 사용한 비교
  
h2. 다. NULL을 사용한 비교
  
 
 h4. (1) NULL컬럼의 적용
{section}{column:width=50%}
  * 개선 전
 {code:SQL}
 SELECT *
  FROM emp
  WHERE ename IS NOT NULL
{code}(n)
 \\
 \\
 \\
 \\ {column}{column:width=50%}
  {code}
 * 개선 후
 {code:SQL}
 SELECT *
  FROM emp
  WHERE ename > ''
{code}(y)
 \\
 \\
 \\
 \\ {column}{section}{section}{column:width=50%}
  {code}
  
 * 개선 전
 {code:SQL}
 SELECT *
  FROM emp
  WHERE empno IS NOT NULL
{code}(n)
 \\
 \\
 \\
 \\ {column}{column:width=50%}
  {code}
  
 * 개선 후
 {code:SQL}
 SELECT *
  FROM emp
  WHERE empno > 0
{code}(y)
 \\
 \\
 \\
 \\ {column}{section}
 - 옵티마이져 모드가 Rule_based 이거나 Cost_based 모드에서 'FIRST_ROWS'로 설정되었을 때 가능하다. {section}{column:width=100%}{column}
  {code}
  
 * 옵티마이져 모드가 Rule_based 이거나 Cost_based 모드에서 'FIRST_ROWS'로 설정되었을 때 가능하다.
  
 {code:SQL}
 SELECT ord_dept, ord_date, SUM(ord_qty), COUNT(*)
 FROM?? order1t
 WHERE? ord_no > 0
 GROUP? BY ord_dept
 ???????? ,ord_date
 ORDER? BY ord_date
 {code}
- SUM, COUNT, MAX, MIN, AVG등의 그룹함수를 사용했거나, GROUP BY, ORDER BY, UNION, MINUS, INTERSECT등을 사용하면 전체범위를 인덱스를 경유함으로 인덱스를 사용하는 것이 훨씬 불리하다.{section}{column:width=50%}{code:SQL}
   
 * SUM, COUNT, MAX, MIN, AVG등의 그룹함수를 사용했거나, GROUP BY, ORDER BY, UNION, MINUS, INTERSECT등을 사용하면 전체범위를 인덱스를 경유함으로 인덱스를 사용하는 것이 훨씬 불리하다.
 * 개선 전
 {code:SQL}
 SELECT *
  FROM emp
  WHERE ename IS NULL
{code}(n)
 \\
 \\ {column}{column:width=50%}{code:SQL}
  {code}
  
 * 개선 후
 {code:SQL}
 CREATE TABLE emp(
  ename VARCHAR2(20) DEFAULT '00'
  ......
 )
{code}(y)
 \\
 \\ {column}{section}{section}
  {code}
  
 - 분포도가 양호할 경우에는 Default 사용이 유리, 그렇지 않을 경우에는 null 값을 가지고 있는 것이 유리하다.
  
 h4. (2) NULL공포증의 해소
  
 * NULL이란?
  
- NULL도 1, A와 같은 하나의 값이다.
 - 어떤 값보다 크지도 않고 작지도 않다.'
 - 그러므로 어떤 값과 비교 될 수도 없다.
 - 즉 NULL과 연산 결과는 NULL이 된다.
  ** NULL도 1, A와 같은 하나의 값이다.
 ** 어떤 값보다 크지도 않고 작지도 않다.'
 ** 그러므로 어떤 값과 비교 될 수도 없다.
 ** 즉 NULL과 연산 결과는 NULL이 된다.
  
 {code:SQL}
 SELECT ord_dept, SUM(ordqty), AVG(ordqty+asnqty), AVG(ordqty)
  FROM ORDER
  WHERE status < 'C'
  GROUP BY ord_dept
 {code}
 
 - status가 null인 값은 작업대상에서 빠진다.
 - AVG(ordqty+asnqty)는 ordqty나 asnqty 중 하나만 NULL을 가진다면 처리대상에서 제외된다.
 - SUM(ordqty) 와 SUM(nvl(ordqty,0)) 은 동일하다.(결과는 동일하나 후자는 불필요한 연산을 수행하므로 불리함)
 - AVG(ordqty+asnqty) 와 AVG(nvl(ordqty,0)+nvl(asnqty,0))는 서로 다르다.
 - AVG(ordqty) 에서 ordqty에 NULL이 있을때와 없을때의 차이점?? {tip:title=NULL 공포증의 해소 방안}'확정은 되었으나 값이 없다' 경우에는 문자 타입일 때는 ' '(Space)나 기타 문자를 필요에 따라 지정하고, 숫자 타입일 때는 0 을 지정한다.
 '미확정'인 값도 하나의 의미를 지닌 값이라고 보아야 한다는 것이 NULL 값이 만들어진 이유이다.
 테이블을 생성시 DEFAULT 제약조건을 이용해서 기본값을 지정하여 처리 할 수도 있다.
 NVL함수의 사용으로 인한 불필요한 연산 보다는 NULL값에 대한 일관성을 유지하는 것이 필요하다. {tip}
  
 h4. 라. 옵티마이져에 의한 취사선택
  
  
 h4. (1) 순위(Ranking)의 차이
  
 {code:SQL}
 SELECT ord_dept, ordqty
  FROM ORDER1T
  WHERE status = 'C'
  AND ord_date like '9502%'
 {code}
 
 - RBO: 순위에 의해서 ord_date 인덱스는 무시되고, status인덱스를 사용.
 - CBO: 분포도에 따라 옵티마이저가 선택.
  
 {code:SQL}
 SELECT ord_dept, ordqty
  FROM ORDER1T
  WHERE status = 'C'
  AND ord_date = '950201'
 {code}
 - 각 컬럼이 별개의 인덱스로 생성이 되어 있다면 인덱스 머지를 일으킴
  
 {code:SQL}
 SELECT ord_dept, ordqty
  FROM ORDER1T
  WHERE ord_dept like '12%'
  AND ord_date like '9502%'
 {code}
 - 두개 인덱스중 하나만 사용함.
 - RBO: 나중에 생성된 컬럼 사용.
 - CBO: 분포도에 따라 옵티마이저가 선택. {tip}범위 처리가 넓다고 할 수 있는 'LIKE', 'BETWEEN', '<', '>'등과 같이 사용될 경우 결코 인덱스 머지를 하지 않고 어느 하나의 인덱스만 사용하고 나머지는 포기한다. {tip}
  
 h4. (2) 낮은 처리비용의 선택
  
 {code:SQL}
 SELECT *
  FROM emp
  WHERE ename > 'A'
 {code}
 
 - FIRST_ROWS 로 설정되었을 때 인덱스를 사용하지만 ALL_ROWS인 경우에는 전체 테이블을 스캔 함.
  
 h4. (3) 힌트(Hint)에 의한 선택
  
 * 옵티마이져가 액세스 경로를 결정할 때 옵티마이져에게 모든 것을 맡기지 않고 사용자가 원하는 보다 좋은 접근경로를 직접 선택해서 최적의 튜닝을 할 수 있도록 도와줌 {tip:title=힌트의 사용 방법}/*\+ \*/ : 힌트의 내용을 여러 라인에 걸쳐서 기술할 수 있음.
 \--\+ : 오직 한 라인에만 기술해야 하며, 컬럼은 반드시 다음 라인에 기술해야 함. {tip}{tip:title=ORACLE_Hint 정리}[^HINT_Dictionray.xls] {tip}
  \--\+ : 오직 한 라인에만 기술해야 하며, 컬럼은 반드시 다음 라인에 기술해야 함. {tip}{tip:title=ORACLE_Hint 정리}[HINT_Dictionray.xls|http://wiki.gurubee.net/download/attachments/688163/HINT_Dictionray.xls?version=1] {tip}