매일매일 딥러닝

안녕하세요

오늘은 서브쿼리 튜닝으로 돌아왔습니다.

서브쿼리 튜닝의 종류는 정말 다양합니다.

하지만!

이번 포스팅에서는

가장 중요한 두 가지!

merging과 unnesting에 대해 다뤄보도록 하겠습니다.

NO_MERGE     vs     NO_UNNEST

 NO_MERGE와 NO_UNNEST의 공통점은

서브쿼리 ( 또는 뷰나 인라인 뷰 )

를 해체하지 말아라. 

라는 의미를 갖고 있습니다.

그럼 그 반대도 있겠죠?

먼저 서브쿼리 UNNESTING 문부터 알아보죠!

[짝꿍처럼 써줘야하는 힌트]

예제를 위한 테이블

[ emp ] : 사원테이블

[ address ] : 주소테이블

예제1. 관할 부서가 한국A인 사원들의 이름, 직업, 연봉을 출력하세요.(서브쿼리로)

[결과]

다음으로 서브쿼리 MERGING 문을 알아보죠!

예제2. 직업이 SALESMAN과 관련된 데이터만 들어있는 뷰를 생성하고 

그 뷰에서

관할부서가 한국A인 사원들의 이름, 직업, 연봉, 관할부서를 출력하세요.

사원 테이블이 1억건이고 그 중에 SALESMAN이 10건 있다고 가정하면

VIEW를 해체하지 않고 주소테이블과 조인하는 것이 더 효율적입니다.

[결과]

오늘은 여기까지!

서브쿼리문을 실행할 때, 서브쿼리문으로 실행할 지,

아니면 조인의 형태로 변경해서 실행할지,

쿼리변환기를 제어하는 기술을 알아보았습니다!

다음엔 PLSQL을 약간 다뤄보겠습니다!

오랜만에 돌아왔네요!

이전 포스팅과 이어지는

조인튜닝 2번째탄을 갖고왔습니다!

오늘은 알아두면 좋은 조인 튜닝 방법에 대해 알아보죠!

조인 튜닝시에 가장 중요한 2가지

1. 조인 순서

2. 조인 방법

nested loop 조인 vs hash loop 조인

nested loop join이란 중첩된 조인 방법으로

두개의 테이블을 조인할 때 한 건, 한 건씩 순차적으로 반복해서 조인하는 방법입니다.

단, 조인되는 건수가 작을 때 유리한 조인 방법입니다!

hash 조인이란 데이터가 적은 테이블을 메모리에 올려두고

해쉬함수를 이용해 두번째 테이블과의 조인을 시도하는 방법입니다.

메모리가 속도가 훨씬 빠르기때문에 대용량 데이터의 경우,

해쉬조인을 하면 속도가 굉장히 빨라집니다.

단, 메모리를 많이 이용하는 방법은 협업시

다른 사람의 쿼리 속도를 늦출 수 있습니다.

먼저 해쉬조인의 원리부터 파악해봅시다!

해쉬조인을 할 때 두개의 테이블은

해쉬테이블과 prob(탐색) 테이블로 나뉩니다.

예제를 위해 사용할 테이블

[ 고객 테이블 ]

[ 주소 테이블 ]

예제1. 고객이름, 주소, 관할부서, 총구매액을 알고 싶습니다.

해쉬조인을 사용하여 두개의 테이블을 조인하세요.

[결과]

/*+ leading(a c) use_hash(c) */와 같이 힌트를 사용하면 됩니다.

아마 힌트를 주지 않아도 옵티마이저가 알아서 hash조인을 하는 경우도 있지만

이러한 힌트를 명시해주는 것이 바람직합니다.

여기서 잠깐!

힌트 사용시 별칭을 이용해야합니다!

예를 들어 아래와 같이 해쉬조인 사용시 힌트를 이용할 수 있습니다.

hash join 사용 힌트

예제2. 이름, 관할부서를 출력하는 OUTER 문장을 작성하는데

주소가 null인 고객D가 출력되게 해쉬조인하세요

(단, 주소 테이블에서 고객테이블 순으로)

혹시 leading을 쓰셨나요?

조인할 때 outer join 사인을 사용하면 join 순서는 무조건 outer join 사인이 없는 쪽에서 먼저 조인이 됩니다. 

따라서

/*+ swap_join_inputs(a) use_hash(c) */ 를 사용해야

아래와 같은 실행계획을 얻을 수 있습니다.

[결과]

예제3. 고객이름, 주소, 관할부서를 출력하는데

해쉬조인 문장으로 병렬처리해서 출력하세요.

/*+ leading(a c) use_hash(c) full(a) full(c) 

parallel(a, 4) parallel(c, 4) */ 

과 같이 병렬처리를 하는 이유는 보다 빠른 쿼리 속도를 위해서 입니다.

hash join시 무조건 table access full을 해야합니다.

