SQLP/SQL 전문가 가이드

문제 정리

개발자 키우기 2024. 7. 28. 12:46

1과목 데이터 모델링의 이해 ( 10점 )

  •  스키마
    • 외부 스키마 - (논리적 독립성) - 계념 스키마 - (물리적 독립성) - 내부 스키마
  • 엔티티
    • 유무형
      • 유형 엔티티 / 개념 엔티티 / 사건 엔티티
  • 속성 
    • 구성 - PF일 ( PK, FK, 일반 )
    • 특성 - 기설파 ( 기본, 설계, 파생 )
      • 기본 <> 설계, 파생(이자)
  • 식별자
    • 대표성 - 주식별자(PK), 보조식별자(UNIQUE 일반 속성)
    • 스스로 - 내부식별자(PK), 외부식별자(FK)  / PK값 안에 FK값이 포함되어 있는지
    • 단일 - 단일식별자, 복합식별자
    • 대체 - 원조식별자, 대리식별자(속성 2개 이상 합쳐 하나로 만듦)
    • 대체 - 본질식별자, 인조식별자
      • 인조식별자는 본질식별자가 복잡한 구성을 가질때 만듬.
      • 인조식별자를 사용하면 중복 데이터가 많아짐.
      • 인조식별자를 사용하면 본직식별자를 사용할때보다 비교적 추가적인 인덱스가 더 필요함.
  • 데이터 모델링의 관계
    • 연관관계는 소스코드에 맴버 변수로 선언
    • 의존관계는 오퍼레이션에서 파라미터 등으로 이용
  • 정규형
    • 이행적 함수종속이 아닐때 3정규형.
    • 이행적 함수종속 일때 3정규형을 만족함.

2과목 SQL 기본과 활용 ( 20점 )

  • MOD()
    • MOD(15, 0) = 0   /   MOD(-15, 2) = -1   /   MOD(-15, -2) = -1   /   MOD(15, -2) = 1
  • NATURAL JOIN
    • USING 조건절을 사용했을 경우 USING 조건절에 사용된 칼럼은 SELECT에서 ALIAS나 테이블명을 붙이면 안 됨
  • VARCHAR / CHAR
    • VARCHAR(5) SQL   /   CHAR(5) SQL 일 경우 
      • VARCHAR는 3자리 수이고 CHAR는 5자리 수이다.
  • 집계 함수
    • 집계할 로우가 없으면 NULL을 반환
  • 윈도우 함수
    • ORDER BY를 사용하면 RANGE UNBOUNDED PRECEDING를 사용.
    • UNBOUNDED PRECEDING ( 파티션 맨 위 )   /    UNBOUNDED FOLLOWING ( 파티션 맨 아래 )
    • RANGE UNBOUNDED PRECEDING 일 경우 동일한 데이터가 있으면 같이 처리하니 주의 필요.
  • LAG / LEAD
    • LAG(A, 0) 변화 없음, LAG(A, 1) 기본값 / 1칸 이동
  • 비율함수
    • RATIO_TO_REPORT - 파티션별 합계에서 차지하는 비율로 파티션별 합이 1 임.
    • PERCENT_RANK - 파티션의 맨 위를 0, 아래를 1로 하고 백분위 순위를 구함. 재일 낮은 수가 0 높은 수가 1 임
    • CUME_DIST - 파티션의 백분율로 0보다 크고 1보다 작거나 같은 값임
  • FOREIGN KEY
    • DDL을 사용할 때 칼럼을 괄호 안에 넣어야 함.
  • VARCHAR / CHAR
    • VARCHAR는 서로 길이가 다르면 다른 값
    • CHAR는 서로 길이가 다르면 빈값으로 채워 비교함
  • IN
    • IN절에 NULL 주의. (무시해야 함)
  • ALTER 문
    • ALTER문에서 ADD에만 COLUMN이란 게 없고 다른 곳에는 다 들어감.
  • RENAME 문
    • RENAME A TO B
  • CONSTRAINT 제약 조건
    • NOT NULL 칼럼인데 CONSTRAINT 제약 조건으로 인하여 NULL이 되려고 하면 해당 SQL은 오류가 뜬다
  • TRIGGER
    • TCL을 이용하여 트랜잭션을 제어할 수 없다.
    • 데이터베이스에 로그인하는 작업은 가능하다.
  • NULL
    • 정렬
      • 오라클은 NULL 값을 가장 큰 값으로 취급
      • SQL Server는 NULL 값을 가장 작은 값으로 취급
    • 인덱스 저장
      • 오라클은 NULL 값을 맨 뒤에 저장
      • SQL Server는 NULL 값을 맨 앞에 저장
  • DML
    • 비절차적 데이터 조작어 - 사용자가 무슨 데이터를 원하는지
    • 절차적 데이터 조작어 - 어떻게 데이터를 접근해야 하는지

