SQL datetime 인덱스 질문입니다

프로그래밍 일반에 관한 포럼입니다.

운영자: 류광

Locked
비회원

SQL datetime 인덱스 질문입니다

전체글 글쓴이: 비회원 » 2008-08-24 12:59

MSSQL 에서 datetime 컬럼이 있습니다
이컬럼을 index 로 잡았습니다

근데 어디선가 본적이 있어서 질문드립니다

인덱스를 가공하게 되면 인덱스가 깨진다고했습니다
예를 들어
select * from testtable
where max(iCode) > 10
=> 해당 쿼리가 말이안되지만 막상 생각이 나는 예제가 없어서 억지로 끼어맞춘겁니다

이런식으로 인덱스인 컬럼의 데이타를 가공하게되면 인덱스 처리가 안된다고 알고있습니다

이런점은 이해가 되는데

datetime 일때
select * from testtable
where createDate = '2008-02-02' => createDate 는 datetime 입니다
이럴때는 실제 createDate 는 datetime 형식이므로 위와같은 데이타가 들어있지 않습니다
그러므로 내부에서 가공이 일어난후에 적용이 됩니다 (그냥 제생각입니다)
그렇다면 인덱스인 컬럼을 가공했으로 인덱스가 적용이 안된다고 알고있는데..
이런경우에도 맞는건가요?

비회원

Re: SQL datetime 인덱스 질문입니다

전체글 글쓴이: 비회원 » 2008-08-25 15:34

비회원 작성:MSSQL 에서 datetime 컬럼이 있습니다
이컬럼을 index 로 잡았습니다

근데 어디선가 본적이 있어서 질문드립니다

인덱스를 가공하게 되면 인덱스가 깨진다고했습니다
예를 들어
select * from testtable
where max(iCode) > 10
=> 해당 쿼리가 말이안되지만 막상 생각이 나는 예제가 없어서 억지로 끼어맞춘겁니다

이런식으로 인덱스인 컬럼의 데이타를 가공하게되면 인덱스 처리가 안된다고 알고있습니다

이런점은 이해가 되는데

datetime 일때
select * from testtable
where createDate = '2008-02-02' => createDate 는 datetime 입니다
이럴때는 실제 createDate 는 datetime 형식이므로 위와같은 데이타가 들어있지 않습니다
그러므로 내부에서 가공이 일어난후에 적용이 됩니다 (그냥 제생각입니다)
그렇다면 인덱스인 컬럼을 가공했으로 인덱스가 적용이 안된다고 알고있는데..
이런경우에도 맞는건가요?

select * from testtable
where createDate = '2008-02-02'

이 쿼리는 MSSQL 2000에서는 createDate 컬럼이 varchar (혹은 char)형태로 캐스팅될 겁니다.
그렇게 되면 인덱스를 탈 수 없지요..

MSSQL 2005에서는 타입에 대해서 캐스팅 우선 순위 비슷한 것이 있는 걸로 압니다.
그래서 createDate가 varchar가 될 지 '2008-02-02'가 datetime으로 변경될지 알 수 없습니다.
(이건 기억이 좀 가물가물 합니다. -_-;;;;)

여튼 확실한 것은
select * from testtable
where createDate = cast('2008-02-02' as datetime)
형태로 캐스팅해서 사용하시는 것이 바람직해 보입니다.

비회원

Re: SQL datetime 인덱스 질문입니다

전체글 글쓴이: 비회원 » 2008-08-25 16:09

비회원 작성:MSSQL 에서 datetime 컬럼이 있습니다
이컬럼을 index 로 잡았습니다

근데 어디선가 본적이 있어서 질문드립니다

인덱스를 가공하게 되면 인덱스가 깨진다고했습니다
예를 들어
select * from testtable
where max(iCode) > 10
=> 해당 쿼리가 말이안되지만 막상 생각이 나는 예제가 없어서 억지로 끼어맞춘겁니다