참고) non-equi-join의 경우 해쉬조인을 쓸수 없습니다.

방법은 예제 1번에서 use_hash대신에 use_merge를 넣는 것입니다!

쉽죠?

지금까지 간단한 방법으로

조인의 쿼리속도를 빠르게하는 방법을 알아보았습니다.

다음 포스팅은 서브쿼리 튜닝입니다!!

[사막에서 바늘찾기]

안녕하세요

오늘은 빅데이터를 오라클로 사용하려면 알아둬야하는 함수 정규식 함수에 대해 알아보겠습니다.

정규식이란

문자열 데이터의 간단한 패턴 및 복잡한 패턴을 검색하고 조작할 수 있습니다.

메타문자와 리터럴을 사용하여 정규식을 지정합니다.

여기서 사용할 메타문자는 여기서 찾아보실 수 있습니다.

SQL 및 PL/SQL에서 정규식 함수의 종류

예제를 통해 각각의 함수에 대해 알아보도록 하겠습니다.

[사원]

예제1. 이름에 W 또든 K가 들어가는 사원들의 이름을 뽑아주세요.

메타문자 참고

 | : 또는을 의미한다.

( ... ) 괄호로 묶인 표현식을 한 단위로 취급

[결과]

예제2. 부서컬럼에서 (구를 제외한) 도시명만 뽑아주세요

메타문자 참고

^ : not을 의미한다.

+ : 1이상의 문자열

 : 공백

쿼리 설명: 

dept에서 첫번째 단어( 공백을 만나기 전까지 한 단어)만 출력하는 쿼리문입니다.

[결과]

예제3. '오라클사에서 만든 오라클 데이터베이스에서도

정규식 오라클 SQL함수를 제공한다'

라는 문장에서 SQL이 몇번 언급되었는지 세어주세요.

결과는 3이 출력되겠죠?

많은 문장이 들어있는 테이블에서 위의 방법으로

가장 많은, 적은 단어를 찾는 등 다양한 응용이 가능합니다.

빅데이터 중 유용한 데이터를 찾는데 도움을 주는 정규식함수를 잘 활용해봅시다~

regexp_replace함수는 전 포스팅에서 다뤘습니다.

오늘은 여기까지!

내일부터는 SQL튜닝방법으로 돌아오겠습니다!

이 글이 도움되셨다면 아래 공감 꾹~ 

안녕하세요~

오늘은 다중 INSERT문에 대해 알아보겠습니다.

사용법을 알아보기 전 의미를 알아볼까요?

다중 INSERT문이란?

데이터를 입력할 때 여러 개의 테이블에

동시에 데이터를 입력할 수 있게 해주는 INSERT문입니다.

다중 테이블 INSERT 구문:

우선 공통적으로 INSERT ... SELECT 문을 사용하여 행을

단일 DML 문의 일부로 다중 테이블에 삽입합니다.

다중 INSERT 문의 종류는 4가지로 나눌 수 있습니다.

예제를 통해 알아봅시다!

우선 이미 고객 테이블이 존재한다고 가정합시다.

[ 고객 테이블 ]

예제1.

주소가 서울인 사람과 파주인 사람, 제주인 사람을

각각 고객_서울, 고객_파주, 고객_제주 테이블에 한번에 입력하세요.

(단, 고객_서울, 고객_파주, 고객_제주 테이블은 이미 존재한다고 가정)

앞서 사용한 SQL문이 바로 조건부 ALL INSERT 문입니다.

예제2.

우리의 타겟 고객은 20-30대이다.

나이가 30 이상 39 이하인 고객은 고객_30 테이블에,

나이가 20 이상 29 이하인 고객은 고객_20 테이블에,

그외의 고객은 고객_예외 테이블에 한번에 입력하세요.

(여기서도 이미 각각의 테이블 구조는 존재한다고 가정)

첫 번째 WHEN 절이 TRUE로 평가되면 ORACLE 서버가 해당 INTO절을 실행합니다.

후속 WHEN 절에서는 위의 행을 건너뛰고 실행합니다.

예제3. 아래와 같은 테이블이 있다고 가정하자

[고객_나이]

(Create 시 sql keyword 주의하기: 예, others)

pivoting insert문을 이용하여

나이대에 대한 정보(주소, 총구매액)를 테이블의 data로 만드세요

(고객2 테이블에 INSERT하세요)

무조건 INSERT ALL문으로 PIVOTING 작업을 하였습니다.

PIVOTING INSERT 문은 컬럼이 데이터가 되어 INSERT가 되는 구문입니다.

[결과]

PIVOT를 모르면 여기에서

추가) PIVOTING은 데이터 분석을 위해 수집된 데이터가 엑셀형태일 경우

DB 에 저장해서 분석하고자 할 때 유용하게 사용할 수 있습니다

오늘은 여기까지!

내일 또 돌아올겠습니다. 

이 글이 도움되셨다면 아래 공감 꾹~