[MSSQL] mssql 에서 hint 사용하기

1. Hint Basic Syntax

- SELECT 문에서의 HINT SYNTAX 

SELECT statement ::=
< query_expression >
[ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }  [ ,...n ]]
[ OPTION ( < query_hint > [ ,...n ]) ] 

- 예제

// Merge Join을 하게하는 Hint

SELECT *
FROM Sales.Customer AS c
INNER JOIN Sales.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
WHERE TerritoryID = 5
OPTION (MERGE JOIN);  

 

2. Hint 종류

* MS-SQL Hint는 4가지가 제공이 됩니다.

  1) JOIN힌트 : 이것은 사용될 조인 기법을 지정한다.
  2) 인덱스힌트 : 검색이나 정렬을 수행하기 위해 사용되어야 하는 특정 인덱스들을 지정한다.
  3) 쿼리 처리 힌트 : 이것은 사용되어야 할 특정 처리 전략을 지정한다.
  4) 잠금힌트 : 이것은 사용되어야 할 특정 잠금모드를 지정한다.

OPTION 절에 오늘 Hint는 아래와 같은 것들이 올 수 있습니다.

<query_hint > ::=
{ 
  { HASH | ORDER } GROUP
| { CONCAT | HASH | MERGE } UNION
| { LOOP | MERGE | HASH } JOIN
| FAST number_rows
| FORCE ORDER
| MAXDOP number_of_processors
| OPTIMIZE FOR ( @variable_name = literal_constant [ ,…n ] )
| PARAMETERIZATION { SIMPLE | FORCED }
| RECOMPILE
| ROBUST PLAN
| KEEP PLAN
| KEEPFIXED PLAN
| EXPAND VIEWS
| MAXRECURSION number
| USE PLAN N "xml_plan"
}

 

아래의 표는 MS 공식 사이트에서 제공해주는 공식 설명서니 한번 참고해보시는것도 좋을 거 같네요.

<This table lists the options available for join hints, query hints, and table hints in Microsoft?? SQL Server™ 2000.>

Hint type	Option	Description	Default setting
Join	LOOP  |  HASH  |  MERGE  |  REMOTE	Specifies the strategy to use when joining the rows of two tables.	Chosen by SQL Server.
Query	{ HASH | ORDER } GROUP	Specifies whether hashing or ordering is used to compute GROUP BY and COMPUTE aggregations.	Chosen by SQL Server.
Query	{ MERGE | HASH |  CONCAT }  UNION	Specifies the strategy to use for all UNION operations within the query.	Chosen by SQL Server.
Query	FAST integer	Optimizes the query for retrieval of the specified number of rows.	No such optimization.
Query	FORCE ORDER	Performs joins in the order in which the tables appear in the query.	Chosen by SQL Server.
Query	ROBUST PLAN	Creates a plan that accommodates maximum potential row size.	Chosen by SQL Server.
Table	FASTFIRSTROW	Has the same effect as specifying the FAST 1 query hint.	No such optimization.
Table	INDEX =	Instructs SQL Server to use the specified indexes for a table.	Chosen by SQL Server.
Table	HOLDLOCK   |  SERIALIZABLE      |  REPEATABLEREAD   |  READCOMMITTED   |  READUNCOMMITTED |  NOLOCK	Specifies the isolation level for a table.	Defaults to a transaction isolation level.
Table	ROWLOCK |  PAGLOCK |  TABLOCK |  TABLOCKX |  NOLOCK	Specifies locking granularity for a table.	Chosen by SQL Server.
Table	READPAST	Skips locked rows altogether.	Wait for locked rows.
Table	UPDLOCK	Takes update locks instead of shared locks. Cannot be used with NOLOCK or XLOCK.	Take shared locks.
Table	XLOCK	Takes an exclusive lock that will be held until the end of the transaction. Cannot be used with NOLOCK or UPDLOCK.	Chosen by SQL Server.

3. 다양한 Hint Syntax및 Hint 예제.

(1) 조인힌트

- :INNER와 JOIN 사이에 JOIN hint이름을 나열한다. { LOOP | MERGE | HASH }

SELECT *
FROM title a
INNER HASH JOIN publishers b
ON a.pub_id=b.pub_id

또는

SELECT *
FROM title a 
INNER JOIN publishers 
ON a.pub_id = b.pub_id
OPTION (HASH JOIN);

의 방식으로 조인힌트를 줄 수 있습니다. 

두 방식의 차이점은 마래의 방법처럼 OPTION 절을 사용하여 hint 를 주는것이 우선순위가 높다는 거네요.

(2) 인덱스 힌트

- 문법 

SELECT select_list
FROM table [ (INDEX ({index_name | index_id} [, index_name | index_id ... ]))]

- 사용방법

1. 0번 인덱스를 타지 않게 하는 방법

SELECT au_lname, au_fname
FROM authors WITH (INDEX(0))
WHERE au_lname LIKE 'C%'

     
INDEX 0번은 인덱스를 타지 않게 하는 옵션입니다. INDEX 1번은 클러스터된 인덱스를 사용하게 하는 옵션이며
2번부터는 생성된 인덱스마다 번호가 부여됩니다.

2. 인덱스 번호로 인덱스를 사용하게 하는 방법 (2,3번 인덱스를 사용, 테이블에 인덱스 번호가 있는지 확인 필요)

SELECT au_lname, au_fname
FROM authors WITH (INDEX(2,3))
WHERE au_lname LIKE 'C%'

            

3. 선택한 인덱스를 사용하게 하는 방법 1

SELECT au_lname, au_fname
FROM authors WITH (INDEX(aunmind))
WHERE au_lname LIKE 'C%'

4. 선택한 인덱스를 사용하게 하는 방법 2

SELECT au_lname, au_fname
FROM authors WITH (INDEX(idx_nci_fname, idx_nci_lname))
WHERE au_lname LIKE 'C%'

     
(3) 쿼리처리 힌트 방법

- option절에 지정하는 hint 

예제)

-- GRUOP BY할때 HASH GROUP hashing 기법으로 그룹핑한다.

SELECT type,count(*)
FROM titles
GROUP BY type
OPTION (HASH GROUP)