'2014/11'에 해당되는 글 5건

  1. 2014.11.21 3-3강
  2. 2014.11.21 3-2강
  3. 2014.11.13 3-1강
  4. 2014.11.12 2-4강
  5. 2014.11.04 2-3강

 

  • 액세스경로의 결정

Select A1, A2, …, B1, B2, …, C1, C2, …
FROM TAB1 x, TAB2 y, TAB3 z
WHERE x.A1 = y.B1
AND z.C1 = y.B2
AND x.A2 = '10'
AND y.B2 LIKE 'B%'

   

구분

Access 순서

인덱스 전략

1

TAB1->TAB2->TAB3

A2
B1, B2

C1

2

TAB2->TAB3->TAB1

B2
C1
A1, A2

3

TAB3->TAB2->TAB1

B2 -> C1, Full Table scan
B2
A1, A2

   

  • JOIN과 LOOP-QUERY
    - LOOP-QUERY는 DBMS 콜이 많다. DBMS 콜도 부하의 원인이 된다.

    - 한쪽만 가지고 집합을 줄일 수 있으면 절대 먼저 조인을 하지 말라.

       

  • Nested Loop JOIN

    - 순차적(부분범위처리 가능)

    - 종속적(먼저 처리되는 테이블의 처리 범위에 따라 처리량 결정)

    - 랜덤(Random) 액세스 위주

    - 연결고리 상태에 따라 영향이 큼

    - 주로 좁은 범위 처리에 유리

       

  • SORT MERGE JOIN

    - 동시적(무조건 전체범위 처리)

    - 독립적(자기의 처리범위만으로 처리량 결정)

    - 스캔 액세스 위주

    - 연결고리 상태에 영향이 없음

    - 주로 넓은 범위 처리에 유리

       

  • Join 방법의 결정

 

신고

'대용량 데이터베이스 솔루션' 카테고리의 다른 글

3-3강  (0) 2014.11.21
3-2강  (0) 2014.11.21
3-1강  (0) 2014.11.13
2-4강  (0) 2014.11.12
2-3강  (0) 2014.11.04
2-2강  (0) 2014.10.20
Posted by TM ~ing

 

  • 추가된 INDEX가 미치는 영향(예제)

    - 인덱스: CHULNO+ORDNO+ITEM
    - 해석: PK를 사용함

SELECT CULNO, ORDNO, ITEM, CHULQTY FROM CULITEM
WHERE CHULNO = '2565' AND ORDNO = '8584' AND LOT = 'P0009'

TABLE ACCESS BY ROWID CHULITEM
INDEX RANGE SCAN PK_CHULITEM

- 인덱스: CHULNO+ORDNO+ITEM, LOT

- 해석: Fully equal이 되는 LOT 인덱스를 사용함

, CHULQTY FROM CULITEM
WHERE CHULNO = '2565' AND ORDNO = '8584' AND LOT = 'P0009'

TABLE ACCESS BY ROWID CHULITEM
INDEX RANGE SCAN CI_LOT

- 인덱스: CHULNO+ORDNO+ITEM, LOT+ITEM

- 해석: 다시 PK를 사용함.

SELECT CULNO, ORDNO, ITEM, CHULQTY FROM CULITEM
WHERE CHULNO = '2565' AND ORDNO = '8584' AND LOT = 'P0009'

TABLE ACCESS BY ROWID CHULITEM
INDEX RANGE SCAN PK_CHULITEM

   

  • 분포도 차이가 심한 경우의 인덱스

    - COL1의 분포도

A

B

C

D

E

78%

4%

6%

7%

5%

-> 이럴 경우는 A를 Null로 해서, 인덱스 사이즈도 줄어 들고, 속도도 빠르게 할 수 있다.

   

  • INDEX 재생성

ALTER INDEX index_name REBUILD
UNRECOVERABLE
PARALLEL 10;

- 현존하는 INDEX를 읽어 재생성
- DDL Lock을 발생시키지 않음

- 병렬처리 가능

- Direct database reads and writes

