테스트를 위해 아래와 같이 테이블과 인덱스를 생성하자. c1 칼럼은 NOT NULL 제약 조건으로 생성했다. t1_x1 인덱스는 NOT NULL 제약 조건 칼럼이 포함되어 있고, t1_x2 인덱스는 그러지 않다.
-- 1 DROP TABLE t1 PURGE; CREATE TABLE t1 (c1 NOT NULL, c2, c3) AS SELECT LPAD ('X', 10, 'X') AS c1 , LPAD ('X', 10, 'X') AS c2 , LPAD ('X', 100, 'X') AS c3 FROM XMLTABLE ('1 to 100000'); CREATE INDEX t1_x1 ON t1 (c1, c2); CREATE INDEX t1_x2 ON t1 (c2);
아래 쿼리는 COUNT (*) 표현식을 사용했다. NOT NULL 칼럼이 포함된 인덱스가 존재하는 경우 크기가 가장 작은 인덱스를 사용하는 실행 계획이 수립된다. t1_x2 인덱스는 t1_x1 인덱스보다 크기가 작지만 널 값을 포함하지 않으므로 테이블 전체 건수를 조회하는데 사용할 수 없다.
-- 2-1 SELECT COUNT (*) FROM t1; --------------------------------------- | Id | Operation | Name | --------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | INDEX FAST FULL SCAN| T1_X1 | ---------------------------------------
대용량 테이블인 경우 INDEX_FFS 힌트와 PARALLEL_INDEX 힌트를 사용하면 테이블 전체 건수를 빠르게 조회할 수 있다.
-- 2-2 SELECT /*+ INDEX_FFS(T1) PARALLEL_INDEX(T1 4) */ COUNT (*) FROM t1; ---------------------------------------------- | Id | Operation | Name | ---------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | PX COORDINATOR | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | | 4 | SORT AGGREGATE | | | 5 | PX BLOCK ITERATOR | | |* 6 | INDEX FAST FULL SCAN| T1_X1 | ----------------------------------------------
t1_x1 인덱스를 삭제해보자.
2-1번 쿼리를 다시 수행해보면 테이블을 FULL SCAN하는 것을 확인할 수 있다. 사용할 수 있는 인덱스가 없기 때문이다.
-- 4-1 SELECT COUNT (*) FROM t1; ----------------------------------- | Id | Operation | Name | ----------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| T1 | -----------------------------------
이런 경우에는 FULL 힌트와 PARALLEL 힌트를 사용해야 한다.
-- 4-2 SELECT /*+ FULL(T1) PARALLEL(T1 4) */ COUNT (*) FROM t1; ------------------------------------------- | Id | Operation | Name | ------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | PX COORDINATOR | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | | 4 | SORT AGGREGATE | | | 5 | PX BLOCK ITERATOR | | |* 6 | TABLE ACCESS FULL| T1 | -------------------------------------------
NOT NULL 칼럼이 포함된 인덱스의 존재 여부에 따라 힌트를 달리 기술하는 것은 번거로운 일이다. 이런 경우 아래와 같은 힌트를 사용할 수 있다. 모순되는 힌트지만 특수한 경우로 이해하자.
[오라클Tip] [일반] COUNT(*)를 빠르게…
작성자이종철|작성시간12.07.26|조회수171 목록 댓글 0 글자크기 작게가 글자크기 크게가인기강좌 --> SQL초보에서전문가까지, 오라클초보에서전문가까지, Unix&Pro*C
www..oraclejava3.co.kr 오라클 강좌에서 확인하세요.
(분당정자점은 양재, 강남에서 15분 거리에 있습니다. ^^)
COUNT(*)를 빠르게…
개발을 하다보면 흔히 어떤 테이블에 원하는 데이터가 있는지 세어보는 경우가 가끔 있습니다. 이러한 경우 COUNT(*)를 사용하는데 좀더 빠르게 할 수 있는 방법이 있어 소개 드립니다.
COUNT를 할 때 전체 테이블을 FULL SCAN 할 수도 있고 INDEX 영역을 FULL SCAN할 수도 있습니다. TABLE을 전체 FULL SCAN 할 때와 INDEX영역을 FULL SCAN할 때 어디가 빠르다고는 장담하지 못합니다. 이러한 경우 테이블 이나 인덱스의 단일 블록을 SCAN하기 때문에 예측하기가 애매할 수 있는 것이죠... 만약 인덱스를 SCAN하는데 하나의 블록이 아니라 MULTI BLOCK을 SCAN한다면 어떨까요? 당연히 SINGLE BLOCK을 SCAN하는 것보다 빠를 수 있습니다. 이때 사용하는 것이 INDEX FAST SCAN이라는 것인데 COUNT하는 경우 당연히 속도가 빨라 집니다.힌트 INDEX_FFS를 이용하면 되는데 아래의 예를 참고하세요~
SQL)SELECT COUNT(*) FROM EMP;
Execution Plan
------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF ‘EMP’
SQL)SELECT /*+ INDEX(EMP pk_emp */ COUNT(*) FROM EMP where empno > 0 ;
Execution Plan
------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX FULL SCAN OF ‘pk_emp’ (UNIAUE) (cost=30)
---------------------------------------------
[아래처럼 인덱스 패스트 스캔을 이용하여 count 하세요]
---------------------------------------------
SQL)SELECT /*+ INDEX_FFS(EMP pk_emp) */ COUNT(*) FROM EMP where empno > 0;
Execution Plan
------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF ‘pk_emp’ (UNIQUE) (cost=2)
아래는 어떤 분이 50000건의 데이터로 간단히 인덱스를 이용하는것으로 count를 했을때의 결과라고
올려 놓은신건데 참고하세요...
-----------------------------------------------------------
힌트를 사용한 성능향상 테스트(//cafe.naver.com/projectprogramming.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=118)
-----------------------------------------------------------
50000만 건을 입력하고 전체 카운터를 가져오는 테스트를 해보겠다.
여기서의 소요시간은 서버환경이나 측정하는 방법에 따라 달라 질 수 있다. 하지만 상대적으로 비교해 볼 수는 있는 것이니 어떤 효과가 있는지를 알기에는 충분하다고 생각한다.
1. select count(idx) idx from 테이블명
- 소요시간 : 203ms
2. select /*+ index(테이블명 인덱스명) */ count(idx) idx from 테이블명
- 소요시간 : 15ms
다음검색
현재 게시글 추가 기능 열기
- 북마크
- 공유하기
- 신고하기
댓글
- 댓글 0
- 댓글쓰기
- 답글쓰기