[DataBase] SQL 쿼리 처리과정 & HINT

** 데이터 진흥원 - SQL 전문가 가이드의 [과목3-1장-2절] 내용

 

> SQL은 구조적인 질의 언어이다. 형식이 구조적임으로 최적의 조회 방법을 찾아낼 수 있다.

> 아래 엔진들을 거치면서 RDBMS들은 최적의 실행 계획을 찾아낸다

엔진		역할	리턴
Parser		SQL문을 파싱하여 파싱트리(내부 구조체)로 변환 + 문법/의미 오류 체크	parsed SQL
Optimizer	Query Transformer	파싱된 SQL을 좀 더 일반적인 형태로 변환	Execution Plan
	Estimator	오브젝트/시스템 통계로 각 수행 단계의 선택도/카디널리티/비용을 계산
	Plan Generator	후보군이 될만한 실행계획들을 생성
Row-Source Generator		실행계획을 SQL 엔진이 실행할 수 있는 코드로 formatting	Row-Source
SQL Engine		SQL 실행

 

** Oracle의 hint는 오류가 있을 시, 무시되고 SQL이 실행된다. (오류가 발생하지 않아 적절한 쿼리 모니터가 필요하다.)

    반면, SQL Server는 힌트가 틀릴 경우 오류가 반환되며, SQL이 실행되지 않는다.

 

■ HINT : 힌트를 사용하면 처리과정을 사용자의 의도에 맞게 조정/강제할 수 있다.

 

> Oracle은 select 다음에 /**/를 사용하여 기재, SQL Server는 HINT에 따라 위치가 달라짐 (아래 힌트 표 참조)

-- ORACLE HINT
SELECT /*+ LEADING(TB1 TB2) USE_NL(TB1) INDEX(TB1 인덱스or(컬럼)) */
       *
  FROM TABLE1 TB1, TABLE2 TB2
 WHERE TB1.KEY = TB2.KEY
 
 -- SQL SERVER HINT
 SELECT *
   FORM TB1 WITH (NOLOCK)
 OPTION ~

 

 

■  자주 사용되는 힌트

