매일매일 딥러닝

안녕하세요!

오늘부터 쿼리의 검색 속도를 높일 수 있는 SQL 튜닝을 포스팅하겠습니다.

대용량 데이터 환경(빅데이터)에서 필수가 되어가고 있습니다.

이제 인덱스 튜닝에 대해 알아보도록 하겠습니다.

[인덱스 튜닝의 종류]

index range scan과 index unique scan은

이전에 index 포스팅 에서 다뤘습니다!

"예제를 위한 테이블 소개"

[사원] 테이블

index skip scan

인덱스 전부 스캔하지 않고 SKIP해서 스캔하는 엑세스 방법

예제1. 직업과 월급으로 결합컬럼 인덱스를 생성하고

아래의 SQL이 index scan할 수 있도록 힌트를 주세요

결합 컬럼 인덱스의 첫번째 컬럼이 where 절에서 검색조건으로 존재해야

그 인덱스를 엑세스할 수 있습니다.

하지만 위와 같은 상황일 때,

emp_job_sal 인덱스를 엑세스하게 하려면 인덱스 스킵 조건을 쓸 수 있습니다.

단, 인덱스 스킵 스캔의 효과를 보기위한 조건이 있습니다.

결합컬럼 인덱스의 첫번째 컬럼의 종류가 많으면 그 효과를 볼 수 없습니다.

index full scan

인덱스 전체를 처음부터 끝까지 스캔하는 방법

예제2. 사원 테이블의 사원번호에 primary key를 생성하고

사원의 인원수가 몇 명인지 카운트 하세요

위의 SQL을 실행하면 INDEX FULL SCAN을 하게 됩니다.

TABLE FULL SCAN보다 훨씬 더 성능이 좋습니다.

만약 위의 결과에서 index full scan이 되지 않는다면 어떻게 해야할까요?

index fast full scan

index full scan보다 더 성능이 좋은 스캔 방법

병렬처리가 가능합니다.

예제3. 부서와 부서별 인원수를 출력하는데

빠르게 출력될 수 있도록 적절한 인덱스를 생성하고 힌트를 주고

병렬처리하여 실행하세요.

<SQL3_2의 힌트 설명>

병렬_인덱스(테이블, 인덱스이름, 사용할 CPU개수)

인덱스가 무엇인지 궁금하다면 여기로!

오늘은 여기까지!

중요한 인덱스 튜닝은 index range scan과 index skip scan이랍니다~

알아두고 넘어갑시다!

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

[사막에서 바늘찾기]

안녕하세요

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

정규식이란

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

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

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

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 에 저장해서 분석하고자 할 때 유용하게 사용할 수 있습니다

오늘은 여기까지!

내일 또 돌아올겠습니다. 

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

오늘은 치환변수에 대해 알아보겠습니다!

스크립트 작성시

SQL문에서 & 치환을 사용하면 값을 입력하도록 요구할 수 있습니다.

동일한 컬럼을 데이터만 바꿔서 검색하거나 입력해야할 때,

앤퍼센드 치환변수를 사용하면 SQL문의 재사용성 및 범용성을 높일 수 있습니다.

[고객 테이블]

예제1. &치환변수를 이용하여 이름을 입력받아

고객 정보를 조회하세요

(&고객이름을 싱글 쿼테이션마크(')로 감싸준 이유는

입력받을 값이 CHAR이기 때문입니다)

결과:

SLQPLUS에서 위의 예제 코드를 입력하면

'ENTER VALUE FOR 고객이름:' 옆에 커서 부분이 나옵니다.

(JAVA의 SCANNER와 같은 방법이라고 생각하면 됩니다.)

A를 입력하면

WHERE절에 A를 넣은 쿼리와 같은 결과를 얻을 수 있습니다.

예제2. 회원가입을 위해 고객이름, 나이, 주소, 전화번호, 추천인을 입력받으세요.

(단, 고객이름은 무조건 입력받고 싶습니다.)

with check option을 사용하면

WHERE절의 조건에 위배되지 않는 값만 입력하게 할 수 있습니다.

(view옵션에서 잠시 언급한 적이 있습니다.)

 가능한 응용프로그램이 아닌 SQL문에서

위와같이 작동되도록 만드는 것이 성능에 더 좋다고 합니다.

(IF문으로 안해도 됨)

오늘은 여기까지

내일은 다중 INSERT문으로 다시 돌아올께요~

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