count 시 인덱스 문제 0 18 3,674

by 야신 [2013.12.10 11:42:58]


오라클은 11g 고 매출테이블에 인덱스가 지역, 매출일 순으로 잡혀있습니다.
매출일별 count 를 하고자 아래와 같이 조회했더니 금방나왔습니다.

인덱스 : INDEX SKIP SCAN, SORT_GROUP_BY
1)
select 매출일, count(*)
  from 매출
where 매출일 = '20131201'
group by 매출일

그런데 아래와 같이 하였더니 오래걸려도 도통 나올생각을 안합니다.
인덱스 : INDEX FULL SCAN , HASH_GROUP_BY
2)
select 매출일, count(*)
  from 매출
where 매출일 IN ( '20131201','20131202') 
group by 매출일

아래와 같이 하면 또 빠르더군요.

3)
select 매출일, count(*)
  from 매출
where 매출일 = '20131201'
group by 매출일
union all
select 매출일, count(*)
  from 매출
where 매출일 = '20131202'
group by 매출일

2) 번의 SQL 을 3번 한것처럼 할수 있는 힌트가 있을까요? USE_CONCAT  써 보았지만 
먹질 안내요. ㅡ_ㅡ;;

----------------------------
ps) select 매출일, count(*)
          from 매출
     where 매출일 between '20131201' and '20131202' 
     group by 매출일

이렇게 했더니
INDEX SKIP SCAN , HASH GROUP BY 플랜을 거쳐 엄청 빨리 나왔습니다.
IN 이 BETWEEN 보다 낫다고 알고 있었는데 멘붕이 오네요.

ps2 ) 바로 위의 것이 IN 보다는 빨랐지만 3) SQL 보다는 느렸네요.
혹시 오해가 있을까봐 추가합니다.
by 용근님 [2013.12.10 12:29:54]
GROUP BY 구문이 빠져있네요 (원래 있는거 아닌가요?)

sort group by  (  no sort ) 라면 group by 라도 부분 범위처리를 하기때문에 빠르게 보일수는 있습니다.

by 야신 [2013.12.10 13:52:49]
보안상 예제로 바꾸다 보니 group by 가 빠졌네요 ^^;;
예리한 지적 감사합니다.

by 아발란체 [2013.12.10 12:36:31]
원인 파악 되시면 공유 좀 부탁 드립니다 ~ ^.^;

by 야신 [2013.12.12 15:39:55]
여러사항을 찾아보다가 아래의 페이지를 찾았습니다.
10g , 11g 에서는 INDEX SKIP SCAN 시 MIN/MAX를 쓸 수 없다는 내용인데
COUNT 역시 집계합수 이기 때문에 동일한것 같네요

http://scidb.tistory.com/134

by 부쉬맨 [2013.12.10 13:03:39]
기본적으로 데이터의 양이 얼마인지는모르겟지만
multiblock i/o 로 table full 처리가되면 빠를수있습니다.
운반단위가 8개나 16개 정해진대로 올라오기 때문입니다.

아시다시피 in 이 between 보다 빠른것은 맞습니다.

그리고 count(*) 쓰는건 index_ffs 가 젤좋다고 듣엇습니다.

by 야신 [2013.12.10 13:54:19]
1일 데이타건수는 약40만건 정도입니다.

by 마농 [2013.12.10 13:24:45]

지역이 선두컬럼이네요. 그래서 스킵스캔 한거구요.
일단은 힌트를 한번 줘보세요. /*+ INDEX_SS(매출 매출인덱스) */
지역 조건이 없이 일자 조건만 오는 쿼리가 많이 사용된다면?
일자를 선두로 하는 인덱스 추가를 고려해 볼만 합니다.


by 야신 [2013.12.10 15:44:19]
다른서버에 있는 원천 데이타를 확인해 보던 것이라
인덱스 추가 요청까지는 힘들겠네요.
인덱스를 강제로 태워봤는데 HASH_GROUP_BY 로 그냥 탑니다. ㅡㅡ;;

by 야신 [2013.12.12 15:41:27]
http://scidb.tistory.com/134

마농님. INDEX SKIP SCAN 과 집계함수가 동시에 사용될 수 없나 봅니다.
ㅡ_ㅡ;;