Oracle 힌트
목적	힌트	예문
최적화 목표	all_rows : 전체 반환 최적화 first_rows(n) : n행 반환 최적화	SELECT /*+ ALL_ROWS */ col1, col2     FROM TBL WHERE col1 = 10; SELECT /*+ FIRST_ROWS(10) */ col1, col2     FROM TBL WHERE col1 = 10;
스캔 방식 (엑세스 경로) + [스캔 종류 설명]	full : 전체 스캔 사용 요청 ** cluster : cluster 인덱스 사용 요청 hash : hash 조인을 요청 (대용량 조인 시 유리할 가능성 높음) index, no_index : 특정 인덱스 (미)사용 index_asc, index_desc : 인덱스의 스캔 방향을 지정 index_combine : 인덱스 복합 사용 지정 (where 조건이 n개일 때) index_join : 인덱스를 조인하여 사용 (위와 같이 복합 조건에서 유용) index_ffs, no_index_ffs : fast full index scan 요청 index_ss, no_index_ss : index skip scan 요청 index_ss_asc,index_ss_desc	SELECT /*+ FULL(A) */ col1, col2 FROM TBL A; SELECT /*+ CLUSTER(TBL) */ * FROM TBL WHERE col1 = 10; SELECT /*+ HASH(A, B) */ col1, col2 FROM TBL1 A, TBL2 B WHERE A.col1 = B.col2; SELECT /*+ INDEX(A idx) */ * FROM TBL1 A WHERE col=2; SELECT /*+ INDEX_ASC(A idx) */ * FROM TBL1 A  WHERE col=2; SELECT /*+ INDEX_COMBINE(A idx1 idx2) */ * FROM TBL A WHERE col1 > 100 AND col2 = 20; SELECT /*+ INDEX_JOIN(A idx1 idx2) */ * FROM TBL A WHERE col1 > 100 AND col2 = 20; SELECT /*+ INDEX_FFS(A idx) */ * FROM TBL A; SELECT /*+ INDEX_SS(A idx) */ * FROM TBL A;
** cluster table : 자주 호출/조인되는 테이블들을 물리적 디스크에 가까이 저장시켜 읽을 때 효율성을 보장하는 방법
최적화 (쿼리 변환) : [최적화]	no_query_transformation : 쿼리 변경이 불가능하도록 강요 use_concat : OR을 UNION ALL로 변경하도록 요청 no_expand : 서브쿼리/뷰의 MAIN으로 확장 금지 (실행계획의 예측 가능성을 높이기 위함) rewrite, no_rewrite :머티리얼라이즈 뷰 사용 (미)허용 merge, no_merge : 뷰/인라인 뷰가 MAIN과 결합 (미)허용 star_transformation, no_star_transformation : DW 대규모 쿼리의 스타 스키마 (미)활용 fact, no_fact : 해당 테이블의 fact테이블 가정 (미)적용 unnest, no_unnest : 서브쿼리 -> 조인 (미)변환	SELECT /*+ NO_QUERY_TRANSFORMATION */ * FROM TBL1 WHERE col IN (SELECT col FROM TBL2); SELECT /*+ USE_CONCAT */ * FROM TBL WHERE col = 10 OR col = 20; SELECT /*+ NO_EXPAND */ * FROM TBL1 WHERE col1 IN (SELECT col FROM TBL2 WHERE col2 = 1); SELECT /*+ REWRITE */ * FROM TBL WHERE col = 10; SELECT /*+ MERGE */ A.* FROM (SELECT * FROM TBL) A WHERE A.col = 10; SELECT /*+ STAR_TRANSFORMATION */ * FROM TBL F JOIN TBL2 M ON F.col = M.col WHERE M.col2 = 'category'; SELECT /*+ FACT(sales) */ * FROM TBL F JOIN TBL M ON F.col = M.col; SELECT /*+ UNNEST */ * FROM TBL WHERE col IN (SELECT col FROM TBL2);
조인 순서	ordered : 작성된 순서대로 조인 진행 leading : 힌트에 작성된 순서로 조인 진행	SELECT /*+ ORDERED */ A.col, B.col FROM TBL1 A, TBL2 B WHERE A.col = B.col; SELECT /*+ LEADING(A B) */ A.col,B.col FROM TBL A,TBL2 B WHERE A.col = B.col;
조인 방식 : [조인 방식 설명]	use_nl, no_use_nl : nested loof 조인 요청 use_nl_with_index : nl 조인을 하되 인덱스를 함께 사용 요청 use_merge, no_use_merge : merge 조인 요청 use_hash, no_use_hash : hash 조인 요청	SELECT /*+ USE_NL(A B) */ A.col, B.col FROM TBL A, TBL2 B WHERE A.col = B.col; SELECT /*+ USE_NL_WITH_INDEX(A B idx) */ A.col, B.col FROM TBL1 A, TBL2 B WHERE A.col = B.col; 아래와 같은 예문으로 응용 SELECT /*+ LEADING(A B C)                      USE_HASH(A B) USE_MERGE(B C) */ A.*, B.*, C.* FROM A JOIN B ON A.key = B.key JOIN C ON B.key = C.key;
병렬 처리	parallel, no_parallel : 대용량 테이블의 병렬 처리 지정 (n지정) pq_distribute : 병렬로 데이터를 나눌 방법 지정 BROADCAST/RANDOM/PARTITION parallel_index, no_parallel_index : idx 스캔에 병렬 (미)처리 (n지정)	SELECT /*+ PARALLEL(t, 8) */ * FROM BIG_TLB t; SELECT /*+ PQ_DISTRIBUTE(A B BROADCAST) */ A.*, B.* FROM TBL1 A JOIN TBL2 B ON A.col = B.col; SELECT /*+ PARALLEL_INDEX(t idx, 4) */ * FROM BIG_TBL t;
기타	append, noappend : ** direct path insert (대용량에 효율) cache, nocache : 버퍼캐시에 저장 (빈도 높은 테이블 효율) push_pred, no_push_pred : 조인 시점에 where 필터를 함께 적용 push_subq, no_push_subq : sub q 조건을 main q 조인 시 반영 qb_name : 쿼리 블럭에 이름을 지정 (복잡한 쿼리에서 해당 지역을 특정) cursor_sharing_exact :cursor_sharing이 sharing/force일 때 리터럴에 맞는 계획을 저장하도록 지정 driving_site : 분산 쿼리의 메인 DB 지정 dynamic_sampling : 최적화를 위한 샘플링 횟수 지정 model_min_analysis	INSERT /*+ APPEND */ INTO BIG_TBL SELECT * FROM TBL; SELECT /*+ CACHE(t) */ * FROM frequently_accessed_table t; SELECT /*+ PUSH_PRED(A) */ * FROM TBL1 A JOIN TBL2 B ON A.col = B.col WHERE A.col2 > 100; SELECT /*+ PUSH_SUBQ */ main.id FROM main WHERE main.id IN (SELECT sub.id FROM sub WHERE sub.value > 100); SELECT /*+ QB_NAME(main_query) */ * FROM TBL; SELECT /*+ CURSOR_SHARING_EXACT */ * FROM TBL WHERE literal_col = 'some_value'; SELECT /*+ DRIVING_SITE(sub) */ main.col, sub.col FROM main@dblink1 main, sub@dblink2 sub WHERE main.id = sub.id; SELECT /*+ DYNAMIC_SAMPLING(t 4) */ * FROM big_table t;
** direct path insert : 버퍼캐시/undo/redo 거치지 않고, 데이터파일에 쓰는 방식 : 빠르지만 공간 낭비와 TBL Lock이 발생한다.

SQL SERVER HINT
데이터 잠금 처리	NOLOCK : 공유 잠금을 요청하지 않음 (타 트랜젝션들을 방해하지 않기 위함) ROWLOCK : 행 수준 잠금 요청 (업데이트 등의 정합성 유지에 사용) TABLOCK : 테이블 단위 잠금 요청 (대용량 데이터 처리 시 사용)	SELECT * FROM TBL WITH (NOLOCK) UPDATE TBL WITH (ROWLOCK) SET col1 = col1 * 1.05 WHERE col2 = 5; SELECT * FROM TBL WITH (TABLOCK) WHERE col = 5;
엑세스 경로	INDEX : 특정 인덱스를 사용하도록 요청 (인덱스 성능 비교)	SELECT * FROM TBL WITH (INDEX(index_name)) WHERE col LIKE '%Smith%';
조인 순서	FORCE ORDER : JOIN 순서를 기재한 순서대로 실행	SELECT * FROM TBL A JOIN TBL2 B ON A.col = B.col WITH (FORCE ORDER)
실행 계획 사용	OPTION(RECOMPILE) : 조건에 따라 실행계획 변경이 필요한 경우 기존의 계획이 아닌 새로운 계획을 요청 OPTION(KEEPFIXED PLAN) : 한 번 작성된 계획을 동적으로 변경하지 못하도록 강제하는 경우 사용	SELECT * FROM TBL WHERE col = @value OPTION (RECOMPILE); SELECT * FROM TBL WHERE col = 5 OPTION (KEEPFIXED PLAN);
조건 최적화	OPTION (OPTIMIZE FOR(@param = x)) : 특정 파라미터에 최적화를 원할 때	DECLARE @value INT = 5; SELECT * FROM TBL WHERE col = @value  OPTION (OPTIMIZE FOR (@value = 10));