HighLight in your life~

 올바른 데이터 유형을 사용하라(이펙티브 오라클, THOMAS KYTE저, P.510)

 날짜는 날짜에, 수는 수에, 그리고 문자열은 문자열에 넣는다. 즉, 데이터 유형을 본래 목적 이외의 용도로 사용하지 않고
가능한 한 가지 구체적인 유형을 사용한다. 또한, 날짜는 날짜와, 문자는 문자와, 수는 수와만 비교한다. 날짜와 수가 문자열에 저장되거나 부적절한 길이를 사용하여 저장되면 시스템에 다음과 같은 상황이 초래된다.

 1)데이터베이스에 삽입되는 순간 날짜가 실제로 날짜이고, 수가 유효한 수라는 것을 검증하는 과정에서 편집 내용이 손실된다.
 2)성능이 떨어진다.
 3)필요한 저장소가 증가된다.
 4)확실히 데이터 무결성이 침해된다.

 관련 오류: 수가 문자열(ORA-01722: 유효하지 않은 수)에, 날짜가 문자열(ORA-01858: 수가 기대되는 곳에 수가 아닌 문자가 발견됨)에 저장된 시스템에서 흔히 발생하기 때문이다.

1. PK, FK 설정 예제

CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(15),
loc VARCHAR2(15),
CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));

CREATE TABLE emp
(empno NUMBER(4),
ename VARCHAR2(15),
deptno NUMBER(2),
CONSTRAINT emp_empno_pk PRIMARY KEY(empno),
CONSTRAINT emp_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno));

2. 제약조건 설정 확인

SELECT table_name, constraint_type, constraint_name, r_constraint_name
FROM user_constraints
WHERE table_name IN('EMP','DEPT');

락의 종류
1)Latch
2)데이타 변경 관리 락(Data Manipulation Lock - DML)
- Table Lock
- 트랜잭션 락/행 락(Row Lock)

3)공유 풀 락/데이터 딕셔너리 락(Data Dictionary Lock - DDL)
- Row cache Lock
- Libraray cache Lock
- Libraray cache Pin

4)Buffer cache Lock
5)RAC(Real Application Cluster) 락

*공유 커서(Shared cursor)
- 공유 커서(Shared Cursor)는 SQL문과 PL/SQL문을 의미하며, 동일한 SQL문과 PL/SQL은 여러 사용자에 의해 공유되어 사용된다.
커서가 공유되면 새로운 오브젝트 핸들과 힙 오브젝트를 생성하는 과정이 생략되므로 커서 수행 속도가 향상되며, 메모리 단편화

방지 등 자원의 효율적인 사용이 가능해진다.

- 바인드 변수를 사용한 SQL들은 변수 값이 달라지더라도 오라클이 바인드 변수 치환을 통해서 동일한 SQL로 인식하므로 부모 커

서와 자식 커서를 모두 공유한다. 하지만 바인드 변수를 사용하지 않는 SQL은 상수 값까지 모두 동일한 SQL이 아니라면 부모 커서

와 자식 커서 모두 공유하지 못하고 별도의 커서를 가지게 된다. 오라클은 SQL을 ASCII 값으로 변환하여 인식하기 때문에 동일한

SQL로 인식하기 위해서는 대소문자뿐만 아니라 공백까지도 모두 동일하게 수행되어야 한다.

- OTLP시스템에서 사용 빈도가 높은 SQL에 대해서는 반드시 바인드 변수를 반드시 사용해야 하고 만약 편중된 데이터 값으로 인

해서 잘못된 실행 계획이 생성된다면 SQL 튜닝을 통해서 해결해야 한다.

- OTLP 시스템에서 사용 빈도가 높지 않고 편중된 데이터를 조회하는 SQL이나 DW 시스템과 같이 SQL 사용 빈도가 높지 않은 시스템에서는 리터럴 SQL 사용을 고려해 볼 수 있다.그런데 이때는 반드시 신뢰성 있는 통계 정보를 생성하고 유지해줘야 한다.

SELECT /*+ index(EMP01, I_dept_nmbr)*/ *
FROM EMP EMP01
WHERE DEPTNUM = :dept_num
AND :dept_num = '30'
UNION ALL
SELECT /*+ full(EMP01)*/ *
FROM EMP EMP01
WHERE DEPTNUM = :dept_num
AND :dept_num != '30';

- dept_num 컬럼에서 30의 분포도가 1% 이하로 가정할 때, index를 사용하여 접근하는 것이 full scan보다 빠르다.
만약 dept_num컬럼의 값이 30인 것들의 분포도가 1%이하 이고, 값이 50인 것들의 분포도가 10%를 훨씬 초과할 때 full scan을 적

용하는 것이 좋다.

1. HASH PARTITION TABLE
1)오라클 서버가 가장 최적으로 분할해 주기 때문에 개발자가 고민할 필요가 없다.
2)오라클 서버가 지정된 데이터 파일에 적절히 나누어서 저장해 준다.
3)데이터의 분할분포를 오라클 서버의 해시 알고리즘에 의해 처리하기 때문에 범위분할 방법보다 훨씬 분포도가 좋을 수 있으며,
일반적으로 2,4,6,8,16,32 단위로 분할한다.

