SQL 및 데이터베이스 관리 주요 개념 요약

1. 데이터 모델링

엔터티 분류

• 기본/키 엔터티: 독립적 생성, 고유 식별자 보유
• 중심 엔터티: 기본 엔터티에서 발생, 업무 중심 역할
• 행위 엔터티: 두 개 이상 부모 엔터티에서 발생

IDEF1X 방법론

• 목적: 관계형 DB 설계, 의미론적 데이터 모델 생성
• 주요 구성: 엔터티, 관계, 속성 표현
• 특징: 표준화된 접근, 데이터 통합 지원

주식별자와 복합식별자

• 주식별자: 엔터티 인스턴스 유일 식별
• 복합식별자: 여러 속성 조합으로 식별자 구성
• 특성: 유일성, 최소성, 불변성, 존재성

반정규화 기법

• 목적: 성능 향상, 데이터 접근성 개선
• 방법: 중복 칼럼 추가, 파생 칼럼 추가, 이력 테이블 칼럼 추가
• 주의: 데이터 일관성 유지 방안 필요

2. SQL 함수와 연산자

NVL, COALESCE 함수

• NVL(expr1, expr2): expr1이 NULL이면 expr2 반환
• COALESCE(val1, val2, ...): 첫 번째 NULL 아닌 값 반환
• 데이터 유형 처리: 더 높은 우선순위 유형으로 반환

TO_CHAR, ABS, LENGTH 함수의 NULL 처리

• TO_CHAR(NULL): NULL 반환
• ABS(NULL): NULL 반환
• LENGTH(NULL): 대부분 NULL 반환, 일부 DB에서 0 반환 가능

TRUNC, ROUND, MOD, CEIL 함수

• TRUNC: 소수점 이하 절사
• ROUND: 반올림
• MOD: 나머지 계산
• CEIL: 올림 (정수로)

날짜형 데이터 연산

• 날짜 + 숫자: 일수 더하기
• 날짜 - 날짜: 일수 차이 계산
• ADD_MONTHS, LAST_DAY 함수 활용

3. 조인과 서브쿼리

FULL OUTER JOIN과 (+) 연산자

• FULL OUTER JOIN: 양쪽 테이블 모든 행 반환
• (+) 연산자: Oracle 전용, LEFT/RIGHT OUTER JOIN 구현
• 차이점: (+)는 FULL OUTER JOIN 직접 구현 불가

EXISTS 조건을 사용한 서브쿼리

• 용도: 서브쿼리 결과 존재 여부 확인
• 특징: 서브쿼리 결과 반환 즉시 종료, 성능상 이점

UNION ALL을 활용한 쿼리 최적화

• UNION vs UNION ALL: UNION ALL은 중복 제거 없음
• 성능: UNION ALL이 일반적으로 더 빠름
• 사용: 중복이 없거나 중복 허용 시 UNION ALL 선호

4. SQL 실행 계획과 성능 최적화

대용량 테이블에서의 장시간 SQL 쿼리

• 원인: 전체 테이블 스캔, 복잡한 조인, 대량 데이터 처리
• 최적화: 인덱스 활용, 파티셔닝, 쿼리 재작성

인덱스 활용 전략

• 적절한 칼럼 선택: 선택도 높은 칼럼
• 복합 인덱스 고려: 자주 함께 사용되는 칼럼들
• 인덱스 재구성: 주기적 관리로 성능 유지

5. 데이터베이스 객체 관리

ALTER TABLE 명령과 성능 영향

• 칼럼 추가: 일반적으로 빠름
• 칼럼 삭제: 테이블 크기에 따라 시간 소요
• 데이터 타입 변경: 데이터 검사/변환 필요, 시간 소요

NUMBER 데이터 타입의 정밀도와 스케일

• NUMBER(p,s): p는 전체 자릿수, s는 소수점 자릿수
• 예: NUMBER(5,2)는 -999.99에서 999.99까지 저장 가능
• 특수 케이스: NUMBER(2,5)는 매우 작은 소수 저장

6. 트랜잭션과 동시성 제어

UPDATE 문과 자동 커밋

• 기본 동작: 자동 커밋되지 않음
• 트랜잭션 제어: COMMIT, ROLLBACK 명령 사용
• 주의: 일부 DB 클라이언트에서 자동 커밋 모드 존재

교착상태(deadlock) 시나리오와 해결 방법

• 원인: 여러 트랜잭션이 서로의 잠금 대기
• 예방: 일관된 순서로 리소스 접근, 트랜잭션 범위 축소
• 해결: DBMS의 자동 감지 및 롤백, 수동 개입

7. 계층적 쿼리

LEVEL 슈도 칼럼

• 용도: 계층 구조에서 각 행의 깊이 표시
• 특징: 루트는 1, 자식은 2 이상의 값
• 활용: 조직도, 제품 카테고리 등 표현

START WITH, CONNECT BY 절

• START WITH: 계층 구조의 루트 조건 지정
• CONNECT BY: 부모-자식 관계 정의
• PRIOR: 부모 또는 자식 행 참조

8. 데이터베이스 보안과 사용자 관리

오라클 사용자 권한 관련 주요 롤

• CONNECT: 기본 연결 권한
• RESOURCE: 객체 생성 권한
• DBA: 모든 관리 권한 (주의 필요)

권한 부여와 취소 전략

• 최소 권한 원칙: 필요한 최소한의 권한만 부여
• 정기적 검토: 불필요한 권한 제거
• 롤 활용: 관련 권한 그룹화하여 관리

9. SQL 문법과 로직

WHERE 절에서의 논리 연산자 우선순위

• 우선순위: AND > OR
• 괄호 사용: 우선순위 변경 가능

괄호 사용을 통한 조건 그룹화

• 목적: 의도한 로직 명확히 표현
• 예: (A OR B) AND C vs A OR (B AND C)

10. RANK, PIVOT

분석 함수 (RANK, DENSE_RANK 등)

• RANK: 동일 값 동일 순위, 다음 순위 건너뜀
• DENSE_RANK: 동일 값 동일 순위, 다음 순위 연속
• 활용: 성적 순위, 판매 실적 등 순위 매기기

PIVOT, UNPIVOT 연산

• PIVOT: 행 데이터를 열로 변환
• UNPIVOT: 열 데이터를 행으로 변환
• 용도: 데이터 분석, 리포트 생성에 유용

11. 데이터베이스 관리와 모니터링

성능 튜닝 기법

• 실행 계획 분석: 비효율적인 연산 식별
• 인덱스 최적화: 적절한 인덱스 생성 및 관리
• 통계 정보 갱신: 옵티마이저 결정 지원

리소스 사용량 모니터링

• CPU, 메모리, I/O 사용량 추적
• 대기 이벤트 분석: 병목 현상 식별
• AWR, ASH 리포트 활용 (Oracle)

12. SQL 표준과 데이터베이스별 차이점

ANSI SQL vs 벤더별 SQL 문법

• ANSI SQL: 표준화된 SQL 문법
• 벤더별 차이: 함수명, 조인 문법 등
• 이식성: ANSI SQL 사용 시 DB 간 이식 용이