[2k5] 비확정적 매개변수(Magic Number)

/********************************************************************************************
-- Title : [2k5] 비확정적 매개변수(Magic Density)
-- Reference : www.dbnuri.com (권0용)
-- Key word : 매개변수 magic density magic number statistics 통계
********************************************************************************************/
-- 오라클 공부할 때 Magic Number를 몇몇 요소에서 봤는데 다 잊어버렸다..
-- CBO 실행 계획시 Magic Number를 해결하기 위한 힌트나 기타 설정을 봤던것도 같은데..OTL
-- 이름도 참 비슷하게 지었지.. Magic Number, Magic Density..
-- 웃긴건 오라클 공부하면서 씨퀄에 대한 이해도가 많이 높아졌다는거...
-- 하지만 지금은 다 날라갔다는거... 점점 머리는 비여간다...흨..ㅠㅠ
-- dbnuri에 자료가 올라와 따라하기 해봤다.

Magic_Density.docx

* Magic Number

	SQL 6.5	SQL 2000, 2005
>, >=, <, <=	30%	30%
=	10%	All Density 값
BETWEEN	25%	9%

USE tempdb;
GO

-- 테이블 생성 및 데이터 입력
DROP TABLE ttt;
GO

WITH temp1(num)
AS
(
        SELECT 1 num
        UNION ALL
        SELECT num + 1 FROM temp1
        WHERE num + 1 <= 5000
),
    temp2(num)
AS
(
        SELECT 1 num
        UNION ALL
        SELECT num + 1 FROM temp2
        WHERE num + 1 <= 600
)
SELECT
      IDENTITY(INT,1,1) "seq"
    , CONVERT(NCHAR(8), GETDATE() + b.num, 112) "dt"
    , CONVERT(NVARCHAR(12), LEFT(NEWID(), 12)) "dmy1"
    , NEWID() "dmy2"
INTO ttt
FROM temp1 a, temp2 b
OPTION (MAXRECURSION 0);
GO

--인덱스생성
CREATE CLUSTERED INDEX cix_seq ON ttt(seq);

CREATE INDEX ix_dt ON ttt(dt);

--데이터확인
SET STATISTICS PROFILE ON;

SELECT MIN(dt), COUNT(*)
FROM ttt;

/*****************************************************
--테스트 1(<=, 30%)
-- 예상 행수가 900000대 5000으로 틀리다.
*****************************************************/
DECLARE @dt NCHAR(8);
SET @dt = N'20090508';

SELECT * FROM ttt WHERE dt <= @dt;
SELECT * FROM ttt WHERE dt <= N'20090508';
/*
Rows Exec StmtText                                   EstimateRows
----- ----- ------------------------------ -------------
5000  1       select * from ttt where dt <= @dt; 900000.1
5000  1       |--Clustered Index Scan            900000.1
-- 3,000,000 * 30% = 900,000

Rows Exec StmtText                                             EstimateRows
----- ----- ------------------------------------- -------------
5000  1       SELECT * FROM [ttt] WHERE [dt]<=@1 5000
5000  1       |--Nested Loops                                 5000
5000  1           |--Index Seek                                 5000
5000  5000      |--Clustered Index Seek                   1
*/

/*****************************************************
--테스트 2(=, All Density)
-- 예상행수가 동일하다.
*****************************************************/
DECLARE @dt NCHAR(8);
SET @dt = N'20090508';

SELECT * FROM ttt WHERE dt = @dt;
SELECT * FROM ttt WHERE dt = N'20090508';
/*
Rows Exec StmtText                                   EstimateRows
----- ----- ------------------------------ -------------
5000 1    select * from ttt where dt = @dt;       5000
5000 1    |--Nested Loops                          5000
5000 1        |--Index Seek                          5000
5000 5000    |--Clustered Index Seek           1
--두 쿼리 실행계획 및 예상 행수 동일
*/

DBCC show_statistics ('dbo.ttt', 'ix_dt');
/*
All density      Average Length    Columns
------------- ----------------- ----------
0.001666667    16                         dt
3.333333E-07  20                         dt, seq
-- 3,000,000 * 0.001666667 = 5,000.001
*/

/*****************************************************
-- 테스트 3(between, 9%)
-- 예상 행수가 270000대 7500으로 틀리다.
*****************************************************/
DECLARE @dt NCHAR(8), @dt2 NCHAR(8);
SELECT @dt = N'20090508', @dt2 = N'20090509';

SELECT * FROM ttt WHERE dt BETWEEN @dt AND @dt2;
SELECT * FROM ttt WITH (INDEX=ix_dt) WHERE dt BETWEEN N'20090508' AND N'20090517';

/*
Rows  Exec  StmtText                                                          EstimateRows
-----  ----- ---------------------------------------------- -------------
10000  1       select * from ttt where dt between @dt and @dt2;  270000
10000  1       |--Parallelism(Gather Streams)                          270000
10000  2           |--Clustered Index Scan                               270000
-- 3,000,000 * 9% = 270,000

Rows  Exec  StmtText                                                                  EstimateRows
-----  ----- ---------------------------------------------------- -------------
10000  1        SELECT * FROM [ttt] WHERE [dt]>=@1 AND [dt]<=@2  7500
10000  1        |--Nested Loops                                                      7500
10000  1            |--Index Seek                                                      7500
10000  10000      |--Clustered Index Seek                                        1
-- 여긴 왜 7,500이 나왔을까..??
-- DBCC show_statistics 봐도 모르겠다.. ㅡ.ㅡ'''''(인덱스 힌트에 의한 듯)
*/