About DATABASE

logminer사용하기~!! 

1. 파라미터 파일에 dictionary file 이 생성될 위치를 지정한다.

    utl_file_dir="d:\oracle"    -->> spfile을 사용할 경우 alter system set utl_file_dir='c:\oracle\ora92\logminer') scope=sfpile; 을 사용하여 dict파일을 생성될 위치를 생성해주면 된다!!

 

2. db 를 restart 한다.

  SQL> shutdown immediate

  SQL> startup

 

4. dbms_logmnr_d.build 를 사용하여 dictionary file을 생성시킨다.

   SQL> execute dbms_logmnr_d.build('dir_file','c:\oracle\ora92\logminer);

 

   PL/SQL 처리가 정상적으로 완료되었습니다.

 

 

 

5. 분석할 아카이브 로그파일및 리두로그파일을 추가시킨다.

 

   SQL>  execute dbms_logmnr.add_logfile('d:\oracle\oradata\ocp\redo01.log',dbms_logmnr.new);

 

    PL/SQL 처리가 정상적으로 완료되었습니다.

 

 

 

 

 

< 참조문서: 분석을 위한 Redo log 지정하기 >

   dictionary file을 생성하였으면 이제 redo log를 분석할 수 있다.
   첫 단계로, 분석하고 싶은 log file을 ADD_LOGFILE procedure를 이용하여
   지정한다. procedure의 parameter로 NEW / ADDFILE / REMOVEFILE 등의 상수를
   이용한다. 값을 지정하지 않을 경우 default로 ADDFILE이 배정된다.

   각 상수의 값은 다음과 같다.

 - NEW : 1
 - ADDFILE : 3
 - REMOVEFILE : 2

 

   1. sqlplus를 사용해서 Oracle Instance를 기동한다.
  SQL> startup

 

   2. dbms_logmnr.add_logfile procedure에 NEW parameter를 지정함으로써
       log의 리스트를 만든다. log는 online redo log file이거나 archived
       log file 중에서 지정해야 하고 일반 filie을 지정할 경우 error가 발생한다.

      SQL> exec dbms_logmnr.add_logfile('/home2/o8ii/oradata/o8ii/redo01.log',1);

 

   3. log 리스트에 첨가하는 작업
      SQL> exec dbms_logmnr.add_logfile('/home2/o8ii/oradata/o8ii/redo02.log',3);

 

   4. log 리스트에서 삭제하는 방법
      SQL> exec dbms_logmnr.add_logfile('/home2/o8ii/oradata/o8ii/redo02.log',2);

 

6. 분석 시점을 지정한다.

    SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'d:\oracle\dir_file',-
       starttime=>to_date('2005/08/09 15:55:10','RRRR/MM/DD HH24:MI:SS'),-
       endtime=>to_date('2005/08/09 16:05:10','RRRR/MM/DD HH24:MI:SS') );

 

    PL/SQL 처리가 정상적으로 완료되었습니다.

 

또는 파일 이름을 지정하면 된다!!~!! 시점을 지정해서 검색도 가능하다!!!

 

7. v$logmnr_contents  를 조회하여 원하는 정보를 추출한다.

     
   SQL> alter session set nls_date_format='RRRR/MM/DD:HH:MI:SS';

 

 

   SQL> col seg_owner for a10

   SQL> col username for a10

 

   SQL> col sql_redo for a30

                                                                                                                                                                                                                                       
                                                                                                                                                                                                                                                                                    
    SQL> select timestamp,seg_owner,username, sql_redo from v$logmnr_contents
             where sql_redo like '%drop%';

TIMESTAMP              SEG_OWNER     USERNAME                 SQL_REDO                                                                                                                                                                                                                                     
------------------- ---------          --------------------- ---------------                                                                                                                                                                                                               
2005/08/09:03:57:33 SYS                  SYS                          drop table emp900;

 

 

 

==========================================================================

 

 

 

 

로그마이너를 통해 원하는 시점의 log정보를 검색해서 내가 어느 시점에 파일을 drop시켰는지 변경했는지를 알 수 있다!!

그 후 데이터베이스의 테이블을 불완전 복구하면 원하는 시점으로 테이블을 복구 할 수 있다!

예를 들어 내가 2008년 7월 22일 15:00:00 시에 scott의 emp테이블을 drop했을경우 나는 오프라인 백업데이터를 가져와 그 시점까지만 복구하면 drop하기 바로전까지 복구 할 수 있는 것이다

 

1.백업된 파일은 내 데이터 베이스로 복사해온다...

 

2.불완전 복구작업을 수행할 때 가장 중요한 포인트 중에 하나는 마지막 오프라인 백업된 모든 데이터 파일들을 현재 경로로 복사해야 한다는 것입니다.