CREATE TABLESPACE TBS1 DATAFILE 'C:\DISK1\TBS1.DBF' SIZE 1M;
CREATE TABLESPACE TBS2 DATAFILE 'C:\DISK1\TBS2.DBF' SIZE 1M;
CREATE TABLESPACE TBS3 DATAFILE 'C:\DISK1\TBS3.DBF' SIZE 1M;

CREATE TABLE JEON1
(ID DATE,
NAME CHAR(2))
PARTITION BY HASH (ID) PARTITION 3 STORE IN (TBS1, TBS2, TBS3);

또는

CREATE TABLE JEON1
(ID DATE, NAME CHAR(2))
PARTITION BY HASH (ID)
(PARTITION P1 TABLESPACE TBS1,
PARTITION P2 TABLESPACE TBS2,
PARTITION P3 TABLESPACE TBS3);

2. LIST PARTITION TABLE

CREATE TABLESPACE DATA01 DATAFILE 'C:\DISK1\DATA01.DBF' SIZE 1M;
CREATE TABLESPACE DATA02 DATAFILE 'C:\DISK1\DATA02.DBF' SIZE 1M;
CREATE TABLESPACE DATA03 DATAFILE 'C:\DISK1\DATA03.DBF' SIZE 1M;
CREATE TABLESPACE DATA04 DATAFILE 'C:\DISK1\DATA04.DBF' SIZE 1M;

CREATE TABLE LOCATIONS
(LOCATION_ID CHAR(2),
STREET_ADDRESS VARCHAR2(30),
POSTAL_CODE VARCHAR2(10),
CITY VARCHAR2(30),
STATE_PROVIENCE CHAR(2),
COUNTRY_ID CHAR(5))
PARTITION BY LIST (STATE_PROVIENCE)
(PARTITION REGION_EAST VALUES ('MA', 'NY', 'CT', 'NH', 'MD', 'VA', 'PA', 'NJ')
STORAGE (INITIAL 20K NEXT 40K PCTINCREASE 50) TABLESPACE DATA01,
PARTITION REGION_WEST VALUES ('CA', 'AZ', 'NM', 'OR', 'WA', 'UT', 'NV', 'CO')
STORAGE (INITIAL 20K NEXT 40K PCTINCREASE 50) TABLESPACE DATA02,
PARTITION REGION_SOUTH VALUES ('TX', 'KY', 'TN', 'LA', 'MS', 'AR', 'AL', 'GA') STORAGE (INITIAL 20K NEXT 40K

PCTINCREASE 50) TABLESPACE DATA03,
PARTITION REGION_CENTRAL VALUES '(OH', 'ND', 'SD', 'MO', 'IL', 'MI', NULL, 'IA')
STORAGE (INITIAL 20K NEXT 40K PCTINCREASE 50) TABLESPACE DATA04);

LIST PARTITION TABLE의 주요 특징
1)설정시 주의해야 할 내용은 분할되는 기준 값이 각 분할에서 중복적으로 정의되지 않아야 한다.
2)각각의 구별되는 컬럼의 값으로 데이터를 부할한다.
3)분류(sort)되지 않은, 전혀 관계(Relationship)가 없는 데이터에 대해 리스트 분할 방법을 적용할 수 있다.
4)분할(Partition) 사이에는 어떠한 관계(Relationship)도 존재하지 않는다.
5)데이터의 분할 및 성능에 향상 효과를 기대할 수 있다.

*파티션 테이블
1) 파티션 뷰가 오라클 7 버전 때까지 사용되던 테이블 분할기법이라면 오라클 8 버전부터는 보다 향상된 기능으로 파티션 테이블 기법이 소개된다. 파티션 뷰가 효과적으로 데이터를 분할하여 테이터베이스의 가용성을 높이고, 성능향상을 제공했지만 반면에 프로그래밍이 어렵고 유지보수와 관리가 불편한 점은 그 한계로 남았다. 오라클 사에서는 버전 8 부터 이러한 문제에 대한 해결방법으로 파티션 테이블 기법을 제공한다.

2) 주의사항
- 분할키는 최대 16개 컬럼으로 생성할 수 있다.
- LONG, LONG RAW 타입은 분할컬럼으로 설정할 수 없다.
- 컬럼의 값이 NULL인 경우에는 MAXVALUE 값에 해당된다.
- 만약, 분할조건 값에 정의되지 않은 값이 입력되면 에러가 발생한다.
ORA-14400 INSERTED PARTITION KEY IS BEYOND HIGHEST LEGAL PARTITION KEY
- 백업과 복구를 분할단위로 수행할 수 있다.

CREATE TABLESPACE chul1999
DATAFILE 'D:\DATA\chul1999.dbf' size 5m;

CREATE TABLESPACE chul2000
DATAFILE 'E:\DATA\chul2000.dbf' size 5m;

CREATE TABLESPACE chul2001
DATAFILE 'F:\DATA\chul2001.dbf' size 5m;

