반응형


안녕하세요


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


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

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



PL/SQL이란?


SQL을 확장한 절차적 언어로

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


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


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


하지만

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



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



 1. PL/SQL을 이용하면 SQL로 반복해서 해야하는 작업을

보다 단순하게 처리할 수 있습니다.


2. R로 데이터 분석을 하기위해 엑셀 등으로

DB의 데이터를 내려받아 분석하는 것이 아니라,

바로 DB에서 PL/SQL을 이용해서 분석할 수 있습니다.


3. SQL 튜닝 방법 중 하나로 PL/SQL을 사용할 수 있습니다.





PLSQL 기본구조


 

     declare

                선언문 (변수, 예외, 커서 등을 선언한다.)

     

     begin

                실행절 (SQL 문, IF 문, LOOP 문 등을 실행한다.)


     end;

/





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


[예제를 위한 테이블]


[ emp ] : 사원테이블


사원명

사원번호

직업

연봉 

부서

 A

1001

 MANAGER

 3000

 파주

 B

1002

 SALESMAN

 1600

 제주

 C

1005

 PRESIDENT

 5000

 서울

 D

1024

 SALESMAN

 1400

 파주

 E

2024

 ANALYST

 3000

 서울 

 F

2215

 SALESMAN

 1350

 서울

 G

2015

 MANAGER

 2900

 일산

 H

1240

 ANALYST

 3000

 파주

 I

3415

 SALESMAN

 1700

 서울




예제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을 이용하면 아래와 같습니다.



SQL> SELECT  직업, sum( decode(부서, '서울', 연봉, 0) ) "서울",

sum( decode(부서, '파주', 연봉, 0) ) "파주",

sum( decode(부서, '일산', 연봉, 0) ) "일산"

FROM emp

GROUP BY 직업;




[결과]


 직업

 서울

 파주

 일산

 MANAGER

 

 3000

 2900

 SALESMAN

 3050

 1400

 

 PRESIDENT

 5000

 

 

 ANALYST

 3000

 3000

 




사용방법)


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: 뷰나 인라인 뷰에 사용


(인라인 뷰 = FROM절의 서브쿼리)



NO_UNNEST: 인라인 뷰 외의 WHERE절 등의 서브쿼리에 사용




 NO_MERGE와 NO_UNNEST의 공통점은


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

를 해체하지 말아라. 


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



그럼 그 반대도 있겠죠?




 MERGE: 뷰나 인라인 뷰를 해체해라



UNNEST: 서브쿼리를 해체해라


(해쉬 테이블로 올릴 테이블을 직접 정하고자 할 때 사용)





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



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



 NO_UNNEST: 서브쿼리를 해체하지 마라


1. PUSH_SUBQ: 서브쿼리부터 수행해라

2. NO_PUSH_SUBQ: 메인쿼리부터 수행해라


UNNEST: 서브쿼리를 해체해라


1. SWAP_JOIN_INPUT: 해쉬테이블로 올릴 테이블 지정

2.NO_SWAP_JOIN_INPUT: 해쉬테이블로 올리지 않을 테이블 지정




예제를 위한 테이블


[ emp ] : 사원테이블


사원명

나이

직업

연봉 

주소

 A

 35

 NULL

 NULL

 파주

 B

 34

 SALESMAN

 1600

 제주

 C

 38

 PRESIDENT

 5000

 서울

 D

 36

 SALESMAN

 1400

 파주

 E

 39

 ANALYST

 3000

 서울 

 F

 32

 SALESMAN

 1350

 서울

 G

 37

 MANAGER

 2850

 일산

 H

 35

 ANALYST

 3000

 파주

 I

 36

 SALESMAN

 1700

 서울



[ address ] : 주소테이블


주소 

 관할부서

 서울

 한국 A

 일산

 한국 B

 파주

 한국 C

 제주

 한국 D



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




[결과]


사원명

직업

연봉 

주소

 C

 PRESIDENT

 5000

 서울

 E

 ANALYST

 3000

 서울 

 F

 SALESMAN

 1350

 서울

 I

 SALESMAN

 1700

 서울




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




 MERGE: 뷰나 인라인 뷰를 해체해라


NO_MERGE: 뷰나 인라인 뷰를 해체하지 마라

 



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

그 뷰에서

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




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


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


[결과]


사원명

직업

연봉 

관할부서

 C

 PRESIDENT

 5000

 한국 A

 E

 ANALYST

 3000

 한국 A

 F

 SALESMAN

 1350

 한국 A

 I

 SALESMAN

 1700

 한국 A




