반응형
- /**********************************************************************************************
-- Title : [2k5] 문자열 검색과 통계
-- Reference : mcpworld.com
-- Key word : show_statistics profile 문자열
**********************************************************************************************/
--CLEAN UP
/*
USE MASTER
GO
DROP DATABASE TESTDB
GO
*/
--임시 데이터베이스 생성
CREATE DATABASE TESTDB
GO
USE TESTDB
GO
CREATE TABLE StringTestTable(
StrValues VARCHAR(10)
)
GO
INSERT StringTestTable VALUES (REPLICATE('A', 4))
GO 100
INSERT StringTestTable VALUES (REPLICATE('B', 4))
GO 50
INSERT StringTestTable VALUES (REPLICATE('C', 4))
GO 30
--입력된 데이터의 분포 형태
SELECT StrValues, COUNT(*) AS CNT FROM StringTestTable GROUP BY StrValues
GO
/*
AAAA 100
BBBB 50
CCCC 30
*/
--현재 테이블의 통계 정보 출력
SP_HELPSTATS StringTestTable, 'ALL'
GO
DBCC SHOW_STATISTICS(StringTestTable, _WA_Sys_00000001_7B905C75)
GO
SET STATISTICS PROFILE ON
--실행 계획 부분에서 ESTIMATEROWS 컬럼값 비교 (SQL 2000에서와 비교)
SELECT * FROM StringTestTable WHERE StrValues LIKE 'AAAA' --2k5:104.2105, 2k:100.0
SELECT * FROM StringTestTable WHERE StrValues LIKE 'CCCC' --2k5: 28.4210, 2k: 50.0
SELECT * FROM StringTestTable WHERE StrValues LIKE 'AA%' --2k5:104.2105, 2k:100.0
SELECT * FROM StringTestTable WHERE StrValues LIKE 'CC%' --2k5: 28.4210, 2k: 50.0
SELECT * FROM StringTestTable WHERE StrValues LIKE '%AA%' --2k5:104.2105, 2k:24.8285
SELECT * FROM StringTestTable WHERE StrValues LIKE '%CC%' --2k5: 28.4210, 2k:24.8285
SELECT * FROM StringTestTable WHERE StrValues LIKE '%AA' --2k5:104.2105, 2k:12.4142
SELECT * FROM StringTestTable WHERE StrValues LIKE '%CC' --2k5: 28.4210, 2k:12.4142
GO
SET STATISTICS PROFILE OFF
GO
--CLEAN UP
/*
USE MASTER
GO
DROP DATABASE TESTDB
GO
*/
반응형