SQL

• SQL(Structured Query Language)

Structured : 체계화 / 구조화 >> 위에서 아래로(순서대로)

Query : 요청 >> DBMS에 요청

Language : 4GL >> 일종의 프로그램

※ Point : Cost ↓ (Insert, Update, Delete) >> 과정

             Benefit ↑ (Select) >> 목적

-3GL은 성능에 큰 차이 X,  4GL은 중요

• Select 기법이 중요(효율 극대화)

1)Simple Query > 한 Table에서 Select(단순 조회)

2)Join   - Product > 조건없이 곱셈( T1(10) x T2(20) = T3(200) ) - TestData 생성용 / Day x 정보

           - Join

           - Self Join

           - Recursive Join

※ 같은 결과물이 나와도 150000(30000x50)번 조회할 일을 30050(30000+50)번에 끝낼 수 있음

※ Net Manager >> IP/TCP 확인

• SQL에서 가장 중요한 것

1.정합성 >> 원하는 Data를 정확하게 가져오는 것

2.속도(Performance) >> 다른 DB에 비해서 ORACLE은 Query에 따른 속도차이가 큼 
                                (효율적인 Query 작성능력 요구)

                           >> 현업에서 적게는 5천만건 ~ 10억건의 DB를 처리해야 하므로 성능에 대한 고려는 중요

3. 가독성 >> 예약어 기준 줄바꿈 / 대문자 권장

• Select문의 핵심은 Join

1.ORACLE JOIN >> SELECT      e.employee_id, e.emp_name, d.department_id, d.department_name

   FROM       employees e, departments d

                          WHERE   e.department_id = d.department_id

                           AND         e.hire_date >= TO_DATE('2003-01-01','YYYY-MM-DD');

  

 

3개 이상 Join >> SELECT  e.employee_id, e.emp_name, d.department_id, d.department_name, t.task_name                         

                        FROM        employees e, departments d, task t

                        WHERE       e.department_id = d.department_id

                        AND          d.task_number = t.task_number

                        AND          e.hire_date >= TO_DATE('2003-01-01','YYYY-MM-DD');

>> 가독성 차원이나 작성차원에서 좀 더 좋을 수 있으나, DB별로 호환이 되지 않을 수 있으므로

       유지보수 차원에서 ANSI(American National Standards Institute) 방식을 사용하는 것이 나을 수 있다.

>> Table Alias를 해주면 코드가 줄어서 메모리 성능 향상이 있을 수 있고, 작성이 편리(추천)

2.ANSI JOIN >>   SELECT            e.employee_id, e.emp_name, d.department_id, d.department_name

                        FROM             employees e

                        INNER JOIN    departments d

                        ON                e.department_id = d.department_id

                        WHERE            e.hire_date >= TO_DATE('2003-01-01','YYYY-MM-DD');

 

3개 이상 Join >> SELECT          e.employee_id, e.emp_name, d.department_id, d.department_name, t.task_name

FROM          employees e

INNER JOIN   departments d

                       ON               e.department_id = d.department_id

                       INNER JOIN    task t

                       ON               d.task_number = t.task_number

                       WHERE          e.hire_date >= TO_DATE('2003-01-01','YYYY-MM-DD');

>> DB별로 호환에 문제가 없고, Join문과 Where문을 분리할 수 있으므로 가독성 차원에서 더 나을 수 있음

>> INNER JOIN / ON 을 JOIN할 때마다 적어줘야 해서 번거롭기는 하지만 깔끔하기는 함

※ Explain Plan

>> Query 실행과정을 Cost를 보여주면서, 작동을 보여줌 >> 튜닝에 필요한 정보를 제공!

•  Join의 4가지 방법(Very Important)

>> 질의연산 중 가장 Resource를 많이 사용하는 연산 중 하나이므로 적절한 방법이 매우 중요!

>> 상황 / 경우에 따라 적절한 방법을 사용해야함

자세한 세부특징은 참조 

출처 : http://www.jidum.com/jidums/view.do?jidumId=167

1.Nested Loop Join (이중 for문 방식으로 Table을 Join함)

>> 선행테이블(드라이빙 테이블)의 처리범위를 하나씩 액세스하면서, 

      추출된 값으로 연결할 테이블(후행 테이블)을 조인하는 방식

>> 실행속도 = 선행 테이블 사이즈 * 후행 테이블 접근횟수

>> 사용법 : /*+ USE_NL(A B) */

ex)   select /*+ use_nl(b,a) */ a.dname, b.ename, b.sal

        from emp b, dept a

        where a.loc = 'NEW YORK'

        and b.deptno = a.deptno

>> Loop 개수를 줄이기 위해 조인에 참여하는 테이블 중 Row수가 적은 쪽을 Driving으로 설정하고, 

       inner 테이블의 연결고리를 결합인덱스를 이용해 최적화함

>> NL Join 기법은 성공하면 바로 결과를 사용자에게 보여주므로 온라인 프로그램에 적당한 조인 기법이다.

