※데이터베이스 이론공부 및 튜닝

※데이터베이스 이론공부

1.명시적,암시적 조인

1)명시적조인(추천)

SELECT * FROM

table a INNER JOIN table b

ON a.id = b.id;

2)암시적조인

SELECT a.*, b.*

FROM table a, table b

WHERE a.id = b.id;

2.조인종류

1)INNER JOIN(교집합)

2)LEFT OUTER JOIN(왼쪽기준 집합) & RIGHT OUTER JOIN(오른쪽기준 집합)

3)FULL OUTER JOIN(합집합)

3.튜닝

https://sfeg.tistory.com/m/347 - 인덱스 활용

http://www.gurubee.net/lecture/2285 - INSERT,UPDATE,DELETE 튜닝

http://12bme.tistory.com/204,http://theone79.tistory.com/376 - SELECT 튜닝

http://www.dator.co.kr/enfant/textyle/245210 - LOGGING 사진 출처

https://sqlandplsql.com/2013/05/22/high-water-mark-oracle/ - HWM 사진 출처

*인덱스가 불가능한 경우

where substr(날짜,1,8) = '20191010'

where 날짜 <> '20191010'

where 날짜 is not null

*인덱스컬럼 가공

where 날짜 LIKE '20191010%'

where 매출 * 12 = 120000 => where 매출 = 120000/12

!!조건부를 걸때 왼쪽컬럼은 최대한 변형없게 한다.

1)INSERT

테이블에 데이터를 INSERT 할때 성능저하시키는 요인은 4가지가 있다.

-인덱스 개수 => 첫번째

-로그 기록(REDO) => 두번째

-HWM BUMP UP => 세번째

-롤백을 위한 로그 기록(UNDO) => 세번째

:첫번째로 인덱수의 개수를 줄이는 방법이 있다. 인덱스란 내가 원하는 데이터를 기준으로 색인화하여 검색속도를 높이기 위해 사용하는 기술이다. DML의 SELECT속도를 향상시키는데 도움을주지만 INSERT,UPDATE,DELETE 할때는 변경된 데이터에 대해서 인덱스를 재작성 해야하기 때문에 속도가 느려진다.

*REDO(다시하다) - 사용자가 했던작업을 다시 시도함.

*UNDO(되돌리다) - 이전에 상태값으로 되돌림.

*HWM(HIGH WATER MARK) - 사용된 적이 있는 Block 과 사용된 적이 없는 Block의 경계점을 의미하는 영역. 

:두번째로 INSERT를 수행하기 전에 어떤 수행을 하는지 로그를 남겨야 된다. 이것을 선 로그(LOG AHEAD)기법이라고 한다. 이런 로그를 남기지 않기위해선 NOLOGGING 방식으로 데이터를 저장하면 된다.(REDO 로그 생성안함)

*테이블의 기본설정은 LOGGING방식이기 때문에 NOLOGGING방식으로 테이블형식을 변경해줘야한다.

 ALTER TABLE EMP NOLOGGING;

:세번째로 HWM은 데이터가 INSERT 될때마다 다음 그림과같이 왼쪽에 데이터가 입력되고 HWM은 뒤로 밀려나게 되는데 INSERT양이 많아지면 부하가 걸릴수 있기 때문에 HWM을 고정시키고 데이터를 HWM 뒤에 존재하는 블 록에 저장시킨다면 HWM BUMP UP과 롤백을 위한 로그 기록(UNDO) 에 의해 발생하는 성능 저하는 해결될 수 있을 것이다.

이와 같이 INSERT를 수행하는 방법이 바로 직접 로딩(DIRECT LOADING) 방식이다.

사용방법은 APPEND 힌트를 사용한다. 예시 : INSERT /*+ APPEND */ …… SELECT …… 

사용방법: insert /*+ append */ into temp nologging select * from temp; (대용량 INSERT할때)

2)DELETE&UPDATE

:DELETE 또는 UPDATE는 INSERT에 비해 더 많은 자원을 사용하게 되며 직접 로딩 또는 NOLOGGING 상태와 같은 방법이 존재하지 않게 된다.

대용량의 데이터를 DELETE 또는 UPDATE를 할때는 TEMP테이블을 만들어 NOLOGGING/직접로딩 방식을 이용해 INSERT를 하면 효과적으로 데이터를 옮길수 있다.

3)SELECT

첫번째 : 적절한 인덱스를 사용한다.(자주사용하는 조건부나 Select 항목을 인덱스로 설정)