- 멀티블럭을 비동기적으로 I/O
- REDO LOG에 쓰지 않도록 할 수 있음

   

  • 비트맵 인덱스

    - 다량의 데이터를 가지고, 분포도가 안 좋은 컬럼에 적합한 인덱스(ex: 옷 색깔, 옷 사이즈 등)

    - or, not, null 을 판단할 수 있다.

    - 블럭레벨 Locking

    - 데이터웨어하우징에 적당

       

  • 조인 수행속도 결정 요소

    - 드라이빙 테이블의 크기에 따라서 수행 속도가 결정 된다. (작을 수록 빠르다.)

    - 1:M 조인시 1부터 Driving 시킨다.

    - 연결에 성공한 양은 다음 연결할 일 양에 영향을 미친다.

       

  • 조인 수행 속도 결정 요소

    - 연결고리에 해당하는 컬럼에는 양쪽 다 인덱스가 있어야 한다.

    - 테이블 조인시, 후행 테이블에는 키 값에 대한 인덱스가 있어야 한다.

    - 양쪽 모두 index가 없으면 SORT MERGE 방법으로 처리

       

  • 실행계획에 따른 ACCESS량

SELECT a.FLD1, …, b.FLD1,… FROM TAB2 b, TAB1 a
WHERE a.KEY1 = b.KEY2
AND b.FLD2 like 'A%'
AND a.FLD1 = '10'

- FLD1 = '10'은 5000개가 있고, b.FLD2 like 'A%'는 100개가 있다. 이런 상황에서는 B가 드라이빙 되는 것이 더 좋다.

   

  • 처리 범위에 따른 ACCESS량

SELECT a.FLD1, …, b.FLD1,… FROM TAB2 b, TAB1 a
WHERE a.KEY1 = b.KEY2
AND b.FLD2 = 'ABC'
AND a.FLD1 = '10'

- FLD1 = '10'은 500ROW가 있고, FLD2 = 'ABC'는 10 Row가 있다. 이런 상황이라면 FLD2를 드라이빙 테이블로 사용하는 것이 효과적이다.

- TAB2를 드라이빙 하기 위해서, ANALZE table, HINT, RTRIM(a.FLD1) 방법을 사용할 수 있다.

   

  • JOIN의 순서에 따른 속도차이

SELECT /*+ ORDERED */ CHULNO, CHULDATE, CUSTNAME
FROM CUSTOMER Y, CHULGOT X
WHERE X.CUSTNO = Y.CUSTNO
AND X.CHULDATE = '941003'
AND T.NATION = 'KOR'

1 rows
0.15 sec
 

NESTED LOOPS
TABLE ACCESS BY ROWID CUSTOMER
INDEX RANGE SCAN CU_NATION
TABLE ACCESS BY ROWID CHULGOT
AND
INDEX RANGE SCAN CH_CUSTNO

INDEX RANGE

SELECT CHULNO, CHULDATE, CUSTNAME
FROM CUSTOMER Y, CHULGOT X
WHERE X.CUSTNO = Y.CUSTNO
AND X.CHULDATE = '941003'
AND T.NATION = 'KOR'

1 rows
0.04 Sec
 

NESTED LOOPS
TABLE ACCESS BY ROWID CHULGOT
INDEX RANGE SCAN CH_CHULDATE
TABLE ACCESS BY ROWID CUSTOMER
INDEX UNIQUE SCAN PK_CUSTNO

   

 

  • JOIN시 INDEX의 영향

    - 조인 연결 고리에서 인덱스 없는 테이블을 드라이빙 한다.

    - 만약 연결 고리에 둘다 이상이 생기면 MERGE JOIN 으로 풀린다.

신고

'대용량 데이터베이스 솔루션' 카테고리의 다른 글

3-3강  (0) 2014.11.21
3-2강  (0) 2014.11.21
3-1강  (0) 2014.11.13
2-4강  (0) 2014.11.12
2-3강  (0) 2014.11.04
2-2강  (0) 2014.10.20
Posted by TM ~ing

 

  • 추가된 인덱스 컬럼의 역할

Select A, B from tab1 where A='2' AND C=51;

