대용량 테이블 전체 건수 조회

한 테이블에 건수가 많으니 카운팅도 오래 걸린다.

힌트나 실행계획을 이해를 해야 하는데, 이렇게 찾아보고 있다.

나는 천만단위 데이터에서 헉헉대고 있는데, 고수분들은 억단위까지는 커버하시는구나. 대단하다..

책을 한번 찾아봐야 할것 같다.

 

아래 2개 블로그의 케이스 모두 효과를 확인하였다.

현재 나의 상황에서는 데브포유 님의 케이스가 가장 빠른 효과를 보여주었다.

정희락님의 케이스도 준수한 속도를 보여주었다. GOOD.

 

추후에 비슷한 케이스가 있을 때 참조하기 위해 2가지 모두 기록해둔다.

고수님들 감사합니다.

 

출처: https://dev4u.tistory.com/248

인덱스를 이용해서 index fast full scan + parallel로 하는 방법입니다.

----------------------------------------------------------------------------

select /*+ index_ffs(a 인덱스명) parallel_index(a 인덱스명) parallel(a 4) */ count(인덱스컬럼명)

from 테이블명 a;

 

환경마다 다르겠지만 회사에서 3억건이 있는 테이블 조회시 24초 정도 걸리더군요.

출처 : https://hrjeong.tistory.com/310

테스트를 위해 아래와 같이 테이블과 인덱스를 생성하자. 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 칼럼이 포함된 인덱스의 존재 여부에 따라 힌트를 달리 기술하는 것은 번거로운 일이다. 이런 경우 아래와 같은 힌트를 사용할 수 있다. 모순되는 힌트지만 특수한 경우로 이해하자.

 

-- 5
SELECT /*+ INDEX_FFS(T1) PARALLEL_INDEX(T1 4) 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       |
-------------------------------------------

 

 

t1_x1 인덱스를 다시 생성해보자.

 

-- 6
CREATE INDEX t1_x1 ON t1 (c1, c2);

 

 

쿼리를 다시 수행하면 T1_X1 인덱스를 FFS하는 것을 확인할 수 있다.

 

-- 7
SELECT /*+ INDEX_FFS(T1) PARALLEL_INDEX(T1 4) 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 |       INDEX FAST FULL SCAN| T1_X1    |
----------------------------------------------