대용량 데이터베이스 솔루션 1권 #18 나머지 사항들

Shared SQL Pool 활용

SELECT * FROM EMP;
SELECT *  FROM EMP;
SELECT * FROM Emp;
SELECT * FROM SCOTT.EMP;

SELECT * FROM EMP WHERE DEPTNO = :V_DEPTNO;
SELECT * FROM EMP WHERE DEPTNO = :V_NO;
SELECT * FROM EMP WHERE DEPTNO = '10';
SELECT * FROM EMP WHERE DEPTNO = '20';

위의 4개의 SQL 과 밑에 4개의 SQL은 각각 다른 SQL문으로 판단되어 Shared SQL Pool에서 공용으로 사용하지 못한다. Dynamic SQL을 제대로 활용하려면 모든 개발자들과 변수이름이나 SQL문 작성에 대한 규칙을 통일시켜서 최대한 같은 의미의 다른 SQL문이 나오지 않게끔 훈련시켜야 한다.

DATA TYPE

1. CHAR TYPE

* 특성
- 고정길이 문자열 : 최대 255, Default 1
- 일부만 입력시 나머지는 BLANK로 채워짐
- 전혀 값을 안주면 NULL
- 지정된 길이보다 길면 ERROR 발생

* 적용
- Data가 고정길이인 경우
- Column 길이가 짧고 거의 모든 Data의 길이가 일정량을 넘는 경우 고려
- 가변길이로 지정할 경우 많은 Chain의 발생이 우려되는 경우
- 처음 입력시가 아니라 조금 뒤에 Data가 입력되는 경우
- Table 생성 SQL에 Default Constraint를 ''(Blank)로 지정하면 유용함
- 길이의 편차가 심한 경우에 사용하면 불필요한 저장공간의 낭비, 수행속도가 저하

2. VARCHAR2

* 특성
- 가변길이 문자 데이터 : 최대길이 4,000 (버전별로 다름)
- 일부만 입력시 뒷부분은 NULL
- 입력한 값의 뒷부분에 있는 BLANK도 문자열로 취급
- 전혀 값을 안주면 NULL
- 지정된 길이보다 길면 ERROR 발생

* 적용
- Data가 가변길이인 경우
- Column 길이의 편차가 심한 경우
- NULL로 입력되는 경우가 많은 경우
- 적절한 PCTFREE를 부여하지 않으면 Chain발생 가능성이 높다.
- Table 생성 SQL에 Default Constraint를 ''(Spcae)로 지정하여 원하는 만큼의 길이를 확보하여 Chain을 감소
- 가능한 CHAR 타입보다 VARCHAR2를 사용

3. 문자TYPE의 비교 법칙

* 양쪽 모두 CHAR
- 서로 길이가 다르면 짧은 쪽에 Space를 추가하여 길이를 같게 한 후 비교
- Blank 수만 다르다면 서로 같은 값

* 어느 한쪽이 VARCHAR2
- 길이가 같고 다른 것이 없다면 같다고 판단
- VARCHAR2는 NOR NULL까지가 길이

* 상수값과 비교
- 상수쪽을 변수타입과 동일하게 바꾸어 비교
- 같은 상수값과 CHAR, VARCHAR2 형이 모두 =(Equal) 비교가 True일지라도 그 2개의 CHAR, VARCHAR2 비교시는 False가 될수 있다.

4. NUMBER

* 특성
- 숫자를 저장
- 전체 자리수는 38자리를 넘을 수 없다
- 소수점이 지정되지 않거나 지정된 자리 이상이 입력되면 반올림되어 저장

* 적용
- NUMBER : 길이를 제한하지 않을 정수입력
- 지정된 소수점이하의 값은 반올림되어 저장되므로 감안하여 소수점 자리수 결정
- 문자값과 비교되면 상대타입을 숫자타입으로 바꾸어 비교하므로 INDEX를 생성한 Column은 가능한 문자타입으로 할 것
- NUMBER로 지정된 Column을 LIKE '%'로 비교하면 INDEX를 사용하지 않으므로 주의
- NUMBER(p,s) 로 지정시 p는 s의 자리수를 포함한 길이므로 감안하여 p의 자리수를 결정
- NUMBER 타입은 항상 가변길이이므로 충분하게 지정할 것

