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