SQLD) 내가 보려고 정리한 사소한 포인트 정리

반정규화

• 데이터 조회시 디스크 입출력 양이 많아서 성능이 저하되거나 조인으로 인한 성능저하가 예상되는 경우 반정규화 수행
• 반정규화 수행 전, 통계/중복/이력/부분 테이블 추가를 통해 반정규화를 회피하려고 함
• 반정규화 검토 대상
  • 한 테이블의 일정 범위만 자주 조회하는 경우
  • 대량의 데이터가 있고 대량의 데이터 범위를 자주 처리하는 경우
  • 통계성 정보를 자주 필요로 하는 경우 통계 테이블을 추가
  • 테이블이 지나치게 많은 조인이 필요한 경우

데이터 성능

• 로우 체이닝 : 로우 길이가 너무 길어서 두 개 이상의 데이터 블록에 걸쳐서 하나의 로우가 저장되는 경우
• 로우 마이그레이션 : 데이터 블록에서 수정이 발생하면 수정된 데이터를 해당 데이터 블록에서 저장하지 못하고 다른 블록의 빈 공간을 찾아 저장하는 방식으로 디스크 입출력 부하가 커진다

SQL Server vs Oracle

SQL 기본

• 처음 테이블을 만들고 insert를 하다가 어떤 컬럼에 default 값을 지정하는 DDL을 수행한 경우, 해당 컬럼에 값을 지정하지 않고 insert를 하는 경우 default 값은 그 이후부터 적용됨. DEFAULT 소급 적용 X
• TCL : 에러 발생 후 커밋을 하면 이전 값이 그대로 보전되어야 함. 변경된 경우는 원자성 원칙에 위배된 것 ACID
  • ** ACIDConsistency 일관성 : 트랜잭션 실행 전 후의 데이터 베이스에 잘못이 없어야 함Durability 지속성 : 트랜잭션 성공적 수행 시 갱신된 내용이 영구적으로 저장됨
  • Isolation 고립성 : 트랜잭션 실행 도 중 다른 트랜잭션의 영향을 받지 않음
  • Atomicity 원자성 : 트랜잭션에서 정의된 연산들은 모두 성공하거나 모두 실행되지 않은 둘 중 하나의 상태
• 같은 이름의 save 포인트를 지정했을 경우, 가장 아래쪽에, 최신에 정의된 save point가 유효함
• 문자열 비교 연산
  • CHAR : 길이가 다른 경우 짧은 쪽에 스페이스를 추가해서 길이를 같게 만든 후 → 서로 다른 문자가 나올 때까지 비교
  • VARCHAR : 서로 다른 문자가 나올 때까지 비교 → 끝까지 같다면 길이가 더 긴 것이 크다고 판단
• 비교 연산자를 조건절에 사용할 때 해당 컬럼에 null인 값이 포함되어 있는 경우, NVL()로 null 값을 처리해야 판단할 수 있음. null은 어떤 비교를 해도 null이 나오기 때문
• NULL은 비교 대상이 안되기 때문에 in(A,B,...,NULL)을 사용해도 NULL인 값을 가져오지 않음 , 즉 비교연산자에 사용된 NULL은 무용지물
• 뒤 또는 앞 등 불필요한 공백이 저장된 데이터를 비교할 때는 TRIM 함수를 사용 LTRIM은 앞 공백, RTRIM은 뒤 공백을 제거
• 집계 함수
  • AVG, SUM 등은 NULL이 포함된 경우 무시하고 계산
  • COUNT(*) 은 NULL을 0으로
• 순위 함수
  • DENSE_RANK : 1,2,2,3,3,4, ...
  • RANK: 1,2,2,4,...
• FROM 절에 ALIAS 사용을 위해 AS 키워드 사용하지 않음
• NVL(COL, B) : COL 에 값이 NULL 이면 B 반환 / NULL 인 값을 처리하기 위한 함수
• dirty read: 아직 커밋되지 않은 값이 읽힌 것
• non repeatable read : 동일한 트랜잭션 수행했는데 그 결과가 다른 경우 (phantom read 도 비슷 , 없던 데이터가 읽히는 것)
• 1/24/60 = 1분 , 1/24 = 1시간
• 숫자/0 = 에러 / 0/숫자 = 0
• 연산에 null이 포함되면 항상 Null
• GROUP BY 하면 ORDER BY 절에 집계 함수 사용 가능

SQL 활용

JOIN

• NATURAL JOIN에 사용된 열을 식별자를 가질 수 없음
  • 두 테이블에 데이터타입과 이름이 동일한 컬럼이 하나 반드시 존재해야 가능
  • 그래서 조인컬럼을 명시하지 않아도 됨
• 예를 들어, col1이 두 테이블에 공통으로 존재하여 조인 조건으로 사용된 상태에서 tab1.col1을 select 절에 사용하면 안됨
• CROSS JOIN 결과 건수
  • tab 1에 row 개수 X tab2에 row 개수
  • 조인 조건이 없는 경우 발생
