변수 테이블의 통계 개선 - 1

1. 사전구성

SELECT ROW_NUMBER() OVER (ORDER BY a.number) seq
  , NEWID() main_code
INTO TB_Main01
 FROM master..spt_values a
  , (SELECT number
    FROM master..spt_values
    WHERE type = 'P'
    AND number < 10) b
WHERE a.type = 'P'
AND a.number BETWEEN 1 AND 1000;

SELECT c.sub_code
  , ASCII(SUBSTRING(sub_code, 1, 1)) * c.number value
INTO TB_Sub01
FROM (
  SELECT b.number, CONVERT(NVARCHAR(36), main_code) sub_code
  FROM TB_Main01 a
    , (SELECT number
      FROM master..spt_values
      WHERE type = 'P'
        AND number BETWEEN 1 AND 40
      ) b
  WHERE a.seq%2 = 0
) c;

 

2. AS-IS

DECLARE @Main TABLE (
  seq INT, main_code NVARCHAR(50)
  INDEX CIDX_Main CLUSTERED (seq, main_code)
);
DECLARE @Sub TABLE (
  sub_code NVARCHAR(50), value INT
  INDEX CIDX_Sub CLUSTERED (sub_code, value)
);

INSERT INTO @Main SELECT * FROM TB_Main01;
INSERT INTO @Sub SELECT * FROM TB_Sub01;

SELECT a.seq, a.main_code, MAX(b.value) max_value
FROM @Main a
INNER JOIN @Sub b
  ON a.main_code = b.sub_code
GROUP BY a.seq, a.main_code;

• 변수 테이블은 통계 정보를 생성하지 않고 실행 계획을 생성하기 때문에 많은 양의 데이터를 처리하는 구문에서는 변수 테이블의 사용을 주의해야한다.
• (참고) SQL-SERVER 버전이 올라감에 따라 Optimizer가 개선되면서 변수 테이블도 실제 실행 계획이 개선되고 있다.
  Microsoft SQL Server 2019 기준 비슷하게 작동함 (변수테이블에서도 Hash Match Join).

 

3. TO_BE

CREATE TABLE #Main (seq INT, main_code NVARCHAR(50));
INSERT INTO #Main SELECT * FROM TB_Main01;

CREATE TABLE #Sub (sub_code NVARCHAR(50), value INT);
INSERT INTO #Sub SELECT * FROM TB_Sub01;

CREATE CLUSTERED INDEX CIDX_Main ON #Main (seq, main_code);
CREATE CLUSTERED INDEX CIDX_Sub ON #Sub (sub_code, value);

SELECT a.seq, a.main_code, MAX(b.value) max_value
FROM #Main a
INNER JOIN #Sub b
  ON a.main_code = b.sub_code
GROUP BY a.seq, a.main_code;

• 임시 테이블은 CREATE INDEX 및 통계 정보를 생성하여 관리하기 때문에 카디널리티 추정이 가능해진다.