이때 ctl파일이나 redo파일등은 복사할 필요 없고, 데이터 파일이 깨진 것이므로 모든 데이터 파일만 복사하면 된다.

 

불완전 복구 방법에서는 특정 파일만 재 설치해서는 모든 데이터를 과거 특정시점으로 되돌릴 수 없기 때문에 반드시 모든 데이터를 과거 시점으로 복구해야 하기 때문이다!!!

 

 

 

3.startup mount

 

4.SQL> set autorecovery on --->> recover database 실행하여 아카이브 적용시 자동으로 auto를 적용시켜줌

 

5.SQL> recover database until time '2008-07-22 15:33:00';

 

6.SQL> alter database open; until time을 사용하여 복구해서 datafile의 scn이 다르기 때문에 resetlogs를 사용하여 강제로 scn과 모든 상태정보값을 초기화 시킨 후 오픈하여야 한다~!

 

7.SQL> alter database open resetlogs;

 

 

 

ORACLE10G 에서 추가된 LOGMINER 의 기능
======================================

출처:한국오라클 불리틴

 

PURPOSE
-------
이 자료는 Oracle10g version에 추가된 LogMiner 기능에 대해 설명한다.

 

Explanation
-----------
Oracle10g LogMiner 에서는 아래의 기능들이 추가되었다.

1.  LogMiner in a Shared Server Environment

 

기존에는 Dedicated mode에서만 logminer실행이 가능했으나 Oracle10g 에서는 MTS 환경에서도 logminer의 실행이 가능하다.

2.  Support for Index Organized Tables

 

Logminer 는 IOT 에 대해서도 Logminer를 통해서 정확한 record 변화를 찾을 수 있다. Oracle9i 까지는 Logminer에서 IOT 를 지원하지 못했다.

3.  Support for new datatypes

 

LogMiner 10g 에서는 아래의 data type을 redo/undo transaction에서 정확히 반영하게 되었다.  즉, 아래의 parameter가 새로

지원된다.

 

  - LONG
  - Multibyte CLOB
  - NCLOB

4.   SQL_REDO/SQL_UNDO without rowids - NO_ROWID_IN_STMT

 

DBMS_LOGMNR.START_LOGMNR procedure실행시에 NO_ROWID_IN_STMT option을 추가할 수 있다. 이 기능은

v$logmnr_contents view의 SQL_REDO 와 SQL_UNDO column에서 rowid 를 나타내지 않도록 하는 option이다.

이 rowid 를 나타내지 않으면 이 sql들을 rowid 가 다른 환경에서도 사용할 수 있으므로 더 유용하다.  logminer 기능을  object가

원래 있는 db가 아닌 다른 db에서 실행할 때 이 기능을 이용하여 rowid 가 아닌 primary key 등에 의해 sql 을 적용할 수 있다.

 

[예제]

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-
         OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
         DBMS_LOGMNR.NO_ROWID_IN_STMT);

 

5.  Removing a logfile from the LogMiner session

 

Oracle9i 까지는 Logminer session을 실행하여 log file이나 archiving file을 add한 후에는 list 에서 삭제할 수가 없었다. 

Oracle10g 부터는 add한 file을 삭제할 수 있는 기능이 추가되었다.

이 기능은 다음과 같이 DBMS_LOGMNR.REMOVE_LOGFILE()를 이용하여 가능하다.

[예제]

SQL>  EXEC DBMS_LOGMNR.REMOVE_LOGFILE(LOGFILENAME => '/oracle/logs/log2.f');

만약 위와 같이 logfile을 삭제한 후에 바로 v$logmnr_contents view를 조회하면
아래와 같이 ora-1306 error가 발생한다. 그러므로 다시 DBMS_LOGMNR.START_LOGMNR 를
실행한 후 조회해야 한다.

SQL>  select count(*) from v$logmnr_contents;
 select count(*) from v$logmnr_contents
                      *
ERROR at line 1:
ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from
v$logmnr_contents

 

Reference Documents
-----------------
<Note:249001.1>

======================================================================================================

얼마전에 저는 정말 절망 속에서 있었습니다.
지난 3개월간 작업한 모든 데이타를 rm -rf 로 날려버렸고.
백업도 2달 전에 해놓은거라. .;; 진짜 암담한 상황이었습니다.

몇몇 지인분들께 여쭤봐도 "포기해~ 그게 정신건강에 좋아" 라는 얘기만 들었습니다.

그러다가 보게 된. http://kldp.org/node/103288

하던중 큰 시행착오는 없었고 저 문서대로만 하면되는데.
umount 하는데 ;; 문제가 많이 발생했습니다. 아래 차근 차근 설명을 쓰겠습니다.