*인덱스 타지않는경우 참조 (참조:http://isstory83.tistory.com/131)

(1)컬럼절 변형 예:TO_CHAR(COLUMN_NAME)

(2)조건절 NULL 또는 NOT NULL

(3)부정형 조건일 경우

(4)NOT EXISTS 사용

(5)LIKE 연산자 사용

(6)OR 조건 사용

두번째 : 조인방법과 조인순서를 최적화한다. 조인순서 A->B->C가 비효율 적일경우에 A->C->B 같은경우가 더 효율적일 수가 있다. 확인해봐야함.

*Nested Loop, Sort Merge Join, Hash Join (참조:http://needjarvis.tistory.com/162)

(1)Nested Loop - 데이터 양이 적을때 효율적 // 테이블 중 Row수가 적은 쪽을 Driven 테이블로 설정

힌트 : /*+ ordered use_nl(A,B)*/

*선행테이블(Driving)의 데이터량이 적을때 효율적이다

(2)Sort Merge Join - 두 결과집합의 크기가 차이가 나지않을경우 효율적

힌트 : /*+ ordered full(A) use_merge(B)*/ 

*조인 연결고리의 비교 연산자가 범위 연산(‘>’,’< ‘)인 경우 Nested Loop 조인보다 유리

*두 결과집합의 크기가 차이가 많이 나는 경우에는 비효율적

(3)Hash Join - 대용량 데이터 처리에서는 상당히 큰 hash area를 필요로 함으로, 메모리의 지나친 사용으로 오버헤드 발생 가능성

힌트 : /*+ ordered use_hash(A,B) */ 

*연결조건 연산자가 ‘=’ 인 동치조인인 경우에만 가능

세번째 : Table Access(Random Access)를 최소화 한다. - IOT나 클러스터 테이블 사용

*클러스터링 팩터(군집도) - 조회쿼리

SELECT INDEX_NAME, TABLE_OWNER, TABLE_NAME, CLUSTERING_FACTOR

      FROM USER_INDEXES

네번째 : Sort나 Hash 작업을 최소화한다.

(1)ORDER BY 최소화

(2)GROUP BY 최소화

(3)UNION -> UNION ALL

(4)MINUS -> NOT EXISTS

(5)INTERSECTION(교집합) -> 조인 검토

(6)DISTINCT 모델러나 설계자한테 문의

다섯번째 : 한 블록은 한번만 Scan하고 넘어간다.

(1)UNION ALL 대신 WHERE 절 구분코드 IN(1,2,3)

(2)소계,총계 구할경우 UNION ALL 대신에 Rollup/Cube Grouping Sets를 사용한다.

*Rollup/Cube

1.GROUP BY - 각 그룹별로 합계만 보여준다.

쿼리:

SELECT CUST_CD,

         FARE_CD,

         SUM (FARE_LOC_AMT),

         COUNT (*)

 FROM ACT_INV_DTL

WHERE CUST_CD IN ('024959', 'T05495')

GROUP BY CUST_CD, FARE_CD

2.ROLLUP - 각 CUST_CD별로 중계를 보여주고 마지막에 총 합계를 보여준다. (일반집계)

쿼리:

SELECT CUST_CD,

         FARE_CD,

         SUM (FARE_LOC_AMT),

         COUNT (*)

 FROM ACT_INV_DTL

WHERE CUST_CD IN ('024959', 'T05495')

GROUP BY ROLLUP(CUST_CD, FARE_CD)

: 일반적인 누적에 대한 총계를 구할 때 아주 편리하게 사용 할 수 있다.

3.CUBE - 각 CUST_CD별 // FARE_CD별로 중계와 합계를 보여준다. (다차원집계)

쿼리:

SELECT CUST_CD,

         FARE_CD,

         SUM (FARE_LOC_AMT),

         COUNT (*)

 FROM ACT_INV_DTL

WHERE CUST_CD IN ('024959', 'T05495')

GROUP BY CUBE(CUST_CD, FARE_CD)

:CUBE는 Cross-Tab에 대한 Summary를 추출하는데 사용 된다

*GROUPING(총계를 나타내는 결과물이면 1, 아니면 0을  반환한다.) - GROUP BY ROLLUP&CUBE 쓸때 사용.

쿼리:

SELECT (CASE WHEN GROUPING(CUST_CD) = 1 THEN '총합계' ELSE CUST_CD END) AS CUST_CD,

            (CASE WHEN GROUPING(FARE_CD) = 1 THEN '운임합계' ELSE FARE_CD END) AS FARE_CD,

            SUM(FARE_LOC_AMT),

            COUNT(*) 

 FROM ACT_INV_DTL 

WHERE CUST_CD IN ('024959','T05495') 

GROUP BY ROLLUP(CUST_CD,FARE_CD)

여섯번째 : 온라인 조회화면이면 페이징 처리를 한다.

일곱번째 : SQL검증한다.(제일중요)

4.옵티마이저(RBO,CBO)

1)규칙기반 옵티마이저(RBO)

2)비용기반 옵티마이저(CBO) - oracle 9i부터 CBO로 적용

Cost,Bytes,Cardinality

1.Cost : 옵티마이저(최적화해주는 프로그램)가 CBO(COST BASE Optimizer- 비용기반 옵티마이저) 상황에서 COST가 가장 적은방향으로 Plan을 세움

*RBO(Rule Based Optimizer) 방식은 규칙 기반 옵티마이저다. 

2.Bytes : Plan상의 Action이 수행될때 Memory Access의 양

3.Cardinality : Plan상의 Action이 수행될때 Select 되는 Row 수