Postgresql Partitioning

Posted at 2012. 1. 22. 17:23 | Posted in Database
반응형
Postgresql 파티션 테이블 생성 및 사용 방법입니다.

http://www.postgresql.org/docs/8.4/static/ddl-partitioning.html


Create Partition Table

먼저 파티션 테이블을 적용할 마스터 테이블을 하나 만듭니다.

  1. -- drop table log cascade;
  2.  
  3. -- master table
  4. create table log (
  5.     -- uuid
  6.     log_uuid UUID not null default uuid_generate_v4(),
  7.     -- 인증 아이디
  8.     athn_id text not null,
  9.     -- 서비스명
  10.     svc_nm text not null,
  11.     -- 오퍼레이션명
  12.     oprt_nm text not null,
  13.     -- 요청일시
  14.     log_rqst_dt timestamp not null,
  15.     -- 응답일시
  16.     log_rply_dt timestamp not null,
  17.     -- 상태 코드
  18.     log_stat_cd text not null,
  19.     -- 등록일시
  20.     log_crtn_dt timestamp not null
  21. );

이제 데이터를 월 단위로 나눠서 담게될 파티션 테이블 12개를 만들어줍니다.

  1. -- partition table
  2. create table log_m01 (
  3.     check (date_part('month', log_rqst_dt) = 1)
  4. ) inherits (log);
  5.  
  6. create table log_m02 (
  7.     check (date_part('month', log_rqst_dt) = 2)
  8. ) inherits (log);
  9.  
  10. create table log_m03 (
  11.     check (date_part('month', log_rqst_dt) = 3)
  12. ) inherits (log);
  13.  
  14. create table log_m04 (
  15.     check (date_part('month', log_rqst_dt) = 4)
  16. ) inherits (log);
  17.  
  18. create table log_m05 (
  19.     check (date_part('month', log_rqst_dt) = 5)
  20. ) inherits (log);
  21.  
  22. create table log_m06 (
  23.     check (date_part('month', log_rqst_dt) = 6)
  24. ) inherits (log);
  25.  
  26. create table log_m07 (
  27.     check (date_part('month', log_rqst_dt) = 7)
  28. ) inherits (log);
  29.  
  30. create table log_m08 (
  31.     check (date_part('month', log_rqst_dt) = 8)
  32. ) inherits (log);
  33.  
  34. create table log_m09 (
  35.     check (date_part('month', log_rqst_dt) = 9)
  36. ) inherits (log);
  37.  
  38. create table log_m10 (
  39.     check (date_part('month', log_rqst_dt) = 10)
  40. ) inherits (log);
  41.  
  42. create table log_m11 (
  43.     check (date_part('month', log_rqst_dt) = 11)
  44. ) inherits (log);
  45.  
  46. create table log_m12 (
  47.     check (date_part('month', log_rqst_dt) = 12)
  48. ) inherits (log);

기본키를 생성합니다.

  1. -- create primary key
  2. alter table log add constraint log_pk primary key (log_uuid);
  3. alter table log_m01 add constraint log_m01_pk primary key (log_uuid);
  4. alter table log_m02 add constraint log_m02_pk primary key (log_uuid);
  5. alter table log_m03 add constraint log_m03_pk primary key (log_uuid);
  6. alter table log_m04 add constraint log_m04_pk primary key (log_uuid);
  7. alter table log_m05 add constraint log_m05_pk primary key (log_uuid);
  8. alter table log_m06 add constraint log_m06_pk primary key (log_uuid);
  9. alter table log_m07 add constraint log_m07_pk primary key (log_uuid);
  10. alter table log_m08 add constraint log_m08_pk primary key (log_uuid);
  11. alter table log_m09 add constraint log_m09_pk primary key (log_uuid);
  12. alter table log_m10 add constraint log_m10_pk primary key (log_uuid);
  13. alter table log_m11 add constraint log_m11_pk primary key (log_uuid);
  14. alter table log_m12 add constraint log_m12_pk primary key (log_uuid);