5. DATE

* 특성
- NLS_DATE_FORMAT를 이용하여 국가별 특수성을 해결
- 특별히 시간을 지정하지 않으면 12:00:00
- 특별히 일자를 지정하지 않으면 현재월의 1일
- SYSDATE는 현재시간을 제공 (Oracle)

* 적용
- 왠만하면 DATE 타입을 쓰지말고 CHAR로 사용하는 것이 훨씬 효율적이다.
  생각보다 비교하거나 연산할일이 많이 없다.
  크기도 1Byte 밖에 차이나지 않는다.
- 하지만 일자나 시간의 연산이 빈번한 경우에는 사용한다.
- 시간이 정상적으로 입력된 경우 일자를 '='로 비교할 수 없다.
- LIKE, SUBSTR 등의 비교가 불가능
- 자주 조건절에 사용되거나 INDEX로 지정될 경우는 문자타입으로 할것을 고려해 볼것
- 주로 DATE 연산이나 Log 등의 Timestamp 성격을 띄는 곳에 사용
- JOIN 되는 상태 Column과는 같은 타입으로 해야 함

6. DATA 길이의 지정

- VARCHAR2 는 가능한 충분하게 최대치를 부여
- CHAR는 가능한 최소 길이 지정
- 한글과 영숫자가 입력되는 Column은 충분한 길이를 부여해야함
- NUMBER 타입의 길이를 제한하지 않을 때는 NUMBER로만 지정하고 가능한 충분하게 지정
- NUMBER 타입의 소수점은 그 이하에서 반올림되어 저장되므로 감안할 것
- NUMBER 타입은 소수점을 지정하지 않으면 소수점 이하는 반올림되어 정수로 저장되므로 소수점이 필요하면 반드시 소수점 지정

HINT (비용 기준 최적화)

- HINT는 절대 전능하지 않다. 말도 안되는 HINT는 Optimizer가 무시한다.
- HINT 는 DELETE, SELECT, UPDATE 바로 다음에 주석+ (/*+ ... */) 에 적어준다.

- RULE : Rule-Based Optimizer를 사용
- FIRST_ROWS : 첫째 레코드의 추출시간을 최소화 할 목적으로 최적화
- ALL_ROWS : 모든 레코드를 처리하는 시간을 최소화 할 목적으로 최적화
- FULL : 지정된 테이블에 대한 FULL-SCAN
- ROWID : 지정된 테이블에 대한 ROWID에 의한 테이블 스캔
- CLUSTER : 지정된 테이블에 대한 클러스터 스캔
- HASH : 지정된 테이블에 대한 해쉬 스캔
- INDEX_ASC : 내림차순(순방향)으로 INDEX 스캔
- INDEX_DESC : 오름차순(역순)으로 INDEX 스캔
- AND_EQUALS : 여러개의 INDEX들을 MERGE하여 사용 (2~5개)
- ORDERED : FROM절에 기술된 TABLE 순서대로 JOIN
- USE_NL : 먼저 특정 TABLE의 ROW를 ACCESS하고 그 값에 해당하는 다른 TABLE의 ROW를 찾는 작업을 해당범위까지 실행하는 JOIN
- USE_MERGE : 먼저 각각의 TABLE의 처리범위를 스캔하여 SORT한 후 서로 MERGE 하면서 JOIN하는 방식

 FETCH 와 SELECT ... INTO 의 차이점

1. FETCH

- MAX를 모르고나 지나치게 넓어서 한번의 FETCH로는 무리인 경우
- Loop Query SQL 내에서 CURSOR 문을 다시 DECLARE 하면 안됨 : 속도가 몇배로 느려짐

2. SELECT ... INTO

- MAX를 알 때 사용
- Loop 내에서 반복되는 n건 처리
- DECLARE SQL에서 JOIN하여 해결