제가 사용하는 centOS 에서만 그런지 모르겠지만.
옵션이 "-" 문자가 아니라 "--" 문자로 해야 작동을 했습니다.
--help 쳐보면 옵션 나오죠^^
저처럼 아무것도 모르는 초짜를 위해서 말씀드리자면.

저 링크에 있는 파일 받아서 설치하기 위해선
1-      ./configure (물론 압축풀린 곳에서)
2-      make
3-      make install

요렇고롬 해주면 됩니다.
패키지로도 되어있다고 하니깐. 우분투 패키지 관리자에서 잘 찾아보시길.

ext3grep 입니다.


설치후에 umount 해주니

device 가 아직 작동중이랍니다.

그래서 fuser -km /dev/sda6 (제 파티션 장치가 /dev/sda6입니다)
해서 연결된 장치를 지워주니 ssh 가 끊깁니다.;

원인인 즉슨, 제가 일반 유저로 로그인해서 su - 로
최고관리자 권한을 가져서 인데

이건
vi /etc/ssh/sshd_config
PermitRoot no <--- yes
/etc/init.d/sshd restart

요렇고롬 세팅 해주면 됩니다.

그런 문제없이 자연스레 umount 가 되시는 분은 그냥 넘어가십시요.

그다음 ext3grep –-dump-names --after=12146454 /dev/sda6
  위의 숫자는 unix time 입니다. php에서 mktime(시,분,초,월,일,연)
  해주면 쉽게 구할 수 있죠.(다른 방법있다면 그걸 선택하세요)

여튼 전 history 명령으로 제가 rm -rf 를 친 그 시점에대해서 복구하려고 위의
세팅을 맞췄습니다.


저렇게 하면 sdb1.ext3grep.stage1 와 sdb1.ext3grep.stage2
파일 비스므레한 파일이 생깁니다. ( 시간이  꽤 걸렸습니다 저는. 한시간 반정도?)
앞에껀 inode 정보 뒤에껀 디렉토리 정보죠.
사람이 읽을 수 있는건 뒤에꺼고요. 필요한건 두파일 모두입니다.

건들진 마세요. 그냥 두시면됩니다.


그다음 --restore-all  옵션과 --after 옵션으로
위에서 가져온 정보에 대해서 복구를 합니다. 다시 재차 조회해 오진 않으므로
시간소요는 적습니다.



이렇게 해서 제가 실행한 위치에 /RESTORE~~~~~ 어쩌구 폴더가 생기면서

그곳에 제 파티션의 파일들이 복구가 되었습니다. ^^ 아 사랑스러워. ^^

그 다음 다시 이 디바이스를 해당 폴더에 mount 시켜주고 복구된 파일을 /RESTORE 에서
원래의 장소로 복사해주면 끝.


정말...ㅜ.ㅜ 눈물을 머금고 처음부터 재작업을 하려고 했는데

이런걸 알게되다니... 위의 글을 써주신 분께 다시한번 감사를.


설명이 좀 부족해서 말이 엉킬수도 있겠네요.

여튼. ^^ 도움이되셨으면 좋겠습니다




http://phpschool.com/gnuboard4/bbs/board.php?bo_table=tipntech&wr_id=67637

delete from t1 where c1 = 'GRACE';

이와 같은 방식으로 random으로 많은 양의 데이터가 삭제될 경우,
인덱스의 leaf block이 드문드문하게 채워져 있게 된다. (Index Fragmentaion)

물론, 장기적으로는 삭제된 공간은 재활용 될 수 있지만
인덱스는 sorting 되어야 한다는 특징에 의하여
Key 값이 들어갈 수 있는 자리가 정해져 있으므로 삭제된 공간을 재활용하지 못할 수 있다.

[Oracle Virus] - 왜 Index가 불필요하게 커지는가? 를 참조하면, 위 내용을 이해할 수 있다.

이 경우,
INDEX OFFLINE REBUILD
INDEX ONLINE REBUILD
INDEX SHRINK
INDEX COALESCE
를 통해 fragmentation 된 leaf 블록들을 정리한다.

이중, coalesce와 shrink는 Oracle 10g부터 동일한 방법(delete + insert)으로 수행되고,
Leaf block 간의 key 값을 compact 하는 것이므로 index의 높이에는 변화가 없다.

단, coalesce는 compact를 통해 확보된 index의 Free block들을 반환하지 않으므로,
Index의 fragmentation을 정리하지만 Index의 크기는 변하지 않는다. 

반면, Shrink는 compact를 통해 확보된 index의 Free Block들을 반환하고 HWM를 조정하므로
Index의 Fragmentation을 정리함과 동시에 Index의 크기를 줄인다.

# Coalesce TEST 

# Shrink TEST 

이 문서는 REDO log buffer 캐쉬의 아키텍쳐의 이해, 및 SGA 구조에 관련해 튜닝 해야 할 문제를 찾아내 해결하는 방법에 대해 설명하고 있습니다.

 