오늘은 여기까지!


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

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


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



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

반응형
반응형



안녕하세요!


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


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


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



[인덱스 튜닝의 종류]



1. index range scan

2. index unique scan

3. index skip scan

4. index full scan

5. index fast full scan

6. index merge scan

7. index bitmap merge scan

8. index join

 


index range scan과 index unique scan은

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



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


[사원] 테이블


EMPNO

ENAME

JOB

SAL

DEPT

 ALLEN

 SALESMAN

 1250

 서울시

 2 

 BAKE

 SALESMAN

 1600

 서울시

 3 

 CLARK

 MANAGER

 3000

 세종시

 4 

 WARD

 CLERK

 1100

 인천광역시

 5 

 JAMES

 ANALYST

 3000

 부산광역시

 6 

 FORD

 ANALYST

 3000

 세종시

 7 

 SMITH

 CLERK

 1300

 세종시

 8 

 MILLER

 MANAGER

 2450

 서울시



index skip scan

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


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

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



      SQL1> SELECT ename, job, sal

                         FROM 사원

                         WHERE sal = 3000;

 




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

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


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

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



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

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



index full scan


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


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

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




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

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



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



쿼리에 index full scan hint를 추가하면 됩니다.


index full scan의 힌트는

 /*+ index_fs(사원 emp_empno_pk) */ 

입니다.


(잘 모르겠다면 예제1번의 정답을 다시 확인하고 오세요!)

 



index fast full scan


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


병렬처리가 가능합니다.


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

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

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




<SQL3_2의 힌트 설명>


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



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




오늘은 여기까지!


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

알아두고 넘어갑시다!



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



반응형
반응형

사막에서 바늘찾기[사막에서 바늘찾기]



안녕하세요


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


정규식이란


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



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



     메타문자: 검색 알고리즘을 지정하는 연산자


     리터럴: 검색 중인 문자



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



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


 함수 이름

  설명

 REGEXP_LIKE

 일반 like보다 더 복잡한 패턴의 데이터를

 검색할 수 있는 함수

 REGEXP_REPLACE

 일반 replace보다 더 복잡한 패턴의 데이터를

 다른 데이터로 대체하는 함수

 REGEXP_INSTR

 일반 instr보다 더 복잡한 패턴의

 철자 또는 단어의 자릿수를 찾는 함수

 REGEXP_SUBSTR

 지정된 문자열 내에서 정규식 패턴을 검색하고

 일치하는 부분 문자열을 출력하는 함수

 REGEXP_COUNT

 문장 내에서 특정 단어가 몇 번 나오는지

 출력하는 함수




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


[사원]


EMPNO

ENAME

JOB

SAL

DEPT

 ALLEN

 SALESMAN

 1250

 서울시 강남구

 2 

 BAKE

 SALESMAN

 1600

 서울시 강남구

 3 

 CLARK

 MANAGER

 3000

 세종시

 4 

 WARD

 CLERK

 1100

 인천광역시

 5 

 JAMES

 ANALYST

 3000

 부산광역시

 6 

 FORD

 ANALYST

 3000

 세종시

 7 

 SMITH

 CLERK

 1300

 세종시

 8 

 MILLER

 MANAGER

 2450

 서울시 강남구




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



메타문자 참고

 | : 또는을 의미한다.

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



[결과]


EMPNO

ENAME

JOB

SAL

DEPT

 2 

 BAKE

 SALESMAN

 1600

 서울시 강남구

 3 

 CLARK

 MANAGER

 3000

 경기도 과천시

 4 

 WARD

 CLERK

 1100

 인천광역시




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



메타문자 참고

^ : not을 의미한다.

+ : 1이상의 문자열

 : 공백


쿼리 설명: 

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



[결과]


EMPNO

ENAME

JOB

SAL

DEPT

 ALLEN

 SALESMAN

 1250

 서울시

 2 

 BAKE

 SALESMAN

 1600

 서울시

 3 

 CLARK

 MANAGER

 3000

 경기도

 4 

 WARD

 CLERK

 1100

 인천광역시

 5 

 JAMES

 ANALYST

 3000

 부산광역시

 6 

 FORD

 ANALYST

 3000

 세종시

 7 

 SMITH

 CLERK

 1300

 세종시

 8 

 MILLER

 MANAGER

 2450

 서울시




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

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

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




결과는 3이 출력되겠죠?


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

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


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



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



오늘은 여기까지!


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




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



반응형

+ Recent posts