- INDEX (A+B) : C의 값을 확인하기 위해 A가 2인 값은 모두 Table Access를 한다.

- INDEX (A+B+C) : A값이 2이고, C 값이 51인 로우에 대해서만 Table Access를 하여 불필요한 Random IO 제거 가능.

   

  • 결합인덱스 컬럼순서결정

    1. 항상 사용하는가?

    - 여러 SQL문의 조건절에서 사용되는지 확인.

    2. 항상 '='로 사용되는가?

    - BETWEEN이나, LIKE를 =로 바꿀수 있는 것 까지 확인 해야함.

    3. 분포도가 좋은 순서 대로

    4. Sort 순서 대로

    5. 어떤 컬럼을 추가?

    - 2개의 인덱스를 두고, 애매한 상황에서 한쪽에 추가 컬럼을 두어 명확하게 한쪽 인덱스를 사용하게 할 수 있음.

       

  • 인덱스 선정 절차

    - 해당 테이블의 액세스 유형 조사

    - 대상 컬럼의 선정 및 분포도 분석

    - 반복 수행되는 액세스

    - 클러스터링 검토

    - 인덱스 컬럼의 조합 및 순서의 결정

    - 시험생성 및 테스트

    - 수정이 필요한 애플리케이션 조사 및 수정

    - 일괄 적용

       

  • INDEX의 활용(선정기준)

    - 분포도가 좋은 컬럼은 단독적으로 생성한다.

    - 자주 조합되어 사용되는 경우는 결합인덱스 생성

    - 각종 액세스 경우의 수를 만족할 수 있는 인덱스간의 역할 분담

    - 가능한 수정이 빈번하지 않는 컬럼

    - 기본키 및 외부키

    - 결합 인덱스의 컬럼순서 선정에 주의

    - 반복 수행 되는 조건은 가장 빠른 수행속도를 내게 할 것

    - 실제 조사된 액세스 종류를 토대로 선정 및 검증

       

  • Index의 활용

    - 새로 추가된 인덱스는 기존 엑세스 경로에 영향을 미칠 수 있음

    - 지나치게 많은 인덱스는 오버헤드를 발생

    - 넓은 범위를 인덱스로 처리시 많은 오버헤드 발생(random io)

    - 옵티마이저를 위한 통계데이타를 주기적으로 갱신

    - 인덱스 개수는 테이블의 사용형태에 따라 적절히 생성

    - 분포도가 양호한 컬럼도 처리범위에 따라 분포도가 나빠질 수 있음

    - 인덱스 사용원칙을 준수해야 인덱스가 사용되어짐

    - 조인시에 인덱스가 사용여부에 주의

       

  • 추가된 인덱스가 미치는 영향

SELECT * FROM TAB1 WHERE A = '10' AND B = '941005' AND C = '123'

- INDEX1(A,B), INDEX2(C)가 존재할 때는 INDEX1을 사용한다.

- INDEX1(A,B,D), INDEX2(C)가 존재할 때는 INDEX2를 사용한다.

-> 이유는 Fully equal이 우선 순위가 높기 때문이다.

 

신고

'대용량 데이터베이스 솔루션' 카테고리의 다른 글

3-3강  (0) 2014.11.21
3-2강  (0) 2014.11.21
3-1강  (0) 2014.11.13
2-4강  (0) 2014.11.12
2-3강  (0) 2014.11.04
2-2강  (0) 2014.10.20
Posted by TM ~ing

 

 

  • OPTIMIZER의 취사 선택

    1. Ranking의 차이

    - '='이 Like 보다 우선 순위가 높아서 '='에 해당하는 EMPNO 인덱스 사용

SELECT * FROM EMP WHERE ENAME LIKE 'AB%' AND EMPNO = '7890'

2. INDEX Merge 회피

- ENAME, JOB 둘 중에 한가지 인덱스만 사용(인덱스 생성 진 시기, 뒤에서 부터 사용)

SELECT * FROM EMP WHERE ENAME LIKE 'AB%' AND JOB LIKE 'SA%'

3. Low COST의 선택

