1. 인덱스
1.인덱스 : 컬럼 + rowid로 구성되어 있으며 이 값들로 정렬이 되어 있습니다.
인덱스를 사용하면 무조건 속도가 빠르다고 생각하는 경우가 있는데 그것은 사실이 아닙니다.
데이터 분포도가 10%가 이상이면 인덱스를 사용하지 않고 그냥 스캔방식으로 처리하는것이 유리합니다.
왜냐하면 인덱스를 사용하게 되면 random acess방식으로 접근하여 데이터를 조회하는 반면에 scan 방식은 차례로 데이터를 조회하기 때문에 분포도가 넓은 데이터를 조회할 때는 인덱스를 사용하지
않는 것이 좋습니다.
또한 오라클은 데이터를 조회할 때 row단위가 아닌 block단위로 접근하는데 인덱스를 사용하면 1 block식 접근하고 인덱스를 사용하지 않으면 mult block으로 데이터에 접근합니다.(대체로 4 block)
결론은 분포도가 좋으면 인덱스를 사용하는 것이 유리하고 그렇지 않으면 사용하지 않는것이 좋지요..
2.인덱스가 사용되지 않는 경우
1) 인덱스 칼럼은 비교되기 전에 변형이 일어나면 인덱스를 사용할 수 없다.
SELECT DEPT,ENAME
FROM EMP
WHREE SUBSTR(JOB,1,4)='SALE'
--> SELECT DEPT,ENAME
FROM EMP
WHREE JOB LIKE 'SALE%'
2) 부정형으로(NOT,<>)으로 조건을 사용한 경우에도 인덱스를 사용하지 않는다.
SELECT DEPT,ENAME
FROM EMP
WHERE EMPNO <> '1234'
--> SELECT DEPT,ENAME
FROM DUAL
WHERE NOT EXISTS (SELECT 'X'
FROM EMP
WHEERE EMPNO <> '1234')
EXISTS을 사용하면 조건을 만족하는 첫번째 로우만 찾게되면 더이상 처리를 하지 않음으로 처리해야 할 일량은 훨씬 줄어든다.
3) 인덱스 칼럼이 NULL로 비교되면 인덱스를 사용할 수 없다.
칼럼값이 NULL인 로우는 인덱스에 저장되지 않기 때문이다.
물론 결합인덱스에서 첫번째 칼럼이 아닌 경우는 인덱스를 사용할 수 있다.
가령 인덱스 구성이 : COL1 + COL2 + .... 이런 식으로 결합인덱스인 경우에 COL1만 NULL이 아니라면 인덱스가 사용된다.
칼럼에 NULL값이 적은 경우라면 어플리케리이션에서 제어를 할 수 있으나 테이블 생성시 디폴트로 'X' 같은 특정 상수값을 주는 편이 좋다.
하지만 테이블에 그 칼럼에 NULL값이 많다면 인덱스를 사용하지 않는 편이 유리하다.
4) 옵티마이져가 필요에 따라 상기 적용원칙을 준수했음에도 불구하고 특정 인덱스를 취사 선택함으로써 사용되지 않는 인덱스가 생길 수가 있다. 이는 인덱스 머지를 피하거나 조건의 순위에 차이가 있을 때의 취사 선택,사용자 힌트,통계정보에 의거하여 산출된 엑세스 비용의 차이등의 이유로 발생되며 옵티마이저 모드에 따라 다를 수도 있다.
항상 PLAN을 확인 하는 습관을 가져 옵티마이저가 어떻게 움직이는지 알아야 할 것이다.
2.외부적 변형
인덱스 칼럼에 변형이 일어나면 상대값과 비교되기 전에 먼저 가공이 된 후 비교가 되기 때문에 인덱스를 사용할 수 없게 된다. 따라서 인덱스 칼럼을 변형하는 것이 아니라 비교되는 대상을 가공함으로써
인덱스를 사용할 수가 있다. 항상 칼럼을 가공하기 보단 비교되는 대상을 가공하는 습관을 가져야 한다.
SELECT DEPT,ENAME
FROM EMP
WHREE SUBSTR(JOB,1,4)='SALE'
SELECT DEPT,ENAME
--> FROM EMP
WHREE JOB LIKE 'SALE%'
SELECT EMPNO,ENAME,JOB
FROM EMP
WHERE SAL*12 = 2400000
--> SELECT EMPNO,ENAME,JOB
FROM EMP
WHERE SAL = 2400000/12
SELECT EMPNO,ENAME,JOB
FROM EMP
WHERE TO_CHAR(HIREDATE,'YYYYMMDD') = '20020423'
--> SELECT EMPNO,ENAME,JOB
FROM EMP
WHERE HIREDATE = TO_DATE('20020423','YYYYMMDD')
만약 결합인덱스가 있을 경우 아래와 같은 실수를 범하기도 한다.
EMP TABLE에 결합인덱스가 JOB + DEPT 또는 DEPT + JOB 이라면 위에 있는 것과 같은 방법으로 하면 인덱스를 사용할 수 없게 된다.
SELECT EMPNO,ENAME,JOB
FROM EMP
WHERE JOB||DEPT = 'CLERK10'
--> SELECT EMPNO,ENAME,JOB
FROM EMP
WHERE JOB = 'CLERK'
AND DEPT = '10'
TAB1 TABLE에 결합인덱스가 COL1 + COL2 또는 COL2 + COL1 이라면
위에있는 것과 같은 방법으로 하면 인덱스를 사용할 수 없게 된다.
(COL1 ='ABC', COL2='DE' 라고 가정한다.)
SELECT EMPNO,ENAME,JOB
FROM TAB1
WHERE COL1||COL2 = 'ABCDE'
--> SELECT EMPNO,ENAME,JOB
FROM TAB1
WHERE COL1 = SUBSTR('ABCDE',1,3)
AND COL2 = SUBSTR('ABCDE',4,2)
마지막으로 어제 설명한 것과 같이 분포도가 넓은 경우,인덱스 머지가 발생하여 효율이 떨어지는 경우,
조인시 데이블의 액세스 순서를 변경하거나 고정하기 위해서 강제로 칼럼을 변경하여 인덱스를
사용하지 못하게 하는 경우도 있다.
1) 분포도가 넓은 경우 -> TAB1 TABLE에 EMPNO ='90'인 경우가 10% 이상이라면 인덱스를 사용하지 않은 편이 좋다.
SELECT *
FROM TAB1
WHERE EMPNO = '90'
--> SELECT *
FROM TAB1
WHERE RTRIM(EMPNO) = '90'
2) 인덱스 머지가 발생하여 효율이 떨어지는 경우
만약 TAB1 TABLE에 EMPNO,ENAME이 각각 인덱스가 있고 EMPNO = '10'을 만족하는 경우가 10건,
ENAME = 'KIM'을 만족하는 경우가 10000건이라면 EMPNO 인덱스만 사용하는 것이 유리할 것이다.
(여기서 수치는 중요한 것이 아니라 그냥 분포상태를 위해 예를 든것임)
왼쪽과 같은 경우는 옵티마이저가 EMPNO,ENAME 인덱스 머지가 일어나서 수행을 하게 된다.
오른쪽은 분포도가 좋은 EMPNO 인덱스만 사용하게 된다.
SELECT * SELECT *
FROM TAB1 --> FROM TAB1
WHERE EMPNO = '10' WHERE EMPNO = '10'
AND ENAME = 'KIM' AND RTRIM(ENAME) = 'KIM'
3) 조인시 데이블의 액세스 순서를 변경하거나 고정하기 위해서 강제로 칼럼을 변경
밑에 사용된 칼럼들은 각각 별도의 인덱스를 가지고 있으며 실제 데이터의 건수를 확인하면 ORD_DATE LIKE '200204%'를 만족하는 로우수가 ORD_DEPT = '2104'를 만족하는 경우보다 훨씬 적다는 것을 알고 있다고 가정하자.
SELECT A.ORDNO,B.ITEM SELECT A.ORDNO,B.ITEM
FROM ORDER1 A,ORDER2 B --> FROM ORDER1 A,ORDER2 B
WHERE A.ORDNO = B.ORDNO WHERE A.ORDNO = B.ORDNO
AND A.ORD_DATE LIKE '200204%' AND A.ORD_DATE LIKE '200204%'
AND B.ORD_DEPT = '2104' AND RTRIM(B.ORD_DEPT) = '2104'
오른쪽과 같이 처리함으로써 유리한 인덱스인 ORD_DATE 인덱스만 사용하게 된다.
4) 부분범위 처리
인덱스를 경유하여 처리하도록 유도하여 정렬을 하지 않고 처리되는 순서로 데이터를 추출하다
운반단위(ARRAY SIZE)에 도달하면 멈추게 함으로써 전체 범위를 모두 액세스하여 정렬한 후 추출하는 것보다 휠씬 유리하게 처리할 수 있다.
(인덱스 순으로 정렬이 되어 있으므로 인덱스를 경유하면 따로 정렬을 할 필요가 없다.)
SELECT SALE_DEPT,SALE_DATE,SLAE_DEPT
FROM TAB1
WHERE SALE_DATE LIKE '200204%'
ORDER BY SALE_DEPT
--> SELECT /*+ INDEX(TAB1 SALE_DEPT_INDEX) */
SALE_DEPT,SALE_DATE,SLAE_DEPT
FROM TAB1
WHERE SALE_DATE LIKE '200204%'
(힌트를 사용하여 SALE_DATE 인덱스를 사용 힌트를 사용하면 역순으로도 구할 수 있음)
SELECT SALE_DEPT,SALE_DATE,SLAE_DEPT
FROM TAB1
WHERE RTRIM(SALE_DATE) LIKE '200204%'
AND SALE_DEPT >' '
(SALE_DATE칼럼을 강제변형,SALE_DEPT 사용함으로 인덱스 사용)
3. 내부적 변형.
인덱스 칼럼은 사용자가 직접 가공을 하지 않아도 서로 다른 데이터 타입을 비교하면 DBMS가 어느 한 쪽을 기준으로 동일한 타입이 되도 록 내부적으로 변형을 시킵니다,
물론 이렇게 되면 인덱스 사용을 할 수가 없게 되는거죠.. 가령
SELECT ENAME,DEPTNO
FROM EMP
WHERE ENAME =10
이런 경우에 ENAME 칼럼이 CHAR,VARCHAR2 같은 문자형으로 정의가 되었다면 DBMS 내부적으로 ENAME =10 -> TO_NUMBER(ENAME) =10 문자형으로 변형을 함으로써 인덱스를 사용할 수 없게 됩니다.
인덱스를 사용하고자 한다면
SELECT ENAME,DEPTNO
FROM EMP
WHERE ENAME ='10'
으로 변형하던지 ENAME 칼럼이 숫자형(NUMBER형) 으로 정의가 되어 있어야 합니다.
여기서 ENAME 칼럼이 숫자형(NUMBER형)이라면
SELECT ENAME,DEPTNO SELECT ENAME,DEPTNO
FROM EMP OR FROM EMP
WHERE ENAME =10 WHERE ENAME ='10'
동일하게 인덱스가 사용가능합니다.
ENAME(비교되는 칼럼)이 숫자형이던 문자형이던지 상관없이 인덱스를 사용할수 있기때문에 10 같은 숫자형태는 항상 '10'식으로 사용하는 습관을 가져야 겠지요.
이것만 보면 우린 비교되는 칼럼(ename)을 숫자형으로 정의하는
것이 유리하지 않나 하고 의구심을 가지게 될 것입니다.
하지만 실무에서는 항상 '='관계로 사용되지 않습니다.
SELECT ENAME,DEPTNO
FROM EMP
WHERE ENAME LIKE '1%'
이런 경우가 많이 발생하는데 ENAME이 숫자형이라면 인덱스를 사용할 수 없게 됩니다.
인덱스를 사용가능하게 하려면 문자형으로 정의가 되어야 겠지요 즉 결론은 특별한 숫자연산이 필요한 경우가 아니라면 문자형으로 정의하는 것이 유리합니다.
또한 RDBMS에서 조인을 많이 사용하는데요
SELECT A.ORDNO,A.ORD_DATE,B.ITEM
FROM ORDER1T A,ORDER2T B
WHERE A.ORDNO = B.ORDNO
AND A.ORD_DATE ='20020427'
이런 경우 A.ORDNO, B.ORDNO 칼럼이 동일하지 않으면 내부적인 변형이 일어나서 인덱스를 사용할 수 없어서 인덱스를 사용하지 못하는 테이블을 전체 스캔을 하는 무서운 결과를 초래하게 되지요.
부정형의 비교를 하게 되면 인덱스를 사용할 수 없게 된다고 이전에 말했지유~~
SELECT *
FROM EMP
WHERE ENAME <> 'KIM'
-> SELECT *
FROM EMP
WHERE NOT EXISTS (SELELCT
FROM EMP
WHERE ENAME ='KIM')
긍정형식으로 변헝을 하게 되면 인덱스 사용이 가능해 집니다.
이젠 NULL을 사용한 비교에 대해서 생각해 보겠습니다.
테이블의 컬럼을 설계할 때 기본값(Default Value)을 ‘NULL’로 할 것인가 아니면 임의의 값(예: X, 0, A등)으로 할 것인가는 인덱스 처리와 밀접한 관계를 가집니다. 만약 분포도가 양호한 컬럼이 ‘NULL’로 정의되어 있다면 우리는 어쩔수 없이 전체 테이블을 엑세스하거나 다른 조건의 도움을 받을 수 밖에 없죠. 또한 너무 많은 로우를 가진 경우에 컬럼을 ‘NULL’로 지정하지 않고 인덱스를 가지고 있다면 넓은 범위를 인덱스로 처리하게 됨으로써 오히려 불리하게 된다고 말했지요.
SELECT ORD_NO,ORD_DATE,ITEM,ORD_QTY
FROM ORDER
WHERE ORD_NO IS NOT NULL -> 인덱스 사용불가
ORDER BY ORD_DATE -> 전체범위 검색
-> SELECT ORD_NO,ORD_DATE,ITEM,ORD_QTY
FROM ORDER
WHERE ORD_NO > ‘ ‘
(ORD_NO 인덱스가 생성되어 있다고 가정, ORD_NO 가 숫자형이라면 WHERE ORD_NO > 0 )
전체 테이블을 읽어 정렬하지 않고서도 인덱스를 경유하도록 함으로써, 인덱스 순서대로 엑세스하다가 운반단위가 채워지면 멈추도록 하여 수십, 수 백배 이상의 수행속도를 향상시킬 수 있는거지요.
만약 비교되는 조건에서 IS NOT NULL’로 사용된 경우는 인덱스를 사용하도록 유도할 수 있으나 'IS NULL’값을 가지는 로우를 가지는 로우를 찾고자 할 경우에는 대체할 방법이 없다.
이런 경우는 테이블 생성시 칼럼 조건을 DEFALUT로 NULL,'X'(상수값) 을 줄 것인지 결정해야 한다.
컬럼의 값이 ‘NULL’인 로우가 많다면 전체 테이블 스캔 방식이 더 낳기 때문이에 DEFALUT NULL을 사용하고 그렇지 않다면 DEFALUT 'X'를 사용한다.
4. 옵티마이저에 의한 취사 선택
1.순위(ranking)의 차이
조건으로 비교된 컬럼들이 인덱스로 지정되어 있고 인덱스 사용규칙을 지켰다고 해서 항상 사용되어 지는 것은 아니다. 인덱스 머지,특히 좁은 분포도와 넓은 분포도를 머지하는 것보다 좁은 분포도의 인덱스
만 사용하는 것이 유리하다.
1)규칙기준(Rule based) 옵티마이저 방식
좁은 분포도와 넓은 분포도의 구분을 조건 연산자의 순위에 따라 판단한다.
즉 '='은 'LIKE'보다 분포도가 좁다고 판단하므로
SELECT *
FROM ORDER1T
WHERE STATUS ='C'
AND ORD_DATE LIKE '200204%'
에서는 STATUS인덱스만 사용되고 ORD_DATE인덱스는 무시된다. 물론 STATUS,ORD_DATE 결합인덱스로 되어 있다면 사용된다.
규칙기준에서는 쉰위에 차이가 있을 때 무조건 높은 순위의 인덱스만 사용되고 나머지는 무시되나 같은 순위로 비교된 경우에는 다르다.
SELECT *
FROM ORDER1T
WHERE STATUS ='C'
AND ORD_DATE ='20020428'
각 칼럼이 인덱스로 생성되어 있다면 인덱스 머지를 일으키게 되며 결합인덱스로 되어 있다면 같이 사용된다. 그러나 주로 넓은 범위가 될 수 있는 'LIKE','BETWEEN','<','>' 등과 같이 사용되었다면 결코 인덱스 머지를 일으키지 않는다.
SELECT *
FROM ORDER1T
WHERE ORD_DEPT LIKE '12%'
AND ORD_DATE LIKE '200204%'
위와 같은 경우에는 어느 하나의 컬럼 인덱스만 사용된다. ORD_DEPT,ORD_DATE 인덱스중 나중에 생성된 인덱스가 사용된다.
2) 비용기준(COST BASED) 옵티마이저 방식
SELECT *
FROM ORDER1T
WHERE STATUS ='C'
AND ORD_DATE LIKE '200204%'
순위(RANKING)이 낮더라도 분포도가 더 좋다면(좁다면) '='로 사용된 STATUS 인덱스는
무시되고 'LIKE'로 사용된 ORD_DATE인덱스가 사용된다.
SELECT *
FROM ORDER1T
WHERE ORD_DEPT LIKE '12%'
AND ORD_DATE LIKE '200204%'
위와 같은 경우에는 어느 하나의 컬럼 인덱스만 사용된다.
ORD_DEPT,ORD_DATE 인덱스중 통계정보에 의해 계산된 비용중 최소비용의 경로를 선택학 되므로 둘 중에 분포도가 좁은 컬럼의 인덱스가 사용되거나 손익분기점 이상이면 인덱스를 사용하지 않고
전체 테이블을 스캔한다.
2. 낮은 처리비용의 선택
비용기준 옵티마이저 환경에서는 인덱스가 있다고 하더라도 전혀 사용하지 않을 수도 있다. 위의 몇가지 예에서 보았듯이 최저의 비용을 가진 액세스를 선택하므로 주어진 조건 및 통계정보에 따라 사용되는 경우가 다양하다.
비용기준 옵티마이저 환경에서도 목표(GOAL)인 'FIRST_ROWS','ALL_ROWS'에 따라 비용계산이 달라지므로 다른 사용형태가 나타날 수 있다.
SELECT *
FROM ORDER1T
WHERE STATUS > 'A'
에서 'FIRST_ROWS'인 경우에는 인덱스를 사용하지만 'ALL_ROWS'에서는 전체 테이블을 스캔한다.
따라서 비용기준 옵티마이저에서 'FORST_ROWS'방식은 온라인에서 'ALL_ROWS'방식은 배치작업에 사용하는 것이 유리하다.
3. 힌트에 의한 선택
힌트란 사용자가 액세스 경로 변경을 위해 SQL내에 오구사항을 기술하면 옵티마이저가 액세스 경로를 선택할 때 이를 참조하도록 한다. 힌트란 장기,바둑에서 훈수와 비슷한 것이다. 실무환경에서는 다양한 액세스 형태가 나타나고 애플리케이션을 작성하는 사람이 보다 나은 경로를 아는 경우가 많이 있다.
이런 경우 힌트를 사용하여 자신이 원하는 경로로 옵티마이저가 선택하게 한다.
SELECT *
FROM ORDER1T
WHERE ORD_DATE LIKE '200204%'
AND ORD_DEPT BETWEEN '10' AND '30'
ORDER BY ORD_DATE
위와 같은 SQL에서는 어떤 인덱스를 사용할 지 확실하지 않다.
만약 ORD_DATE로 정렬된 데이터를 부분범위처리(운반단위까지 처리하고 멈추도록) 하려면 다음과 같은 힌트를 사용하여 수행속도를 향상시킬 수 있다.
SELECT -+ INDEX(ORDEF1T ORD_DATE_INDEX)
*
FROM ORDER1T
WHERE ORD_DATE LIKE '200204%'
AND ORD_DEPT BETWEEN '10' AND '30'
위 SQL은 ORD_DATE인덱스를 경유함으로써 부분범위처리가 가능하고 또한 ORD_DATE인덱스 로 정렬되어 있으므로 ORDER BY ORD_DATE를 사용하지 않아도 똑같은 결과를 가져온다. ORDER BY절을 사용하게 되면 ORACLE에서 조건에 맞는 데이터를 읽어와서 메모리에서 다시 정렬작업을 해야 함으로 수행속도가 불리해 진다.
SQL내에서 힌트를 사용하는 방법은 '-+', '/*+ */'두가지 방법이 있는데 전자는 한라인에 기술해야 하고
후자는 여러 라인데 힌트를 기술할 수 있고 또한 주석도 포함 가능하다.
SELECT /*+ INDEX(A SEX_INDEX) 남자환자가 거의 없으므로 SEX_INDEX를 사용함, */
FROM PATIENTS A
WHERE SEX = 'M'
여기서 이것은 남자환자가 거의 없으므로 SEX_INDEX를 사용함, 주석임.
힌트는 또한 코딩이 잘못되었거나 논리적으로 맞지 않거나 사용자의 요구를 옵티마이저가 판단할 때 너무 많은 비용이 소요된다고 판단하면 에러 메세지 없이 힌트를 무시한다. 따라서 전에도 말했지만 SQL문을 작성하고 나면 항상 EXPLAIN PLAN OR SQL_TRACE를 확인하는 습관을 가져야 한다.
여기서 자주 사용하는 힌트를 정리해 보자
1) RULE : RULE BASED 옵티마이저 사용하게 한다.
2) FIRST_ROWS : 첫째 레코드의 추출시간을 최소화 할 목적으로 사용한다.(온라인화면)
3) ALL_ROWS : 모든 레코드를 처리하는 시간의 최소화 할 목적으로 사용한다.(배치작업)
4) FULL : 지정된 테이블에 대한 전체 스캔
5) ROWID : 지정된 테이블에 대한 ROWID에 의한 테이블 스캔
6) CLUSTER : 지정된 테이블에 대한 클러스터 스캔
7) HASH : 지정된 테이블에 대한 HASH 스캔
8) INDEX : 내림차순으로 INDEX SCAN
9) INDEX_DESC : 오름차순 으로 INDEX SCAN
10) AND EQUALS : 여러개의 INDEX드을 머지하여 사용(2 ~5개)
11) ORDERED : FROM 절에 기술된 순으로 JOIN(힌트 사용하지 않을 시 대체로 FROM 절에서 멀리 떨어진 테이블 순으로 JOIN 된다)
12) USE_NL: 먼저 특정 테이블의 로우를 액세스하고 그 값에 해당하는 다른 테이블의 로우를 찾는 작업을 해당 범위까지 실행한다. (데체로 조인시 사용하는 방법)
13) USE_MERGE : 먼저 각각의 테이블의 처리범위를 스캔하여 SORT한 후 서로 머지하면서 조인하는 방식
14) PARALLEL : 병렬처리 프로세스의 개수를 지정한다.
(넓은 범위의 데이터를 조회할 경우 프로세스 개수를 여러개로 처리하여 빠른 응답을 가져올 수 있다.)
15) CACHE : 데이블의 로우들을 메모리내에 상주시킨다.
(한번 조회한 데이터를 계속사용하고자 할 경우 메모리에 상주 시킴으로서 빠른 응답을 가져온다.)
16) NOCACHE : 메모리네에 상주 시킨 옵션의 해제
5. 인덱스 선정
인덱스가 액세스의 호율을 높여 주는것은 사실이지만 테이블 특성과 생성시키고자 하는 인덱스 컬럼의 분포노,처리범위 등을 정확히 파악 해서 지정하여야 한다.
1. 인덱스 대상 테이블의 선정
1) 테이블의 크기가 적은 것은 인덱스를 만들지 않아도 된다.
RDBMS는 멀티블럭 I/O를 하기 때문에 한번 I/O로 처리될 수 있는 양은 인덱스 없이 전체 테이블을 스캔하더라도 무리가 없다. 다만 조인의 연결고리가 되는 칼럼에 인덱스가 없을 때는 조인의 방향이
달라 질 수 있으며 참조무결성(REFERENTIAL INTEGRITY)을 지정한 경우 테이블 단위의 LOCK이 발생할 수 있으므로 이러한 컬럼은 인덱스를 생성 시키는 것이 좋다.
2) 보관용 테이블이나 전체 테이블 스캔용으로만 사용되는 경우는 인덱스를 생성시킬 필요가 없다.
다량의 데이터를 입력할 경우에는 인덱스가 없는 경우가 유리하므로 (인덱스가 있다면 인덱스 순으로 정렬을 함으로 많은 부하가 발생한다.) 데이터를 입력한 후 인덱스를 생성하기도 한다. 또한 수정,삭제시에도 많은 부하가 발생함으로 인덱스의 개수나 생성시점을 잘 결정해야 한다.
랜덤액세스가 빈번하거나 특정 범위의 데이터나 특정순서로 스캔이 요구되는 경우 다른 테이블과 순차적 조인이 발생되는 테이블은 인덱스르 가져야 한다.
배치형태로 사용되는 테이블은 처리 유형에 따라 인덱스를 가지지 않거나 사용시기에 따라 인덱스의 개수와 인덱스 대상 칼럼을 적절히 변화시킨다.
어떤 테이블은 인덱스를 가지지 않고 클러스터링이나 해쉬를 통해 보다 양호한 액세스를 얻을 수 있으며 일반적으로 몇 개의 다른 인덱스와 역할을 적절히 분담하도록 해야 한다.
2. 인덱스 컬럼의 선정
1) 분포도와 손익 분기점
인덱스를 생성시키고자 하는 컬럼의 분포도가 10% 이상은 되지 않아야 한다. 여기서 분포도란 어떤 컬럼이 테이블에 평균적으로 분포되어 있는 정도를 말한다.
분포도 = 100*(데이터별 평균 로우수/테이블의 총 로우수) = 100*(1/컬럼 값의 종류)
2) 결합 인덱스 특징
조건절에 결합인덱스의 첫번째 컬럼을 사용핮 않으면 인덱스는 사용되지 않으므로 첫번째 컬럼 선정을 잘 해야 한다.
결합 인덱스를 구성하는 컬럼들은 그 순서를 어떻게 하느냐에 따라 수행속도에 많은 영향을 미친다.
그러므로 결합 인덱스를 생성하고자 할 때 그 분포도 및 사용 조건들을 감안하여 어떤 순서로 할 것인지 판단하는 것이 중요하다.
3. 결합인덱스 컬럼 선정 순서방법
1) 항상 사용되는가?
첫번 째 컬럼이 사용되지 않으면 인덱스가 사용되지 않기 때문에 항상 사용되는 조건이 가장 우선된다.
2) 항상 '='로 사용되는가?
앞선 컬럼이 '='이 아니라면 뒤에 있는 컬럼이 '='로 사용되었더라도 처리 범위를 줄여주지 못한다. '='조건으로 사용된 컬럼은 단지 체크 처리만 할 뿐이다.
3) 분포도가 좋은 컬럼을 우선한다.
위의 두 조건을 만족하는 컬럼이 하나 이상이라면 분포도가 좋은 컬럼을 우선으로 해야 한다.
4) 자주 사용되는 정렬의 순서는 ?
인덱스는 결합된 컬럼의 순서로 정렬되어 저장되므로 인덱스의 컬럼순서와 동일한 정렬순서라면 국이 정렬작업을 하지 않아도 정렬된 결과를 추출할 수 있다.
6. 조인의 최적화
조인에는 크게 NESTED LOOP,SORT MERGE 조인이 있습니다.
이 중에서 일반적으로 많이 사용하는 NESTED LOOP 조인은 순서에 크게 영향을 미친다. NESTED LOOP조인은 먼저 연결되는 테이블에서 만족하는 데이터를 이용하여 연결되어지는 테이블과 조인을 하는 방식이기 때문이다.
** 조인 순서
여기서는 가장 많이 사용하는 NESTED LOOP조인에 관해서만 생각해 보자.
테이블의 로우수는 조인을 하려고 하는 테이블의 전체로우를 말하는 것이 아니라 조건을 만족하는 로우수를 말한다.
만약 테이블1(10000로우,KEY1),테이블2(1000로우,KEY2),테이블3(2로우,KEY3)인 테이블을 NESTED LOOP 조인을 한다고 하면
1. 테이블1,테이블2,테이블3 순으로 조인을 한다면
1) 테이블1의 10000로우 중에서 첫번째 로우를 읽는다.
2) 읽혀진 테이블1의 값에 대응하는 테이블2의 로우를 찾는다.
(엑세스 결과는 테이블2에 있을수도 없을수도 있다 하지만 성공여부에 관계없이 작업은 수행된다)
3) 2)작업에서 성공한 로우롸 대응되는 테이블3의 로우를 찾는다.
(엑세스 결과는 테이블3에 있을수도 없을수도 있다 하지만 성공여부에 관계없이 작업은 수행된다)
4) 테이블1의 10000로우 중에서 두번째 로우를 읽는다.
(테이블1의 10000로우를 읽을때 까지 반복된다.)
-> 최소 10000 회 이상 엑세스가 일어난다.(가장 나쁜 경우)
2. 테이블3,테이블2,테이블1 순으로 조인을 한다면
1) 테이블3의 2로우 중에서 첫번째 로우를 읽는다.
2) 읽혀진 테이블3의 값에 대응하는 테이블2의 로우를 찾는다.
(엑세스 결과는 테이블2에 있을수도 없을수도 있다 하지만 성공여부에 관계없이 작업은 수행된다)
3) 2)작업에서 성공한 로우롸 대응되는 테이블1의 로우를 찾는다.
(엑세스 결과는 테이블1에 있을수도 없을수도 있다 하지만 성공여부에 관계없이 작업은 수행된다)
4) 테이블3의 2로우 중에서 두번째 로우를 읽는다.
(테이블3의 2로우를 읽을때 까지 반복된다.)
-> 최대 4회 이하 엑세스가 일어난다.(가장 유리한 경우)
3. 테이블3,테이블1,테이블2 순으로 조인을 한다면
1) 테이블3의 2로우 중에서 첫번째 로우를 읽는다.
2) 읽혀진 테이블3의 값에 대응하는 테이블1의 로우를 찾는다.
(엑세스 결과는 테이블1에 있을수도 없을수도 있다 하지만 성공여부에 관계없이 작업은 수행된다)
3) 2)작업에서 성공한 로우롸 대응되는 테이블2의 로우를 찾는다.
(엑세스 결과는 테이블2에 있을수도 없을수도 있다 하지만 성공여부에 관계없이 작업은 수행된다)
4) 테이블3의 2로우 중에서 두번째 로우를 읽는다.
(테이블3의 2로우를 읽을때 까지 반복된다.)
-> 최대 2000회 이하 엑세스가 일어난다.
즉 여기서 NESTED LOOP 조인을 한다고 하면 분포도가 가장 좋은(만족하는 범위가 적은) 테이블 순으로 조인을 하는것이 가장 유리하다.
4. 가장 유리한 경우를 이용하는 예
SELECT /*+ ORDERED USE_NL(A B C) */
*
FROM 테이블1 A,테이블2 B,테이블3 C
WHERE A.KEY1 = B.KEY2
AND B.KEY2 = C.KEY3
...
-> 힌트는 옵티마이저가 작업자가 예상하는대로 선택하게끔 하기 위해서 사용한다.
EXECUT PLAN을 확인하여 원하는대로 옵티마이저가 선택한다면 사용하지 않아도 된다.
ORDERED -> FROM절에서 기술된 순으로 SQL이 풀리게 한다.
(테이블1,테이블2,테이블블3 순으로)
USE_NL -> NESTED LOOP 조인을 사용한다는 것이고 테이블 A,B,C순으로 조인한다
7. 조인의 연결고리
.연결 고리 상태 정상
- 조건절에 기술되는 조인의 연결컬럼에 인덱스가 모두 존재하는 상태
테이블1(인덱스 KEY1),테이블2(인덱스 KEY2) 있다고 하면
** 테이블1,테이블2 순서로 조인
1) 테이블1의 처리범위에서 처리해야 할 첫번째 로우를 읽는다
2) 1)에서 읽은 상수값을 이용하여 테이블2의 연결고리가 되는 KEY2인덱스가 있음으로 ROWID를 이용하어 대응되는 값을 읽는다.
3) 1),2) 과정을 끝날 때까지 반복한다.
** 테이블2,테이블1 순서로 조인
1) 테이블2의 처리범위에서 처리해야 할 첫번째 로우를 읽는다
2) 1)에서 읽은 상수값을 이용하여 테이블1의 연결고리가 되는 KEY1인덱스가 있음으로 ROWID를 이용하어 대응되는 값을 읽는다.
3) 1),2) 과정을 끝날 때까지 반복한다.
--> 연결고리 상태가 정상인 경우는 어느 테이블을 먼저 조인하느냐는 중요하지 않으며 다만 위에서 말했듯이 먼저 처리범위를 줄여 주는 테이블이 먼저 처리되면 수행속도가 향상된다.
2.한쪽 연결고리 이상
- 어느 한쪽의 연결고리에 인덱스가 없는 경우
** 테이블1(인덱스 KEY1),테이블2(인덱스 없음)순으로 조인
1) 테이블1에서 처리해야 할 범위의 첫번째 로우를 읽는다.
(읽어진 로우는 전부 상수값이 됨)
2) 테이블1에서 읽어진 값이 상수가 되었으나 연결해야 할 테이블2에는 인덱스가 없으므르 테이블2를 전부 스캔하면서 만족하는 로우를 읽는다.
3) 테이블을 전체 스캔을 하다가 만족하는 로우를 찾았더라도 조인이란 만족하는 모든로우를 추출해야 하므로 테이블 끝까지 스캔한다.
(인덱스가 없는 테이블2를 여러번 전체 스캔한다.)
4) 위 과정을 끝날 때 까지 반복한다.
-> 가장 최악의 경우이다.
** 테이블2(인덱스 없음),테이블1(인덱스 KEY1)순으로 조인
1) 테이블2에서 처리해야 할 범위의 첫번째 로우를 읽는다.
(읽어진 로우는 전부 상수값이 됨)
2) 테이블2에서 읽어진 값이 상수을 테이블1을 만족하는 값을 ROWID를 이용하여 대응되는 값을 읽는다.
3) 위 과정을 끝날 때 까지 반복한다.
-> 인덱스가 없는 테이블2를 여러번 전체 스캔하는것을 피할수 있다.
한쪽 연결고리 이상인 경우는 인덱스가 없는 테이블을 먼저 처리되도록 해야 한다.
이러한 이유로 옵티마이저는 연결고리의 어느 한 쪽에 인덱스가 없으면 무조건 없는 쪽을 먼저 처리하도록 실행계획을 수립한다.
** 연결고리 이상이 발생하지 않도록 설계시 주의 사항
1) 마스터 테이블에는 자세한 내역을 관리하기 위해 'A','B','C'로 컬럼을 분리하였으나 그 밖의 다른 테이블에는 FOREIGN KEY역할만 하므로 결합하여 하나의 컬럼인 'D'로 지정한 경우
-
SELECT *
FROM TABLE1,TABLE2
WHERE A||B||C = D
-> TABLE1 컬럼들이 사용제한(컬럼변형으로 인한 인덱스 사용불가)이 되어 TABLE1이 먼저 액세스 된다.
-
SELECT *
FROM TABLE1,TABLE2
WHERE A = SUBSTR(D,1,1)
AND B = SUBSTR(D,2,1)
AND C = SUBSTR(D,3,1)
-> TABLE2 컬럼들이 사용제한(컬럼변형으로 인한 인덱스 사용불가)이 되어 TABLE1이 먼저 액세스 된다.
2) 테이터 타입의 차이에 의해 발생되는 경우
어떤 사용자는 EMPNO 컬럼을 CHAR,어떤 사용자는 저장공간을 절약하기위해 EMPNO 컬럼을 NUMBER로 선언하였다면 내부적인 컬럼의 변형으로 인덱스를 사용하지 못하게 된다.
3. 양쪽 연결 고리 이상
양쪽 연결 고리에 인덱스가 없다면 어느 방향으로 조인을 하더라도 문제가 된다.(한쪽 고리 이상에서 설명했듯이 나중에 조인이 되는 데이블을 여러번 전체 스캔이 발생한다.)
이러한 경우 옵티마이저는 연결고리 이상에 영향을 받지 않는 SORT MERGE방식으로 조인을 하게 된다.
- SORT MERGE 조인
양쪽 테이블의 처리범위를 각자 액세스하여 정렬한 결과를 차례로 스캔하면서 연결고리의 조건으로 머지해 가는 방식의 조인이다.
8. NESTED LOOP JOIN 과 SORT MERGE JOIN -
1. NESTED LOOP JOIN
- 먼저 어떤 테이블의 처리범위를 하나씩 액세스하면서 그 추출된 값으로 연결할 테이블을 조인하는 방식
SELECT /*+ USE_NL(A B) */
*
FROM TAB1 A,TAB2 B
WHERE A.KEY1 = B.KEY2
AND A.COL1 = 'AB'
AND B.COL2 = '10'
위 SQL이 NESTED LOOP JOIN으로 처리 되며 TAB1,TAB2순으로 처리된다고 가정하면
1) 먼저 TAB1의 KEY1인덱스를 경유하여 COL1 = 'AB'인 처리범위 중 첫번째 로우를 액세스한다.
2) KEY1인덱스에 있는 ROWID에 의해 TAB1의 로우를 액세스한다.
3) TAB1을 만족하는 값들이 상수값이 되었으므로 TAB2의 KEY2 인덱스를 이용하여 대응되는 인덱스 로우를 찾는다.
4) KEY2에 있는 인덱스에 의해 ROWID를 이용하여 TAB2의 로우를 액세스한다. 경우에 따라 KEY2, COL2 인덱스를 같이 사용될 수도 있다. 액세스된 모든 값들은 상수값이 된다.
5) 최종적인 결과르 운반단위로 보낸다.
-> 조건에 사용된 모든 컬럼에 각각 인덱스가 있다고 하더라도
사용된 인덱스는 TAB1(COL1),TAB2(KEY2 혹은 COL2와 같이 사용)
** 특징
- 순차적으로 처리된다. DRIVING TABLE의 처리범위에 있는 각각의 로우들이 순차적으로 처리,테이블 간의 연결도 순차적이다. (순차적)
- 먼저 액세스되는 테이블의 처리범위에 의해 처리량이 결정된다. (선행적)
- 나중에 처리되는 테이블은 앞서 처리된 값을 받아 액세스된다. 자신에게 주어진 상수값에 의해 스스로 범위를 줄이는 것이 아니라 값을 받아서 처리범위가 정해진다(종속적)
- 주로 랜덤 액세스 방식으로 처리된다. DRIVING TABLE의 인덱스 액세스는 첫번째 로우만 랜덤 액세스이고 나머지는 스캔이며 연결작업은 모두 랜덤 액세스이다.(랜덤 액세스)
- 주어진 조건에 있는 모든 컬럼들이 인덱스를 가지고 있더라도 모두가 사용되는 것은 아니다. 연결되는 방향에 따라 사용되는 인덱스들이 전혀 달라질 수 있다.(선택적)
- 연결고리가 되는 인덱스에 의해 연결작업이 수행되므로 연결고리 상태가 매우 중요하다.(연결고리 상태,방향성)
- 연결작업 수행 후 마지막으로 체크되는 조건은 부분범위처리를 하는 경우에는 조건의 범위가 넓을수록,아니면 없다면 오히려 빨라진다.(부분범위처리 가능)
** 사용기준
- 부분범위처리를 하는 온라인 프로그램에 유리하다.
- 조인되는 어느 한쪽이 상대방 테이블에서 추출된 결과를 받아야 처리범위를 줄일 수 있는 상태라면 항상 유리해진다.
- 처리량이 적은 경우에 유리하다(많더라도 부분범위처리가 가능하면 유리하다)
- 연결고리 이상상태가 발생하지 않도록 해야 한다.
- 순차적으로 처리되기 때문에 driving table선정이 중요하다.
2. SORT MERGE JOIN
- 양쪽 테이블의 처리범위를 각자 액세스하여 정렬한 결과를 차례로 스캔하면서 연결고리의 조건으로 머지해 가는 방식이다.
SELECT /*+ USE_MERGE (A B) */
*
FROM TAB1 A,TAB2 B
WHERE A.KEY1 = B.KEY2
AND A.COL1 = 'AB'
AND B.COL2 = '10'
위 SQL이 SORT MERGE JOIN으로 처리된다고 가정하면
1) TAB1은 COL1인덱스를 경유하여 COL1 ='AB'인 범위를 차례로 액세스하여 연결고리인 KEY1의 값으로 정렬해 둔다.
2) TAB2은 COL2인덱스를 경유하여 COL2 ='10'인 범위를 차례로 액세스하여 연결고리인 KEY2의 값으로 정렬해 둔다.
3) 두개의 정렬된 결과를 스캔하면서 KEY1 = KEY2를 만족하는 로우를 추출한다.
** 특징
- 테이블 각자가 자신의 처리범위를 액세스하여 정렬해 둔다.(동시성)
- 각각의 테이블은 다른 테이블에서 어떤 상수값을 받지 않는다. 자신에게 주어진 조건에 의해 범위를 줄인다(독립적)
- 항상 전체 범위 처리를 한다.(전체범위처리)
- 주로 스캔방식으로 처리된다. 자신에게 주어진 조건으로 처리범위를 줄이는 경우에만 인덱스를 사용한다.(스캔방식)
- 연결고리가 되는 컬럼들은 인덱스를 전혀 사용하지 않는다. (선택적)
- 조인의 방향과는 무관하다.(무방향성)
** 사용기준
- 전체범위를 처리할 경우 유리하다.
- 처리량이 많은 경우(항상 전체범위처리를 하는 경우)유리하다. 스캔방식으로 처리하기 때문에 랜덤액세스를 줄일 수가 있기 때문에...
- 연결고리 이상 상태에 영향을 받지않으므로 연결고리를 위한 인덱스를 생성할 필요가 없을 때 유용하다.
- 스스로 자신의 처리범위를 어떻게 줄일 수 있느냐가 수행속도에 많은 영향을 미치므로 보다 효울적으로 액세스할 수 있는 인덱스 구성이 요구된다.
** 조인의 튜닝
1. NESTED LOOP,SORT MERGE 조인이 유리한지 판단한다.
2. 연결고리의 상태를 확인한다. 연결고리에 이상이 있다면 이미 액세스 방향이 정해져 있으므로 (인덱스가 없는 테이블이 먼저 처리된다) 현 상태의 문제점을 확인하여 인덱스 추가 여부를 결정한다.
3. 연결고리를 제외한 컬럼들의 조건에 사용된 연산자와 인덱스이 상태를 비교하여 처리범위를 가장 많이 줄여 주는 조건을 찾는다. 조건에 사용된 컬럼들이 인덱스가 많더라도 대체로 인덱스 머지를 피하는 것이 유리하기 때문에 가장 최소의 범위로 처리하는것이 일반적으로 가장 빠르다.
4. 현재의 인덱스가 최소의 범위를 액세스할 수 있도록 구성되어 있는지 확인한다. 확인결과 글렇지 못하면 그 다음 순서의 최소범위를 찾아 이 경로로 처리되어도 충분할 것인지를 검토한다. 부분범위처리가 가능한 경우라면 처리범위가 넓더라도 수행속도가 충분히 보장되는 경우가 있으므로 부분범위처리로 유도할 수 있는지 살펴본다.
만약 적절한 조건을 찾으수 없었다면 인덱스를 변경시킬 방법을 연구 해 보아야 한다. 그러나 특정한 액세스유형을 위해 인덱스를 추가하게 되면 기존의 액세스에 영향을 미칠 수 있으므로 반드시 종합적인
판단을 해야 한다.
9. 부분범위처리
- 어떤 sql에서 where 절에 주어진 조건을 만족하는 전체범위를 처리하지 않고 운반단위까지만 먼저 처리하여 그 결과를 추출시킨 뒤 다음 작업을 사용자의 요구가 있을 때까지 수행을 멈추는 처리를 말한다. 따라서 아무리 넓은 범위의 처리를 요구하더라도 아주 빠른 수행속도를 보장받을 수 있다.
- 주어진 범위가 넓더라도 운반단위에 채워질 테이터가 많다면 언제나 빠른 수행속도를 보장받을 수 있다. 그러나 운반단위에 채워질 데이터가 많지 않다면 모든 처리 범위를 수행한 후에 결과를 추출시킬 수가 있어 빠른 수행속도를 보장받지 못한다.
1.부분범위 처리의 자격
- 전체범위를 하지 않고서는 도저히 결과를 추출할 수 없는 경우를 제외하고 부분범위처리가 모두 가능하다. (
단 UNION ALL은 부분범위 처리가 가능하다.
2. 옵티마이저 모드에 따른 부분범위 처리
- 규칙기준,비용기준의 FIRST_ROWS는 부분범위처리가 가능하나 비용기준의 ALL_ROWS는 불가능하다.
3.부분범위처리의 수행속도 향상원리
- 부분범위처리에서는 경우에 따라 오히려 넓은 범위를 가지는 경우가 더 빠르다.
SELECT * FROM ORDER;
이 SQL을 SQL*PLUS에서 수행시켜 보면 ORDER 테이블에 아무리 많은 데이터가 있더라도 0.01초 이내에 데이터가 추출된다. 이 테이블의 첫번째 블럭을 스캔한 모든 로두들이 운반단위로 보내 추출시키는 부분범위처리를 한다.
SELECT * FROM ORDER ORDER BY ITEM;
이 SQL은 오랜시간이 지나서야 첫번째 운반단위가 추출된다.
왜 이렇게 차이가 나는걸까?
첫번째 SQL은 부분범위처리를 했기 때문에 실제로 수행한 일량은 한 운반단위
만큼만 처리하고 멈춘것이고 두번째 SQL은 전체범위처리를 해야 하기 때문에 전체
테이블을 액세스하여 임시 저장공간에 저장하여 전체를 정렬을 한 후 한 운반단위를
추출하고 멈추었다.
만약 두번째 SQL을(ITEM이란 인덱스가 있다고 가정..)
SELECT * FROM ORDER WHERE ITEM > ' ';
또는
SELECT /*+ INDEX(ORDER ITEM_INDEX)*/ * FROM ORDER;
위와 같이 인덱스를 경유하게 한다면 부분범위처리가 되어 엄청난 수행속도의 차이를 느끼게 될 것이다.
따라서 온라인 화면과 같은 어떤 범위의 데이터를 빠른 응답을 원한다면 부분범위처리가 가능하게 하여야 할 것이다.
** 특징
- 액세스를 주관하는 컬럼의 처리범위는 좁을 수록 유리하다. 언제나 빠른 수행속도를 보장받는다.
- 액세스를 주관하는 컬럼의 범위가 넓더라도 그 외의 조건을 만족하는 범위가 넓다면 빠른 수행속도를 보장받는다.
- 액세스 주관컬럼의 범위가 넓고 그 외 컬럼의 범위가 좁아서 늦어지는 경우는 처리범위가 좁은 컬럼이 액세스를 주관하는 컬럼이 되도록하면 수행속도가 빨라 진다. 이런 경우 힌트나 사용제한 기능을 이용하면 된다.
- 즉 부분범위처리를 할 경우
액세스 주관 조건의 범위가 넓고 검증조건의 범위가 좁은 경우를 제외하고 전부 빠른 수행속도를 보장 받는다.
4.부분범위처리로의 유도
1) 인덱스를 이용한 SORT의 대체
- ORDER BY를 이용하여 정렬을 한 경우는 전체범위처리가 된다. 이런 경유 인덱스를 이용하여 정렬을 하면 부분범위처리가 된다.
SELECT * FROM ORDER ORDER BY ITEM;
-> SELECT * FROM ORDER WHERE ITEM > ' ';
(SELECT /*+ INDEX(ORDER ITEM_INDEX)*/ * FROM ORDER;)
2) 인덱스만 액세스하는 부분범위 처리
- 인덱스는 첫번째 로우를 찾을때만 랜덤 액세스를 하고 그 다음부턴 스캔을 하지만 테이블을 액세스하는 해위는 항상 랜덤액세스를 한다. 그러므로 테이블 랜덤액세스를 하지 않고 인덱스로만 처리된다면
엄청난 수행속도를 보장받게 된다.
인덱스만 사용하는 실행계획이 수립되기 위해서는
* WHERE 조건에 사용된 모든 컬럼들이 하나의인덱스로 결합 되어 있거나..
* 인덱스 머지가 되는 실행계획이 수립된다면 머지되는 두개의 인덱스내에 모든 컬럼들이 속해야 한다.
만약 ORD_DATE+CUSTNO로 결합 인덱스가 있는 ORDER테이블이 있다면
SELECT ORD_DEPT,COUNT(*)
FROM ORDER
WHERE ORD_DATE LIKE '200205%'
GROUP BY ORD_DATE;
-> ORD_DATE 조건을 만족하는 전체범위를 인덱스를 통해 테이블을 랜덤 액세스하여 GROUP BY한 후 결과를 추출한다.
SELECT CUSTNO,COUNT(*)
FROM ORDER
WHERE ORD_DATE LIKE '200205%'
GROUP BY CUSTNO;
-> 결합인덱스인 (ORD_DATE + CUSTNO) 사용하는 실행계획을 수립하므로 휠씬 빠른 수행속도를 얻을수 있다.
10. 부분범위의 활용
1.MAX 값의 처리
아주 다량의 데이터를 가지고 있는 테이블에서 최대값을 찾거나 기본키 생성을 위해 일련번호의 최대값을 찾아 1을 더하는 경우가 발생한다. 이런 겅우 보다 효율적인 방법을 제시하고자 한다. (BOARD TABLE에 SEQ로 인덱스가 생성되어 있다고 가정하자)
SELECT /*+ INDEX_DESC(A SEQ_IDX01) */
SEQ + 1
FROM BOARD A
WHERE REQ_YMD ='20020615'
AND ROWNUM =1
-> BOARD 테이블은 SEQ_IDX01란 인덱스순으로 정렬이 되어있는데 이 정렬된 순서를 역으로 읽어와서 첫번째만 가져오므로 읽어온 SEQ가 최대값이 된다. 물론 최소값을 구하려면 힌트만 /*+ INDEX(A SEQ_IDX01) */ 변경하면 된다.
2. EXISTS 활용
가령 어떤 자료가 있는것인지 확인 하고자 할 경우
SELECT COUNT(*)
INTO :CNT
FROM EMP
WHERE DEPT_NO ='4403'
IF CNT > 0 THEN
....
이런 경우 존재확인을 위해 EMP TABLE에서 부서코드 '4403'을 만족하는 전체 데이터를 읽게된다.
->
SELECT 'X'
INTO :CNT
FROM DUAL
WHERE EXISTS(SELECT 'X'
FROM EMP
WHERE DEPT_CODE ='4403')
이렇게 처리를 함으로써 EMP TABLE에서 부서코드 '4403'을 만족하는 데이터가 있으면 더이상 검색을 하지 않는다. EXISTS 함수는 참,거짓을 리턴하는 불린형 함수이다.
SELECT ORD_DEPT,ORD_DATE,CUSTNO
FROM PRODUCT
WHERE ORD_DATE LIKE '200206%'
MINUS
SELECT ORD_DEPT,ORD_DATE,'DN01'
FROM SALE
WHERE CUSTNO ='DN01'
이 SQL은 다량의 처리범위를 각각의 로우마다 랜덤 액세스로 대응되는 로우를 찾아 확인 하는것을 피하기위해 전체저리가 최적화(ALL_ROWS)되도록 머지(MERGE)방법으로 유도한 경우이다. 배치처리에서는 전체를 처리해야 함으로 좋은방법이라 할 수 있겠지만 온라인에서는 첫번째 운반단위가 추출되는 시간이 많이 소모된다.
이것을 피하기 위해 부분범위 처리로 변경하면
SELECT ORD_DEPT,ORD_DATE,CUSTNO
FROM PRODUCT A
WHERE ORD_DATE LIKE '200206%'
AND NOT EXISTS(SELECT ORD_DEPT,ORD_DATE,'DN01'
FROM SALE B
WHERE B.ORD_DEPT = A.ORD_DEPT
AND B.ORD_DATE = A.ORD_DATE
AND B.CUSTNO ='DN01')
부분범위처리 속도향상의 원리에 의해서 액세스주관 컬럼이 아닌 조건의 범위가 같이 넓으면 수행속도는 보장된다. CUSTNO ='DN01'을 만족하면 로우가 적다면 아주빠른 수행속도를 보장받을 수 있지만 그렇지 않다면 앞의 방법(MINUS)유리할 수도 있다.
11. 다중처리
** 이원화를 통한 부분범위처리
우리는 sql를 여러개로 나누어서 사용하는것이 네트워크 부하를 많이 발생시키므로 좋지않다는 것을
알고 sql를 가능한 통합하려고 한다. 하지만 항상 그런것이 유리하다고 말할 수 있는 것은 아니다.
SELECT DEPT_CODE,EMPNO,SUM(SALARY),NVL(SUM(BONUS),0)
FROM 급여테이블
WHERE DEPT_CODE LIKE '11%'
AND 급여일 BETWEEN '20020101' AND '20020631'
GROUP BY DEPT_CODE,EMPNO
만약 부서코드가 11FH 시작하는 부서가 많다면 온라인화면에서는 원하는 수행속도를 보장할 수 없다.
하지만 부서는 그리 많지 않으므로 SQL을 두개로 나누면
SELECT 부서코드
INTO :DEPTNO
FROM 부서테이블
WHERE 부서코드 LIKE '11%'
(부서코드로 인덱스를 가지고 있다고 가정)
급여테이블에서 가져오는 것 보다 휠씬 로우수가 적을 것이다.
위의 SQL에서 받은 부서코드를 이용하여
SELECT EMPNO,SUM(SALARY),NVL(SUM(BONUS),0)
FROM 급여테이블
WHERE 부서코드 = :DEPTNO
AND 급여일 BETWEEN '20020101' AND '20020631'
GROUP BY EMPNO
이렇게 두개의 SQL로 나누어서 처리함으로써 원하는 수행속도를 보장 받을 수 있다.
** 다중처리
1. 다중처리란 오퍼레이션이 실행될 때 한번 DBMS를 호출할 때 여러 건을 동시에 처리하도록 하여 DBMS의 호출 횟수를 줄임으로써 시스템 부하를 감소시키는 것을 말한다.
2. SELECT 다중처리
SELECT 다중처리는 많이 알고 있을거란 생각합니다. PRO*C를 이용하여 설명을 하고자 합니다.
먼저 FETCH받는 변수를 선언할 때 배열로 선언을 하여 선언된 배열수 만큼 한번에 다중처리를 합니다.
#define ARR_SIZE 100;
short s_ind[ARR_SIZE];
EXEC SQL BEGIN DECLARE SECTION
char emp_name[ARR_SIZE][20];
int emp_number[ARR_SIZE]
float salary[ARR_SIZE]
EXEC SQL END DECLARE SETION;
-> 변수 선언부입니다.
배열에서 앞에있는 ARR_SIZE은 다중처리를 받을 갯수이고 두번째 있는 배열(char emp_name[ARR_SIZE][20])에서 20은 배열의 길이를 말합니다. 즉 char[20]이므로 string을 뜻합니다.
EXEC SQL
SELECT ENAME,ENPNO,SAL
INTO :emp_name :s_ind,
:emp_number :s_ind,
:salary :s_ind
FROM EMP
WHERE EMPNO > 1000;
(변수 뒤에 :s_ind을 붙인 것은 다들 아시겠지만 indicate 변수라고 fetch시 null값이 오면 에러가 나는것을 방지하기 위해서 사용했습니다.)
-> 위 sql을 수행시 만약 fetch받는 변수가 배열이 아니라면 하나의 값만 받겠지만 현재는 최대 100개를 동시에 받아서 처리를 할 수 있게 됩니다. 물론 커서를 선언하더라도 마찬가지이겠지요..
100건 이상이라면 당연히 커서를 사용하여 반복문으로 처리를 해야 합니다.
만약 커서를 사용하여 100건 이상을 처리할 경우 주위해야 할 것은 만약 fetch시 100건이 채워지지 않으면 1403에러(no data found) 발생한다는 것입니다.
sqlca.sqlerrd[2] -> 페치된 총 로우수가 저장되어 있음.
int num_ret =0;
int end_flag =1;
EXEC SQL
DECLARE cur CURSOR FOR
SELECT ENAME,ENPNO,SAL
FROM EMP
WHERE EMPNO > 1000;
EXEC SQL OPEN cur
while(end_flag>0){
EXEC SQL FETCH cur INTO :emp_name,:emp_number,:salary ;
....
if(sqlca.sqlcode == 1403) end_flag =0;
if((sqlca.sqlerrd[2]-num_ret) >0)
....
num_ret = sqlca.sqlerrd[2];
}
EXEC SQL CLOSE cur;
3.UPDATE 다중처리
배열로 지정된 필드를 UPDATE,INSERT,DELETE 할 경우 FOR를 이용해야 한다. 만약 FOR를 이용하지 않는다면 UPDATE,INSERT,DELETE할 경우 항상 배열로 크기만큼 수행하게 된다.(위와 간다면 100번 수행) 이럴경우 PK같은 NULL을 허용하지 않는 필드가 있다면 에러가 발생할 것이다.
cnt = sqlca.sqlerrd[2];
EXEC SQL
FOR :cnt
UPDATE EMP
SET SAL = :salary*1.12
WHERE EMPNO =:emp_number;
FOR :cnt 때문에 UPDATE문은 페치된 건 수 만큼 처리는 되지만 실재로 UPDATE문은 한번만 처리되므로 부하를 감소할 수 있다. FOR :cnt 항상 변수를 사용해야 한다.
상수를 사용하면 에러가 발생함.
4. INSERT 다중처리
EXEC SQL
INSERT INTO EMP(ENAME,EMPNO,SAL)
VALUES(:emp_name,:emp_number,:salary);
한번에 100건이 입력된다.
5. DELETE 다중처리
UPDATE,INSERT 다중처리와 동일하다.
12. 다중처리를 추가한 배치처리
OUTER 조인을 활용하여 다중처리를 한번에 100개씩 처리하는 배치..
#define ARR_SIZE 100;
main() {
short s_ind[ARR_SIZE];
EXEC SQL BEGIN DECLARE SECTION
char emp_name[ARR_SIZE][20];
varchar v_fld1[ARR_SIZE][10];
varchar v_rowid[ARR_SIZE][20];
int loop;
int v_num1[ARR_SIZE];
EXEC SQL END DECLARE SETION;
int num_ret=0;
....
EXEC SQL DECLARE cur CURSOR FOR
SELECT a.num1,DECODE(a.fld1,'A',0.01,'B',0.07,0.09)*b.NUM2,
b.rowid
FROM TAB1 a,TAB2 b
WHERE a.fld1 = b.fld1(+)
AND a.fld2 like '9510%' ;
EXEC SQL OPEN cur;
indt = 1;
while(indt){
EXEC SQL FETCH cur INTO :v_num1 :s_ind,:v_fld1 :s_ind,:v_rowid :s_ind;
if(sqlca.sqlcode ==1403) indt =0;
loop = sqlca.sqlerrd[2] - num_ret;
num_ret = sqlca.sqlerrd[2];
if(loop > 0){
update_rows();
EXEC SQL COMMIT WORK;
}
}
EXEC SQL CLOSE cur;
EXEC SQL COMMIT WORK RELEASE; /* COMMIT 후 데이터 베이스와 연결을 종료한다 */
}
update_rows()
{
EXEC SQL FOR :loop
UPDATE TAB2
SET num2 = num2 + :v_num1
WHERE :v_rowid IS NOT NULL
AND ROWID = :v_rowid;
EXEC SQL FOR :loop
INSERT INTO TAB2
SELECT ;v_fld1,:v_num1
FROM DUAL
WHERE :v_rowid IS NULL ;
}
위에서 :loop는 sqlca.sqlerrd[2] - num_ret 즉 총추출 로우에서 지금까지 추출된 로우수를 뺀값이다
방금 페치된 수를 뜻한다.
다시 한번 더 말하면 다중처리에서 주의할 점은 마지막 페치에서 배열수를 다 채우지 못하면 1403에러가 발생한다는 것이다.
따라서 위와 같이 처리를 해줘야 한다.
INSERT INTO TAB2에서 FROM DUAL WHERE :v_rowid IS NULL이렇게 사용한 이유는 INSERT문에서는 WHERE조건을 사용할 수 없기 때문이다.
즉 데이터가 없을 경우는 INSERT를 하고 있을 경우는 UPDATE처리를 한다. (WHERE 조건 때문에...)
13. 정렬을 이용한 쓰기작업의 효율화
1번의 배치에서 TAB2테이블을 ROWID로 정렬을 하여 사용하면 더욱 성능이 향상된다. 배치처리와 같이 다량의 데이터를 처리하는 작업은 처리할 데이터를 액세스하거나 가공처리 하는 작업보다 쓰기작업이 부하가 휠씬 크다. 따라서 읽고 가공하는 부분을 조금 손해를 보더라도 쓰기작업을 부하가 줄어든다면 전체 처리 시간을 감소할 수 있을 것이다.
이 방법은 1.배치작업에서 단지 SURSOR로 지정한 SELETE에서 ROWID로 정렬을 하는 것이다.
ROWID로 정렬하면 ROWID가 있는 UPDATE만 위로 오게 된다. NULL값인 INSERT는 밑에 있게 된다.
따라서 UPDATE,INSERT는 각각 수행할 수가 있다.
ROWID는 BLOCK NUMBER,ROW NUMBER,DATAFILE NUUMBER로 구성되어 있으므로 ROWID로 정렬을 했다는 것은 블럭 및 블럭내 로우 순으로 정렬이 되었다는 의미이므로 쓰기작업을 할 경우 해당 블럭에 처리되어야 할 모든 로우가 동시에 단 한번의 작업으로 인해 이루어 진다.
#define ARR_SIZE 100;
main() {
EXEC SQL BEGIN DECLARE SECTION
char emp_name[ARR_SIZE][20];
varchar v_fld1[ARR_SIZE][10];
varchar v_rowid[ARR_SIZE][20];
short s_ind[ARR_SIZE];
int loop;
int v_num1[ARR_SIZE];
EXEC SQL END DECLARE SETION;
int num_ret=0;
....
EXEC SQL DECLARE cur CURSOR FOR
SELECT a.num1,DECODE(a.fld1,'A',0.01,'B',0.07,0.09)*b.NUM2,
b.rowid
FROM TAB1 a,TAB2 b
WHERE a.fld1 = b.fld1(+)
AND a.fld2 like '9510%'
ORDER BY b.rowid ;
EXEC SQL OPEN cur;
indt = 1;
while(indt){
EXEC SQL FETCH cur INTO :v_num1 :s_ind,:v_fld1 :s_ind,:v_rowid :s_ind;
if(sqlca.sqlcode ==1403) indt =0;
loop = sqlca.sqlerrd[2] - num_ret;
num_ret = sqlca.sqlerrd[2];
if(s_ind[0] == -1)
{ /* 첫번째 페치된 로우가 rowid = null */
process_up_ins(0); /* insert 작업만 수행하는 함수 */
}
else
{ /* 첫번째 페치된 로우가 rowid != null */
if(s_ind[loop-1] == -1)
{
process_up_ins(1); /* insert,update 같이 수행한다 */
}
else
{
process_up_ins(2); /* update 만 수행하는 함수 */
}
EXEC SQL COMMIT WORK;
}
}
EXEC SQL CLOSE cur;
EXEC SQL COMMIT WORK RELEASE; /* COMMIT 후 데이터 베이스와 연결을 종료한다 */
}
process_up_ins(flag)
int flag;
{
if(flag == 2)
{
EXEC SQL FOR :loop
UPDATE TAB2
SET num2 = num2 + :v_num1
WHERE ROWID = :v_rowid;
}
else if(flag == 1)
{
EXEC SQL FOR :loop
INSERT INTO TAB2
SELECT ;v_fld1,:v_num1
FROM DUAL
WHERE :v_rowid IS NULL ;
EXEC SQL FOR :loop
UPDATE TAB2
SET num2 = num2 + :v_num1
WHERE :v_rowid IS NOT NULL
AND ROWID = :v_rowid;
}
else
{
EXEC SQL FOR :loop
INSERT INTO TAB2
VALUES(:v_fld1,:v_num1);
}
}
14. INSERT가 많은 경우의 배치처리
만약 우리가 처리하고자 하는 배치작업에 INSERT가 휠씬 많다는 것을 알고 있다면 보다 효율적으로 배치작업을 할 수가 있다.
대부분의 처리가 INSERT작업이라면 약간의 UPDATE작업을 위해서 각각의 로우들에 대해 INSERT,UPDATE를 확인한다는 것은 매우 비효율적이다. 이럴 경우 얼마되지 않은 UPDATE작업을 선별해 둔다면 나머지는 확인작업을 거치지 않고 INSERT작업을 수행하면 된다.
여러가지 방법중에 가장 효율적인 방법을 이용하여 UPDATE대상로우들을 선별하면 먼저 INSERT 작업을 다중처리한 다음 UPDATE작업을 수행한다.
#define ARR_SIZE 100;
main()
{
EXEC SQL BEGIN DECLARE SECTION
char emp_name[ARR_SIZE][20];
varchar v_fld1[ARR_SIZE][10];
varchar v_rowid[ARR_SIZE][20];
short s_ind[ARR_SIZE];
int loop;
int v_num1[ARR_SIZE];
EXEC SQL END DECLARE SETION;
int num_ret=0;
....
/* UPDATE 할 대상을 저장할 임시테이블 */
EXEC SQL CREATE TABLE TEMP_SAVE AS
SELECT A.NUM1,A.FLD1,B.NUM2,B.FLD2,.....
FROM TAB1 A,TAB2 B
WHERE A.FLD1 = B.FLD1
AND B.FLD2 BETWEEN '200205%' AND '200206%' ;
EXEC SQL INSERT INTO TAB2(FLD2,NUM2,...)
SELECT FLD1,NUM1,DECODE(....)
FROM TAB1 A,TAB3 C
WHERE A.FLD1 = C.FLD3
AND ....;
/* UPDATE할 대상을 선별한다 */
EXEC SQL DECLARE cur CURSOR FOR
SELECT a.num1,....,b.rowid
FROM TEMP_SAVE a,TAB2 b
WHERE a.fld1 = b.fld2;
EXEC SQL OPEN cur;
indt = 1;
while(indt){
EXEC SQL FETCH cur INTO :v_num1 :s_ind,:v_fld1 :s_ind,:v_rowid :s_ind;
if(sqlca.sqlcode ==1403) indt =0;
loop = sqlca.sqlerrd[2] - num_ret;
num_ret = sqlca.sqlerrd[2];
EXEC SQL FOR :loop UPDATE TAB2
SET NUM2 =num2 + :v_num1,....
WHERE ROWID = :v_rowid;
}
EXEC SQL CLOSE cur;
EXEC SQL DROP TABLE TEMP_SAVE
EXEC SQL COMMIT WORK RELEASE; /* COMMIT 후 데이터 베이스와 연결을 종료한다 */ }
위의 SQL에서 TEMP_SAVE을 CREATE,DROP을 하지 않고 TEMP_SAVE을 만들어두고 배치를 수행하기 처음에 TRUNCATE시켜도 무방하다..
주의할 점은 UPDATE 쿼리가 복잡하다면 UPDATE 대상 로우들과 정보를 제공하는 로우들을 조인할 때 반드시 처리범위가 적은 UPDATE대상 집합이 먼저 수행되는 실행계획을 수립해야 한다.
15. SQL을 이용한 배치처리
UPDATE TAB3 T3
SET AMT3 = DECODE(COL3,'A',100,1000),
(COL3,FLD3,QTY3...) =(SELECT DECODE(T2.COL2,'1',SUBSTR(COL4,1,4)...),
FROM TAB2 T2,TAB4 T4
WHERE T2.ITEM2 = T3.ITEM3
..... )
WHERE ITEM3 IN(SELECT ITEM2
FROM TAB2
WHERE PROD IN(SELECT .... ));
AND EXISTS (SELECT 'X' FROM TAB2
WHERE ....)
AMT3 처럼 서브쿼리의 도움을 받지않고 처리가능한 것은 불필요한 서브쿼리가 수행되는 것을 방지하기 위해 따로 분리한다.
서브쿼리로 처리해야 하는 것은 (COL3,FLD3,QTY3...) 같이 처리한다. 또한 UPDATE 문의 WHERE 조건에도 서브쿼리를 이용하여 다양하게 처리를 할 수가 있다..
주의할 점은
1.이부분의 서브쿼리는 (COL3,FLD3,QTY3...) =(SELECT DECODE(T2.COL2,'1',SUBSTR(COL4,1,4)...)
'=' 관계이므로 반드시 메인쿼리의 로우마다 단 하나의 결과를 추출해야 한다. 하나 이상이라면 그룹함수들을 이용하여 하나의 로우가 되도록처리를 해야 한다. 이 서브쿼리는 메인쿼리의 작업대상 로우수만큼 반복수행되므로 처리범위가 좁아야 한다. 그렇지 않다면 다중처리를 이용한 배치를 만들어야 할 것이다.
2.하나의 SQL이므로 전체 작업이 완료시 COMMIT,ROLLBACK이 가능하므로 너무 많은 로우를 처리할 때는 사용하지 않는것이 좋다. (메모리를 너무 많이 소모하게 되므로...)
3.처리시 발생되는 에러처리가 어렵다..
4.동일한 서브쿼리를 이용하여 여러개의 테이블을 UPDATE할 수 없다.
5.만약 서브쿼리가 실페했을 때(NO DATA FOUND) SET 절에 기술한 값들은 NULL값이 된다.
이것을 해결하기 위해 EXISTS를 이용할 수도 있다. 아니면 COUNT(),그룹함수 NVL(MAX(),어떤 값),NVL(MIN(),어떤 값) 같은 최소한 1건이상을 리턴하게 한다면 해결된다. COUNT()함수는 하나도 로우가 없더라도1로우가 추출된다.. 하지만 NVL()함수를 그룹함수와 같이 사용하지 않으면 소용이 없다..
왜냐하면 한것도 추출되지 않을 경우 O 로우이기 때문이다..
16. IN 조건에 대한 액세스 분석 및 문제 해결
IN의 사용은 LIKE,BETWEEN 성격의 액세스 문제점을 해소하기 위한 좋은 해결책을 제시하는 개념이다.
이것은 LIKE,BETWEEN의 조건이 안고 있는 값의 DISTINCT를 보장하기 못하는 기본적인 한계를 극복하게 해 주는 것이다.
실행계획상에서 INLIST나 CONCATINATION이 나타나면 IN의 사용으로 인해 조건범위를 그냥 스캔하지 않고 인덱스에서 정확하게 해당 값을 찾아 널뛰기를 하였다는 증거이므로 항상 실행계획을 통해 본인이 의도한 IN의 효과가 나타났는지 확인하는 주의가 필요하다.
INLIST는 CONCATINATION 실행계획의 복잡성을 간소화한 것으로 액세스량에는 별차이가 없는 것으로 인식하면 된다.
한가지 주의할 것은 IN 조건에 값을 열거한 경우는 가장 뒤의 값부터 먼저 실행되어 나타나므로 정렬을 위하여 사용할 수도 있고, SUB-QUERY가 들어가는 경우는 값을 UNIQUE하게 만든 다음 액세스가 이루어진다
특히 이러한 성격은 MAIN절의 두 테이블의 연결(조인)을 위해 IN SUBQUERY(CORLATED QUERY)를 사용할 수 있는 아주 적절한 방법을 제공한다는 것이다.
LIKE,BETWEEN의 해결책으로 IN을 사용하여 연결액세스를 의도한 경우는 실행계획상에서 반드시 IN안의 SUBQUERY가 먼저 수행되도독 강제하는 것이 좋다.
이러한 강제는 PUSH_SUBQ과 같은 힌트를 사용하기 보다는 일반적인 조인에서의 연결키 한쪽을 컬럼변형으로 인한 인덱스사용불가인 서프레싱기법을 이용하는 것이 무난하다.
SELECT *
FROM SALE
where SALEDATE = '19971219'
AND CUST_CODE IN ('C30060','C30050','C30040','C30030','C30020')
Rows Execution Plan
------- ---------------------------------------------------
SELECT STATEMENT
0 INLIST ITERATOR CONCATENATED
20 TABLE ACCESS BY INDEX ROWID :SALE
25 INDEX RANGE SCAN :PK_SALE (U)
아래를 보면 조금 더 자세히 알수 있다.
SELECT /*+ USE_CONCAT */ *
FROM SALE
where SALEDATE = '19971219'
AND CUST_CODE IN ('C30060','C30050','C30040','C30030','C30020')
Rows Execution Plan
------- ---------------------------------------------------
SELECT STATEMENT
0 CONCATENATION
0 TABLE ACCESS BY INDEX ROWID :SALE
1 INDEX RANGE SCAN :PK_SALE (U)
0 TABLE ACCESS BY INDEX ROWID :SALE
1 INDEX RANGE SCAN :PK_SALE (U)
0 TABLE ACCESS BY INDEX ROWID :SALE
1 INDEX RANGE SCAN :PK_SALE (U)
10 TABLE ACCESS BY INDEX ROWID :SALE
11 INDEX RANGE SCAN :PK_SALE (U)
10 TABLE ACCESS BY INDEX ROWID :SALE
11 INDEX RANGE SCAN :PK_SALE (U)
SELECT CUST_CODE,COUNT(*)
FROM SALE
where SALEDATE = '19971219'
AND CUST_CODE IN ('C30060','C30050','C30040','C30030','C30020')
CONFIDENTIAL 23 2001-09-06
GROUP BY CUST_CODE
CUST_C COUNT(*)
------ ----------
C30050 10
C30060 10
SELECT /*+ INDEX(SALE SALE_CUST_SALEDATE_IDX) */ *
FROM SALE
where SALEDATE = '19971219'
AND CUST_CODE IN ('C30060','C30050','C30040','C30030','C30020')
Rows Execution Plan
------- ---------------------------------------------------
SELECT STATEMENT
0 INLIST ITERATOR CONCATENATED
20 TABLE ACCESS BY INDEX ROWID :SALE
25 INDEX RANGE SCAN :SALE_CUST_SALEDATE_IDX(NU)
SELECT /*+ INDEX(SALE SALE_CUST_SALEDATE_IDX) USE_CONCAT */ *
FROM SALE
where SALEDATE = '19971219'
AND CUST_CODE IN ('C30060','C30050','C30040','C30030','C30020')
Rows Execution Plan
------- ---------------------------------------------------
SELECT STATEMENT
0 CONCATENATION
0 TABLE ACCESS BY INDEX ROWID :SALE
1 INDEX RANGE SCAN :SALE_CUST_SALEDATE_IDX(NU)
0 TABLE ACCESS BY INDEX ROWID :SALE
1 INDEX RANGE SCAN :SALE_CUST_SALEDATE_IDX(NU)
0 TABLE ACCESS BY INDEX ROWID :SALE
1 INDEX RANGE SCAN :SALE_CUST_SALEDATE_IDX(NU)
10 TABLE ACCESS BY INDEX ROWID :SALE
11 INDEX RANGE SCAN :SALE_CUST_SALEDATE_IDX(NU)
10 TABLE ACCESS BY INDEX ROWID :SALE
11 INDEX RANGE SCAN :SALE_CUST_SALEDATE_IDX(NU)
17. 연결 액세스 분석1 (조인)
1)용어정리
1.대상 릴레이션
액세스의 대상이 되는 오브젝트를 2차원의 행렬구조을 가진 집합으로 정의하는 내용이다.
액세스되는 실제 2차원의 행렬구조의 형태로 데이터 값을 가진 집합을 의미한다.
객체지향적인 개념으로 말하면 테이블의 인스턴스를 의미한다.
쿼리를 빌어 쉽게 표현하면 ‘FROM’절에 있는 테이블을 의미하는 것으로 해석하면 될 것이다.
2.중간 릴레이션
대상이 되는 릴레이션을 액세스하여 최종 리턴되기를 원하는 목표릴레이션을 도출하기까지의 과정속에서 만들어지는 2차원의 행렬구조의 집합을 의미한다.
GROUP BY를 가진 인라인뷰의 경우는 먼저 수행되어 메모리상의 테이블 형태로 임시저장되는데, 이러한 SQL 수행중간에 생성되어 사라지는 가상 릴레이션을 의미하는 것으로 해석하면 된다.
3.목표 릴레이션
SQL구사로 인해 보기를 원하는 결과OUTPUT으로 모두 2차원의 행렬구조의 집합이다.
실제 SQL을 통해 우리가 보고자 하는 릴레이션을 의미한다.
4.DRIVING TABLE(OUTER TABLE)
주로 NESTED-LOOP조인에서 먼저 액세스 되는 테이블을 의미한다.
5.DRIVED TABLE(INNER TABLE)
주로 NESTED-LOOP조인에서 나중에 액세스 되는 테이블을 의미한다.
6.LOOKUP
조인과정에서 INNER TABLE의 인덱스를 통한 연결을 의미한다.
즉 DRIVING의 결과 조인값으로 DRIVED OBJECT의 매칭되는 조인값을 찾는 행위를 표현한다.
주로 매칭되는 조인값을 찾기위해 연결인덱스(LOOKUP INDEX)를 쳐다본다는 의미로 해석하면 될 것이다.
7.LOOKUP INDEX(INNER TABLE 연결INDEX)
조인에서 DRIVING의 연결키 값이 결정된 다음 그 값으로 INNER TABLE의 연결인덱스를 쳐다보게 되는데, 이때 대상이되는 인덱스를 의미한다.
8.UNIQUE LOOKUP
INNER TABLE 연결INDEX가 PK나 UNIQUE 인덱스이고 인덱스 구성 컬럼 모두의 값이 연결되어 넘어오는 경우에 발생하는 액세스 행위를 의미한다.
9.RANGE LOOKUP
위의 UNIQUE LOOKUP 의 경우를 제외한 일반적인 일대다 연결의 경우, INNER TABLE 연결INDEX의 RANGE SCAN을 의미한다.
10.OBJECT
액세스가 인덱스나, 테이블만을 의미 할 수도 있고, 인덱스를 통한 테이블의 액세스도 동시에 존재하므로 그냥 편하게 모든 경우를 대표할 수 있는 표현으로 썼다. 객체지향의 객체를 의미한 것이 아님에 유념하기 바란다.
11.ONE PLUS SCAN
RANGE LOOKUP의 경우 발생하는 연결시도 횟수만큼의 LOOKUP INDEX 스캔량을 의미한다. UNIQUE LOOKUP이 아닌 경우, 매 연결시도마다 한번더 인덱스를 스캔해야 하므로 발생한다. ONE PLUS SCAN은 곧 연결시도의 횟수를 의미하는 것으로 LOOKUP INDEX FILTERING이 존재하는 경우, INNER TABLE의 ROWID 액세스 양과 비교하여 FILTERING양를 계산해 낼 수 있다. DRIVING FILTERING이 존재하는 경우는 아주 쉽게 트레이스상의 DRIVING 최종 액세스양에서 이 ONE PLUS SCAN을 제함으로써 FILTERING양을 계산해 낼 수 있을 것이다. 액세스 량의 정확한 계산을 위해 필요한 아주 중요한 개념이다.
12.FILTERING
WHERE절의 특정 컬럼에 대한 조건이 해당 오브젝트의 일량에 바로 기여를 하지 못하고 다음의 액세스로 연결(조인만을 의미하는 것이 아님)되기 직전에 걸러지는 양을 의미한다. 이는 단일액세스(조인이 없는 단일 테이블 액세스)의 경우는 인덱스를 통한 액세스의 경우라야 TABLE 액세스를 줄여주는 일량으로 존재가치가 있으며, 연결액세스(조인)의 경우는 연결전에 연결시도의 횟수를 줄여주는 기여를 하기도 한다. 잘 이용하면 나름대로 큰 역할을 수행할 수 있으므로 튜닝의 방법으로 쓰일 수 있다.
13.DRIVING OBJECT FILTERING(DRIVING FILTERING)
조인전 FILTERING의 경우를 정확하게 표현한 경우로 조인전(연결전)에 먼저 읽힌 오브젝트에서 최종적으로 걸러진 양을 의미한다. 인덱스만 액세스 했던지, 아니면 테이블을 통해 액세스가 이루어 졌던지간에 SQL의 조건에서 액세스에 바로 기여를 하지 못하고 다음 연결전에 걸러주는 역할만의 일량이다. 트레이스 상에서 보면 LOOKUP을 하기 직전의 일량에서 걸러지는 양을 의미한다.
14.LOOKUP INDEX FILTERING
DRIVING의 값이 결정되고 실제적으로 INNER TABLE 연결인덱스를 통해 연결이 시도된 다음, INNER TABLE 연결인덱스의 구성컬럼중 WHERE 조건에 등장하지만 LOOKUP과정에서 LOOKUP INDEX의 일량에 바로 기여를 하지 못하고 INNER TABLE로의 ROWID 액세스를 줄여주는 FILTERING역할만을 수행한 컬럼의 액세스 량을 의미한다.
15.연결시도횟수
실제로 조인(연결)을 시도한 횟수를 의미하며, 이는 DRIVING OBJECT FILTERING이 존재하지 않는다면 트레이스상에서 DRIVING의 최종적인 일량과 일치하며, DRIVING OBJECT FILTERING이 존재한다면 이를 제외한 일량과 일치한다.
16.연결성공횟수
연결시도횟수중 INNER TABLE의 인덱스를 LOOKUP하여 조인값이 존재하여 실제로 연결이 이루어진 횟수를 의미한다.
17.연결실패횟수
연결시도횟수중 INNER TABLE의 인덱스를 LOOKUP하였으나 조인값이 존재하지 않아서 실제로 연결이 이루어 지지 못한 횟수를 의미한다. 위의 세가지 연결과 관련된 정의는 드라이빙에서의 연결을 기준으로 한 정의이므로 INNER TABLE이 상세 테이블이어서 일대다 조인이 이루어져 1회연결로 여러 레코드가 선택되더라도 그것은 1회를 의미함을 기억하기 바란다.
18.순수스캔량
RANGE LOOKUP의 경우 발생하는 ONE PLUS SCAN을 제외한 LOOKUP INDEX의 스캔량을 의미한다. LOOKUP INDEX FILTERING이 존재하지 않는다면 이것만큼 테이블로의 ROWID 액세스가 이루어 진다. 특히 이것은 연결성공에 대한 순수스캔량임에 유념하기 바란다.
19.연결성공의LOOKUP RANGE 조합
RANGE LOOKUP에서 연결성공횟수의 각회마다 조회된 LOOKUP INDEX의 순수스캔량(ONE PLUS SCAN 제외)의 조합
우리가 SQL 튜닝을 위해 관심을 가지고 추적해야 하는 내용은 다음과 같다.
1. DRIVING FILTERING
2. LOOKUP INDEX FILTERING
3. JOIN TRY COUNT
4. JOIN SUCESS COUNT
5. JOIN FAILURE COUNT
6. 연결성공 각각의 LOOKUP SCAN량
트레이스상에서 LOOKUP 인덱스의 일량에 2와 5가 혼재되어 존재가 가능하므로 추적이 어렵다는 오해가 있을 수 있는데, 이는 잘못된 생각이다. 연결의 실패는 연결시도횟수에 해당하는 PLUS ONE SCAN을 제외하면, 제외하는 숫자 속에 이미 연결실패 횟수도 필터링되어 버리는 경우이다. 이는 매우 중요한 개념이다. 이것은 DRIVING의 필터링 양만 파악하면 4와 5 그리고 6(4의 조합)을 제외하고는 모든 액세스를 파악할 수 있다는 것을 의미한다. 그리고 어떤 상황에서도 UNIQUE LOOKUP(NO LOOKUP FILTERING 이 당연보장) 만은 모든 값을 추적할 수 있다.
4,5,6의 파악이 어려운 RANGE LOOKUP의 경우, 튜닝의 관점에서 4,5,6의 추가 파악이 중요하지 않는 경우가 많고, 정 필요할 것 같으면 SQL을 적절하게 구사하여 오브젝트의 속을 파악할 줄 아는 능력을 갖추면 가능하다.
18. 연결액세스 분석 II : SUB-QUERY
관계형 데이터베이스를 사용하면서 여러 테이블을 통해 흩어져 있는 정보 릴레이션을 조합하여 목표릴레이션을 만들기 위해서는 반드시 조인을 사용하여야 한다는 강박관념에서 벗어나야 한다. SQL을 통해 목표릴레이션을 만들어 내는데 있어서 관계형 데이터베이스는 다양한 형태의 데이터 연결방법을 제공한다.
1. 단순 서브쿼리의 후수행시의 트레이스
서브쿼리는 먼저 수행하여 메인쿼리로 연결이 이루어 질 수도 있고, 메인쿼리를 먼저 수행한 다음 서브쿼리로 연결이 이루어 질 수도 있다. 서브쿼리에 메인쿼리의 컬럼이 존재하지 않는 단순 서브쿼리의 경우는 실행계획을 통해 보면 메인에서 그냥 조인을 한 경우와 비슷한 형태로 나타난다.
아래의 쿼리를 통해 사용자가 의도하는 실행계획의 모습이 조건의 SALEDATE , 1998년 1월을 액세스한 다음 서브쿼리로 연결되는 형태로 처리를 예측했다면 사용자가 의도했던 모습과는 조금 차이가 있다.
그것은 서브쿼리 안에 존재하는 CUST_CODE 조건 때문에 메인에서도 똑같이 이 조건이 반영되어 SALE TABLE의 PK 인덱스를 사용하지 못하고 오히려 더 넓은 범위의 SALE_CUST_SALEDATE_IDX 인덱스를 사용하게 된 것이다.
SELECT *
FROM SALE
WHERE SALEDATE LIKE '199801%'
AND CUST_CODE IN (SELECT CUST_CODE
FROM CUSTOMER
WHERE CUST_CODE BETWEEN 'C30100' AND 'C30200')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0 0 0 0 0 0
Fetch 63 1.82 1.82 0 12926 0 6200
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 63 1.82 1.82 0 12926 0 6200
Rows Execution Plan
------- ---------------------------------------------------
SELECT STATEMENT
6200 NESTED LOOPS
6200 TABLE ACCESS BY INDEX ROWID :SALE
24401 INDEX RANGE SCAN :SALE_CUST_SALEDATE_IDX(NU)
6200 INDEX UNIQUE SCAN :PK_CUSTOMER (U)
사실 이쿼리는 ‘saledate like '199801%'’ 조건을 살리기 위해 서브쿼리가 먼저 수행되어 SALE_CUST_SALEDATE_IDX INDEX의 일량을 정확하게 액세스 할 수 있도록 하는 것이 더 일량의 측면에서 튜닝된 실행계획을 보여준다. 아래의 경우를 확인하기 바란다
SELECT /*+ INDEX(A SALE_CUST_SALEDATE_IDX) */
*
FROM SALE A
WHERE SALEDATE LIKE '199801%'
AND CUST_CODE IN (SELECT CUST_CODE || ''
FROM CUSTOMER B
WHERE CUST_CODE BETWEEN 'C30100' AND 'C30200')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0 0 0 0 0 0
Execute 1 0 0 0 0 0 0
Fetch 63 1.02 1.02 0 6610 0 6200
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 63 1.02 1.02 0 6610 0 6200
Rows Execution Plan
------- ---------------------------------------------------
SELECT STATEMENT
6200 NESTED LOOPS
101 VIEW SYS.
101 SORT UNIQUE
102 INDEX RANGE SCAN :PK_CUSTOMER (U)
6200 TABLE ACCESS BY INDEX ROWID :SALE
6301 INDEX RANGE SCAN :SALE_CUST_SALEDATE_IDX(NU)
최초의 쿼리에서 메인쿼리 액세스 일량을 분석하기 위해 다음과 같은 3가지 쿼리를 수행시켜 보자.
SELECT COUNT(*) FROM SALE
WHERE CUST_CODE BETWEEN 'C30100' AND 'C30200';
COUNT(*)
----------
24400
SELECT COUNT(*) FROM SALE
WHERE SALEDATE LIKE '199801%';
COUNT(*)
----------
6200
SELECT COUNT(*) FROM SALE
WHERE SALEDATE IN (SELECT TESTDATE
FROM DATETAB
WHERE TESTDATE LIKE '199801%')
AND CUST_CODE BETWEEN 'C30100' AND 'C30200'
COUNT(*)
----------
6200
이 3가지의 쿼리의 결과로 보면 메인절을 먼저수행한다는 가정을 하면, PK_SALE 인덱스를 사용하여 해당범위의 날짜에 해당하는 데이터를 먼저 액세스하여 서브쿼리를 연결하는 것이 일량의 측면에서 가장 최적의 실행계획이 될 것이라고 생각할 수 있다.
[출처] [펌]대용량 솔루션|작성자 새내기