3과목 SQL 고급 활용 및 튜닝 ( 40점 )

  • Response Time
    • Service Time + Wait Time
    • CPU Time + Queue Time
  • INDEX SKIP SCAN
    • 범위조건일 때 사용가능하지만 IN절은 불가능하다.
  • Look
    • INSERT - TX Look, Exclusive
      • 만약에 pk와 같은 제약조건이 있을 때는 TX-S 요청부터 한다.
    • RS 모드
      • for update
    • RX
      • DML
    • INSERT /*+ APPEND */ - TM Look, Exclusive
    • SQL Server
      • HOLDLOCK - 트랜잭션 격리성 수준을 Serializable 수준으로 변경.
      • UPDLOCK - 오라클의 for update와 같음.
      • READPAST - Lock이 걸린 행은 읽지 않고 건너뜀.
      • TABLOCK - 테이블 레벨 Lock 설정.
    • 공유 LOCK 지속시간
      • READ COMMITTED
        • 레코드 읽기 직전, 레코드 이동 순간
      • REPEATABLE READ
        • 레코드 읽기 직전, COMMIT 이후
      • SERIALIZABLE
        • 트랜잭션 시작, COMMIT 이후
  • 실행계획
    • 오라클
      • EXPLAIN PLAN, DBMS_XPLAN.DISPLAY
      • 예상 Sort 정보는 포함되어 있지 않다.
    • SQL SERVER
      • SET SHOWPLAN_TEXT ON
  • AUTO 트레이스
    • SET AUTOTRACE TRACEONLY EXPLAIN / STATISTICS
    • 실제 사용한 CPU Time은 포함되어 있지 않다.
  • 트레이스
    • 오라클
      • ALTER SESSION SET SQL_TRACE = TRUE;
      • /*+ gater_plan_statistics */
      • query = disk + buffer cache
    • SQL SERVER
      • set statistics profile on ( 쿼리 결과 집합 + 쿼리 프로필 )
      • set statistics io on ( 디스크 동작 양 )
      • set statistics time on ( 사용 시간 )
  • AUTO TRACE VS TRACE 비교
    • consistent gets = query 버퍼캐시에서 읽은 블록 개수 ( undo 제외 )
    • sql*net roundtrip to/from client = fetch call count ( fetch 수행 횟수 )
    • row processed = fetch rows ( 총 row 수 )
  • dbms_xplan.display_cursor VS TRACE 비교
    • Buffers = current + query
    • Reads = pr
  • SQL 트레이스 VS AutoTrace
    • query = consistent gets
    • current = db block gets
    • disk = physical reads
  • dbms_xplan.display_cursor
    • 라이브러리 캐시에 적재된 커서의 실제 실행계획과 트레이스 정보를 출력할 수 있음
  • 대기 이벤트
    • 대기 이벤트가 발생하는 대표적인 이유 
      • SGA에 공유 메모리를 획득하기 위하여 래치를 얻어야 하는데 다른 프로세스와 경합이 발생한 경우
      • 디스크 I/O가 필요한 경우
      • 할 일이 없을 경우
    • latch: shared pool ( 하드 파싱이 심할 경우 )
    • library cache lock/pin ( 다른 사용자가 DDL을 수행할 경우 )
    • log file sync ( 커밋 명령을 받은 서버 프로세스가 LGWR에게 로그버퍼의 내용을 로그파일에 기록해 달라고 요청하는 경우 )
  • Response Time Analysis 응답 시간 분석 성능 관리 방법론
    • 대기 이벤트를 기반으로 세션 또는 시스템 전체에 발생하는 병목 현상과 그 원일을 찾아 문제를 해결하는 방법
  • Ratio + 응답 시간 분석 방법론 -> Statspack -> AWR ( Automatic Workload Repository )
    • Statspack - SQL로 딕셔너리 조회
    • AWR - DMA 방식으로 SGA 공유 메모리 접근 / 수집된 정보를 dba_hist_ 로 시작하는 각종 뷰를 사용자가 볼 수 있음
  • AWR 보고서 요약
    • 캐시 크기, 부하 프로파일, 인스턴스 효율성, 최상위 대기 이벤트, Shared Pool 통계, IO 프로파일, 메모리 통계
  • 인덱스 힌트
    • SQL SERVER
      • FROM 절에 인덱스 힌트를 지정
      • WITH(INDEX(0))
        • 클러스터형 인덱스면 클러스터형 인덱스 Full Scan을 수행.
        • 비클러스터형 인덱스면 Table Full Scan을 수행.
      • WITH(INDEX(1))
        • 클러스터형 인덱스면 클러스터형 인덱스 Range Scan 수행.
        • 비클러스터형 인덱스면 에러 발생.
  • 클러스터형 인덱스
    • 오라클은 Iot 인덱스를 생성하는 기준은 PK 칼럼.
    • SQL Server의 클러스터형 인덱스를 생성하는 기준은 사용자가 정할 수 있음.
    • 클러스터형 인덱스는 한 개만 생성 가능함.
  • CASE 문
    • WHERE 절에 CASE 문을 사용하면 UNION ALL 형태의 쿼리 변환이 이루어지지 않음.
  • 조인
    • SQL Server
      • OPTION(FORCE ORDER)
      • Table A INNER LOOP JOIN TABLE B ON ( A.T = B.T ) INNER MERGE JOIN TABLE C on ( ... )
  • Sort Merge 조인
    • DRIVING 집합의 조인 칼럼에 인덱스가 있다면 PGA에 저장하지 않음
    • INNER 집합은 정렬해서 PGA에 저장
      • PGA에 데이터를 읽을 수 있기 때문에 INNER 테이블을 반복적으로 액세스 할 때 버퍼 캐시를 경유하지 않음.
  • 스칼라 서브 쿼리 To 일반 조인문 Outer 표기
    • 주문을 기준으로 고객과 결제구분을 스칼라 서브 쿼리를 사용했을 경우.
      • 고객은 주문이 필수가 아니고 주문은 고객이 필수일 경우 조인하면 Outer는 필요 없음.
      • 주문과 결제구분이 서로 필수가 아니면 조인할 경우 Outer가 필요함.
    • 스칼라서브 쿼리를 인라인뷰로 만들어 조인할 때 Outer 표기를 잊지 말자.
  • Bind Variable Peeking
    • 첫 하드파싱할때 바인드 변수 값을 살짝 보고 칼럼 분포를 이용해 실행계획을 결정
  • Adaptive Cursor sharing
    • 특정 실행계획으로 수행했다가 다른 바인드 변수가 입력되었을 때 I/O가 예상보다 많이 발생하면 다른 실행계획을 추가로 생성하여 바인드 변수 값 분포에 따라 다른 실행 계획을 선택
  • Cardinality Feedback / Statistics Feedback
    • 최소 실행계획에서 예상한 카디널리티값이 실행과정에서 차이가 크다고 판단하면 카디널리티 값을 어딘가에 저장했다가 다음 실행 시 다른 실행계획을 생성.
  • Adaptive Plans
    • 런타임에 실행계획을 변경하는 것으로 예를 들어 실행계획에서 NL조인을 수행하려고 하는데 실행 과정에서 NL조인이 불리하다고 판단하여 HASH조인으로 변경하는 것. 
  • I/O 비용 모델
    • 비용의 기준은 디스크 I/O Call 횟수
  • 쿼리변환
    • 결과만 보장하면 무조건 쿼리 변환을 수행 ( 휴리스틱 쿼리 변환 )
    • 예상 비용이 낮을 때만 쿼리 변환을 수행 ( 비용기반 쿼리 변환 )
    • 옵티마이저는 대부분 비용기반 쿼리 변환을 수행함
  • 힌트
    • MERGE는 인라인 VIEW
    • UNNEST는 중첩된 서브쿼리와 스칼라 서브쿼리
    • USE_NL, USE_MERGE, USE_HASH, NL_SJ, MERGE_SJ, HASH_SJ, NL_AJ, MERGE_AJ, HASH_AJ
    • NO_EXPAND ( 익스 팬더 )
      • OR 조건을 분해해서 UNION ALL 형태로 변환하는 것을 방지 
    • APPEND ( 앱 앤드 )
      • INSERT시 버퍼캐시를 경유하지 않음
    • USE_CONCAT
      • NVL / DECODE 사용 시 사용 가능
      • COALESCE / CASE 사용시 사용 불가능
    • SQL Server
      • option (MAXDOP 4) = parallel dml
  • 조건절 이행 Transitive Predicate Generation
    • 추론을 통해 새로운 조건절을 만들어 주는 것으로 아래의 쿼리가 대표적인 예시이다.
