1 문제

2 솔루션

다음과 같은 테이블이 있다.

num         a1          a2          a3          a4          
----------- ----------- ----------- ----------- ----------- 
1           1           2           3           4
2           5           6           7           8
3           9           10          11          12

요구사항
경우에 따라 특정 컬럼의 내용만 빼고 순서대로 세로로 결과를 반환해야 한다. 예를 들면 a2 컬럼이 필요 없다면 다음과 같은 결과를 반환해야 한다.

a1          
----------- 
1
3 --2 빠짐
4
5
7
8 --6 빠짐
9
11 --10 빠짐
12

준비

USE TEMPDB
GO
--DROP TABLE TEST2
CREATE TABLE TEST2(NUM INT PRIMARY KEY,A1 INT, A2 INT, A3 INT, A4 INT)
INSERT INTO TEST2 VALUES(1,1,2,3,4)
INSERT INTO TEST2 VALUES(2,5,6,7,8)
INSERT INTO TEST2 VALUES(3,9,10,11,12)
SELECT * FROM TEST2
GO

해결

--실행계획: 모두 CLUSTERED INDEX SEEK 했지만 정렬비용이 많이 들어갔다. 
--WHERE절을 빼면 클러스터드 인덱스 스캔을 하지만 정렬비용이 줄어드는 것을 볼 수 있다.
SELECT A1 FROM TEST2
WHERE NUM > 0
UNION
SELECT A2 FROM TEST2
WHERE NUM > 0
UNION
SELECT A3 FROM TEST2
WHERE NUM > 0
UNION
SELECT A4 FROM TEST2
WHERE NUM > 0
 
--데이터의 분포도나 양에 따라서 실행계획이 틀려질 수 있다는 점 유의.
--WHERE절이나 GROUP BY를 이용해서 사용자에게 원하는 데이터만 전송하는 것이 좋다.
--만약 오라클의 FIRST_ROW방식의 옵티마이저를 흉내 낸다면...
--OPTION(FAST 10)의 식으로 흉내 낸다.
 
SELECT A1 FROM TEST2
--WHERE NUM = 1 --이렇게 필터링하면 UNION ALL을 써서 정렬비용을 없앤다.
UNION
SELECT A2 FROM TEST2
--WHERE NUM = 1
UNION
SELECT A3 FROM TEST2
--WHERE NUM = 1
UNION
SELECT A4 FROM TEST2
--WHERE NUM = 1
OPTION(FAST 12)
 
 
--위에 것을 이용해서 적절히 프로시저를 만든다.
CREATE PROC PROC_TEST  @A1 BIT = 1, @A2 BIT = 1, @A3 BIT = 1, @A4 BIT = 1
AS
BEGIN
DECLARE @SQL VARCHAR(200)
SET @SQL = ''
 
IF(@A1=1)
             SET @SQL = 'SELECT A1 FROM TEST2 UNION '
IF(@A2=1) 
             SET @SQL = @SQL + 'SELECT A2 FROM TEST2 UNION '
IF(@A3=1)
             SET @SQL = @SQL + 'SELECT A3 FROM TEST2 UNION '
IF(@A4=1)
             SET @SQL = @SQL + 'SELECT A4 FROM TEST2'
EXEC(@SQL)
END
GO
 
SELECT * FROM TEST2
EXEC PROC_TEST 1,0,1,1 --필요한 컬럼에 1, 필요 없는 컬럼에 0을 매개변수로 전달
 
--좀 더 정리하자면 다음과 같이 될 수 있다.
CREATE PROC PROC_TEST2  @A1 BIT = 1, @A2 BIT = 1, @A3 BIT = 1, @A4 BIT = 1
AS
BEGIN
   SELECT A1 FROM TEST2
   WHERE 1 = @A1
   UNION
   SELECT A2 FROM TEST2
   WHERE 1 = @A2
   UNION
   SELECT A3 FROM TEST2
   WHERE 1 = @A3
   UNION
   SELECT A4 FROM TEST2  
   WHERE 1 = @A4
END
GO
SELECT * FROM TEST2
EXEC PROC_TEST2 1,0,1,1

실제로 이러한 비정규화 문제는 많은 사이트에서 일어난다. 심지어는 MSSQL Server에도 이러한 패턴의 테이블이 보일 정도다. 가로를 세로로 펼치기 위해서는 위와 같이 UNION이나 UNION ALL을 사용해야 한다. 가로로 많이 펼쳐져 있다면 그만큼 더 비효율적이 된다. 실제로 같은 속성인데 여러 개의 컬럼으로 분리가 되어 있으므로 인덱스를 모두 생성할 수도 없는 노릇이다.