[SQLP] SQL SERVER 쿼리 힌트

-- 테이블 FULL 스캔하도록하는 힌트
select * from test.emp with(index(0));

실행계획

select * from test.emp with(index(0))

|--Clustered Index Scan(OBJECT:([master].[TEST].[EMP].[EMP_PK]))

 

-- EMP_PK : EMPNO
-- pk 인덱스를 타게하는 힌트
select * from test.emp e with(index(1)) where empno = 100; -- 테스트상 pk가 1번
select * from test.emp e with(index=emp_pk) where empno = 100;
select * from test.emp e with(index(emp_pk)) where empno = 100;

 

실행계획

select * from test.emp e with(index=emp_pk) where empno = 100

|--Clustered Index Seek(OBJECT:([master].[TEST].[EMP].[EMP_PK] AS [e]), SEEK:([e].[EMPNO]=(100.)) ORDERED FORWARD)

 

-- EMP_JOB_IDX : JOB
-- EMP_JOB_IDX 인덱스 타게하는 힌트
select * from test.emp with(index(4)) where job = 'President'; --테스트 상 인덱스 번호가 4임.
select * from test.emp with(index=emp_job_idx) where job = 'President';
select * from test.emp with(index(emp_job_idx)) where job = 'President';

실행계획

select * from test.emp with(index=emp_job_idx) where job = 'President'

|--Nested Loops(Inner Join, OUTER REFERENCES:([master].[TEST].[EMP].[EMPNO]))

|--Index Seek(OBJECT:([master].[TEST].[EMP].[EMP_JOB_IDX]), SEEK:([master].[TEST].[EMP].[JOB]='President') ORDERED FORWARD)

|--Clustered Index Seek(OBJECT:([master].[TEST].[EMP].[EMP_PK]), SEEK:([master].[TEST].[EMP].[EMPNO]=[master].[TEST].[EMP].[EMPNO]) LOOKUP ORDERED FORWARD)

// sql server 는 pk가 아닌 인덱스를 태울 때(클러스터 쿼리가 아니면), 내부적으로 clustered 된 테이블과 nl조인을 하는 것 같음.(rowid가 없는가?)

 

// SQL server Index seek은 Oracle의 Index Range Scan과 같다.

// SQL server Index scan은 Oracle의 Index Full Scan과 같다.

-- Index seek 힌트
select * from test.emp with(forceseek,index=emp_pk) where empno > 100;
-- Index scan 힌트
select * from test.emp with(forcescan,index=emp_pk) where empno > 100;

실행계획

select * from test.emp with(forceseek,index=emp_pk) where empno>100

|--Clustered Index Seek(OBJECT:([master].[TEST].[EMP].[EMP_PK]), SEEK:([master].[TEST].[EMP].[EMPNO] > (100.)) ORDERED FORWARD)

실행계획

select * from test.emp with(forcescan,index=emp_pk) where empno > 100

|--Clustered Index Scan(OBJECT:([master].[TEST].[EMP].[EMP_PK]), WHERE:([master].[TEST].[EMP].[EMPNO]>(100.)))

 

-- emp,dept 순서로 nl조인하는 힌트
-- 1
select * from test.emp e with(index(emp_job_idx)),test.dept d with(index(dept_pk))
where d.deptno = e.empno
and e.job = 'Sales Representative'
option(force order,loop join);
-- 2
select * from test.emp e with(index(emp_job_idx))
inner loop join test.dept d with(index(dept_pk))
on d.deptno = e.deptno
where e.job = 'Sales Representative';
-- 3
select * from test.emp e with(index(emp_job_idx))
inner join test.dept d with(index(dept_pk))
on d.deptno = e.deptno
where e.job = 'Sales Representative'
option(loop join);​

실행계획

select * from test.emp e with(index(emp_job_idx))  inner join test.dept d with(index(dept_pk))  on d.deptno = e.deptno  where e.job = 'Sales Representative'  option(force order,loop join)

|--Nested Loops(Inner Join, OUTER REFERENCES:([e].[DEPTNO]))

|--Nested Loops(Inner Join, OUTER REFERENCES:([e].[EMPNO]))

|    |--Index Seek(OBJECT:([master].[TEST].[EMP].[EMP_JOB_IDX] AS [e]), SEEK:([e].[JOB]='Sales Representative') ORDERED FORWARD)

|    |--Clustered Index Seek(OBJECT:([master].[TEST].[EMP].[EMP_PK] AS [e]), SEEK:([e].[EMPNO]=[master].[TEST].[EMP].[EMPNO] as [e].[EMPNO]) LOOKUP ORDERED FORWARD)

|--Clustered Index Seek(OBJECT:([master].[TEST].[DEPT].[DEPT_PK] AS [d]), SEEK:([d].[DEPTNO]=[master].[TEST].[EMP].[DEPTNO] as [e].[DEPTNO]) ORDERED FORWARD)

 