이런식으로 인덱스인 컬럼의 데이타를 가공하게되면 인덱스 처리가 안된다고 알고있습니다

이런점은 이해가 되는데

datetime 일때
select * from testtable
where createDate = '2008-02-02' => createDate 는 datetime 입니다
이럴때는 실제 createDate 는 datetime 형식이므로 위와같은 데이타가 들어있지 않습니다
그러므로 내부에서 가공이 일어난후에 적용이 됩니다 (그냥 제생각입니다)
그렇다면 인덱스인 컬럼을 가공했으로 인덱스가 적용이 안된다고 알고있는데..
이런경우에도 맞는건가요?
(그냥 제생각입니다)
생각하지 마시고 실행계획을 보세요.
정말 인덱스를 안타는지...

비회원

테스트 해봤습니다

전체글 글쓴이: 비회원 » 2008-08-25 16:49

코드: 모두 선택

create table test
(
   iIdx int,
   date datetime
)

데이타 10개 삽입

create index IDX_test on test(date)

select * from test where = '2008-08-02'
select * from test where = cast('2008-08-02' as datetime)
이 두방법으로 실행을 해보니까 둘다 table scan 으로 나옵니다
table scan 이면 인덱스를 안타는거 아닌가요?

비회원

Re: 테스트 해봤습니다

전체글 글쓴이: 비회원 » 2008-08-25 17:43

비회원 작성:

코드: 모두 선택

create table test
(
   iIdx int,
   date datetime
)

데이타 10개 삽입

create index IDX_test on test(date)

select * from test where = '2008-08-02'
select * from test where = cast('2008-08-02' as datetime)
이 두방법으로 실행을 해보니까 둘다 table scan 으로 나옵니다
table scan 이면 인덱스를 안타는거 아닌가요?
table scan이면 인덱스는 안타는 것입니다.
인덱스를 본다면 index scan이나 index seek가 될 것입니다.

인덱스를 탈 것이냐 아니냐는 옵티마이저가 결정합니다.
테스트 셋이 10개 정도라면 옵티마이저가 table scan이 비용이 적다고 판단할 수도 있습니다.

10000개 정도 레코드 추가해서 테스트를 해보니
두 경우 모두 index seek를 하네요.

다만, 전자의 경우는 항상 index seek를 한다고 보장이 되지는 않을 것 같네요.

비회원

Re: SQL datetime 인덱스 질문입니다

전체글 글쓴이: 비회원 » 2008-08-25 19:25

비회원 작성: datetime 일때
select * from testtable
where createDate = '2008-02-02' => createDate 는 datetime 입니다
이럴때는 실제 createDate 는 datetime 형식이므로 위와같은 데이타가 들어있지 않습니다
그러므로 내부에서 가공이 일어난후에 적용이 됩니다 (그냥 제생각입니다)
그렇다면 인덱스인 컬럼을 가공했으로 인덱스가 적용이 안된다고 알고있는데..
이런경우에도 맞는건가요?
select * from testtable
where cDate BETWEEN '2008-02-02 00:00:00.000' and '2008-02-03 00:00:00.000'

정도로 바꿔 써 주시는 정도의 정성은 들여주세요.
어려운거 아닙니다.

비회원

MSSQL DBA로서...

전체글 글쓴이: 비회원 » 2009-06-26 10:24

제가 알기로는.. operand 오른쪽 값을 변형하는 것은 괜찮지만, 왼쪽을 변형하면 index를 사용하지 못합니다.
테스트 해보신 분의 말씀이 거의 정확하지만, 부연하자면 'yyyy-mm-dd' 형태를 where 절에서 비교하거나 cast된 것을 비교하거나 일정정도의 data가 2%미만으로 분포하고 있다면, index seek 이 될 듯 합니다.

