인덱스 정보

INDEX NAME       COLUMN_LIST     Type
---------------  --------------- ----------
IDX_ADDRGRP_01   UKEY            Unique
IDX_ADDRGRP_02   USERID, GNAME   Normal
IDX_ADDRGRP_03   USERID, UKEY    Normal

성능 문제 SQL - DB Version: 9.2.0.4

SQL> SELECT a.gname,
           ,COUNT(a.ukey) AS cnt
     FROM  imsi.addrgrp   a
     WHERE a.userid       = :B1
     GROUP BY a.gname ;

SELECT STATEMENT CHOOSE-Cost : N/A
 SORT GROUP BY
  TABLE ACCESS BY INDEX ROWID IMSI.ADDRGRP(l)
   INDEX RANGE SCAN IMSI.IDX_ADDRGRP_02(NU) (USERID, GNAME)

문제점

• 특정 userid가 가진 데이터를 가지고 gname별로 ukey값의 데이터 Row수를 가져 오는 쿼리이다.
  WHERE절에 userid와 SELECT-LIST에 gname 컬럼은 인덱스를 경유하여 데이터를 가지고 왔는데 COUNT하는 과정에서
  ukey값 데이터를 가지고 오기 위해서 Table Access가 발생
• 만일 WHERE절을 통해 나온 결과 값이 1000만건일 경우 Table Access를 하기 위해 많은 Block IO가 발생

개선 후 SQL

SQL> SELECT a.gname,
           --,COUNT(a.ukey) AS cnt
           COUNT(*) AS cnt   <-- 변경
     FROM  imsi.addrgrp   a
     WHERE a.userid       = :B1
     GROUP BY a.gname ;

SELECT STATEMENT CHOOSE-Cost : N/A
 SORT GROUP BY
  INDEX RANGE SCAN IMSI.IDX_ADDRGRP_02(NU) (USERID, GNAME)

해설

• COUNT함수는 NULL 값은 Counting되지 않고 NOT NULL 값만 Counting되는데, 현재 ukey값을 확인 한 결과 NOT NULL 제약조건이
  걸려있기 때문에 특정 컬럼 값이 아닌 WHERE, GROUP BY를 거쳐서 나온 결과 집합을 가지고 COUNT(*), COUNT(1)와 같은 형태로
  변경해 줌으로써, Table Access를 통한 성능 저하를 해소 할 수 있다.

COUNT함수가 NULL데이터 처리 방법

Create Table & Data 생성

SQL> CREATE TABLE NULL_T4
      (login_id NUMBER NOT NULL
      ,userid   VARCHAR2(10) NOT NULL
      ,login_date VARCHAR2(8));

-- Data Insert
INSERT INTO NULL_T4 VALUES ( 1, 'userl', '20100101') ;
INSERT INTO NULL_T4 VALUES ( 2, 'admin', NULL) ;
INSERT INTO NULL_T4 VALUES ( 3, 'user2', '20100301') ;
INSERT INTO NULL_T4 VALUES ( 6, 'user1', '20100601') ;
INSERT INTO NULL_T4 VALUES ( 7, 'admin', NULL) ;
INSERT INTO NULL_T4 VALUES ( 8, 'user3', '20100801') ;
INSERT INTO NULL_T4 VALUES (101, 'user1', '20100102') ;
INSERT INTO NULL_T4 VALUES (102, 'admin', NULL) ;
INSERT INTO NULL_T4 VALUES (103, 'user2', '20100302') ;
INSERT INTO NULL_T4 VALUES (106, 'userl', '20100602') ;
INSERT INTO NULL_T4 VALUES (107, 'admin', NULL) ;
INSERT INTO NULL_T4 VALUES (108, 'user3', '20100802') ;
INSERT INTO NULL_T4 VALUES (201, 'user1', '20100602') ;
INSERT INTO NULL_T4 VALUES (202, 'admin', NULL) ;
INSERT INTO NULL_T4 VALUES (203, 'user2', '20100802') ;

-- Key Commit
COMMIT;

-- 인덱스 생성
SQL> CREATE INDEX IDX_null_t4_01 ON NULL_T4(userid) ;

1). NOT NULL & NULL 모든 데이터 COUNT

Case1)
SQL> SELECT count(*) cnt
     FROM   null_t4
     WHERE  userid = 'admin';

       CNT                   
----------                   
         5

Case2)
SQL> SELECT count(1) cnt
     FROM   null_t4
     WHERE  userid = 'admin';

       CNT                   
----------                   
         5

2). COUNT()에 NOT NULL 컬럼으로 COUNT

SQL> SELECT count(login_id) cnt
     FROM   null_t4
     WHERE  userid = 'admin';

       CNT                   
----------                   
         5

3). COUNT()에 Nullable 컬럼으로 COUNT

SQL> SELECT count(login_date) cnt
     FROM   null_t4
     WHERE  userid = 'admin';

       CNT                   
----------                   
         0

COUNT 함수 선언 시, Table Access 여부의 기준

• Oracle 9i
  : Table Access 여부의 기준은 Counting되는 대상의 컬럼이 인덱스 컬럼, 일반 컬럼 차이
• Oracle 10g
  : Table Access 여부의 기준은 Counting되는 대상의 컬럼의 Type이 Nullable, NOT Nullable 차이

1). NOT NULL 컬럼을 COUNT - Oracle Version: 10.2.0.5

SQL> SELECT count(login_id) cnt
     FROM   null_t4
     WHERE  userid = 'admin';

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |     7 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_NULL_T4_01 |     5 |    35 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
                                                                                    
Predicate Information (identified by operation id):                                 
---------------------------------------------------                                 
                                                                                    
   2 - access("USERID"='admin')                                                     

2). Nullable 컬럼을 COUNT

SQL> SELECT count(login_date) cnt
     FROM   null_t4
     WHERE  userid = 'admin';

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                |     1 |    13 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| NULL_T4        |     5 |    65 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_NULL_T4_01 |     5 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
                                                                                               
Predicate Information (identified by operation id):                                            
---------------------------------------------------                                            
                                                                                               
   3 - access("USERID"='admin')                                                                

정리

• Oracle 9i까지는 COUNT함수를 선언했을 경우 Counting 되는 필드가 인덱스 유무 여부에 따라 Table Access 결정
• Oracle 10g부터는 COUNT함수를 선언했을 경우 Counting 되는 필드가 NOT NULL 제약 조건의 여부에 따라 Table Access 결정
• COUNT함수는 NULL값을 Counting 하지 않는다.