엄범

 

DBGuide.net 오라클

 

오라클 장점? 왜 기업에서 오라클을 선호하는가?

  • 성능이 좋고 다양한 옵션이나 기능을 제공한다는 장점도 있긴 하지만, 가장 큰 이유 중 하나는 RAC를 통한 고가용성.
  • DB 이중화 / 클러스터링

 

타입 관련 팁

  • 숫자 타입은 `` NUMBER``를 쓰는 것이 좋다.
    • `` NUMBER(<정수자리 길이제한>, <소수자리 길이제한>)``로, 가변길이 타입이다
  • 문자열은 `` CHAR || VARCHAR2``로 쓰는 것이 좋다.
    • 그냥 VARCHAR는 추후 다른 용도로 쓰일 수 있다고 Oracle에서 사용하지 않기를 권고함.
  • 날짜 저장은 `` DATE``를 사용하는 것이 좋다.
  • 키는 보통 `` long``으로 한다.
    • 보통 내부적인 키는 long으로 한다. VARCHAR2같은 string으로 하면 공백이 들어가는 등의 문제가 생김.
    • 필요한 경우 VARCHAR2 키를 추가하는 식으로 한다.
  • 핸드폰 번호 같은건 "-"를 떼고 문자 타입으로.
    • "-"가 포함 되어 있으면 데이터 정합성에 좋지 않음
    • 속도에도 좋지 않음
      • 010-1234-567이나 010-123-4567이나 모두 같은 번호인데 중복 체크하려면 함수를 써야한다.
      • 이렇게 함수를 쓰면 인덱스가 안걸려 느리다
  • 어떤 타입을 쓰든, 들어와야 하는 형식을 제약해야 하는 경우 `` CONSTRAINT``를 적극 사용할 것. 좋은 기능이다.

 

DB의 Query Pool (정확한가? 좀 더 찾아봐야 할 듯)

  • ``sql SELECT * FROM TRAD WHERE TID=?``
  • DB는 쿼리가 들어오면 다음 과정을 수행한다.
    • 문법에 맞는지 체크
    • TRAD, TID 등이 있는 테이블인지, 있는 컬럼인지 체크
    • 파라미터 바인딩
    • 쿼리 수행
  • 여기서 문법에 맞는지 체크, 있는 테이블인지 체크 하는 부분을 매번 수행하지 않기 위해 일종의 Pool에 쿼리를 위와 같은 형태로 저장해둔다. 이 때 SQL마다 유니크한 ID를 부여한다.
  • SQL ID는 통계를 낼 때 사용한다. 
  • 통계 시 보기 편하게 하기 위해, 다음과 같은 형식으로 주석을 달기도 한다.
    • ``sql SELECT /* UserMapper.selectUserList */``

 

FK 걸 때 주의해야 할 점?

Oracle CONSTRAINT clause  

  • 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.
      • 쿼리 실행 결과가 정상적일지라도, 레코드가 참조되고 있으면 변경을 아예 허용하지 않음.

 

Pagination

https://en.wikipedia.org/wiki/Select_(SQL)#Result_limits

 

12c 버전 이후라면? : Row Limiting Clause

 

12c 이전에는?

 

ROW_NUMBER() 사용한 방법

```sql

SELECT *

FROM (

    SELECT /*+ first_rows(25) */

        tid,

        ...etc fields,

        ROW_NUMBER() OVER (ORDER BY tid DESC) rn

    FROM

        trad

)

WHERE

    rn BETWEEN 25 AND 50

ORDER BY rn;

```

  • 안쪽 SELECT 에서 *을 쓰면 구문 오류가 발생하기 때문에, 필요한 컬럼을 명시해준다.
  • /*+ 주석은 Oracle Hint로, 같이 넣어준다.
  • Offset Method(rownum을 사용하는 방식)와 성능상 차이는 없어보이고, 다만 그 방법은 두 단계의 inline view를 사용해야 해서 가독성이 좀 떨어지기 때문에 요게 더 낫긴 하다.
    • 둘 다 index full scan이 뜬다.
  • 이미 조회가 끝난 앞쪽 부분에 row가 추가 되었을 때, 전체적으로 1개 만큼 밀리게 되므로 이전 페이지에서 보였던 마지막 항목이 다음 페이지 처음에 또 보일 수 있음.

 

이전 결과의 마지막 항목을 기준으로 그 다음부터 찾아나가는 방법

  • Index range scan이 뜨며 인라인 뷰를 사용하지 않기 때문에 싱글 스레드로 조회 시 성능이 가장 좋음
  • 이미 조회가 끝난 앞쪽 부분에 row가 추가 되었을 때, 문제 없음. (이전 결과 기준 n개의 다음 항목 조회이므로)
  • 병렬 조회가 불가능하다는 것이 제일 큰 단점
  • 조회 조건으로 거는 컬럼이 Unique 해야 한다는 제약도 있음.

 

Query

테이블 생성 CREATE DDL

```sql

create table CARD_HIST (
HIST_ID VARCHAR2(20) not null,
CID VARCHAR2(40) not null,
REG_YMDT VARCHAR2(20) default TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') not null,
CONSTRAINT PK_CARD_HIST PRIMARY KEY(HIST_ID),
CONSTRAINT FK_GRTE_TID FOREIGN KEY (GRTE_TID) REFERENCES TRAD(TID)
)
/
comment on column CARD_HIST.HIST_ID is 'YYYYMMDDHH + 숫자(10)';

```

 

```sql

-- CONSTRAINT 추가

ALTER TABLE USERS

ADD CONSTRAINT PK_USERS PRIMARY KEY(USER_ID);

 

-- comment 달기

comment on column CARDS.CID is '카드ID'

 

-- index 생성

CREATE {UNIQUE} INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3.......)

```

 

tablespace 관련 쿼리