◆ 1.REDO로그 버퍼란 무엇인가???

REDO log buffer는 SGA내에 존재하며 데이타베이스에 대한 변경에 관한 정보를 보관 유지하는 버퍼입니다. 이 정보는 REDO 엔트리에 격납됩니다. REDO 엔트리는 데이타베이스에 대해서 행해진 변경을 재실행하기 위해서 필요한 정보를 포함하고 있습니다. 필요에 따라서 개개의 REDO 엔트리는 데이타베이스의 복구에 사용됩니다.   REDO엔트리는 Oracle 서버 프로세스에 의해 유저의 메모리 영역으로부터 SGA에 있어서의 REDO log buffer에 카피됩니다. 이 때 REDO 로그 엔트리는 버퍼내의 영역에 차례로 써집니다. 백그라운드 프로세스의 LGWR는, REDO log buffer의 내용을 디스크상의 액티브한 온라인 REDO 로그 파일 (온라인 REDO 로그 파일 그룹)에 씁니다. 초기화 파라미터 LOG_BUFFER는 REDO log buffer의 사이즈를 바이트 단위로 지정한 것입니다. 1개의 트랜잭션(transaction), 혹은 다중의 트랜잭션(transaction)에서 대량의 REDO log buffer에의 기입이 발생하는 환경에서는 일반적으로는 이 값을 크게 해 로그 파일에 대한 I/O수를 줄일 수가 있습니다. LOG_BUFFER 사이즈의 설정에 관해서는 KROWN#18227을 확인해 주세요.

 

◆ 2.REDO 로그 Latch

데이터 블록에 대한 변경이 필요할 때 이하의 순서에 의해 REDO log buffer내에 REDO 레코드가 작성됩니다. 다른 프로세스가 해당 프로세스보다 높은 COMMIT SCN(System Change Number)를 생성하고 있지 않는 것을 확인한다.   ■ REDO를 쓸 수 있는 스페이스가 있는지를 확인한다. 충분한 스페이스가 없는 경우는 LGWR가    REDO log buffer의 내용을 디스크상의 REDO 로그 파일에 쓰거나 로그 스윗치를 실시한다. ■ REDO log buffer내에 필요한 스페이스를 할당한다. ■ REDO log buffer에 REDO 레코드를 카피하고, 리커버리 시에 사용 가능하도록 관련성 실시 데이타베이스에는 이 처리를 실행하기 위해 3개의 REDO Latch가 있습니다. ・redo copy latch redo copy latch는 상기 순서를 실행하는 동안 보관 유지되고 있습니다. 빈 공간을작성하기 위해서 로그 스윗치가 실행되었을 때에 해방되어 로그 스윗치가 종료하면 다시 취득합니다.   ・redo allocation latch SGA내의 REDO log buffer에 대한 REDO 엔트리의 기입을 직렬화하기 위해서 사용됩니다. 트랜잭션(transaction)량이 적은 혹은 1 CPU의 서버인 경우에는 redo allocation latch는 redo copy latch를 획득하지 않고  REDO를 REDO log buffer에 씁니다.빈 스페이스를 획득하기 위해서 로그 스윗치가 필요하게 되는 경우는 이 Latch는 redo copy latch와 함께 해방됩니다. 이 Latch는 데이타베이스에 1개 밖에 없습니다.   ・redo writing latch LGWR 프로세스에 대해서 동시에 복수 프로세스에 의한 로그 스윗치의 요구가 발생하는 일을 막습니다. 빈 공간이 필요한 프로세스는 LGWR에 REDO 로그 버퍼로부터 REDO 로그 파일에의 쓰거나  혹은 로그 스윗치의 실행을 요구 하거나 단지 대기할까를 판단하기 이전에 이 latch을 취득해야 합니다.

 

◆ 3.REDO Latch에 관한 인스턴스 파라메터

REDO log buffer에 있어서의 Latch 할당의 동작에 관련하는 2개의 파라미터가 있습니다.   ・LOG_SIMULTANEOUS_COPIES   시스템이 복수CPU를 가질 때 redo copy latch의 수를 제어   ・LOG_SMALL_ENTRY_MAX_SIZE   REDO log buffer에 REDO를 쓸 때 redo allocation latch를 취득할지 아닐지를 결정하는 기준

 

◆ 4.REDO로그 버퍼의 퍼포먼스 튜닝