- CBO에 의해 Full Table SCAN

SELECT * FROM EMP WHERE EMPNO >'10'

4. HINT에 의한 선택
- 힌트로 인하여 JON Index만 사용함.

SELECT /*+ INDEX(EMP JOB_IDX) */ *
FROM EMP
WHERE ENAME LIKE 'AB%'
AND JOB LIKE 'SA%'

※ CBO는 통계 정보를 바탕으로 하기 때문에, 어디로 튈지 모른다.

   

  • INDEX의 활용

    - 6 블록 이상의 테이블에 적용(이하는 연결고리만)

    - 컬럼의 분포가 10~15%(약 3천건)에 적용

    - 분포도가 범위 이내이고 절대량이 많은 경우 단일 테이블 클러스터링을 검토

    - 분포도가 범위 이상이고 부분범위 처리를 목적으로 하는 경우에는 적용

       

  • INDEX Merge 방식

SELECT COL1, COL2, … FROM TAB1 WHERE COL1 = 'ABC' AND COL2 = '123' ;

- COL1과 COL2 인덱스의 Rowid를 비교해 가면서 읽고, 같은 rowid인것에 table access를 하여 값을 가져온다.

   

  • INDEX MERGE와 결합인덱스 비교

    - INDEX MERGE는 해당하는 각 인덱스의 값에 대하여 RANGE SCAN을 전부 해야하지만, 결합 인덱스로 구성하면 해당하는 값만 바로 가져올 수 있다.

       

  • INDEX MERGE 불리한 경우

    - MERGE 되는 INDEX들의 분포도 차이가 많이 날 경우는 똑똑한 조건으로 INDEX를 사용하고, 멍청한 조건을 체크조건으로 사용하는 것이 더욱 효과적이다.

       

  • INDEX MERGE 유리한 경우

    - MERGE 되는 INDEX들의 분포도 차이가 적고 MERGE 된 ROW의 수가 아주 적은 경우

    - MERGE 되는 비율이 높고 부분범위 처리를 하는 경우

       

  • INDEX MERGE와 결합 INDEX 비교 예제

    - INDEX 별도 생성(culdate, status)

쿼리

결과

SELECT /*+ AND_EQUAL(A ch_culdate ch_status) */ custno, culdate
FROM CHULGOT A
WHERE CULDATE ='941003'
AND STATUS = '90'

13 rows, 0.21sec

- 결합 INDEX 생성(chuldate+status)

쿼리

결과

SELECT CUSTNO, CHULDATE FROM CULGOT A
WHERE CHULDATE = '980120'
AND STATUS = '90'

13 rows, 0.01 sec

   

  • EQUAL이 결합 인덱스에 미치는 영향

    - 결합 인덱스의 앞에 조건은 '=' 조건으로 해야 ACCESS 효율을 향상 시킬 수 있다.

    - RANGE 조건은 '='로 바꿀 수 있는지 확인

 

 

신고

'대용량 데이터베이스 솔루션' 카테고리의 다른 글

3-2강  (0) 2014.11.21
3-1강  (0) 2014.11.13
2-4강  (0) 2014.11.12
2-3강  (0) 2014.11.04
2-2강  (0) 2014.10.20
2-1강  (0) 2014.10.17
Posted by TM ~ing

 

  • INDEX의 구조
  1. 한 개의 컬럼으로 인덱스 생성 시, 같은 값이면 RowID로 정렬한다.
  2. Rowid는 block#, loc, file#로 구성되어 있다.
  3. 결과는 INDEX 순서대로 나온다.

   

  • INDEX를 사용하지 않는 경우
  1. Index 컬럼의 변형: 좌변을 변형하지 말라.
  2. NOT Operator: 부정형 Operator가 나오면 Index를 사용하지 않음.(Bitmap 인덱스는 가능)
  3. NULL, NOT NULL: NULL은 모르는 값이기 때문에 인덱스를 사용하지 않음.
  4. Optimizer가 스스로 사용하지 않음: Optimizer의 Rule에 따라서 인덱스를 사용하지 않을 수 있다.

   

  • Index COLUMN의 변형(external)