인덱스를 생성합니다. 마스터 테이블은 인덱스를 생성하지 않습니다.

  1. -- create index
  2. create index log_m01_athn_id_idx on log_m01 (athn_id);
  3. create index log_m01_svc_nm on log_m01 (svc_nm);
  4. create index log_m01_oprt_nm on log_m01 (oprt_nm);
  5.  
  6. create index log_m02_athn_id_idx on log_m02 (athn_id);
  7. create index log_m02_svc_nm on log_m02 (svc_nm);
  8. create index log_m02_oprt_nm on log_m02 (oprt_nm);
  9.  
  10. create index log_m03_athn_id_idx on log_m03 (athn_id);
  11. create index log_m03_svc_nm on log_m03 (svc_nm);
  12. create index log_m03_oprt_nm on log_m03 (oprt_nm);
  13.  
  14. create index log_m04_athn_id_idx on log_m04 (athn_id);
  15. create index log_m04_svc_nm on log_m04 (svc_nm);
  16. create index log_m04_oprt_nm on log_m04 (oprt_nm);
  17.  
  18. create index log_m05_athn_id_idx on log_m05 (athn_id);
  19. create index log_m05_svc_nm on log_m05 (svc_nm);
  20. create index log_m05_oprt_nm on log_m05 (oprt_nm);
  21.  
  22. create index log_m06_athn_id_idx on log_m06 (athn_id);
  23. create index log_m06_svc_nm on log_m06 (svc_nm);
  24. create index log_m06_oprt_nm on log_m06 (oprt_nm);
  25.  
  26. create index log_m07_athn_id_idx on log_m07 (athn_id);
  27. create index log_m07_svc_nm on log_m07 (svc_nm);
  28. create index log_m07_oprt_nm on log_m07 (oprt_nm);
  29.  
  30. create index log_m08_athn_id_idx on log_m08 (athn_id);
  31. create index log_m08_svc_nm on log_m08 (svc_nm);
  32. create index log_m08_oprt_nm on log_m08 (oprt_nm);
  33.  
  34. create index log_m09_athn_id_idx on log_m09 (athn_id);
  35. create index log_m09_svc_nm on log_m09 (svc_nm);
  36. create index log_m09_oprt_nm on log_m09 (oprt_nm);
  37.  
  38. create index log_m10_athn_id_idx on log_m10 (athn_id);
  39. create index log_m10_svc_nm on log_m10 (svc_nm);
  40. create index log_m10_oprt_nm on log_m10 (oprt_nm);
  41.  
  42. create index log_m11_athn_id_idx on log_m11 (athn_id);
  43. create index log_m11_svc_nm on log_m11 (svc_nm);
  44. create index log_m11_oprt_nm on log_m11 (oprt_nm);
  45.  
  46. create index log_m12_athn_id_idx on log_m12 (athn_id);
  47. create index log_m12_svc_nm on log_m12 (svc_nm);
  48. create index log_m12_oprt_nm on log_m12 (oprt_nm);

마스터 테이블에 인서트가 될 때 각 파티션 테이블로 데이터를 인서트할 트리거를 만듭니다.

룰(RULE), 트리거(TRIGGER) 두가지 방법이 있는데 전 트리거를 사용하겠습니다.

  1. create language plpgsql;
  2.  
  3. create or replace function log_insert_trigger()
  4. returns trigger as $$
  5. begin
  6.     if ( date_part('month', new.log_rqst_dt) = 1 ) then
  7.         insert into log_m01 values (new.*);
  8.     elsif ( date_part('month', new.log_rqst_dt) = 2 ) then
  9.         insert into log_m02 values (new.*);
  10.     elsif ( date_part('month', new.log_rqst_dt) = 3 ) then
  11.         insert into log_m03 values (new.*);
  12.     elsif ( date_part('month', new.log_rqst_dt) = 4 ) then
  13.         insert into log_m04 values (new.*);
  14.     elsif ( date_part('month', new.log_rqst_dt) = 5 ) then
  15.         insert into log_m05 values (new.*);
  16.     elsif ( date_part('month', new.log_rqst_dt) = 6 ) then
  17.         insert into log_m06 values (new.*);
  18.     elsif ( date_part('month', new.log_rqst_dt) = 7 ) then
  19.         insert into log_m07 values (new.*);
  20.     elsif ( date_part('month', new.log_rqst_dt) = 8 ) then
  21.         insert into log_m08 values (new.*);
  22.     elsif ( date_part('month', new.log_rqst_dt) = 9 ) then
  23.         insert into log_m09 values (new.*);
  24.     elsif ( date_part('month', new.log_rqst_dt) = 10 ) then
  25.         insert into log_m10 values (new.*);
  26.     elsif ( date_part('month', new.log_rqst_dt) = 11 ) then
  27.         insert into log_m11 values (new.*);
  28.     elsif ( date_part('month', new.log_rqst_dt) = 12 ) then
  29.         insert into log_m12 values (new.*);
  30.     else
  31.         raise exception 'month out of range. fix the log_insert_trigger() function!';
  32.     end if;
  33.  
  34.     -- return null
  35.     return null;
  36. end;
  37. $$
  38. language plpgsql;
  39.  
  40. -- log 마스터 테이블에 트리거 적용
  41. create trigger log_insert_trigger
  42. before insert on log
  43. for each row execute procedure log_insert_trigger();


