매일매일 딥러닝

안녕하세요

오늘은 SQL 마지막 포스팅으로

SQL에 특화된 프로그래밍 언어인

PL/SQL에 대해 알아보도록 하겠습니다!

PL/SQL이란?

SQL을 확장한 절차적 언어로

관계형 데이터베이스에서 사용되는 Oracle의 표준 데이터 엑세스 언어입니다.

그렇다면 PL/SQL을 왜 배워야 할까요?

우선 큰 규모의 화사는 데이터를 대부분 오라클에 저장합니다.

하지만

SQL만으로 데이터를 분석하고 처리하려면 많은 노력이 수반됩니다.

PL/SQL을 사용하면 아래와 같은 장점이 있습니다.

PLSQL 기본구조

예제를 통해 활용해보도록 합시다!

[예제를 위한 테이블]

[ emp ] : 사원테이블

예제1. 사원번호를 입력하면 해당 사원의 월급에 대한 순위가 출력되게 하시오

(단, 없는 사원번호를 입력하였을 때 예외처리를 하시오)

설명)

1. set serveroutput on 을 사용하여 데이터를 출력하는 dbms_output.put_line함수를 이용할 수 있다.

2. accept는 데이터를 입력받기 위한 것으로

&치환변수를 이용하여 값을 입력받는다.

3. exception이란 예외처리하는 것으로

no_data_found란 데이터가 없을 때 발생하는 오류로

오라클에서 지정해 놓은 예외처리 방법 중 하나이다.

예제2. 직업을 입력하면 해당 직업의 토탈월급이 출력되는 함수를 생성하고 실행하시오.

설명)

1. create를 이용하여 함수를 생성한다.

2. 가로의 값은 입력받을 파라미터를 지정하는 것이다.

3. return 값이 꼭 존재해야하며,

여기서는 v_sumsal의 타입인 number로 지정한다.

사용방법)

SQL> SELECT 사원명, 직업, job_sal_sum('SALESMAN') FROM emp;

예제3. 직업, 부서별 토탈월급을 출력하시오

(PL/SQL 프로시져를 이용하세요)

우선 SQL을 이용하면 아래와 같습니다.

[결과]

사용방법)

SQL1> variable v_result1 refcursor;

SQL2> exec job_dept(:v_result1);

SQL3> print v_result3;

설명)

프로시져란 PLSQL 코드를 database에 저장하기 위한 것으로

함수와 달리 return절이 없으며 결과를 반드시 출력하지 않아도 됩니다.

1. 연결 연산자와 함수를 이용하여 SQL문을 완성합니다.

2. 만약 부서가 위의 테이블보다 훨씬 많이 존재한다고 가정한다면,

for loop문을 이용하여 만든 PLSQL문이 보다 효율적이며,

부서가 추가되었을 때에도 코드를 수정하지 않아도 된다는 장점이 있습니다.

PL/SQL을 간단히 다뤄보았습니다!

더 궁금한 점이 있다면 댓글로 남겨주세요~

안녕하세요

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

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

하지만!

이번 포스팅에서는

가장 중요한 두 가지!

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을 약간 다뤄보겠습니다!

안녕하세요!

오늘부터 쿼리의 검색 속도를 높일 수 있는 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튜닝방법으로 돌아오겠습니다!

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