SELECT *
  FROM A, B
 WHERE A.COL = 1
   AND B.COL = A.COL
   AND B.COL = 1       -- 해당 줄이 조건절 이행으로 옵티마이저가 내부적으로 생성

 

  • 소트
    • PGA의 Sort Area 공간이 찰 때마다 디스크에 임시 저장한 데이터들을 Sort Runs라고 한다.
ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
ALTER SESSION SET SORT_AREA_SIZE = 1000000000;
  • 벌크 INSERT 시 PK제약조건 해제
ALTER TABLE A MODIFY CONSTRAINT A_PK DISABLE DROB INDEX;
ALTER TABLE A MODIFY CONSTRAINT A_PK ENABLE NOVALIDATE;

 

  • 함수/프로시저
    • 함수/프로시저에 내장된 SQL이 참조하는 오브젝트에 변경이 생기면 변경 이후 최초로 함수/프로시저를 실행할 때 재컴파일이 일어남.
  •  PL/SQL에 내장된 SQL
    • 오라클은 커서를 자동으로 캐싱해 주기 때문에 Parse Call 은 한 번만 수행된다.
    • PL/SQL이 100번 수행되면 내장된 SQL에 대해서는 총 201번 Call이 발생한다.
  • 파티셔닝
    • 파티셔닝은 세그먼트 단위로 관리한다.
    • 파티션의 시작 숫자는 1이다.
    • 파티션 개수의 문제에서 형변환이 이루어지는지 확인하자.
    • 파티셔닝 된 테이블에 비파티션 인덱스를 생성할 때는 조건절이 = 이고 파티션 키 값이 조건절에 없을 때 유리함.
    • 테이블 Full Scan, Index Fast Full Scan시 블록기반이기 때문에 파티셔닝 수보다 병렬도를 더 높일 수 있다.
  • LISTAGG
    • GROUP BY 하여 그룹끼리를 한 줄로 나열해 주는 함수
    • 쿼리 내에 GROUP BY가 포함되어야 하며 WITHIN GROUP(ORDER BY )으로 정렬도 수행 가능
  •  MATERIALIZE
    • 임시테이블
  • WITH절
    • 매우 많은 데이터를 읽어 GROUP BY, 조인 등을 통해 집합 크기를 많이 줄일 수 있을 때 MATERIALIZE 방식으로 수행하거나 절차적 프로그램 패턴을 집합적인 처리 패턴으로 전환할 때 성능에 좋다
    • 참조하는 원본 집합과 결과 집합의 크기가 차이가 없거나 원본 집합이 작고 수행빈도가 높을 경우 성능이 나쁘다
  • Lock
    • DB 모니터링 과정에 Lock이 자주 나타나는 것은 자연스럽지만 블로킹이 자주 나타나는 것은 좋지 않다.
    • SQL Server
      • with(nolock) 힌트는 dirty read를 허용하는 힌트로 신중하게 사용하자.
    • 트랜잭션 격리성 수준을 Serializable로 설정하면 별도의 동시정 제어를 하지 않아도 SQL 문장 수준과 트랜잭션 내에서 일관성이 보장됨.
  • FOR UPDATE
    • 조인하는 양쪽 테이블 모두에 로우 LOCK이 걸리지만 컬럼을 지정해서 한쪽 테이블만 Lock이 걸리게 할 수 있다
    • Lock은 10초 동안 유지되거나 Commit 해야 풀린다
    • Lock은 결과집합 레코드에만 걸린다.
  • Snapshot too old
    • Table Full Scan, 해시조인을 사용하자.
    • 오랜시간 Fetch 해야 하는 대량의 데이터를 조회할때는 Order by를 추가하자.
    • 대량의 Update 이후에는 해당 테이블을 Full Scan 하자.
  • 리스너
    • 전용 서버 방식 - 서버 프로세스를 생성
    • 공유 서버 방식 - 가용한 Dispatcher 포트번호를 클라이언트에 전송
  • 인덱스 스캔 범위
    • 인덱스 액세스 조건이 인덱스 스캔 범위를 줄이는데 큰 영향을 끼친다.
  • IOT
    • IOT의 PK 인덱스을 조건절에 사용하면 나머지 컬럼과 연결되어 있기 때문에 추가적인 랜덤 액세스가 필요없다.
    • IOT의 인덱스를 조건절에 사용하면 PK 인덱스까지만 연결되어 있기 때문에 인덱스와 PK 인덱스외의 컬럼이 필요하면 랜덤 액세스가 수행된다.
    • 넓은 범위 스켄에 유리함. 즉 BETWEEN과 부등호 조건에 유리함.
  • Hash Join
    • 드라이빙 테이블과 이너 테이블의 조인조건으로 이루어진 해시맵은 PGA에 담겨있기 때문에 해시맵에 이너 테이블이 조인을 시도할때는 버퍼 캐시를 이용하지 않기 때문에 래치 및 버퍼 Lock이 필요없다.
  • session_cached_cursor
    • 해당 파라미터 설정 값을 늘리면 세션 커서 캐싱 기능으로 라이브러리 캐시 탐색 효율이 올라간다.
  • 동적 샘플링 레벨
    • 동적 샘플링 레벨을 높이면 더 정확한 실행계획을 수행해 주지만 SQL Parsing 부하는 줄이지 못한다.
  • 다중 트랜잭션이 동시에 몰릴때 채번
    • 채벌 테이블 - 심각한 Lock 경합 발생
    • MAX() - 중복값 발생 또는 중복값 방지를 위해서 Insert 재시도에 따른 성능저하
    • 시퀸스를 사용하자.
  • TM Lock
    • DML을 수행할때 테이블마다 자동으로 설정되는데 인덱스를 생성할때도 발생한다.
  • 효율성
    • a -> b 로 조인
      • a가 대량의 테이블이고 b가 소량일때는 비효율이 많다.
      • a가 대량의 테이블이고 b도 대량일때는 비효율이 없다.
  • 양쪽 테이블을 동적으로 파티셔닝 한다는 의미
    • PQ_DISTRIBUTE(A HASH HASH)
  • 세그먼트에 할당된 익스텐트끼리 서로 인접하지 않은 이유는 DBMS가 하나의 테이블스페이스를 여러 데이터파일로 구성하여 파일 경합을 피하기 위함이다. 
  • LMC(Leftmost Child)
    • 루트와 브랜치 블록의 가장 왼쪽 첫번째 레코드
  • ROWID
    • 데이터 블록 주소(DBA) + 로우 번호
      • 데이터 블록 주소 = 데이터 파일 번호 + 블록 번호(데이터 파일 내에서 부여한 상대적 순번)
  • IN 조건은 OR 조건절을 다르게 표현한 것임으로 두조건은 쿼리변환으로 인해 Union All 방식으로 변환되면 Index Range Scan이 가능하다.
  • INDEX With Order By
    • SELECT 절에 가공한 인덱스를 Order By에 사용하면 변경된 데이터를 기준으로 정렬을 수행해야하기 때문에 Sort 연산을 생략할 수 없다. 