(Output  Data가 적은 경우에 적합 - OLTP 상황)

출처 : 유튜브 - https://www.youtube.com/watch?v=WfuLUE7lccs&t=263s

2. INDEX NESTED LOOP JOIN(Single loop join) 

>> NESTED LOOP JOIN 방식에서 후행(Driven) 테이블의 조인 속성에 인덱스가 존재할 경우 사용

>> 선행 테이블(Driving)의 각 레코드들에 대하여, 후행 테이블의 인덱스 접근 구조를 사용하여, 
      직접 검색 후 조인하는 방식

3. Sort Merge join

>> R과 S의 레코드들이 각각 조인 애트리뷰트 A, .B 값에 따라 물리적으로 정렬되어 있는 경우, 

      두 파일을 모두를 조인 애트리뷰트의 순서에 따라 동시에 스캔하면서 A, B 값이 동일한 레코드를 검색

>> 정렬되어 있지 않은 경우는 우선 외부 정렬을 사용하여 정렬 후 조인

>> 사용법 :  /* USE_MERGE(A B) */

ex)   select /*+ use_merge(a b) */a.dname, b.empno, b.ename

        from   dept a,emp b

        where  a.deptno = b.deptno

        and    b.sal > 1000 ;

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

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

>> Sorting이 필요한 경우에 유용(어짜피 정렬을 하므로)

출처 : 유튜브 - https://www.youtube.com/watch?v=HyZtBGXLN00

4. Hash join

>> Hash Join은 해슁기법을 이용하여 조인을 수행

>> 조인 칼럼을 기준으로 해쉬함수를 수행하여 서로 동일한 해쉬 값을 갖는 것들 사이에서 
     실제 값이 같은지를 비교하면서 조인을 수행

>> NL Join 랜덤액세스 와 Sort Merge Join 의 문제점인 정렬 작업의 부담을 해결하기 위한 대안으로 등장

>> Hash Join은 조인 칼럼의 인덱스가 존재하지 않을 경우에도 사용할 수 있는 기법이다.

>> Hash Join은 해쉬 함수를 이용하여 조인을 수행하기 때문에 ='=로 수행하는 조인, 
     동등조건에만 사용 할 수 있다.

>> 해쉬 함수가 적용될 때 동일한 값을 항상 같은 값으로 해슁됨이 보장된다.

>> Hash Join 작업을 수행하기위해 해쉬 테이블을 메모리에 생성해야한다.

>> 메모리에 적재할 수 있는 영역에 크기보다 커지면 임시 영역(디스크)에 해쉬 테이블을 저장한다.

>> Hash Join을 할 때는 결과 행의 수가 적은 테이블을 선행 테이블로 사용하는 것이 좋다.

>> 선행 테이블을 Build Input 이라하며, 후행 테이블은 Prove Input이라한다.

>> ORDER BY를 할때는 Merge Join이 나을 수도 있다

출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=26744589

• HINT (  /*+ */  )

-Plan 변경이 가능

-튜닝의 지름길

• FUNCTION

-Query의 생명 >> HardCoding을 하지않고, 간결하게 작성하기 위해서는 적절한 함수사용이 필요

-남발하면 성능을 저하시키므로, 상황에 따라 사용

-DB별로 사용가능 여부가 다르므로 확인

-사용자 지정 함수도 가능

-모든 함수를 암기할 수 없으므로 효율적인 구글링이 필요

• Procedure와 Function

-공통점 : 값을 return

-차이점 : Procedure > 2개 이상 return 가능 

             Function > 1개 return만 가능

• 구글링 TIP

-문제를 파악 > 문제해결을 위한 가설을 세움 > 가설에 필요한 내용을 검색

ex) 재귀호출, 상위 부서 찾기

• Procedure와 Function

-공통점 : 값을 return

-차이점 : Procedure > 2개 이상 return 가능 

               Function > 1개 return만 가능

 

• GROUP BY

-GROUP BY 절에 들어가는 내용은 Data를 보고자하는 관점의 컬럼(성별, 지역, 나이)

-SELECT(보여줄 내용, 합계)의 갯수 < GROUP BY의 갯수

문법적으로 가능은 하나, 별로 바람직하지 못하다

WHY? 분류한 관점을 누락시켜 결과 데이터와 매칭이 되지 못함

• View의 장점

1.보안 : 사용자에게 특정 Data를 제외하고 보여주고 싶을 때,
           그 부분을 제외하고 View로 구성하여 사용자에게 제공

2.유지보수 : 변경사항이 발생했을 때 조인한 각각의 Table을 변경하는 것이 아니라
                 View만을 변경해서 보여줌

 

• Query를 짜는 방식 

JOIN 상황에서 그냥 일반조인, 서브쿼리, 스칼라 서브쿼리, 인라인뷰, 함수형 등
다양한 방식이 있을 수 있는데, 업무특성(앞단, 뒷단)/개발자의 Style대로 작성