[SQL고수되기] 네번째 이야기

출처: 네이버지식인 노하우
http://kin.naver.com/knowhow/detail.nhn?d1id=8&dirId=8&docId=552877&qb=U1FM6rOg7IiY65CY6riw&enc=utf8&section=kin&rank=6&search_sort=0&spq=0

이번 이야기에서는 저번에 이어 테이블을 Create할 것입니다.

그나저나 다른 두개 테이블에대해서 PK는 만들어오셨는지요. 참~~ 쉽죠~잉 ^^

가장 중요한건 EMP_INF 테이블하곤 관리 자체가 다르다는 점입니다.

그 이유는 PK의 첫번째가 시기정보가 들어간다는 점입니다.

이게 굉장히 중요한데요.. 앞으로 차차 설명드리겠습니다.

 

그럼 테이블을 만들어봐야 겠지요?

Table 생성은 보통 이렇게들 하시지요?

CREATE TABLE EMP_INF
(
    EMP_NO  CHAR    (8 ) Primary Key,  /* 사번   */
    SNAME   VARCHAR2(50),              /* 이름   */
    PHONE   VARCHAR2(20),              /* 핸드폰 */

    ....
)
문법적으론 맞는 스크립트입니다.

하지만 관리영역이 없죠?

또, 중요한 PK영역을 Default로 설정하고 있습니다.

또, 테이블의 특성에 맞는 Block에 대한 정의 또한 없습니다.

 

그럼 다시 만들어보겠습니다.

CREATE TABLE EMP_INF
(
    EMP_NO  CHAR    (8 ) Primary Key,  /* 사번   */
    SNAME   VARCHAR2(50),              /* 이름   */
    PHONE   VARCHAR2(20),              /* 핸드폰 */

    ...

    ...

    CONSTRAINT EMP_INF_PK PRIMARY KEY (EMP_NO)
    USING INDEX TABLESPACE TS00 PCTFREE 10
    STORAGE ( INITIAL 5M )
)
TABLESPACE TS01
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL        20M

            NEXT           20M

            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOLOGGING
NOCACHE
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
NOMONITORING;

아시는 분들도 계실테지만 부분적인 설명을 드리겟습니다.

익숙하지만 첨보시는 분들도 계실테고 또 왜 하는지 모르고 팬턴 복사 하드시

Copy&Paste하셔서 사용하셨던 분들도 계셨을 겁니다.

상단에 EMP_INF_PK라고 따로 이름을 설정했습니다. Data Mining이나 튜닝등 관리적인 차원에서 관리하려면 반드시 이름을 명시하셔야합니다. 그래야 활용가능합니다. 왜냐하면 인덱스도 테이블이기 때문입니다. 여러분들이 SELECT 하시게되면 EMP_INF 테이블을 그대로 찾는 경우도 있지만(TABLE FULL SCAN) 대부분 인덱스를 거쳐 검색(ROWID SEEK INDEX)하게됩니다. 저기요 바로 찾는게 더 빠르지 않을까요? 라고 말씀하시는 분들이 계실겁니다.(^^) 바로 찾는다는 얘기는 하드디스트의 트렉을 처음부터 끝까지 I/O한다는 말이됩니다. 어떨까요 짜증나겠죠? 그러나 인덱스를 활용하게되면 어떨까요? 참고로 인덱스는 DB가 START됨과 통시에 MEMORY에 Loading됩니다.

 

　　인덱스정보:

　　┌─────┬─┬─────┬───┐　　　┌───┬───┐

　　│INDEX Col1│..│INDEX ColN│ROWID │　->　│EMP_NO│ROWID │

　　└─────┴─┴─────┴───┘　　　└───┴───┘

 

인덱스 테이블에서 WHERE절에 해당하는 Col정보들을 검색하여 Buffer에 탑제하게 됩니다.(이걸 SCAN이라고 합니다.) 이후에 검색된 INDEX정보별로 저장되 있는 ROWID(저장위치)에 해당하는 실제 정보를 찾아들어가서 결과를 표시하게 됩니다. 이해하시는 분들도 계실테지만 조금 어렵죠?

검색 기법은 차차 이야기 할 예정이니 걱정마십시오. 중요한건 Table 생성을 잘해야 검새도 잘 할수 있다는 것입니다. ^^

그럼 인덱스 테이블(EMP_INF_PK)은 어디 생성될까요? 또 정보테이블(EMP_INF)은 어디 생성될까요? 바로 Tablespace라는 공간(데이터파일)에 생성됩니다. 근데 자세히 보시면 서로 다르다는 거 확인하셨나요? 그 이유가 멀까요? 관리되는 방법 자체가 다르기 때문입니다. 인덱스 테이블은 PCTFREE만 있고요 정보테이블은 PCTFREE와 더불어 PCTUSED라는 옵션이 하나더 있습니다.

