* 클러스터 인덱스 PK를 NON 클러스터로 바꾸고, 파티션 인덱스를 클러스터로 바꾸는것은 어떨까요?
저번에 복제 테이블 파티션 때문에 질문을 드렸는데요.
해당업체에서 테이블 파티션 기능을 이용안하려고 합니다.
일단 데이터를 건드리는것이 불안하고, 안 써본 기능이라서, 사용하기가 불편하신것 같습니다.
그래서 파티션 인덱스를 만들어서, 해당 파티션인덱스만 타고 테이블액세스까지 안 가게 해서
동일한 효과를 봤는데, 아예 파티션 인덱스를 클러스터 인덱스로 만드는건 어떨까 하는 생각이 들었습니다.
그러면 데이터 자체도 파티션 인덱스의 데이터 순서대로 다시 정렬된다고 생각이 들어서요.
기존 PK 클러스터 : 사용자ID + 기관번호 + 처리번호
파티션 인덱스 : 검색일자 + 사용자ID + 기관번호 + 처리번호 INCLUDE (회사코드)
기존 PK 클러스터인덱스의 조건을 순서대로 검색하는 경우는 거의 없고
모두 검색일자가 반드시 들어갑니다.
이렇게 바꾸는 것이 문제가 없는지, 또 불필요한 것인지등, 많은 조언좀 부탁드리겠습니다.
Comment 5
-
방랑도사
2013.02.27 14:55
-
하흥수
2013.02.27 17:41
네 조언 감사합니다.
클러스터인덱스는 리프에 데이터가 들어가니까, include절이 없는걸 미처 생각못했네요. ^^
non클러스터인덱스가 있는 상태에서 cluster index 생성이나 리빌드는 주의해야겠군요.
반드시 non클러스터 인덱스를 삭제하고 해야겠습니다. (unusable이 가능하면 unusable로 놓고나서 리빌드하면 좋은데)
그리고 검색일자만 넣고 조회하는 경우가 대부분이고,
검색일자 + 다른조건 을 입력하는 경우는 union all로 따로 분리를 시켰습니다.
in 조건의 경우는 어떻게 주면 되는 건가요?
where 검색일자 between '20130227' and '20130228'
and 기관코드 in ( '10001','10002','10003',....)
...
이런식으로 해도 검색일자 다음의 기관코드까지 인덱스를 타는건가요???? 궁금합니다. ^^
-
차태욱
2013.02.27 17:53
where 검색일자 between '20130227' and '20130228'
and 기관코드 in ( '10001','10002','10003',....) 조건으로 검색을 하신다면,인덱스가 말씀하신 두개 밖에 없다는 가정 하에.....
1. 검색일자 + 사용자ID + 기관번호 + 처리번호 로 클러스터드 인덱스를 변경 했을 경우,
--> 일자까지만, Seek 조건으로 Range Scan에 사용되고,
기관코드는 Clustered Index 의 Filter조건으로 사용됩니다.
2. 인덱스 변경을 하지 않았을 경우,
--> 기존의 NC 인덱스에 날짜를 Seek 조건으로 Range Scan 하고,
기관코드를 Filter 조건으로 읽은 후, Bookmark Lookup 할 수도 있고,,,
--> 예상되는 건수가 굉장히 많다면,,,Clustered Index Scan을 할 수도 있겠네요Seek 후 Range Scan의 필터조건으로 사용 되기에,,, 기관코드까지는 인덱스를 안탄다고 봐야겠네요 ^^
-
방랑도사
2013.02.27 18:44
http://blog.daum.net/pilgrimfortruth/2682 여기에서 중간에 있는 "인덱스 선행 칼럼이 범위조건일 때의 비효율" 부분을 읽어보시면 도움이 될듯합니다. -
하흥수
2013.02.28 09:31
감사합니다. 무슨 말인지 이해했습니다. ^^
에..비슷한경험이 있는데요. 대략 4천만건정도 되는 테이블을 연도별 파티션 나누고, 넌클러스터 인덱스 다시 잡고,, 완전히 마물될때까지 2시간정도 걸렸던거 같네요.
클러스터 인덱스를 파티션잡으면 말씀하신대로 데이터가 재정렬됩니다. 질문에 "파티션 인덱스 : 검색일자 + 사용자ID + 기관번호 + 처리번호 INCLUDE (회사코드) " 라고 하셨는데 클러스터의 리프는 데이터이므로 포괄열을 할필요도 없고 지정할수도 없습니다.
테이블 크기가 크고 분할기준이 명확하면 파티션처리가 유리하겠지요.
전 작업시에 넌클러스터 인덱스를 드랍하고 , 클러스터 파티션기준으로 리빌드하고 ,다시 넌클러스터 인덱스를 잡았습니다.
테스트시에 바로 클러스터인덱스 리빌드를 하니 늦어졌거든요. 추측에 리빌드과정에서 넌클러스터인덱스들의 주소가 클러스터->힙->클러스터 변동이 되어서 그런가 싶습니다.
조회시에 검색일자는 반드시 들어가지만 검색일자만 있는경우도 있나요? 검색일자가 인덱스 맨앞에있는 상태에서 검색일자가 between 조건이면 뒤에 조건이 있어도 인덱스 안타고 체크조건으로만 쓰이니 유의하셔야 합니다.
저같은경우 날짜뒤에 조건(질문자님께는 사용자ID ,기간번호) 이 있냐 없냐에 따라 UNION ALL 로 실행계획을 분리시켰습니다. 일자 뒤에 조건이 없다면 그냥 일자만 스캔하도록 하고 , 조건이 있다면 IN 조건으로 만들어서 뒤에까지 인덱스가 먹히도록 했습니다.
작업 잘 마무리 하시구요. 반드시 테스트디비에서 테스트해보시길 바랍니다.