/* Sort 연산 생략 불가 */
SELECT TO_CHAR(A.날짜) AS 날짜
FROM TABLE_A A
ORDER BY 날짜

/* Sort 연산 생략 가능 */
SELECT TO_CHAR(A.날짜) AS 날짜
FROM TABLE_A A
ORDER BY A.날짜
  • DECODE
    • 세번째 인자가 문자형이거나 NULL이면 네번째 인자도 문자형이다.
  • Include 인덱스
    • SQL Server만 가능함.
    • 리프 블록에만 추가적인 컬럼을 저장하여 수직적탐색에는 인덱스만 사용하고 수평적 탐색에서 필터조건으로 활용되어 테이블 랜덤 액세스 횟수를 줄일 수 있다.
  • IN-List Iterator
    • IN-List Iterator은 IN 절을 Union ALL로 변환하여 인덱스 스켄의 수직탐색을 여러번 반복하는 것이기 때문에 수직탐색이 많을때나 인덱스로 Sort 연산을 생략해야할때는 좋지 못한 방식이다.
  • Hash Join vs Sort Merge Join
    • Sort Merge Join은 양쪽 테이블을 PGA에 담다가 넘치면 TEMP 테이블에 저장하지만 Hash Join은 Build Input 테이블만 PGA에 담아 조인하기 때문에 Sort Merge Join보다 TEMP 테이블을 사용할 확률이 적은 것도 장점이다.
  • 중첩된 서브쿼리 Unnest
    • 서브쿼리를 합치지 않고 따로 최적화를 시도하면 서브쿼리는 항상 Inner 테이블이 된다. Unnest 힌트를 사용할때는 드라이빙 테이블을 지정할 수 있는데 만약 서브쿼리가 먼저 드라이빙 되지 않는다면 Unnest 하지 않는것과 별반 차이가 없다(캐싱기능을 제외하고). 따라서 서브쿼리가 먼저 드라이빙 테이블이 되고자 할때 사용하자. 
      • QB_NAME(SUBQ) / LEADING(거래@SUBQ)
  • 스칼라 서브쿼리
    • 스칼라 서브쿼리의 캐싱 효과를 높이기 위해서는 메인쿼리의 집합이 커야한다. 재사용성이 높을 수록 캐시의 효과가 두드러지기 때문이다. 만약 메인쿼리 집합이 작은 경우는 거이 활용도 안되는 데이터를 캐싱하기 때문이다.
    • 스칼라 서브쿼리는 결국 NL조인되기 때문에 보통 대량의 데이터를 처리할때 활용하는 병렬 쿼리에서는 스칼라 서브쿼리를 사용하지 않는 것이 좋다. 해시 조인으로 처리해야 효과적이기 때문이다.
  • 소트 튜닝
    • COUNT(STOPKEY) = 페이징 처리시
    • FIRST ROW - 인덱스를 활용한 MIN/MAX 구할시
  • 분석함수
    • rank와 row_number 윈도우 함수를 사용하면 Top N 소트 알고리즘이 작동한다. 더 나아가 row_number 함수가 더 효율적이다.
  • 통계정보
    • 통계정보를 수집하는 이유는 더 최적화된 실행계획을 세워 I/O를 최소화 하기 위함이다. 
    • 테이블 통계
      • 총 레코드 수, 총 블록 수, 레코드당 평균 길이, 샘플링한 레코드 수, 통계정보 수집일시
    • 인덱스 통계
      • 브랜치 레벨, 총 리프 블록 개수, 총 레코드 개수, 인덱스 키 값 조합의 개수, 수평적 탐색 비용, 테이블 랜덤 액세스 비용, 클러스터링 팩터
    • 컬럼 통계
      • 중복을 제거한 개수, 선택도, 컬럼 평균 길이, 최소값, 최대값, null 개수
  • SQL Server 힌트
    • 테이블 - 테이블명 다음에 WITH 절을 사용하여 지정 (NOLOCK 등)
    • 쿼리 - 쿼리 맨 마지막에 OPTION 절을 사용하여 지정 (FORCE ORDER 등)
    • 조인 - FROM절에 지정 (LOOP 등)
  • 백그라운드 프로세스
    • SMON(System Monitor) = Database cleanup/shrinking thread
    • PMON(Process Monitor) = OPS(Open Data Service)
  • 오라클은 Lock Escalation이 발생하지 않는다.
  • INSERT 튜닝
    • Direct Path Insert / Nologging
  • Update 튜닝
    • Truncate / Insert
    • 조인을 내포한 Update
      • 수정 가능 조인 뷰
      • Merge
  • 분산 쿼리
    • 원격에 있는 큰 테이블을 로컬에 작은 테이블과 조인을 할때 로컬에서 SQL을 수행하면 큰 테이블에 데이터를 가져오기 때문에 성능 저하가 불가피하다. 따라서 이럴 경우는 원격에서 쿼리를 수행하도록 driving_site 힌트를 사용하자.
  • 인덱스 파티셔닝
    • 글로벌 - 인덱스 경합 분산
    • 로컬 - 테이블 파티션 추가, 삭제가 빈번할때나 이력석 데이터를 주로 관리할 때
    • 비파티션 - 소트연산으로 부분범위처리, 테이블의 이동,삭제시 unusable되므로 주의
  • 조인 테이블 제거
    • A와 B 테이블을 조인하여 결과를 낼때 A 데이터만 필요하다고 가정하면 A데이터와 B데이터를 조인하는 A 테이블의 컬럼이 Unique일 경우 exists 보다 b테이블을 제거하는것이 좋다.
      • 만약 A테이블의 컬럼이 Unique가 아닌 경우에는 해당 컬럼에 is not null 조건을 붙이고 조인을 제거하자.

