꿈꾸는 개발자, DBA 커뮤니티 구루비

NULLABLE 조회에대한 개선방법 찾기



CREATE TABLE NULL_T2
(
  login_id NUMBER NOT NULL,
  userid VARCHAR2(10) NOT NULL,
login_date VARCHAR2(8)
) ;

INSERT INTO NULL_T4 VALUES ( 1,'user1','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,'user1','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') ;

COMMIT ;


--NOT NULL & NULL 모든 데이터 COUNT
SQL> SELECT COUNT(*)
  2  FROM null_t4
  3  WHERE userid = 'admin' ;

  COUNT(*)
----------
         5

Elapsed: 00:00:00.00


SQL> SELECT COUNT(1)
  2  FROM null_t4
  3  WHERE userid = 'admin' ;

  COUNT(1)
----------
         5

Elapsed: 00:00:00.00

--COUNT()에 NOT NULL 컬럼으로 COUNT

SQL> SELECT COUNT(login_id)
  2  FROM null_t4
  3  WHERE userid = 'admin' ;

COUNT(LOGIN_ID)
---------------
              5 ---> NOT NULL 속성으로 만들어진 컬럼이므로, 항상 모든 데이터를 COUNT 한다.

Elapsed: 00:00:00.00

- COUNT()에 NULLABLE 컬럼으로 COUNT

SQL> SELECT COUNT(login_date)
  2  FROM null_t4
  3  WHERE userid = 'admin' ;

COUNT(LOGIN_DATE)
-----------------
                0 ---> NULLABLE 컬럼은 NULL 인 데이터를 제외한 로우만 COUNT 한다.

Elapsed: 00:00:00.00
SQL>

10g 이전 버전 : COUNT 함수에서 사용되는 컬럼이 인덱스에 존재하지 않는다면 테이블 액세스가 발생
10g 이후 : COUNT 함수에서 처리되는 컬럼의 NOT NULL 제약 조건이 존재하는지에 따라 테이블 액세스 여부가 결정된다.



SQL> SELECT COUNT(login_id)
  2  FROM NULL_T4
  3  WHERE userid = 'admin' ;

COUNT(LOGIN_ID)
---------------
              5

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4073186510

------------------------------------------------------------------------------------
| 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 |
------------------------------------------------------------------------------------

SQL> SELECT COUNT(login_date)
  2  FROM NULL_T4
  3  WHERE userid = 'admin' ;

COUNT(LOGIN_DATE)
-----------------
                0

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 912975890

-----------------------------------------------------------------------------------------------
| 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 |
-----------------------------------------------------------------------------------------------