CREATE TABLE jeon(idate date, no char(2),
name v2(20), qty number)
PARTITION BY RANGE(idate)
(PARTITION t1 values less than(to_date('2000')) TABLESPACE chul1999,
PARTITION t2 values less than(to_date('2001')) TABLESPACE chul2000,
PARTITION t3 values less than(MAXVALUE) TABLESPACE chul2001);

SQL> INSERT INTO JEON PARTITION (T1)
VALUES((TO_DATE('1999-01-01'), '01', 120);

SQL> UPDATE JEON PARTITION (T1)
SET QTY = 0
WHERE IDATE = TO_DATE('1999-01-01');

SQL> SELECT * FROM JEON PARTITION (T1);

SQL> DELETE FROM JEON PARTITION (T1)
WHERE IDATE = TO_DATE('1999-01-01');

3)범위분할 테이블의 상태를 자료사전으로 부터 확인하는 방법

COL TABLE_NAME FORMAT A10
COL PARTITION_NAME FORMAT A14
COL HIGH_VALUE FORMAT A57
COL TABLESPACE_NAME FORMAT A16

SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE, TABLESPACE_NAME
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME='JEON';

*파티션 뷰

SQL> CREATE TABLESPACE CHUL1999
DATAFILE 'C:\DISK1\CHUL1999.DBF' SIZE 5M;

SQL> CREATE TABLE JEON1999
(IDATE DATE, NO CHAR(2),
NAME VARCHAR2(20), QTY NUMBER)
TABLESPACE CHUL1999;

SQL> CREATE TABLESPACE CHUL2000
DATAFILE 'C:\DISK2\CHUL2000.DBF' SIZE 5M;

SQL> CREATE TABLE JEON2000
(IDATE DATE, NO CHAR(2),
NAME VARCHAR2(20), QTY NUMBER)
TABLESPACE CHUL2000;

SQL> CREATE VIEW TOT_CHUL
AS SELECT * FROM JEON1999
UNION ALL
SELECT * FROM JEON2000;

SQL> SELECT * FROM TOT_CHUL;

1. 파티션 뷰의 장점
1)WHERE 조건을 만족시켜야 파티션 뷰가 사용될 수 있다.
2)파티션 뷰를 구성하는 각각의 테이블에 별도의 인덱스가 생성될 수 있고 병렬옵션(PARALLEL OPTION)이 사용될 수 있다.
3)병렬옵션으로 파티션 뷰 전체를 병렬 스캔할 수 있다.

2. 파티션 뷰의 단점
1)파티션 뷰를 통해 테이블을 변경할 수 없다.
2)파티션 뷰 전체에 인덱스를 생성할 수 없다.

3. 파티션 뷰와 관련된 PARAMETER
- 어떤 컬럼 값을 기준으로 테이블들이 파티션 되었고 개발자의 SQL문에 의해 검색이 될 때 조건과는 상관없는 파티션은 무시되고 관련된 파티션들만 검색하게 하는 PARAMTER가 있다. 이 PARAMETER는 init<SID>.ora 파일에 정의할 수 도 있고 ALTER SESSION 명령어에 의해 설정할 수도 있다.

C:\> edit init<SID>.ora

PARTITION_VIEW_ENABLED=TRUE

또는

SQL> ALTER SESSION SET PARTITION_VIEW_ENABLED = TRUE;

Data pump
Fast Performance: Data Pump Export에서 direct path method를 사용시 single stream data unload에서 기존의 export보다 2배가 빠르다. Data Pump Import에서는 single stream의 data load 시 기존의 import보다 15~45배 빠르다고 한다.

C:\> sqlplus /nolog
SQL> conn sys/oracle as sysdba;
SQL> create directory dump as 'C:\Dump';
SQL> grant read, write on directory dump to public;
SQL> host

C:\> expdp system/oracle dumpfile=full.dmp logfile=export.log directory=dump full=y job_name=data_export

만약 중간에 끊기면(Ctrl+C)
Export> stop_job ---> job을 정시킨다.
이 작업을 정지하겠습니까([예]/아니오): 예

C:\> sqlplus /nolog
SQL> conn sys/oracle as sysdba;
SQL> select owner_name, job_name, operation, job_mode, state from dba_datapump_jobs;
SQL> exit

C:\> expdp system/oracle attach=data_export

C:\> sqlplus /nolog
SQL> conn sys/oracle as sysdba;
SQL> select owner_name, job_name, operation, job_mode, state from dba_datapump_jobs;

Export> start_job ---> job을 다시 시작한다.
Export> status

C:\>

C:\> impdp system/oracle directory=dump dumpfile=full.dmp schemas=scott logfile=import.log

-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------

2011년 10월 27일 추가 구문들

C:\> sqlplus /nolog
SQL> conn sys/oracle as sysdba;
SQL> create directory dump as 'C:\Dump';
SQL> grant read, write on directory dump to public;
SQL> host

C:\> expdp scott_d_p/scott_d_p dumpfile=dp_scott.dmp logfile=db_scott.log directory=dump schemas=scott_d_p job_name=exp_d_p

C:\> expdp scott_t_p/scott_t_p dumpfile=dp_scott.dmp logfile=db_scott.log directory=dump schemas=scott_t_p job_name=exp_c_p