• NON equi join : 조인 조건을 제외한 cross 조인을 한 후, where 절로 필터 처리 후 select
• n개의 테이블을 조인하면 최소 n-1번의 조인이 필요함

CUBE, ROLLUP, GROUPING SET 비교

• GROUPING SETS(A,B,C)
  • = GROUP BY A UNION ALL GROUP BY B UNION ALL GROUP BY C
  • 각 인자 컬럼을 기준으로 그룹핑한 결과를 중복 제거 없이 합친 결과
  • 인자의 순서는 무관!
• ROLLUP
  • 그룹핑 컬럼이 N 개 생기면 N+1 level의 subtotal이 생김
  • 예를 들어 2개의 컬럼으로 그룹핑한 경우 3개의 소계 행이 생겨야 함
  • 인자의 순서가 바뀌면 그 결과도 바뀜
  • GROUP BY A, B 하면 A와 B 컬럼의 값으로 조합을 만들어 그 조합들이 행으로 오는 것
  • 자동 정렬
  • GROUP BY 해서 나온 그룹 마다 소계를 추가하고, 마지막 1건의 total 을 추가
• CUBE
  • ROLLUP과 달리 인자 순서와 상관없이 모든 경우의 조합을 만듦
  • 자동 정렬 안해줌
  • 모든 경우의 소계 행을 추가
  • N 개의 그룹이 만들어진다면 2^N개의 소계가 만들어짐
  • CUBE(A,B) 면 A로 그룹한 결과의 소계 + B로 그룹한 결과의 소계 + A,B로 그룹한 결과의 소계

PARTITION

• 위→ 아래 누적 값 : RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• PERCENT_RANK : 파티션 하나를 100이라 치고 파티션 내 행의 순서별 백분율을 구하는 함수
• CUME_DIST
• NTILE
• RATIO_TO_REPORT = 해당 값/ 전체 값
• MOD(M,N) : M을 N으로 나누었을 때의 나머지
• OVER() = default, 전체 테이블이 하나의 파티션이라고 가정하는 디폴트 !

계층형 테이블

• 계층형 질의에서 ROOT LEVEL = 1
• connect by에서 prior 키워드가 바로 앞에 붙은 쪽이 자식이 되는것connect by A = prior B 하면 A → B로 역방향
• connet by prior A = B 하면 B가 부모 → A 가 자식으로 순방향

서브쿼리

• 단일행 : 단일행비교연산자 사용해서 실행 결과 1 건 반환
• 다중행 : 다중행비교연산자 사용해서 실행결과 여러개 반환
• 다중컬럼 : 결과가 부분 테이블 형태로 나옴, 이걸 조건절에서 받아쓰려면 일치하는 컬럼으로 받아줘야함
• ex) WHERE (A,B) IN (SELECT C1,C2 FROM TAB2 WHERE C2=1;) 이렇게 서브쿼리에서 도출된 테이블의 각 c1,c2 행이 a,b에 대응되어야 함
• 스칼라 = 결과 하나 반환

TRIGGER

• :OLD = 수행 전 데이터, :NEW = 수행 후 데이터
• 특정 체이블에 DML 문이 수행되었을 때 자동으로 동작하도록 작성된 프로그램

값 치환 함수

• COALESCE(a, b, c, ...) : 컬럼 a를 확인 했을 때 값이 null 이면 같은 행의 컬럼 b 값을 확인하고... 를 반복해서 null이 아닌 값을 찾고 인자로 온 모든 컬럼의 값이 null 이면 null을 출력하거나 마지막에 default 표현식을 출력
• NULLIF(A,B) : A가 null이면 B 출력 == NVL(A,B)
• ISNULL(A,B) : A==B 면 NULL 출력
• MIN, MAX 를 데이터 없는 테이블에 적용하면 NULL 반환
• AVG, SUM은 null 값을 제외하고 계산
• DECODE(COL, A, a, B, b, ...)
• COL 컬럼의 값이. A 이면 a 를 출력, B 면 b 출력 / 그래서 이 함수의 인자 개수는 반드시 홀수여야 함
• ABS(A): A 의 절댓값

CONSTRAINTS

• DELETE, UPDATE CONSTRAINT
  • cascade : 부모 삭제 → 자식 삭제
  • set null : 부모 삭제 → 자식의 부모 컬럼 값 Null
  • set default : 부모 삭제 → 자식의 부모 컬럼 값 default 값으로
  • restrict : 자식이 참조하고 있는 부모 삭제 불가
• INSERT CONTRAINT
  • automatic : 부모가 없는 경우 자식이 입력하면 부모 값 만들어서 참조
  • set null
  • set default
  • dependent : 부모가 있는 경우에만 자식이 참조 가능

기타

• 권한 집합 = ROLE
• VIEW의 특징
  • 독립성 : 테이블 구조 변경이 응용프로그램에 영향 미치지 않음
  • 편리성 : 복잡한 질의를 뷰로 미리 생성해 두어 관련 질의를 단순하게 할 수 있음
  • 보안성
  • 실제 데이터를 가지고 있지 않음 (물리적 저장 X)
