(Oracle) 오라클 개요 및 정리
(Oracle) 오라클 개요 및 정리
oracle 장점? 왜 기업에서 오라클을 선호하는가?
- 성능이 좋고 다양한 옵션이나 기능을 제공한다는 장점도 있긴 하지만, 가장 큰 이유 중 하나는 안정성.
- 어떤 관점에서 안정성이 좋은가? RAC를 통한 고가용성 =>DB 이중화 / 클러스터링
- 간단히 정리하면, 스토리지와 오라클 인스턴스를 분리해서 인스턴스에 장애가 나더라도 유실 없이 다른 인스턴스에서 곧바로 Active로 서비스 할 수 있다는 것.
타입 관련 팁
- 숫자 타입은
NUMBER를 쓰는 것이 좋다.NUMBER(<정수자리 길이제한>, <소수자리 길이제한>)로, 가변길이 타입이다
- 문자열은
CHAR || VARCHAR2 - 키는 보통
VARCHAR2 or LONG으로 한다. 단, VARCHAR2는 공백이 들어갈 수 있다는 점 감안해야 한다. - 핸드폰 번호 같은건 “-“를 떼고 문자 타입으로.
- ”-“가 포함 되어 있으면 데이터 정합성에 좋지 않음
- 속도에도 좋지 않음
- 010-1234-567이나 010-123-4567이나 모두 같은 번호인데 중복 체크하려면 함수를 써야한다.
- 이렇게 함수를 쓰면 인덱스가 안걸려 느리다
[!tip] 어떤 타입을 쓰든, 들어와야 하는 형식을 제약해야 하는 경우
CONSTRAINT를 적극 사용할 것.
날짜 타입 관련 팁
- 날짜시간 저장은
DATE / TIMESTAMP를 사용하는 것이 좋다.- TIMESTAMP가 nano, milis 까지 저장.
- JPA에서 LocalDateTime을 DB 변환 할 때도
DATE / TIMESTAMP로 변환하는 것이 표준임. - DATE는 oracle 고유 타입이고 TIMESTAMP는 ANSI SQL 표준이므로 가능하면 TIMESTAMP를 쓰는게 좋지만 기존 시스템 컨벤션이 DATE라면 그냥 따라도 좋은 정도다.
- 날짜시간 저장에
VARCHAR2를 사용하는 것은 당장은 편하지만 아래 문제를 야기한다.- ‘2023-13-40’ 같은 유효하지 않은 날짜에 대한 필터링 불가
- ‘2023-1-2’, ‘2023/05/10’ 같은 일관되지 않은 포맷에 대한 저장 (특히 배치를 신규로 전환한다거나 할 때.)
ADD_MONTH같은 DB의 날짜 함수를 바로 사용 할 수 없고, 모든 연산에TO_DATE변환이 선행되어야 함.- 저장공간을
TIMESTAMP, DATE보다 더 많이 사용함. - 삽입할 때 TRUNC()함수로 시분초를 잘라 넣어라. CONSTRAINT를 걸어서 항상 시분초를 자른 값이 들어오도록 제한할 수 있다.
날짜에 VARCHAR2를 쓰는 것은, 근본적으로, 데이터베이스가 그 컬럼의 ‘의미(semantic)’를 이해하지 못하기 때문에 나쁘다.
DATE, TIMESTAMP인 경우 옵티마이저가 아래 처럼 사고하는게 가능한데…
- “
my_timestamp_col은 시간의 흐름을 나타내는 컬럼이군.” - “이 컬럼의 통계 정보(히스토그램)를 보자. 전체 데이터는 5년에 걸쳐 분포되어 있군.”
- “사용자가 요청한 기간은 한 달이네. 이건 전체 데이터의 약 1/60 (약 1.6%) 정도에 해당하겠군.”
- “1.6% 정도의 적은 데이터를 읽는 것이니, 인덱스를 사용해서 접근(Index Range Scan)하는 것이 압도적으로 유리하겠다.”
반면 VARCHAR2를 쓰면 옵티마이저는 아래 처럼 사고하게 된다.
- “
my_varchar_col은 그냥 문자열 컬럼이군.” - “이 컬럼의 통계 정보를 보자. 값들이
'2021...','2022...','2023...'등으로 시작하는군. 히스토그램상'2'로 시작하는 문자가 가장 많군.” - “사용자가
'2023-05-01...'부터'2023-05-31...'사이의 문자열을 요청했네. 이게 전체 문자열 중에서 얼마나 많은 비중을 차지하는지 전혀 예측할 수 없다.'2023-05'로 시작하는 문자열이 전체 데이터의 90%를 차지할 수도 있고, 0.01%만 있을 수도 있다.” - (최악의 추측) “얼마나 될지 모르겠으니, 그냥 테이블 전체를 다 읽어서(Full Table Scan) 필터링하는 게 안전하겠다. 인덱스를 타는 비용보다 이게 더 쌀 수도 있어.” (잘못된 판단)
NOT IN 할 때, null인 컬럼은 조회 결과에 포함되지 않는다.
주의 해야 할 점. NOT IN (1, 2, 3) 이면, (1, 2, 3, null)이 아닌 항목들만 조회 결과에 포함된다.
DB Query Pool
SELECT \* FROM TRAD WHERE TID=?- DB는 쿼리가 들어오면 다음 과정을 수행한다.
- 문법에 맞는지 체크
- TRAD, TID 등이 있는 테이블인지, 있는 컬럼인지 체크
- 파라미터 바인딩
- 쿼리 수행
- 여기서 문법에 맞는지 체크, 있는 테이블인지 체크 하는 부분을 매번 수행하지 않기 위해 일종의 Pool에 쿼리를 위와 같은 형태로 저장해둔다. 이 때 SQL마다 유니크한 ID를 부여한다.
- SQL ID는 통계를 낼 때 사용한다.
- 통계 시 보기 편하게 하기 위해, 다음과 같은 형식으로 주석을 달기도 한다.
SELECT /\* UserMapper.selectUserList \*/
FK 걸 때 주의해야 할 점?
- FK의
ON DELETE기본 옵션은NO ACTION- 삭제하고자 하는 항목이 참조되는 중이라면 삭제 불가.
NO ACTION과RESTRICT의 차이?- NO ACTION means to allow the change if the new value in the child table is still valid after statement and all the triggers are completed (i.e. still found in the parent table of the relationship),
- 즉, 일단 query를 실행해본 다음, trigger 하기 전에 실행 결과 상태에서 무결성 체크하고, 위배 시 statement를 거절하는 방식.
- while RESTRICT means that changing the value is not allowed at all (no change or delete is allowed if there are child records).
- 트랜잭션 내에서 무결성 제약조건 위배하는 순간 rollback.
- 쿼리 실행 결과가 정상적일지라도, 레코드가 참조되고 있으면 변경을 아예 허용하지 않음.
- NO ACTION means to allow the change if the new value in the child table is still valid after statement and all the triggers are completed (i.e. still found in the parent table of the relationship),
오라클 버전 관련
- 11g : default & not null이 같이 있는 항목에 null 넘길 시 오류 발생하는 문제 있음
- 오라클 XE는 어떤 용도로든 완전 무료다. but 성능 이슈로 운영에는 못쓴다.
잘못 삭제한 row 복구하기
https://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr002.htm
1
2
3
SELECT *
FROM 테이블명 AS OF TIMESTAMP TO_TIMESTAMP('2025-05-25 15:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE ...;
This post is licensed under CC BY 4.0 by the author.