by 마농 [2013.12.12 15:52:27]
집계함수 동시사용이 안된다는게 아닌데요?
MIN/MAX 동시사용이 안된다는 거네요.
지금 사용하신거에는 MIN/MAX는 없죠. COUNT 만 있고
또한 1번의 결과만 봐도 스킵스캔을 분명히 했구요.
3번도 했구요..
안타깝게도 2번만 풀스캔을 한거죠.

by 야신 [2013.12.12 16:53:20]
아! 
INDEX SKIP SCAN 했다고 해 놓고도 제가 착각했습니다.
답을 찾을려다가 억지로 끼워 맞출라고 했나봐요...ㅡㅡ;

by feelie [2013.12.10 15:43:45]

1번쿼리가 늦은 이유는 인덱스 스킨스캔을 수행했지만, sort group by 을 수행하기때문에 수행속도가 늦습니다.

2.번쿼리가 좀 빠른 이유는 인덱스 풀스켄(multiblock read)을 수행하고 hash group by을 수행하였기 때문에
정렬을 수행하지 않기 떄문에 빠릅니다.

3번쿼리가 빠른 이유는 플랜이 없어 확신 할수는 없지만, 아마도 이미 데이터가 메모리에 있기때문이 아닐까 생각합니다.

지역의 로우수가 많지 않다면 서버쿼리로 지역을 조회하는것도 테스트 해볼만 한것 같습니다.
where 지역 in ( select 지역 from 지역_table)
and 매출일 = '20131201'

이부분을 보니 저도 궁금한점이 있네요...

sort group by로 수행되는 쿼리를 hash_group by로 수행하기 위해서  파라메터(_gby_hash_aggregation_enabled) 값으로 조정할수 있다고 알고 있는데요...

반대의 경우는 어떻게 해야 할까요?? ( 파라메터 값 말고...)
위의 질문을 보면
1번 쿼리가 인덱스 스킨스캔과 sort group by 을 수행했고
파라메터는 변경하지 않고 2번을 수행한 경우 hash group by을 수행했는데요...
(파라미터 변경에 대한 말씀이 없는것으로 봐서 아마도 파라미터 변경은 없는것 같습니다)

위의 경우만 생각했을떄 _gby_hash_aggregation_enabled = true 을 전제로 하고,
인덱스스킨스캔-sort group by을 인덱스 스킨스캔-hash groupby 로 변경한다면
성능이 좋을것 같은데요...
파라미터외에 다른 방법을 혹시 아시는 분 있나요????

by 마농 [2013.12.10 15:55:01]
1, 3 번이 빠르고, 2번이 느린거네요.
원인은 인덱스 스킵스캔을 하느냐 못하느냐의 차이인것 같습니다.
그룹바이 방식 때문은 아닌듯 생각되네요.

1번이 그룹바이가 해쉬가 아닌 소트로 풀린 이유는 이퀄조건으로 단일값이기 때문에
소트 부하가 없기 때문이겠죠. 오히려 해쉬함수가 부담될 듯.

by feelie [2013.12.10 17:52:05]

이부분도 참고해 보세요...
http://scidb.tistory.com/entry/치명적인-Hash-Group-By-버그


by 마농 [2013.12.12 16:04:20]
위에 feelie 님이 제시하신 지역테이블을 IN 조건으로 주는 방법도 해보셨나요? 결과는?

by 야신 [2013.12.12 19:15:25]
아래와 같은 SQL 로 돌려봤습니다.
4) select 매출일, count(*)

  from 매출
where 지역 in ( select 지역 from 지역리스트 ) 
  and 매출일 in ( '20131201','201302')

group by 매출일

HASH GROUP BY
NESTED LOOPS
SORT_UNIQUE
  →TABLE ACESS FULL : 지역리스트
INLIST ITERATOR
  
→ INDEX RANGE SCAN : 매출


위와 같이 타네요


by 마농 [2013.12.13 09:41:03]

INLIST 로 인덱스 정상적으로 타네요.
스킵스캔 한것처럼 속도가 개선되었죠?


by 야신 [2013.12.14 10:04:34]
비록 Inlist로 범위는 범위를 탄것처럼 보이긴 하는데 속도체감이 별로 느껴지지 안네요. ㅡㅡ;
아마도 Hash_group_by 영향 같습니다
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입