'yyyy-mm-dd' 형태에서 type이 다르므로 index를 사용하지 못할 거라는 여러분들의 예상이 어긋난 것은 mssql 이 type cast를 자동으로 하기 때문입니다. 'yyyy-mm-dd' 형태의 문자열을 ''yyyy-mm-dd 00:00:00.000'의 datetime type으로 자동으로 type cast가 발생합니다. 물론 위와 같은 간단한 형태는 옵티마이저란 놈이 알아서 처리해 주지만, 복잡한 형태의... 이를테면 여러개의 함수로 둘러쌓인 형태... 경우는 알아서 해주지 못합니다.

기본적으로 비교하는 data type은 서로 같은 것을 사용해야 나중에 황당한 꼴을 당하지 않습니다.

비회원

덧붙여서..

전체글 글쓴이: 비회원 » 2009-06-26 10:30

select *
from testtable
where createDate = '2008-02-02'

위의 sql이 원하는 바가 '2008-02-02 00:00:00.000' 의 row를 return 하는 것이 목적이면 예상되로 될 것이지만... '2008-02-02' 날의 모든 row를 return 하는 것이라면, 아래와 같이 수정하는 것이 의도된 바를 제대로 반연한 것이라고 할 수 있습니다.

select *
from testtable
where createDate between convert(datetime, '2008-02-02 00:00:00.000') and convert(datetime, '2008-02-02 23:59:59.997')

convert(datetime, '2008-02-02 23:59:59.997') 이라고 마지막 조건을 명시한 것은 밀리세컨드 단위에서 .999는 .998은 경우에 따라서 다음날로 넘어가는 일이 발생합니다. 문서에서는 무슨무슨 어쩌구저쩌구.. 설명은 하는데... 귀찮아서 pass 해서 저도 정확한 이유는 잘 모릅니다.(--a;;)

ened
전체글: 30
가입일: 2007-10-05 16:07

Re: 덧붙여서..

전체글 글쓴이: ened » 2009-06-26 17:49

비회원 작성:select *
convert(datetime, '2008-02-02 23:59:59.997') 이라고 마지막 조건을 명시한 것은 밀리세컨드 단위에서 .999는 .998은 경우에 따라서 다음날로 넘어가는 일이 발생합니다. 문서에서는 무슨무슨 어쩌구저쩌구.. 설명은 하는데... 귀찮아서 pass 해서 저도 정확한 이유는 잘 모릅니다.(--a;;)
MS SQL 2005 이하 버전에서 날짜 오차버그가 있다고 해서 발생되는걸로 알고 있습니다.
SQL 2008 버전에서 수정되었다고 어디서 주워들은 기억이 있습니다만,
현재 급한 용무로 찾아드리기 어렵네요. :(
KISS; Keep It Simple, Stupid

비회원

Re: 덧붙여서..

전체글 글쓴이: 비회원 » 2009-11-11 22:06

ened 작성:
비회원 작성:select *
convert(datetime, '2008-02-02 23:59:59.997') 이라고 마지막 조건을 명시한 것은 밀리세컨드 단위에서 .999는 .998은 경우에 따라서 다음날로 넘어가는 일이 발생합니다. 문서에서는 무슨무슨 어쩌구저쩌구.. 설명은 하는데... 귀찮아서 pass 해서 저도 정확한 이유는 잘 모릅니다.(--a;;)
MS SQL 2005 이하 버전에서 날짜 오차버그가 있다고 해서 발생되는걸로 알고 있습니다.
SQL 2008 버전에서 수정되었다고 어디서 주워들은 기억이 있습니다만,
현재 급한 용무로 찾아드리기 어렵네요. :(
오차버그가 아니고 MSSQL의 DATETIME의 경우 정확도가 3.33밀리초이고 SMALLDATETIME은 1분이기 때문입니다
2008에서부터는 DATETIME2 자료형이 새로 나왔고 이 자료형의 정확도가 100나노초 이기 때문에 23:59:59.9999999 까지 표현 가능합니다. 2008에서라도 DATETIME이면 .999 .998의 경우 다음날로 넘어갑니다.

Locked