변경 전(인덱스 사용 못함)

변경 후(인덱스 사용 가능)

SELECT * FROM EMP
WHERE SUBSTR(DNAME,1,3) = 'ABC'

SELECT * FROM EMP
WHERE DNAME LIKE 'ABC%'

SELECT * FROM EMP
WHERE SAL * 12 = 12000000

SELECT * FROM EMP
WHERE SAL = 12000000 / 12

SELECT * FROM EMP
WHERE TO_CHAR(HIREDATE,'YYYMMDD') =
'940101'

SELECT * FROM EMP
WHERE HIREDATE = TO_DATE('940101','YYYMMDD')

SELECT * FROM EMP
WHERE NVL(COMM,0) < 100

설계시, Default를 0으로 했어야 한다.

SELECT * FROM EMP
WHERE EMPNO BETWEEN 100 AND 200
AND NVL(JOB,'X') = 'CLERK'

SELECT * FROM EMP
WHERE EMPNO BETWEEN 100 AND 200
AND JOB = 'CLERK'

SELECT * FROM EMP
WHERE DEPTNO || JOB = '10SALESMAN'

SELECT * FROM EMP
WHERE DEPTNO = '10' AND JOB = 'SALSMAN'

   

  • 의도적인 SUPPRESSING

변경 전

변경 후

-- MANAGER는 테이블의 90%다
SELECT * FROM EMP
WHERE JOB = 'MANAGER'

-- 좌변을 가공해서 INDEX 못쓰게 함.
SELECT * FROM EMP
WHERE RTRIM(JOB) = 'MANAGER'

SELECT * FROM EMP
WHERE EMPNO = 8978

SELECT * FROM EMP
WHERE RTRIM(EMPNO) = 8978

   

  • SUPPRESSING 예제

변경 전

변경 후

-- STATUS 90이 테이블의 90%이다.
SELECT CUSTNO, CHULDATE FROM CHULGOT
WHERE CUSTNO = 'DN02' AND STATUS = '90'

-- STATUS 인덱스를 사용하지 않게 좌변을 가공한다.
SELECT CUSTNO, CHULDATE FROM CHULGOT
WHERE CUSTNO = 'DN02' AND RTRIM(STATUS) = '90'

-- Rule에 의해서 STATUS가 인덱스를 사용함
-- STATUS 90이 테이블의 90%이다.
SELECT CUSTNO, CHULDATE FROM CHULGOT
WHERE CUSTNO LIKE 'DN%'
AND STATUS LIKE '9%'

-- STATUS 인덱스를 사용하지 않게 좌변을 가공한다.
SELECT CUSTNO, CHULDATE FROM CHULGOT
WHERE CUSTNO LIKE 'DN%'
AND RTRIM(STATUS) LIKE '9%'

-- 10년 매출 정보가 있다.
SELECT X.CUSTONO, CHULDATE, CUSTNAME
FROM MECHUL1T X, MECHUL2T Y
WHERE X.SALENO = Y.SALENO
AND X.SALEDEPT = '710'
AND Y.SALEDATE LIKE '9411%'

-- 1달의 양으로 조인에 참여하는 양을 줄인다.
-- MECHUL1T가 드라이빙 되지 않게 한다.
SELECT X.CUSTONO, CHULDATE, CUSTNAME
FROM MECHUL1T X, MECHUL2T Y
WHERE X.SALENO = Y.SALENO
AND RTRIM(X.SALEDEPT) = '710'
AND Y.SALEDATE LIKE '9411%'

-- ORDDEPT 순서대로 정렬이 된다.
SELECT X.ORDNO, ORDDATE, ITEM
FROM ORDER1T, ORDER2T Y

WHERE X.ORDNO = Y.ORDNO
AND X.ORDDATE LIKE '9411%'
AND Y.ORDDEPT = '710'
ORDER BY ORDDATE

-- X가 드라이빙 되고, ORDDATE 인덱스를 타서,
--ORDER BY가 필요 없어진다.
SELECT X.ORDNO, ORDDATE, ITEM
FROM ORDER1T, ORDER2T Y