Select Partition Table

이제 파티션 테이블은 다 만들었습니다.

이렇게 만들어진 파티션 테이블을 관리용(월 단위 삭제라든가..)으로 사용할땐 상관이 없지만, 조회(select)시에 그냥 하면 파티션을 제대로 이용하지 못합니다.

파티션을 만들 때 CHECK 구문에 들어갔던 조건절이 반드시 들어가야 합니다.

이게 뭔 소리냐 하면...

  1. select
  2.     count(*)
  3. from
  4.     log
  5. where
  6.     log_rqst_dt between date '2011-04-11' and date '2011-05-02'

파티션의 기준이 됐던 "log_rqst_dt" 컬럼의 범위로 검색을 했씁니다.

어떻게 검색이 되는지 보면 아래와 같습니다..


월단위로 나눠서 저장했던 12개의 테이블을 다 스캔하게 됩니다. ㅠ_ㅠ

log_m04, log_m05 테이블만 조회 해야 하지요....

아래와 같이 파티션 테이블 만들때의 CHECK 조건을 명시해줘야 합니다.

  1. select
  2.     count(*)
  3. from
  4.     log
  5. where
  6.     log_rqst_dt between date '2011-04-11' and date '2011-05-02'
  7.     and date_part('month', log_rqst_dt) in (4,5)

7번 라인처럼 하면 두개의 테이블만 스캔합니다.


이번에는 인덱스로 지정한 컬럼으로 검색 해봅시다.

  1. select
  2.     count(*)
  3. from
  4.     log
  5. where
  6.     date_part('month', log_rqst_dt) in (1,2,3)
  7.     and athn_id = 'athn_02'
  8.     and log_rqst_dt between date '2011-01-01' and date '2011-03-02'

마찬가지로 파티션을 검색에 적용하려면 조건을 넣어줘야 합니다.



물론 날짜 범위 검색이 필수가 아니게 되면 12개 테이블을 전부 스캔하게 되니 주의!!!

반응형
저작자표시
//

테이블스페이스 및 테이블 관리 쿼리

Posted at 2011. 1. 27. 13:34 | Posted in Database/Oracle
반응형
테이블 스페이스 용량 확인
select
	b.file_name as file_name,
	b.tablespace_name as tablespace_name,
	b.bytes/1024 as total_size,
	((b.bytes-sum(nvl(a.bytes,0))))/1024 as used_size,
	(sum(nvl(a.bytes,0)))/1024 free_size,
	trunc((sum(nvl(a.bytes,0))/(b.bytes))*100,3) || '%' free_percent
from
	dba_free_space a, dba_data_files b
where
	a.file_id(+) = b.file_id
	-- and a.tablespace_name like 'DEV_%'
group by
	b.tablespace_name, b.file_name, b.bytes
order by
	b.tablespace_name;

테이블별 용량 확인
select
	segment_name,
	sum(bytes) as bytes
from
	dba_segments
where
	segment_type = 'TABLE'
	-- and owner like 'DEV_%' 
group by
	segment_name
order by
	bytes desc;


한번 늘어난 테이블스페이스(실제 파일)은 사이즈가 커지면 줄어 들지 않습니다. DBA 작업을 하지 않는 이상...

delete 문으로 데이터를 삭제할 경우 데이터는 삭제되지만 테이블의 공간을 차지하고 있습니다.

입력(insert)과 삭제(delete)를 반복해도 테이블의 용량은 계속 늘어나게 됩니다.

데이터가 풀차려고 하면 테이블을 정리해줘야합니다. (Table Shrink)

ALTER TABLE 테이블명 ENABLE ROW MOVEMENT;
ALTER TABLE 테이블명 SHRINK SPACE;
ALTER TABLE 테이블명 SHRINK SPACE CASCADE;
ALTER TABLE 테이블명 DISABLE ROW MOVEMENT;

반응형
저작자표시

'Database > Oracle' 카테고리의 다른 글

Oracle Database 10g Express Edition. HTTP 포트번호 변경  (0) 2009.03.09
Oracle 계정 생성  (1) 2009.02.16
//

SELECT 쿼리 연습용 샘플 스키마

Posted at 2009. 12. 22. 18:45 | Posted in Database/MySQL
반응형
Oracle 10g 에 있는 HR, OE, SH, SCOTT 스키마의 데이터들을 MySQL 로 옮겼습니다.

MySQL 5.1.37
InnoDB
utf-8

테이블 이름이 겹쳐서 [스키마]_[테이블] 로 했습니다. ㅠ_ㅠ

주소: nerv-team.co.kr
포트: 3306
아뒤: public
비번: public

