database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
ㆍMS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MS-SQL Q&A 5852 게시물 읽기
No. 5852
MSSQL2005에서 인덱스를 타지를 않습니다..
작성자
김민석
작성일
2011-03-08 15:50
조회수
6,761

안녕하세요.

현재 MSSQL2000에서 MSSQL2005로 마이그레이션을 했습니다.

컬럼속성, 인덱스등 모두같은데요..

 SELECT SIDO, GUGUN, ZIPCODE
    , ADDRESS
    , DBO.TEXT_SPLIT(ZIPCODE, '-', 1) ZIPCODE1
    , DBO.TEXT_SPLIT(ZIPCODE, '-', 2) ZIPCODE2
FROM DBO.TS_ZIPCODE_TB (NOLOCK)
WHERE SIDO = '서울'
 AND GUGUN = '강남구'

 

MSSQL 2000에서 IDX_TS_ZIPCODE_TB_02 -(SIDO,GUGUN 으로 결합인덱스) : Index Seek 이리타구요..

MSSQL 2005에서 IDX_TS_ZIPCODE_TB_02 -(SIDO,GUGUN 으로 결합인덱스) : Clustered Index Scan 이리탑니다..

그래서 강제로 WITH (INDEX (IDX_TS_ZIPCODE_TB_02)) 인덱스를 타게하면 실행계획에 Index Seek 나옵니다..

왜그런건가요??

답변좀 부탁드립니다..

*통계정보

 

 

 실행계획

 

31,1,SELECT SIDO, GUGUN, ZIPCODE
    , ADDRESS
    , DBO.TEXT_SPLIT(ZIPCODE, '-', 1) ZIPCODE1
    , DBO.TEXT_SPLIT(ZIPCODE, '-', 2) ZIPCODE2
FROM DBO.TS_ZIPCODE_TB
WHERE SIDO = '서울'
 AND GUGUN = '강남구',1,1,0,NULL,NULL,NULL,NULL,24.05578,NULL,NULL,NULL,0.06190631,NULL,NULL,SELECT,0,NULL
31,1,  |--Compute Scalar(DEFINE:([Expr1004]=[TOWNSTORY].[dbo].[TEXT_SPLIT]([TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[ZIPCODE],'-',(1)), [Expr1005]=[TOWNSTORY].[dbo].[TEXT_SPLIT]([TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[ZIPCODE],'-',(2)))),1,2,1,Compute Scalar,Compute Scalar,DEFINE:([Expr1004]=[TOWNSTORY].[dbo].[TEXT_SPLIT]([TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[ZIPCODE],'-',(1)), [Expr1005]=[TOWNSTORY].[dbo].[TEXT_SPLIT]([TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[ZIPCODE],'-',(2))),[Expr1004]=[TOWNSTORY].[dbo].[TEXT_SPLIT]([TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[ZIPCODE],'-',(1)), [Expr1005]=[TOWNSTORY].[dbo].[TEXT_SPLIT]([TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[ZIPCODE],'-',(2)),24.05578,0,2.405578E-06,133,0.06190631,[TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[ZIPCODE], [TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[SIDO], [TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[GUGUN], [TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[ADDRESS], [Expr1004], [Expr1005],NULL,PLAN_ROW,0,1
31,1,       |--Clustered Index Scan(OBJECT:([TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[IDX_TS_ZIPCODE_TB_01]), WHERE:([TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[SIDO]='서울' AND [TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[GUGUN]='강남구')),1,3,2,Clustered Index Scan,Clustered Index Scan,OBJECT:([TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[IDX_TS_ZIPCODE_TB_01]), WHERE:([TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[SIDO]='서울' AND [TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[GUGUN]='강남구'),[TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[ZIPCODE], [TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[SIDO], [TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[GUGUN], [TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[ADDRESS],24.05578,0.04979167,0.0067988,109,0.05659047,[TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[ZIPCODE], [TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[SIDO], [TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[GUGUN], [TOWNSTORY].[dbo].[TS_ZIPCODE_TB].[ADDRESS],NULL,PLAN_ROW,0,1
 

[Top]
No.
제목
작성자
작성일
조회
5856필드명이 아닌 데이터로만 조회 가능할까요? [1]
김태희
2011-03-15
5992
5855프로시저 파라미터 질문 [2]
이은택
2011-03-14
6782
58542개이상중복되는 Row 얻어오는방법이있을까요.. [1]
육식동물
2011-03-14
6770
5852MSSQL2005에서 인덱스를 타지를 않습니다..
김민석
2011-03-08
6761
5850긴급_도와주세요 [1]
홍광표
2011-02-28
6697
5849where 절 질문입니다. [3]
초롱이
2011-02-28
6956
5847테이블 결과 합치기 가능한가요? [1]
최지훈
2011-02-23
8062
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.016초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다