오라클 count 튜닝 - olakeul count tyuning

테스트를 위해 아래와 같이 테이블과 인덱스를 생성하자. 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 인덱스를 삭제해보자.

-- 3
DROP INDEX 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(*)를 빠르게…

인기강좌 --> 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를 했을때의 결과라고
올려 놓은신건데 참고하세요...

-----------------------------------------------------------
힌트를 사용한 성능향상 테스트(http://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
• 댓글쓰기
• 답글쓰기