1. NULL의 특징 및 관련 함수
Null의 특징
NULL과 숫자 혹은 날짜를 더하면 NULL이 된다.
NULL과 어떤 값을 비교할 때, '알 수 없음'이 반환된다.
Null 관련 함수(Oracle DB)
NVL
Null이면 다른 값으로 바꾼다. NVL(age, 99)는 age 컬럼이 null값을 가지면 99로 바꾼다.
NVL2
NVL + DECODE라고 할 수 있다. NVL2(age, 1, 0)은 age 컬럼이 null이 아니면 1, null이면 0을 반환한다.
NULLIF
두 값이 같으면 null, 아니면 첫 번째 값을 반환한다. NULLIF(exp1, exp2)는 exp1 = exp2이면 null, 아니면 exp1을 반환한다.
COALESCE(합치다)
NULL이 아닌 최초의 인자값을 반환한다. COALESCE(exp1, exp2, .....)는 exp1부터 순서대로 null을 판단하여 최초로 null이 아닌 값을 반환한다.
2. 형변환
형변환은 타입이 다른 컬럼들에 대해 연산이나 비교 등이 필요할 때 컬럼의 타입을 변경하는 것이다. 데이터 베이스 관리 시스템이 자동으로 타입을 변경해주는 것을 암시적(Implicit) 형변환, 개발자가 직접 타입을 변경하는 것을 명시적(explicit) 형변환이라고 한다.
형변환 함수(Oracle DB)
- TO_NUMBER(문자열) : 문자열을 숫자로 변환한다.
- TO_CHAR(숫자 | 날짜, [FORMAT]) : 숫자 혹은 날짜를 지정된 FORMAT 형태로 변환한다.
- TO_DATE(문자열, [FORMAT]) : 문자열을 지정된 FORMAT의 날짜형으로 변환한다.
형변환 시 인덱스로 사용 불가
인덱스는 기본적으로 변형이 발생하면 인덱스로 사용할 수 없다. 형변환도 변형이므로 인덱스를 사용하지 못하게 만든다.
SELECT * FROM EMP WHERE EMPNO = '100';
위 예제에서 EMPNO는 숫자형 컬럼이였는데, 문자열 '100'과 비교하게 함으로써 암시적 형변환을 하였다. EMPNO가 기본키라면 인덱스로 잡혀있는데, 이런 경우 인덱스로 사용하지 못하게 된다.
해결 방법은 '100'을 숫자형으로 명시적 형변환을 해주는 것이다. WHERE EMPNO = TO_NUMBER('100')으로 변환하면 EMPNO는 변형이 발생하지 않으므로 그대로 인덱스로 사용이 가능하다.
3. 내장형 함수(BUIL_IN FUNCTION)
내장형 함수는 DBMS 벤더별로 약간의 차이가 있지만, 거의 비슷한 문법을 갖고 있다. 내장형함수에는 형 변환 함수, 문자열 및 숫자형 함수, 날짜형 함수가 있다. Oracle 기준으로 이런 기능을 하는 함수들이 있다는 것을 기억하고, 각 벤더의 DBMS를 활용할 때는 함수를 키워드로하여 검색이 필요할 것 같다.
문자열 함수
이 중, CONCAT, SUBSTR, LENGTH, LPAD/RPAD, TRIM, CONVERT, REPLACE 등은 자주 사용하는 것 같다.
숫자형 함수
날짜형 함수
4. ROWNUM/ROWID(Oracle DB)
ROWNUM과 ROWID는 Oracle 데이터베이스에서 제공하는 조회 결과 및 데이터를 구분하기 위한 값이다.
ROWNUM
SELECT문 결과에 대해서 논리적인 일련번호를 부여한다. 보통 조회 결과의 출력 행 수를 제한하기 위해 사용하며, 페이지로 결과를 조회할 때 이 값을 응용한다.
아래 예제는 게시판 등에서 페이지 형태로 데이터를 조회하는 쿼리문의 일부이다. ROWNUM을 select하는 것을 볼 수 있다.
SELECT *
FROM ( SELECT ROWNUM list, ENAME FROM EMP )
WHERE list BETWEEN 5 AND 10;
SQL Server(MSSQL)에서는 TOP, MySQL에서는 LIMIT이라는 이름으로 사용한다.
ROWID
ROWID는 Oracle 데이터베이스에서 데이터를 구분할 수 있는 유일한 값이다. ROWID를 통해서 데이터가 어떤 데이터 파일, 어느 블록에 저장되어 있는지도 확인할 수 있다.
ROWID의 구조
- 오브젝트 번호 : 오브젝트별로 유일한 값이다. 만약 EMP 테이블의 값들의 ROWID를 조회한다면, 모든 데이터의 ROWID는 똑같은 오브젝트 번호로 시작한다.
- 상대 파일 번호 : 테이블 내의 EXTENT에 따른 번호이다. 같은 테이블에 있는 데이터라도 EXTENT 위치가 다르면 달라진다.
- 블록 번호 : 어느 블록에 데이터가 있는지 알려준다.
- 데이터 번호: 위 3가지 번호가 같은 경우라면, 데이터가 저장된 순서를 알려준다. 이에 따라 각 데이터의 저장 시점의 상대적인 시각을 판단할 수 있다.
참조
0. [도서] 영진닷컴 - SQL 개발자 이론서 + 기출문제
1. Gamcho's Blog - 오라클 SQL ...
https://midas123.tistory.com/161?category=745908
2. 꿈꾸는 개발자, DBA 커뮤니티 구루비 - 인덱스의 핵심인 ROWID 이해
http://www.gurubee.net/lecture/2927