굉장히 중요한 옵션중에 하나입니다. 매우매우 중요!! 별표 5개 ★★★★★!!!!

모든 테이블은 Block(8K)으로 구성됩니다.

 

　　　　　　　　　　　Block모형:

　　　　　　　　　　　　┌─────┐                   

　　　　　　　　　　　　│　　　　　│

　　　　　　　　　　──├─────┤<─　PCTFREE 영역(10%)

　　　　　　　　　　▲　│　　　　　│

　　　　　　　　　　│　│　　　　　│

　　　　　　　　　　│　│　　　　　│

　　　　　　INSERT영역　├─────┤<─　PCTUSED 영역 (40%)

　　　　　　　　　　│　│　　　　　│

　　　　　　　　　　│　│　　　　　│

　　　　　　　　　　▼　│　　　　　│

　　　　　　　　　　──└─────┘

 

INSERT질의가 수행되게 되면 위 그림에서 INSERT영역 사이에 등록됩니다. 그위에 PCTFREE 영역은 UPDATE시 활용되는 영역입니다. 또 PCTUSED 영역을 DELETE질의에 의한 데이터 삭제시 해당 BLOCK이 재사용될 수 있게 하는 표시부분으로 보시면 됩니다. 근데 인덱스테이블은 PCTFREE만 있는 이유는 인덱스정보는 실제 테이블이 삭제되더라도 절대 삭제되지 않습니다. 그래서 PCTFREE만 존재하는 것입니다. 강제로 잡으셔도 안됩니다.^^ 해결은 INDEX REBUILD 명령어로 해결하곤 합니다. 자세한 내용은 차차 설명드리도록 하겠습니다. 내용이 참 해깔리죠?

그럼 우리 만들려고 하는 테이블 기준으로 실제 구현내용에 대해 설명드릴께요. 중요한 내용은 테이블별 특징을 정의한 부분을 알고 계셔야 이해가 빠르실겁니다.

 

EMP_INF 테이블의 특징이 어땠었지요? 한번 추가되면 삭제되지 않고 수정만 발생함 이랬던걸로 기억합니다. 삭제가 없고 수정만 발생한다면 PCTFREE(Default 10으로 함)와 PCTUSED(Default 40으로 함)는 어떻게 하면 좋을까요? 당연히 삭제가 없으니 PCTUSED가 높을 필요는 없겠죠? 반대로 PCTFREE는 어떨까요 조금 높아도 되겠지요? 물론 수정이 빈번하지 않다면 그냥 Default로 해도 무방합니다. 그럼 중요한점 머가 좋아질까요? 바로 Block의 단편화 현상이 많이 없어지게 됩니다. 쉽게 말씀드리면 삭제가 됬다는 것은 Block안에 내용이 살아졌다는 얘기입니다. 그럼 비어있겠지요? 다시 사용하면 되겠네요.라고 말하시는 분들도 계실겁니다. 절대 그렇지 않습니다. 오라클은 현재 진행형이라 지금 처리하는 BLOCK위주로 INSERT가 진행됩니다. 하지만 BLOCK 사용량이 PCTUSED 이하가되면 다시 사용할 수 있게됩니다. PAY_INF처럼 수정이 없고 재생성될 수 있다 상황에서 PCTUSED가 40이라면 어떨까요?아주 운이없게 41% 사용됬다면 그BLOCK을 재사용이 안되겠지요? 만약 과거 데이터라면 더더욱 비참하겠네요. 하지만 PCTUSED가 80이라면 조금만 삭제되도 재사용하게됩니다. 이말은 그만큼 단편화되는 일이 적어지게 되는것입니다.

 

이처럼 첫 테이블 생성시에 잘못된 설계로 인해 SELECT시 너무 많은 BLOCK이 읽어지게 된다면 그만큼 서버에도 부하가 가게 되는 것입니다.

 

마지막으로 INITIAL 부분을 검토해보겠습니다. 이건 멀까요? 말그대로 최조 생성시 크기입니다.

너무 작다면 어떨가요? 데이터 10만건을 INSERT중에 공간이 작다면 늘리고 진행해야 겠지요? 뚝딱하면 공간이 늘어나는 것(EXTENTS)도 아니고 또 테이블 TRANSACTION 중에 빈번히 EXTENT가 발생한다면 그만큼 서버의 부하도 커지겠지요? 또 NEXT가 너무 작아도 마찬가지겠지요? 따라서 생성되는 량을 예상하여 잡는게 좋을것입니다.

 

후 오늘 정말 중요한 내용을 쉽게 설명하려했지만그래도 조금 어려운 부분이 없지않아 있었던듯 합니다.

 

그냥 글보시지 마시고 일별 출퇴근 정보(WORK_INF) 와 월별 시급 정보(PAY_INF) 테이블에 대해 시간 되시면 한번 검토해보시면 좋을듯 합니다.

 

다음 이야기는 테이블 생성 후 관리에 대해 이야기해보겠습니다.