• 기본 연산자를 사용하면 성능이 저하됨
• ALL : 정렬하지 않고, 중복 제거하지 않고 출력
• DISTICT : 중복을 제거하고 출력
• NOT IN(inline view).IN을 하면 모든 건수가 출력됨
• inline view에 null 이 포함되면 null로 인해 모든 조건이 참이 됨 → 0 건 출력
• NOT EXISTS ⇒ OUTER JOIN + 조인 조건이 된 컬럼의 is null 체크 (WHERE 기준이 아닌 테이블의 조인 컬럼 IS NULL)
• ANY == NOT EXISTS IN
• WHERE EXIST ( ... = null) ⇒ 아무것도 만족할 수 없어서 0건의 결과를 출력
• MERGEWHEN MATCHED THEN // 병합 조건과 일치할 때 아래 DML 수행DELETE WHERE ... // 위 DML을 수행한 행에 대한 것들만 다음 DML을 수행할 수 있음INSERT VALUES ...
• WHENE NOT MATCHED THEN // 병합 조건과 일치하지 않을 때 수행
• UPDATE SET ..
• MERGE INTO TABLE1 USING TABLE2 ON (병합 조건절) // TABLE1을 기준으로 병합
• TRUNCATE, DROP : 로그 남지 않아서 rollback 불가능

옵티마이저

실행 계획 순서

1. 선행 테이블 full access
2. 인덱스 스캔
3. 인덱스에서 찾은 row id 로 테이블 access
4. join
5. 후행 테이블에 대해서 1-3 과정 반복
6. join은 아래에서 위 순서로 실행
7. 마지막 select

JOIN 종류

• HASH JOIN
  • 인덱스 없어도 됨
  • 대용량 데이터에 사용
  • EQUI 조인에서만 사용 가능 → 비동등 조인인 경우 SM JOIN
  • 데이터 집계용
• SORT MERGE JOIN
  • 대량 데이터 정렬 후 조인
  • 동등, 비동등 조인 모두 사용 가능
  • 데이터 집계용
  • 인덱스 유무 영향 없음
• NESTED LOOP JOIN
  • OLTP 온라인 소량 조회에 많이 사용됨
  • 조인컬럼에 적당한 인덱스가 있는 경우 → 없는 경우 Hash join
  • 자연조인이 효율적일 때 유리한 방식
  • 선행 테이블이 작을수록 유리함
  • 선행 테이블이 너무 크면 랜덤 액세스 부하가 너무 큼 → 이 때 hash join을 해라!

인덱스 스캔 방식

1. index unique scan
   • not null, uniqe key 필요, 인덱스 구성 컬럼을 조건절에서 모두 동등 조건을 걸어줘야 함
   • 예를들어 인덱스가 col1, col2로 구성되어 있다면 where col1=n and col2=m 이렇게
2. index range scan
   • between like < > 등 범위로 조건 비교
3. index skip scan
   • 선행 컬럼에 대한 조건 없이 후행 컬럼에 대한 조건만 있는 경우
4. index full scan
   • 조건절에서 인덱스 컬럼 중 하나 이상을 사용하거나 사용한 컬럼이 모두 하나의 인덱스에 존재하는 경우 하나의 인덱스를 풀 스캔!
   • 인덱스 컬럼 중 하나는 반드시 not null 이어야 함
   • 벙렬 처리 못함
5. index fast full scan
   • index full scan과 동일한데 인덱스에서 row id를 찾아 테이블에 접근하는게 아니라 그 값 자체를 인덱스에 별도의 컬럼을 추가해서 가지고 있음
   • 병렬 처리가 가능
   • 정렬하지 않음
   • 비트맵 인덱스는 이 방식이 불가능함

인덱스 종류

• B 트리 인덱스
  • 키 값 + row id
  • 브랜치 (분기용) + 리프 (인덱스를 구성하는 컬럼 값)
  • 행단위 lock 지원
  • 차수가 높은 (=행, 데이터가 여러개인) 컬럼에 적용
  • null 비교 부정형 비교 X
  • RDB의 주요 인덱스로 가장 많이 사용됨
  • OLTP 시스템 환경에서 많이 사용됨
  • 대용량 테이블 탐색에 불리함
  • 일치 및 범위 검색에 유리
• 비트맵 인덱스
  • 대용량 데이터 처리에 용이
  • key + start row id + end row id + bitmap
  • 행 단위 lock 지원 X
  • null 비교 부정형 비교 O
  • DW AD-HOC 질의 환경을 위해 설계됨
  • 하나의 인덱스 키가 여러개의 포인터 행을 저장
• 클러스터드 인덱스
  • 리프페이지 = 데이터 페이지
  • 인덱스 키 컬럼 순으로 물리적 정렬되어 있음
• 인덱스 구성 컬럼이 아닌 데이터를 업데이트 할 때 index update 부하 없음

실행 계획

• 액세스 기법, 예상비용, 조인 순서
• 위→ 아래, 안 → 밖 순서로 읽기
• 실행계획을 시각화한 것 = SQL 처리 흐름도