-- emp,dept 순서로 merge조인하는 힌트
-- 1
select * from test.emp e with(index(emp_job_idx)),test.dept d with(index(dept_pk))
where d.deptno = e.empno
and e.job = 'Sales Representative'
option(force order,merge join);
-- 2
select * from test.emp e with(index(emp_job_idx))
inner join test.dept d with(index(dept_pk))
on d.deptno = e.deptno
where e.job = 'Sales Representative'
option(force order,merge join);
-- 3
select * from test.emp e with(index(emp_job_idx))
inner merge join test.dept d with(index(dept_pk))
on d.deptno = e.deptno
where e.job = 'Sales Representative'
option(force order);

실행계획

select * from test.emp e with(index(emp_job_idx)),test.dept d with(index(dept_pk))  where d.deptno = e.empno  and e.job = 'Sales Representative'  option(force order,merge join)

|--Merge Join(Inner Join, MERGE:([e].[EMPNO])=([d].[DEPTNO]), RESIDUAL:([master].[TEST].[DEPT].[DEPTNO] as [d].[DEPTNO]=[master].[TEST].[EMP].[EMPNO] as [e].[EMPNO]))

|--Sort(ORDER BY:([e].[EMPNO] ASC))

|    |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[EMPNO]))

|         |--Index Seek(OBJECT:([master].[TEST].[EMP].[EMP_JOB_IDX] AS [e]), SEEK:([e].[JOB]='Sales Representative') ORDERED FORWARD)

|         |--Clustered Index Seek(OBJECT:([master].[TEST].[EMP].[EMP_PK] AS [e]), SEEK:([e].[EMPNO]=[master].[TEST].[EMP].[EMPNO] as [e].[EMPNO]) LOOKUP ORDERED FORWARD)

|--Clustered Index Scan(OBJECT:([master].[TEST].[DEPT].[DEPT_PK] AS [d]), ORDERED FORWARD)

 

-- emp,dept 순서로 hash조인하는 힌트
-- 1
select * from test.emp e with(index(emp_job_idx)),test.dept d with(index(dept_pk))
where d.deptno = e.empno
and e.job = 'Sales Representative'
option(force order,hash join);
-- 2
select * from test.emp e with(index(emp_job_idx))
inner join test.dept d with(index(dept_pk))
on d.deptno = e.deptno
where e.job = 'Sales Representative'
option(force order,hash join);
-- 3
select * from test.emp e with(index(emp_job_idx))
inner hash join test.dept d with(index(dept_pk))
on d.deptno = e.deptno
where e.job = 'Sales Representative'
option(force order);

실행계획

select * from test.emp e with(index(emp_job_idx)),test.dept d with(index(dept_pk))  where d.deptno = e.empno  and e.job = 'Sales Representative'  option(force order,hash join)

|--Hash Match(Inner Join, HASH:([e].[EMPNO])=([d].[DEPTNO]), RESIDUAL:([master].[TEST].[DEPT].[DEPTNO] as [d].[DEPTNO]=[master].[TEST].[EMP].[EMPNO] as [e].[EMPNO]))

|--Nested Loops(Inner Join, OUTER REFERENCES:([e].[EMPNO]))

|    |--Index Seek(OBJECT:([master].[TEST].[EMP].[EMP_JOB_IDX] AS [e]), SEEK:([e].[JOB]='Sales Representative') ORDERED FORWARD)

|    |--Clustered Index Seek(OBJECT:([master].[TEST].[EMP].[EMP_PK] AS [e]), SEEK:([e].[EMPNO]=[master].[TEST].[EMP].[EMPNO] as [e].[EMPNO]) LOOKUP ORDERED FORWARD)

|--Clustered Index Scan(OBJECT:([master].[TEST].[DEPT].[DEPT_PK] AS [d]))

 

-- sort group by 힌트 오라클과는 다르게 정렬작업을 수행하면서 group by 함.
select deptno,count(*) from test.emp 
group by deptno
option (order group);

실행계획

select deptno,count(*) from test.emp   group by deptno  option (order group)

|--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1005],0)))

|--Stream Aggregate(GROUP BY:([master].[TEST].[EMP].[DEPTNO]) DEFINE:([Expr1005]=Count(*)))

|--Index Scan(OBJECT:([master].[TEST].[EMP].[EMP_DEPT_IDX]), ORDERED FORWARD)

 

-- hash group by 힌트
select deptno,count(*) from test.emp 
group by deptno
option (hash group);

실행계획

select deptno,count(*) from test.emp   group by deptno  option (hash group)

|--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1005],0)))

|--Hash Match(Aggregate, HASH:([master].[TEST].[EMP].[DEPTNO]), RESIDUAL:([master].[TEST].[EMP].[DEPTNO] = [master].[TEST].[EMP].[DEPTNO]) DEFINE:([Expr1005]=COUNT(*)))

|--Index Scan(OBJECT:([master].[TEST].[EMP].[EMP_JOB_IDX]))