REDO log buffer에 있어서의 경합은 모든 DML 및 DDL문은 실행전에 REDO 엔트리를 작성 할 필요가 있기 때문에 퍼포먼스상 중요합니다. 경합은 Latch 경합 혹은 REDO 로그 버퍼에 대한 빈 공간의 과도의 요구가 있는 것으로 확인할 수 있습니다. 이하에 열거한 2 종류의 경합이 발생하고 있는 일을 검출하는 것이 가능합니다.   ・Latch 경합 다음의 SQL는 REDO 로그에 있어서의 Latch의 사용율을 표시하는 것입니다.         SELECT  substr(ln.name, 1, 20) "latch type",                   100*(misses + immediate_misses)/(gets +                   immediate_gets + immediate_misses) "latch utilization(%)"        FROM v$latch l, v$latchname ln      WHERE  ln.name in ('redo allocation', 'redo copy')    　　 and ln.latch# = l.latch#;   이 결과 latch utilization가 1%를 넘는 경우 Latch 경합이 발생하고 있다란것을 의미합니다. redo copy latch의 전에 redo allocation latch의 튜닝을 실시하는 일을 추천합니다.redo allocation latch는 인스턴스에 1개 밖에 없기 때문에 redo allocation latch의 경합은 퍼포먼스상 영향도가 높기 때문입니다.   Oracle7,8i에서는 redo alocation latch의 경합이 발생하는 경우 LOG_SMALL_ENTRY_MAX_SIZE의 값을 낮추고 redo copy latch가 획득되기 쉽게 조정하는 것이 좋다. 추천치는 평균의 REDO 사이즈이며 이하의 SQL의 결과의 'redo size'를 'redo entries'의 값으로 나눈 값이 됩니다.       SELECT value        FROM v$sysstat     WHERE name in ('redo size','redo entries');   다만 이 SQL를 실행할 때는 실전 환경과 같은 조건이 아니면 적절한 결과를 얻을수 없습니다. redo copy latch 경합이 발생하고 있는 경우는 LOG_SIMULTANEOUS_COPIES 파라미터를 증가시키는 것으로 새로운 redo copy latch를 추가시킬 수가 있습니다.이 파라미터의 추천치는 CPU수*2 가 됩니다.   Oracle8i에서는 redo allocation latch 경합이 발생하고 있는 경우, 특정의 처리에 대해서는  NOLOGGING 옵션을 사용하면 생성되는 REDO를 감소시킬수  있습니다 혹은 LOG_BUFFER 파라미터를 증가시키는 것으로 latch의 부하를 낮추는 것이 가능합니다. LOG_BUFFER 파라미터의 튜닝에 관해서는 KROWN#18227 로 소개있습니다.redo copy latch 경합이 발생하고 있는 경우 Oracle8 이전과 같이 LOG_SIMULTANEOUS_COPIES를 증가 시킨다. ・영역 할당의 리퀘스트의 경합 REDO log buffer의 영역 할당을 대기한 회수는 "redo buffer allocation retries"라는 통계로 카운트 됩니다. 다음의 SQL를 사용하고, 어플리케이션이 동작중의 통계치를 감시해야 합니다.          SELECT name,valus 　    FROM v$sysstat 　　WHERE name = 'redo buffer allocation retries';   "redo buffer allocation retries"의 값은 0인 것이 이상적입니다. 이 값이 증가 경향에 있는 경우에는 LOG_BUFFER 파라미터의 값이 너무 작을 가능성이 있습니다. LOG_BUFFER의 튜닝 방법에 대해서는 Krown:18227을 참조하십시오.

CTAS 를 통한 테이블 복제시 제약 조건

 

	Local	Remote
Column Name, Type, Length		그대로 Copy 됨
Column Default Value		No
Index		No
Constraint		No
Not Null ( PK 에 의한 Not Null 포함 )		그대로 Copy 됨그대로 Copy 됨
Grant		No
Synonym		No
Trigger		No

 

테스트 결과는 첨부 화일 참조 바랍니다.

 

1. CTAS 의 경우 Parallel Hint 는
   CT(Create Table) 에서는 사용하여도 의미가 없고(ORACLE 이 무시함),
   AS 에서만 Parallel Hint 를 사용한다.
   (As Select /*+ paralllel(a) parallel(b) */ * from tab1 a, tab2 b)
   식으로 사용한다.

2. 캐릭터셋의 다른 DBMS 간의 CTAS 사용시 Column Size 가 달리 될수 있다.
   [ 참고 - http://cafe.naver.com/prodba/2014 ]
   이 경우 _keep_remote_column_size=true 로 설정 후 DB Restart 후에 CTAS 를 사용하면 된다고함
   _keep_remote_column_size 파라미터의 의미 : remote column size does not get modified
   Default 값은 False 임  

3. 그럼 가장 빠르게 테이블 복제 [ Export / Import 제외 ]
  
  3.1 Create Table COPY_Table as select * from Source_Table@remote where 1=2
   를 통해서 테이블 껍데기만 Copy 후에
  3.2 alter session enable parallel dml;
  3.3 insert /*+ parallel(Copy_Table, 10) */ into Copy_Table nologging
      select /*+ parallel(Source_table, 10) */ * from Source_Table@remote ;
  3.4 Creae index ... Nologging PARALLEL ;
      CREATE INDEX XAK_COPY_Table ... NOLOGGING PARALLEL ;
  3.5 ALTER index ... LOGGING NOPARALLEL ;
      ALTER index XAK_COPY_Table logging noparallel ;
  3.6 통계정보 수집
     exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'COPY_Table ');
  3.7 기타 추가 작업
     Column Default Value, Index, Constraint, Grant, Synonym, Trigger 