WHERE RTRIM(X.ORDNO) = Y.ORDNO
AND X.ORDDATE LIKE '9411%'
AND Y.ORDDEPT = '710'

   

  • INDEX COLUMN의 변형(암시적 변형)

CREATE TABLE [dbo].[SAMPLET](

[CHR] [char](10) NULL,

[NUM] [decimal](12, 3) NULL,

[VAR] [varchar](20) NULL,

[DAT] [date] NULL

)

   

적용 쿼리

실제 수행 쿼리

SELECT * FROM SAMPLET WHERE CHR = 10;

SELECT * FROM SAMPLET WHERE CONVERT(int,CHR) = 10;

SELECT * FROM SAMPLET WHERE NUM LIKE '201411%'

SELECT * FROM SAMPLET WHERE CONVERT(VARCHAR(30),NUM) LIKE '201411%'

SELECT * FROM SAMPLET WHERE DAT = '2014-11-04'

SELECT * FROM SAMPLET WHERE DAT = CONVERT(DATE,'2014-11-04')

SELECT * FROM SAMPLET WHERE NUM = CHR

SELECT * FROM SAMPLET WHERE NUM = CONVERT(DECIMAL(12,3),CHR)

   

  • INDEX COLUMN의 변형(암시적 변형) - 예제

변경 전

결과

-- STATUS는 CHAR이다.
SELECT SUM(UNCOST) FROM CULGOT WHERE STATUS = 90

-- 좌변의 Converting으로 인덱스 사용 못함

  

--CFMDEPT는 NUM이다.

SELECT CULNO, CUSTNO, UNCOST FROM WHERE CFMDEPT LIKE '71%'

-- 좌변의 Converting으로 인덱스 사용 못함

--CFMDEPT는 NUM이고, ORDDEPT는 CHAR이다.

SELECT ORDNO, CHULNO, STATUS

FROM ORDER1T X, CHULGOT Y

WHERE X.CUSTNO = Y.COSTNO

AND X.ORDDEPT = Y.CFMDEPT

AND y.CHULDATE LIKE '9711%'

-- X.ORDDEPT가 NUM으로 변형이 되서 인덱스 사용을 못하기 때문에, X(ORDER1T)가 Driving 된다.

   

  • NOT Operator

변경 전

변경 후

SELECT 'NOT FOUND!' INTO :COL1

FROM EMP

WHERE EMPNO <> '1234'

SELECT 'NOT FOUND!' INTO :COL1

FROM EMP

WHERE NOT EXISTS (SELECT 1 FROM EMP EMP='1234')

SELECT * FROM EMP

WHER ENAME LIKE '김%'

AND JOB <> 'SALES'

SELECT * FROM EMP a
WHERE a.ENAME LIKE '김%' AND NOT EXISTS (SELECT 1 FROM EMP b WHERE a.ENAME =b.ENAME AND b.JOB='SALES')

   

  • NULL, NOT NULL

변경 전

변경 후

SELECT * FROM EMP
WHERE ENAME IS NOT NULL

SELECT * FROM EMP
WHERE ENAME >''

SELECT * FROM EMP

WHERE COMM IS NOT NULL

SELECT * FROM EMP
WHERE COMM > 0

   

  • NULL 값의 적용 기준
    1. NULL도 하나의 값이다.(모르는 값)
    2. NULL은 어떤 연산결과를 해도 NULL이 된다.
    3. 미확정 값을 표현하고자 할 때 사용
    4. 특정 값이 많고, 나머지 값만으로 인덱스 액세스 하고자 할 때 사용.
    5. 결합 인덱스의 구성컬럼이 된다면 NOT NULL
    6. 입력 조건 값으로 자주 사용되면 NOT NULL
신고

'대용량 데이터베이스 솔루션' 카테고리의 다른 글

3-1강  (0) 2014.11.13
2-4강  (0) 2014.11.12
2-3강  (0) 2014.11.04
2-2강  (0) 2014.10.20
2-1강  (0) 2014.10.17
1-4강  (0) 2014.10.15
Posted by TM ~ing