'대용량 데이터 베이스 솔루션'에 해당되는 글 1건

  1. 2014.11.04 2-3강

 

  • 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