4. CTAS 를 쓸것인가 ? Export/Import 를 쓸것인가 ?
   CTAS - Parallel 처리가 가능하다는 강점이 있다.
          단. DB Link 를 통해서 통신 하기 때문에, Network 로 근거리(한국 내 정도 ^^;)어야 하고,
              양쪽 DB 의 Characterset 이 동일 해야 한다. 기타 DB LInk 사용에 따른 제약이 없어야 한다.
   Export/Import - Dump 화일 생성을 위한 저장할 공간이 필요하다.
                   양쪽 DB 의 Characterset 이 다르더라도, Sub Set 에서 Super Set 으로 이관시 아무런
                   문제가 되지 않는다. 비교적 먼거리 시에는 Remote 에서 Dump 화일 생성, ftp 전송,
                   Local Import 수행이 가능하다.

일자 데이터 타입이란 YYYYMMDD 형(시/분/초 제외)을 이야기 하는 것이다. 이때 DATE 타입을 선택할 것인가 아니면 VRACHR2(8)을 선택할 것인가의 문제이다. 이것은 성능 문제이기도 하지만 물리 모델링, 개발효율성, 데이터 품질 등을 같이 생각 해야 한다. 물리모델링 시에 많은 모델러들이 일자 데이터 타입과 관련하여 이구동성으로 이야기 하는 것이 아래의 SQL 이다.

 

SELECT ...                     

  FROM ...                     

 WHERE 기준일자 = TO_DATE('20091021', 'YYYYMMDD') ;

 

위의 SQL 에서 일자 컬럼에 시/분/초가 포함되어 있다면 조회가 되지 않는다.

그렇다고 SQL 을 아래처럼 작성하는 것은 개발효율성이 떨어지고 성능에도 이롭지 못하다.

 

SELECT ...                     

  FROM ...                     

 WHERE 기준일자 BETWEEN TO_DATE('20091021','YYYYMMDD') AND TO_DATE('200910212359', 'YYYYMMDDHH24MISS') ;

 

아니 땐 굴뚝에 연기가 날까?

VARCHAR2(8)을 선호하는 사람들이 주로 이 문제를 제기한다. DATE 타입은 시/분/초가 들어감으로써 세가지 문제(데이터가 조회되지 않을 수 있고, 개발효율성과 성능이 떨어짐)가 발생함으로  VARCHAR2(8)을 사용해야 한다는 것이다.

 

하지만 과연 이 말이 사실일까? 모든 문제는 모델러가 시/분/초가 들어갈 수 있게 설계를 했기 때문이다. 왜 그런지 아래 스크립트를 보고 증명해보자

 

-- DATE 형과 VARCHAR2 형을 동시에 가진 테이블 생성                           

CREATE TABLE DT                                                              

( V_DT  VARCHAR2(8 BYTE),                                                    

  D_DT  DATE ) ;                                                                                                                                    

                                                                              

--일자 타입이 date 인 경우에 시/분/초가 입력될 경우 걸러내는 Constraint임.        

--ex) 시/분/초를 포함하는 SYSDATE를 입력하면 에러를 발생시키기 위함.                        

--CHECK 절에 OR 가 있는 이유는 NULL 을 허용하기 위해서 이다.

ALTER TABLE DT                                                               

ADD CONSTRAINT D_DT_CHK                                                      

CHECK (D_DT = TRUNC(D_DT) OR D_DT IS NULL) ;        

            

이제 DATE 타입에 정상적인 데이터와 걸러져야 하는 데이터를 INSERT 해보자                            

 

--NULL을 대입해도 에러 발생하지 않음                                                                              

INSERT INTO DT (V_DT, D_DT) VALUES(NULL, NULL) ;                                                  

 

--SYSDATE는 시/분/초가 들어감으로 INSERT 되면 안됨                                                                              

INSERT INTO DT (V_DT, D_DT) VALUES (NULL, SYSDATE) ;                          

ORA-02290: 체크 제약조건(D_DT_CHK)이 위배되었습니다                   

                   

--에러 발생하지 않음                                                                             

INSERT INTO DT (V_DT, D_DT) VALUES (NULL, TRUNC(SYSDATE)) ;            

 

