RDBMS NULL

Image

RDBMS VERSION

RDBMS	VERSION	VERSION CHECK SQL
TIBERO	7 FS02 (Compiled at Jun 20 2022 11:29:40 build seq 202489 init rev {2022-03-10})	SELECT * FROM VT_VERSION;
ORACLE	Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production	SELECT * FROM V$VERSION;
MySQL	8.0.29	SELECT VERSION();
MariaDB	10.8.3-MariaDB-1:10.8.3+maria~jammy	SELECT VERSION();
MS SQL Server	Microsoft SQL Server 2022 (CTP2.0) - 16.0.600.9 (X64)	SELECT @@VERSION;
IBM Db2	DB2 v11.5.7.0	SELECT * FROM SYSIBMADM.ENV_INST_INFO;
PostgreSQL	PostgreSQL 14.3 (Debian 14.3-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit	SELECT VERSION();
CUBRID	11.2.0.0658	SELECT VERSION();

NULL

•

NULL의 추상적인 의미

◦

미지의 값

◦

알 수 없는 값

◦

정해지지 않은 값

•

NULL ⇒ 스폐셜 마커

NULL 저장

RDBMS	ORACLE	TIBERO	MySQL	MariaDB	MS SQL Server	PostgreSQL	IBM Db2	SQLite	CUBRID
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
'NULL'	string	string	string	string	string	string	string	string	string
' '	string	string	string	string	string	string	string	string	string
''	NULL	NULL	string	string	string	string	string	string	string

•

'' ⇒ empty 값에 NULL 또는 string으로 RDBMS 마다 다른 경우가 있음

이기종 RDBMS간에 데이터 마이그레이션 작업 수행 시 주의 ORACLE (NULL) ⇒ PostgreSQL(NULL) : 문제 없음 PostgreSQL('') ⇒ ORACLE(NULL) : '' 값이 NULL로 치환 되어 결과가 달라지는 문제가 발생할 수 있음 // '' 값을 입력 시 ORACLE NOT NULL 제약 조건에 의해 입력 실패 할 수 있음 (사전 협의 필요)

NULL 저장 SQL

NULL 크기

•

정해지지 않은 값이라고 하지만 데이터 베이스에 정해지지 않은 값이라고 마킹 정도는 해주어야하기 때문에 분명 NULL도 정해진 크기가 있음

TIBERO

ORACLE

TIBERO | ORACLE

MySQL / MariaDB

PostgreSQL

MS SQL Server

CUBRID

IBM Db2

RDBMS별 NULL이 HEAP에 저장되는 실제 크기

RDBMS	ORACLE	TIBERO	MySQL	MariaDB	MS SQL Server	PostgreSQL	IBM Db2	CUBRID
NULL 크기 (byte)	0	0 또는 1	0	0	0	0	-	0 또는 선언된 데이터 타입을 따름

NULL 처리 방식

SELECT NULL

SELECT 절 NULL 연산

RDBMS	ORACLE	TIBERO	MySQL	MariaDB	MS SQL Server	PostgreSQL	IBM Db2	SQLite	CUBRID
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
1 + NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
1 - NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
1 * NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
1 / NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

SELECT 절 NULL 문자 결합

RDBMS	ORACLE	TIBERO	MySQL	MariaDB	MS SQL Server	PostgreSQL	IBM Db2	SQLite	CUBRID
'A'\|\|NULL\|\|'C’	AC	AC	NULL (연산)	NULL (연산)	-	NULL	NULL	NULL	NULL
'A'+NULL+'C’	-	NULL (연산)	NULL (연산)	NULL (연산)	NULL	-	-	NULL (연산)	NULL
CONCAT('A',NULL,'C')	-	-	NULL	NULL	AC	AC	-	-	NULL
CONCAT('A',NULL)	A	A	NULL	NULL	A	A	NULL	-	NULL

•

NULL (연산) : 문자 결합이 아닌 문자 연산

•

- : 미지원

SELECT 절 NULL 날짜시간 연산

RDBMS	ORACLE	TIBERO	MySQL	MariaDB	MS SQL Server	PostgreSQL	IBM Db2	SQLite	CUBRID
날짜시간함수 + NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

SELECT 절 NULL 부울 비교

RDBMS	ORACLE	TIBERO	MySQL	MariaDB	MS SQL Server	PostgreSQL	IBM Db2	SQLite	CUBRID
NULL or TRUE	-	-	TRUE (1)	TRUE (1)	-	TRUE	TRUE	TRUE (1)	-
NULL or FALSE	-	-	NULL	NULL	-	NULL	NULL	NULL	-

•

TRUE : TRUE 반환

•

TRUE (1) : 1 반환

•

- : 미지원

SELECT 절 NULL처리 SQL 

집계 함수

•

테스트 결과를 통해 NULL과의 연산/ 비교는 NULL처리가 되거나 NULL을 무시해버립니다.

•

집계 관련 함수에서는 NULL이 포함된 로우로 인한 어떤 영향이 있을까요

테스트 테이블

x	xx
1	NULL
2	NULL
3	NULL
4	40
5	50

RDBMS	ORACLE	TIBERO	MySQL	MariaDB	MS SQL Server	PostgreSQL	IBM Db2	SQLite	CUBRID
COUNT(*)	5	5	5	5	5	5	5	5	5
COUNT(x)	5	5	5	5	5	5	5	5	5
COUNT(xx)	2	2	2	2	2	2	2	2	2
SUM(x)	15	15	15	15	15	15	15	15	15
SUM(xx)	90	90	90	90	90	90	90	90	90
SUM(x+xx)	99	99	99	99	99	99	99	99	99
AVG(x)	3	3	3	3	3	3	3	3	3
AVG(xx)	45	45	45	45	45	45	45	45	45
AVG(x+xx)	49.5	49.5	49.5	49.5	49	49.5	49	49.5	49.5

•

집계 함수에서는 NULL 로우는 포함하지 않고 집계가 수행 됩니다.

◦

COUNT(xx), SUM(xx), AVG(xx)

•

집계 함수에서 두개 이상의 COLUMN을 같이할 경우 NULL 로우에 해당하는 값은 전부 무시 됩니다.

◦

x 컬럼과 xx컬럼의 NULL 값을 고려하지 않고 집계 함수가 사용 될 경우 의도한 결과가 도출 되지 않을 수 있습니다.

◦

SUM(x+xx), AVG(x+xx)

•

비교 대상 RDBMS 모두 동일 합니다. 다만, 소수점 처리에서 차이가 있습니다.

◦

소수점자리를 버리는 경우가 있음.

▪

MS SQL, IBM Db2 :  정수를 연산했을 때 소수점이 나오면 버리는 특성

주의 사항

•

데이터베이스에 지원하지 않는 함수가 없는 경우 PROCEDURE를 생성하여 사용 할 때 주의

외부 함수/프로시저 작성 시 주의 SQL

집계 함수 NULL처리 SQL

NULL 정렬

숫자형

x	xx
1	NULL
2	NULL
3	NULL
4	40
5	50
6	NULL

문자형

x	xx
1	NULL
2	NULL
3	NULL
4	A
5	Z
6	NULL

날짜형

x	xx
1	NULL
2	NULL
3	NULL
4	2022-01-01 00:00:00.000
5	2022-12-31 00:00:00.000
6	NULL

RDBMS	ORACLE	TIBERO	MySQL	MariaDB	MS SQL Server	PostgreSQL	IBM Db2	SQLite	CUBRID
NUMBER ORDER BY xx ASC;	가장 큰 값	가장 큰 값	가장 작은 값	가장 작은 값	가장 작은 값	가장 큰 값	가장 큰 값	가장 작은 값	가장 작은 값
STRING ORDER BY xx ASC;	가장 큰 값	가장 큰 값	가장 작은 값	가장 작은 값	가장 작은 값	가장 큰 값	가장 큰 값	가장 작은 값	가장 작은 값
DATETIME ORDER BY xx ASC;	가장 큰 값	가장 큰 값	가장 작은 값	가장 작은 값	가장 작은 값	가장 큰 값	가장 큰 값	가장 작은 값	가장 작은 값

가장 작은 값

가장 큰 값

•

ORDER BY 를 수행하여 정렬을 수행하면 분명 NULL의 크고 작음을 확인 할 수 있습니다.

•

MIN/MAX 집계 함수 결과에서는  NULL을 값으로 처리하지 않음

RDBMS	ORACLE, TIBERO, MySQL, MariaDB, MS SQL Server, PostgreSQL, IBM Db2, SQLite, CUBRID
NUMBER MIN(x),MIN(xx)	1 40
NUMBER MAX(x),MAX(xx)	6 50
STRING MIN(x),MIN(xx)	1 A
STRING MAX(x),MAX(xx)	6 Z
DATETIME MIN(x),MIN(xx)	1 2022-01-01-00.00.00.000000
DATETIME MAX(x),MAX(xx)	6 2022-12-31-00.00.00.000000

NULL SORT SQL

NULL INDEX

INDEX와 NULL

1번 SQL : SELECT * FROM tbl WHERE xx IS NOT NULL;
2번 SQL : SELECT * FROM tbl WHERE xx IS NULL;

CREATE INDEX idx_tbl_x ON tbl(xx);
SQL
복사

•

INDEX에 NULL 포함

◦

1번 SQL : NULL이 아닌 것만 INDEX FFS SCAN 수행

◦

2번 SQL : NULL인 것을 찾기 위해 INDEX RANGE SCAN 수행

•

INDEX에 NULL 미포함

◦

1번 SQL : NULL이 아닌 것만 INDEX FFS SCAN 수행

◦

2번 SQL : NULL인 것을 찾기 위해 TABLE ACCESS (FULL) 수행

데이터 건수(NOT NULL: 60,000 / NULL: 4,000)

TOTAL_CNT	NOTNULL_CNT	NULL_CNT
64,000	60,000	4,000

데이터 분포도

X	XX	COUNT(*)
1	NULL	1,000
2	20	10,000
3	NULL	1,000
4	40	10,000
5	NULL	1,000
6	60	10,000
7	70	10,000
8	NULL	1,000
9	90	10,000
10	100	10,000

TIBERO

ORACLE

MySQL / MariaDB

PostgreSQL

MS SQL Server

IBM Db2

CUBRID

RDBMS별 INDEX에 NULL 포함 여부 확인

RDBMS	ORACLE	TIBERO	MySQL	MariaDB	MS SQL Server	PostgreSQL	IBM Db2	CUBRID
INDEX NULL 포함 여부 (단일)	X (단일)	X (단일)	O (단일,복합)	O (단일,복합)	O (단일,복합)	O (단일,복합)	O (단일,복합)	X (단일)

RDBMS	ORACLE	TIBERO	CUBRID
단일 컬럼	X	X	X
복합 컬럼 특정 컬럼의 NOT NULL일 경우	O	O	X

복합키 NULL 인덱스 구성 검증 SQL

•

IS NULL

◦

INDEX에 NULL 포함 RDBMS

▪

INDEX 스캔을 통해 NULL의 결과 확인

◦

INDEX에 NULL 미포함 RDBMS

▪

함수 기반 인덱스 활용 : NVL(xx, ‘unkown’) 형태로 만들어서 활용

▪

ORACLE, TIBERO의 경우 복합 인덱스 컬럼에 포함 된 컬럼이 1개 이상 NOT NULL 제약 조건에 해당하면 다른 컬럼은 NULL을 포함한다.

•

([notnull column], xx)

▪

ORACLE, TIBERO의 경우 복합 인덱스 구성 시 NOT NULL 제약 조건 컬럼이 없을 경우 NULL을 인덱스에 포함하고 싶을 경우

•

(xx,’1’)

•

IS NOT NULL

◦

INDEX에 NULL 포함 RDBMS

▪

INDEX 스캔을 통해 NULL이 아닌 결과 확인

◦

INDEX에 NULL 미포함 RDBMS

▪

해당 컬럼의 MIN 값을 구해서 활용

•

공백을 string으로 처리하는 RDBMS의 경우 

◦

정수 또는 문자:  WHERE xx >=’’; 활용

◦

음수 : WHERE xx > (select MIN(xx)-99 from tbl9_null limit1);

•

ORACLE, TIBERO는 INDEX FFS 자동으로 동작

▪

ORACLE, TIBERO의 경우 복합 인덱스 컬럼에 포함 된 컬럼이 1개 이상 NOT NULL 제약 조건에 해당하면 다른 컬럼은 NULL을 포함한다.

•

([notnull column], xx)

▪

ORACLE, TIBERO의 경우 복합 인덱스 구성 시 NOT NULL 제약 조건 컬럼이 없을 경우 NULL을 인덱스에 포함하고 싶을 경우

•

(xx,’1’)

정리

NULL 저장

•

'' (empty) 값이 RDBMS(이하: DB) 마다 NULL 또는 문자열 취급이 다르다.

•

문자열 취급 DB에서 NULL 처리 DB로 이관 시 주의 필요 (결과 값이 달라질 수 있으면 제약 조건 위반이 되어 이관 자체 건수에서 배제 될 수 있다.)

NULL 크기

•

DB마다 NULL 크기 3개 패턴이 있다.

◦

NULL 무조건 0 byte

◦

TIBERO의 경우 연속 된 NuLL에 대한 크기 부여

col1	col2	col3	BYTE
NULL	NULL	NULL	1 + 0 + 0

col1	col2	col3	BYTE
NULL	NULL	1	1 + 1 + type

col1	col2	col3	BYTE
NULL	1	NULL	1 + type +

TIBERO의 경우 NULL 허용 컬럼을 최대한 뒤로 빼는게 TABLE 크기에서 효율적이다.

◦

CUBRID의 경우 데이터 타입의 크기를 미리 할당

INT ⇒ 4 byte / datetime ⇒ 8 byte / VARCHAR, CLOB ⇒ 0 byte

NULL 연산/내부 함수/외부 함수,프로시저

•

NULL 연산

◦

NULL 과 연산되는 모든 값은 NULL로 처리 된다.

•

내부 함수

◦

변환/집계 함수 수학적 연산이 들어가는 함수는 NULL을 배제하고 연산한다.

◦

문자열 접합 함수의 경우 NULL이 들어간 경우 NULL처리 하거나 NULL을 제거하고 접합 한다.

•

외부 함수, 프로시저

◦

함수,프로시저 작성 시 NULL을 고려하지 않으면 의도치 않게 결과가 NULL로 출력 될 수 있다.

NULL 정렬

•

DB 마다 NULL을 정렬 하는 값은 가장 큰 값으로 여기거나 가장 작은 값으로 여겨서 정렬한다. 

◦

중간 값은 없다

◦

NULL을 제거하지 않고 정렬하여 값을 얻으려고 할 경우 의도치 않은 NULL이 출력 될 수 있다.

◦

대소 및 MIN/MAX 함수에서는 NULL을 배제 하고 처리한다.

NULL 인덱스

•

DB마다 INDEX에 NULL을 포함하는 경우가 있고 하지 않는 경우가 있다.

◦

싱글/복합 인덱스 구성 시 NULL을 모두 포함거나 경우에 따라 포함한다.

▪

복합 인덱스 구성 시 특정 컬럼이 NOT NULL인 경우 다른 컬럼은 NULL을 포함한다.

◦

싱글/복합 인덱스 모두 NULL을 포함 하지 않는다.

▪

함수 인덱스를 활용하여, 인덱싱 처리를 할 수 있다.

RDBMS NULL

RDBMS VERSION

NULL

NULL 저장

NULL 크기

TIBERO

ORACLE

TIBERO | ORACLE

MySQL / MariaDB

PostgreSQL

MS SQL Server

CUBRID

IBM Db2

NULL 처리 방식

SELECT NULL

집계 함수

NULL 정렬

NULL INDEX

TIBERO

ORACLE

MySQL / MariaDB

PostgreSQL

MS SQL Server

IBM Db2

CUBRID

정리

NULL 저장

NULL 크기

NULL 연산/내부 함수/외부 함수,프로시저

NULL 정렬

NULL 인덱스

END.