[SQLD] 2-2. SQL 기본 및 활용

1. ANSI/ISO SQL 조인

• INNER JOIN : TABLE_A INNER JOIN TABLE_B ON 조건 INNER JOIN TABLE_C ON 조건
• USING : TABLE_A INNER JOIN TABLE_B USING(?) (USING 안에 ALIAS 사용 X)
• CROSS JOIN : 모든 데이터의 조합
• LEFT (OUTER) JOIN : 조인 수행시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어 온다.
• A LEFT OUTER JOIN B ON A.? = B.? == A, B WHERE A.? = B.?(+)

2. EXISTS

• EXISTS(서브쿼리) 의 서브쿼리 결과가 존재하면 True, 그렇지 않으면, False 를 리턴한다.

 

3. 집합 연산자의 종류

• UNION : 중복된 행은 하나의 행으로 만든다.
• UNION ALL : 중복된 행도 그대로 결과로 표시된다.
• INTERSECT : 여러 개의 SQL문의 결과에 대한 교집합이다. 중복된 행은 하나의 행으로 만든다.
• EXCEPT : 앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합이다. 중복된 행은 하나의 행으로 만든다.

4. 일반 집합 연산자

• UNION
• INTERSECTION
• DIFFERENCE(= EXCEPT, MINUS(Oracle))
• PRODUCT(= CROSS JOIN)

5. Oracle 계층형 질의

• START WITH 루트 IS NULL CONNECT BY PRIOR 자식 = 부모(순방향 전개) ORDER SIBLINGS BY ? DESC
• 루트 노드의 LEVEL 값은 1이다.
• ORDER SIBLINGS BY절은 형제 노드 사이에서 정렬을 지정하는 구문이다.

6. 서브쿼리

• 연관(Correlated) 서브쿼리는 서브쿼리가 메인쿼리 컬럼을 포함하고 있는 형태의 서브쿼리이다.
• 다중 컬럼 서브쿼리는 서버쿼리의 결과로 여러 개의 컬럼이 반환되어 메인 쿼리의 조건과 비교되는데, SQL Server에서는 현재 지원하지 않는 기능이다.
• 스칼러 서브쿼리란 SELECT절에 사용되는 서브쿼리이다.
• Inline View, Dynamic View는 FROM절에 사용되는 서브쿼리이다.

7. 뷰 사용의 장점

• 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
• 편리성
• 보안성

8. ROLLUP, GROUPING SETS, CUBE

• GROUP BY ROLLUP(?1, ?2) = GROUP BY GROUPING SETS(?1, (?1, ?2), ())
• GROUPING(?) : ? 가 NULL이면 1, 그렇지 안으면, 0
• CUBE : 인자로 주어진 컬럼의 결합 가능한 모든 조합에 대해서 집계를 수행하므로 다른 그룹 함수에 비해 시스템에 대한 부하가 크다.

9. RANK 함수

• RANK() OVER(ORDER BY ~) : 동일 순위(1등 1등 3등 ...)
• ROW_NUMBER() OVER(ORDER BY ~) : 동일 순위(1등 2등 3등 ...)
• DENSE_RANK() OVER(ORDER BY ~) : 동일 순위(1등 1등 2등 ...)
• OVER(PARTITION BY ?) : ? 별

10. LAG, LEAD

• LAG 함수는 현재 읽혀진 데이터의 이전 값을, LEAD함수는 이후 값을 알아내는 함수이다. SQL Server에서는 지원하지 않는 함수이다.

11. DCL

• DBMS에 생성된 USER와 다양한 권한들 사이에서 중개 역할을 할 수 있도록 DBMS에서는 ROLE을 제공한다. 이러한 ROLE을 DBMS USER에게 부여하기 위해서는 GRANT 명령을 사용하며, ROLE을 회수하기 위해서는 REVOKE 명령을 사용한다.
• GRANT SELECT, UPDATE ON TABLE_A TO USER_A : USER_A에게 TABLE_A SELECT(WHERE 조건 검색) 와 UPDATE 권한을 부여

12. PL/SQL

• Block구조로 되어있어 각 기능별로 모듈화가 가능하다.
• IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.
• DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.
• Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다.
• Block으로 묶고 한 번에 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.
• 동적 SQL 또는 DDL 문장을 실행할 때 EXECUTE IMMEDIATE를 사용하여야 한다.
• 트리거 : COMMIT, ROLLBACK 실행 안됨, 프로시저 : COMMIT, ROLLBACK 실행 가능

13. SQL 실행계획

• CBO, 비용기반 옵티마이저 : 테이블 및 인덱스 등의 통계 정보를 활용하여 SQL문을 실행하는데 소요될 처리시간 및 CPU, I/O 자원량 등을 계산하여 가장 효율적일 것으로 예상되는 실행계획을 선택하는 옵티마이저
• 액세스 기법, 질의 처리 예상 비용, 조인 순서, 조인 방법 등이 표현

14. 인덱스

• 기본 인덱스 : Primary Key Index
• 인덱스는 조회만을 위한 오브젝트이며, 삽입, 삭제, 갱신의 경우 오히려 부하를 가증한다.
• Balance Tree는 관계형 데이터베이스에서 가장 많이 사용되는 인덱스이다.
• 테이블의 전체 데이터를 읽는 경우는 인덱스를 사용하지 않는 FTS를 사용한다.
• 인덱스가 존재하는 상황에서 데이터를 입력하면, 매번 인덱스 정렬이 일어나므로 데이터 마이그레이션 같이 대량의 데이터를 삽입할 때는 모든 인덱스를 제거하고, 데이터 삽입이 끝난 후에 인덱스를 다시 생성하는 것이 좋다.
• 인덱스를 이용한 데이터 조회는 항상 테이블 전체 스캔보다 빠른 것을 보장할 수 없다.
• 인덱스는 INSERT와 DELETE 작업과는 다르게 UPDATE 작업에는 부하가 없을 수도 있다.

15. Join 기법

• NL Join : 데이터를 집계하는 업무 보다는 OLTP의 목록 처리 업무에 많이 사용, 선택도가 낮은 테이블이 선행 테이블로 선택되는 것이 일반적으로 유리, 유니크 인덱스를 활용하여 수행시간이 적게 걸리는 소량 테이블을 조인할 때 적합하다. 조인 칼럼에 적당한 인덱스가 있어서 자연조인이 효율적일 때 유용하다.
• Hash Join : 소트 머지 조인하기에 두 테이블이 너무 커서 소트 부하가 심할 때 유용, 동등 조인에서만 사용할 수 있다.