Constraint는 데이터 품질을 보장해준다

위에서 보는 바와 같이 Constraint는 시/분/초가 들어가지 않도록 보장해준다.

이것은 성능이 느린 VARCHAR2 타입을 사용하지 말아야 하는 이유가 될 수 있다.

 

어쩔 수 없이 VARCHAR2(8)을 사용하더라도 Constraint를 사용하라

일자 데이터 타입에 VARCHAR2(8)을 사용할 때 날짜가 아닌 데이터가 들어가는 문제도 마찬가지로 해결할 수 있다. 아래처럼 Constraint를 사용하면 된다.

 

--일자 타입이 VARCHAR2 인 경우에 잘못된 데이터를 걸러내는 Constraint 임.        

--ex) ‘20090230’을 걸러낸다.

--CHECK 절에 OR 가 있는 이유는 NULL 을 허용하기 위해서 이다.                                                  

ALTER TABLE DT                                                               

ADD CONSTRAINT V_DT_CHK                                                      

CHECK (V_DT = TO_CHAR(TO_DATE(V_DT,'YYYYMMDD'), 'YYYYMMDD') OR V_DT IS NULL) ;

 

이제 VARCHAR2(8)에 일자가 아닌 데이터를 넣어보자

 

--2월30일은 일자가 아니므로 INSERT 되면 안됨                                                                             

INSERT INTO DT (V_DT, D_DT) VALUES ('20090230', NULL) ;                       

ORA-01839: 지정된 월에 대한 날짜가 부적합합니다

 

--에러 발생하지 않음                                                                             

INSERT INTO DT (V_DT, D_DT) VALUES ('20090228', NULL) ;  

 

성공적으로 일자가 아닌 String 을 걸러 내었다.

 

결론

이제 알 것이다.  Constraint가 데이터 품질을 향상시키고 개발효율성을 높이며 성능에 까지 영향을 끼친다는 사실을

반박하라. 시/분/초가 들어감으로 VARCHAR2(8)을 사용해야 한다는 주장을

사용하라. 성능과 데이터 품질을 향상 시키는 DATE 타입을

오라클에서 시/분/초는 제외하고 일자만 저장되는 데이터 타입을 제공한다면 하는 아쉬움이 진하게 남는다.  미래의 버젼에 이러한 요구사항을 해결한 데이터 타입이 나오길 기대한다.

PS

DATE 타입을 성급히 적용하면 안 된다. 기존의 시스템은 일자가 아닌 데이터도 문제 없이 처리가 되었을 것이다. 하지만 DATE 타입으로 바꾸면 에러로 떨어진다. 따라서 에러의 처리정책과 처리Logic 등이 세워진 이후에 적용하라. 이런 골치 아픈 문제 때문에 VARCHAR2(8)을 사용하는 것은 말이 안 된다. 원칙적으로 에러로 떨어지는 것이 정당한 것이고 데이터를 수정하고 다시 처리하는 것이 옳다.

  

아래는 DATE 타입과 VARCHAR2 타입의 장단점 이므로 참고하기 바란다.

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

성능상으로는 DATE 형이 우월하다

여기에는 두 가지 이유가 있다.

 

첫 번째, DATE 타입은 옵티마이져가 날짜임을 인식한다.

 