3과목 실기 ( 30점 )

  • 트레이스에 Partition 구문이 나오면 INDEX를 만들 때 Local인지 Global 인지 비파티션인지 주의.
  • 상품과 주문상품을 상품코드끼리 조인을 수행할 때 대부분의 상품을 주문했다고 가정하고 주문상품을 드라이빙 테이블로 정했을 경우 결국 대부분의 상품의 데이터가 필요하기 때문에 상품의 조인조건에 인덱스가 존재하더라도 Full Scan 하는 것이 더 좋다. 
  • 인라인 뷰 안에 order by가 있고 그 밖에 rownum이 있으면 쿼리변환이 이루어지지 않으니 NO_MERGE 힌트를 사용하지 말자.
  • INNER 테이블이 인라인 뷰면 이미 버퍼캐시에 존재하기 때문에 NL Join 시 배치 I/O가 일어날 수 없다. 따라서 NO_NLJ_BATCHING힌트가 필요 없다. 하지만 INNER 테이블이 일반적인 테이블이고 버퍼캐시에 담겨 있지 않아 I/O가 발생하면 배치로 인하여 정렬이 망가질 수 있기 때문에 Order by 나 NO_NLJ_BATCHING 힌트가 필요하다.
  • 코드성 테이블인 경우 소량이기 때문에 인덱스를 생성하지 않아도 된다.
  • Index로 찾아지는 row 수가 1퍼센트라고 해도 그 양이 많으면 Index Range 스켄보다 Index Fast Full 스켄을 고려볼 수 있다. 그 이유는 인덱스를 Single Block I/O방식으로 읽으면 대량이기 때문에 버퍼캐시 히트율도 낮아지며 시간도 오래 걸리기 때문이다.
  • 부분범위처리를 위하여 인덱스를 구성할때 Order By 기준이 COL1 DESC, COL2, CO3 DESC 라면 인덱스 생성도 따라서 맞춰야한다.
  • 인라인뷰 일경우만 실행계획에 VIEW가 나온다. 스칼라 서브쿼리나 중첩된 서브쿼리는 실행계획에서 VIEW가 아니라  대부분 필터로써 활용된다.
  • 해시 조인할 경우 인덱스를 사용하지 않으니 인덱스 컬럼이 가공되어도 문제가 없으니 해시 조인할 때 = 조건을 최대한 많이 추가하자.
/* 인덱스 비활성화 및 활성화 */
ALTER TABLE T MODIFY CONSTRAINT T_PK DISABLE DROB INDEX;
ALTER TABLE T MODIFY CONSTRAINT T_PK ENABLE NOVALIDATE;
  • 프로시저를 ONE-SQL로 변환시켰을때 COMMIT; 을 잊지 말자.
  • 인라인뷰일때만 실행계획에 VIEW라고 나온다. 스칼라서브쿼리나 중첩된 서브쿼리는 필터 등으로 대게 나온다.