SELECT 만 할 수 있구요.. sql 덤프는 [phpMyAdmin SQL Dump] 사용했습니다.

※ 주의! 꼭 OE 보다 HR 을 먼저 IMPORT 시키세요. OE에서 HR_EMPLOYEES 를 참조합니다.


SCOTT(기본 샘플)

scott.zip





HR(Human Resources)

hr.zip





OE(Order Entry)

oe.zip





SH(Sales History)

sh.zip





테이블 정보





반응형
저작자표시

'Database > MySQL' 카테고리의 다른 글

계정 생성  (1) 2009.11.08
//

계정 생성

Posted at 2009. 11. 8. 23:11 | Posted in Database/MySQL
반응형
- 관리자 로그인
$ mysql -uroot -p비밀번호 mysql

- 계정 생성
mysql> insert into user ( host, user, password ) values ( 'localhost', '사용자명', password('패스워드') );

- 데이터베이스 생성
mysql> create database 디비명;

- 데이터베이스 권한 주기
mysql> insert into db ( host, db, user, select_priv, insert_priv, update_priv, delete_priv, create_priv, alter_priv, drop_priv) values ( 'localhost', '디비명', '사용자명', 'y', 'y', 'y', 'y', 'y', 'y', 'y' );

- 새로 적용
mysql> flush privileges;

반응형
저작자표시

'Database > MySQL' 카테고리의 다른 글

SELECT 쿼리 연습용 샘플 스키마  (1) 2009.12.22
//

Oracle Database 10g Express Edition. HTTP 포트번호 변경

Posted at 2009. 3. 9. 21:01 | Posted in Database/Oracle
반응형
기본 OrcleXE 를 설치하면 http 포트가 8080으로 잡힙니다.

하지만 톰켓을 설치하게 되면 톰켓도 8080을 사용하지요...

톰켓 포트번호를 바꾸던가 오라클 포트번호를 바꾸면 되는데...

오라클 포트번호 변경법~

접속시 DBA 계정으로 로그인해야 합니다.

>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on 월 3월 9 14:29:47 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

사용자명 입력: system
암호 입력:

다음에 접속됨:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> -- 현재 사용중인 포트번호 알기
SQL> select dbms_xdb.gethttpport from dual;

GETHTTPPORT
-----------
       8080

SQL> -- HTTP 포트 변경하기
SQL> begin
  2     dbms_xdb.sethttpport('8089');
  3  end;
  4  /

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

SQL> select dbms_xdb.gethttpport from dual;

GETHTTPPORT
-----------
       8089

SQL>


잘 변경 되었습니다.



HTTP 사용을 막으려면 포트번호를 '0' 으로 설정하면 됩니다.

반응형
저작자표시

'Database > Oracle' 카테고리의 다른 글

테이블스페이스 및 테이블 관리 쿼리  (1) 2011.01.27
Oracle 계정 생성  (1) 2009.02.16
//

Oracle 계정 생성

Posted at 2009. 2. 16. 22:45 | Posted in Database/Oracle
반응형

Oracle Database 10g Release 2 (10.2.0.1)


1. 오라클 테이블 스페이스 만들기

CREATE TABLESPACE [tablespace_name]
DATAFILE '/home/oracle/oradata/DANBEE/[file_name].dbf' SIZE 500m;

SQL> CREATE TABLESPACE ktbcn
  2  DATAFILE '/home/oracle/app/oracle/oradata/orcl/ktbcn.dbf' size 1000m;

SQL> CREATE TABLESPACE 테이블스페이스명
  2 DATAFILE 'C:\oraclexe\oradata\XE\파일명.dbf' size 100m -- 기본크기
  3 AUTOEXTEND ON -- 크기자동확장옵션
  4 NEXT 10m -- 크기확장단위
  5 MAXSIZE 500m; -- 최대크기


2. 오라클 유저 만들기
CREATE USER [user_name]
IDENTIFIED BY [password]
DEFAULT TABLESPACE [tablespace_name]
TEMPORARY TABLESPACE TEMP;

SQL> CREATE USER ktbcn
  2  IDENTIFIED BY ktbcn
  3  DEFAULT TABLESPACE ktbcn
  4  TEMPORARY TABLESPACE temp;


3. 권한주기

GRANT connect, resource TO [user_name];

SQL> GRANT connect, resource TO ktbcn; 

반응형

'Database > Oracle' 카테고리의 다른 글

테이블스페이스 및 테이블 관리 쿼리  (1) 2011.01.27
Oracle Database 10g Express Edition. HTTP 포트번호 변경  (0) 2009.03.09
//

티스토리툴바