SELECT ...                     

  FROM ...

 WHERE 기준일자 BETWEEN to_date('20091020', 'yyyymmdd') and to_date('20091021', 'yyyymmdd)

 

기준일자는 DATE 타입이다. 위의 조건대로라면 옵티마이져는 정확히 이틀간(10월 20일부터 21일까지)이라는 것을 인지한다. 따라서 옵티마이져는 인덱스로 SCAN 할 것인지 아니면 FULL TABLE SCAN 할 것인지 판단할 수 있다.

 

하지만 기준일자가 아래처럼 VARCHAR2(8) 타입이라면 달라진다.

 

SELECT ...                      

  FROM ...

 WHERE 기준일자 BETWEEN '20091020' and '20091021'

 

옵티마이져는 '20091020'가 일자인지 인식 하지 못한다. 따라서 이틀 치의 데이터를 조회한다는 사실을 인식하지 못한다. String 타입이므로 이것은 당연한 것이다.

 

두 번째, DATE 타입은 7 byte를 차지하고 VARCHAR2(8) 은 8 byte를 차지한다.

 

두 가지 이유에 의해서 성능은 DATE 타입이 조금이라도 우월함을 알 수 있다.  

 

그럼에도 불구하고 DATE 타입을 사용하지 않는 가장 큰 이유는 무엇일까? 크게 3가지 이유로 요약된다.

 

첫 번째, DATE 타입의 문제점은 sysdate 등을 입력할 경우 시/분/초 가 들어감으로 SQL을 실행하면 결과값이 나오지 않는다.(이 문제는 위에서 이미 언급되었음)

 

두 번째, 년도나 월 데이터를 조회할 때 LIKE를 사용하지 못하고 BETWEEN 을 사용해야 한다는 것이다. 이것은 큰 문제라고 보지 않는다. 조건절이 조금 길어질 뿐 INDEX RANGE SCAN 이라는 점은 같기 때문이다.

 

세 번째, SYSDATE 등을 INSERT할 때 TRUNCT 등의 함수를 사용하여 시/분/초 등을 잘라내야 한다.

                

그렇다면 VARCHAR2 타입의 문제점은 없는가?
크게 3가지의 문제점이 있다.

 

첫 번째, 성능문제(옵티마이져가 일자인지 알 수 없음)

이 문제는 ORACLE 11g를 사용하면 더욱 심각한 차이가 발생할 수 있다.

왜냐하면 Bind Aware 기능이 강화되었기 때문에 변수를 마치 상수처럼 취급할 것이고 이에 따라 DATE 타입이 성능 면에서 훨씬 우월해 질것이다. 

                 

두 번째, VARCHAR2 타입의 문제점은 날짜가 아닌 데이터가 들어갈 수 있다는 것이다. 예를 들면 'ABCDEFGH' 혹은 '20090231' 등의 잘못된 데이터가 입력될 수 있다. 이것은 데이터 품질에 치명적이다. 혹자는 'DATE 타입도 시/분/초가 들어가므로 마찬가지 아니냐' 라고 생각할 수 있지만 근본적으로 다르다. DATE 타입을 사용하면 시/분/초는 TRUNC 등의 함수를 사용하여 Cleansing할 수 있지만 VARCHAR2는 그렇게 할 수 없다.

 

예를 들어 '20091032' 라는 데이터를 Cleansing 해야 한다고 치면 10월 31일로 할 것인가? 아니면 11월 1일로 할 것인가? 이것은 함부로 판단할 수 없는 것이다. 혹자는 'CHECK 기능을 추가하여 모든 프로그램에서 일자가 아닌 것을 CHECK 하면 되지 않냐?' 라고 할 수 있다.

 

맞는 말이다. 하지만 프로그램을 사용하지 않고 직접 DB에 KEY IN 하여 INSERT 할 수도 있기 때문에 원천적으로 원인을 제거한다고 볼 수 없다.(급한 경우에는 이렇게 하기도 한다)

 

세 번째, 성능문제에도 불구하고 개발편의성을 위해 VARCHAR2를 사용하였지만 일자연산이 발생하면 오히려 개발생산성이 저하된다.

 

예를 들면 일자끼리 빼서 차이를 본다든지 아니면 일자에 며칠을 더해서 본다든지 일자에 연산이 일어날 경우 오히려 TO_DATE 등의 함수를 사용해야 한다. 이런 경우는 자주 발생되는 편이다.

 

이상으로 DATE 타입과 VARCHAR2 타입의 장단점을 살펴보았다.

이 글과 관련된 POST 도 참고하기 바란다.

http://ukja.tistory.com/265


출처 : http://scidb.tistory.com/87?srchid=BR1http%3A%2F%2Fscidb.tistory.com%2F87

1.해당하는 PK를 삭제한다
: ALTER TABLE TABLE명 DROP CONSTRAINT PK명;

 

2.변경하고자 하는 COLUMN으로 Unique Index를 생성한다.
: CREATE UNIQUE INDEX PK명 ON TABLE명(COLUMN명) TABLESPACE TABLESPACE명;

 

3.PK에 속성을 추가한다.
: ALTER TABLE TABLE명 ADD CONSTRAINT PK명 PRIMARY KEY(COLUMN명);

 

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

 

 

-- PK 제거하기

Alter TABLE 테이블이름 drop primary key cascade

 

--PK추가 하기

ALTER TABLE 테이블이름 ADD CONSTRAINT 인덱스 이름 PRIMARY KEY(field1, field2)


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

1. PK INDEX Create

 

ALTER TABLE TABLE_NAME
        ADD CONSTRAINTS PK_INDEX_NAME
            PRIMARY KEY (PK_1, PK_2, PK_3)
            USING INDEX
            TABLESPACE TABLESPACE_NAME
            STORAGE(    INITIAL     1280K
                        NEXT        1280K
                        PCTINCREASE 0   );

2. INDEX Create

 

CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(PK_1, PK_2, PK_3)

TABLESPACE TABLESPACE_NAME

STORAGE ( INITIAL 5M NEXT 5M PCTINCREASE 0 );

 

3. PK_INDEX Drop

 

ALTER TABLE TABLE_NAME DROP CONSTRAINT INDEX_NAME;

 

4. INDEX Drop

 

DROP INDEX INDEX_NAME;