티스토리 뷰

1. 먼저.. 오라클에서 SDO_GEOMETRY 타입 필드를 사용할 이전대상 테이블(TABLENAME)을 선택하고,
VIEWNAME'으로 뷰를 생성합니다. 

-- 뷰로 재생성해주는 스크립트
-- SDO_GEOMETRY_TYPE의 필드는 SDO_UTIL.TO_WKTGEOMETRY() 를 이용, Well-Known-Text 로 변환 필요.
select 'create or replace view '||lower(owner||'.VIEWNAME as select '||
       wmsys.wm_concat(decode(data_type, 'SDO_GEOMETRY',
          'SDO_UTIL.TO_WKTGEOMETRY('||column_name||’) as ‘||column_name||', column_name))||
       ' from '||lower(owner||'.'||table_name)
from dba_tab_columns where table_name = 'TABLENAME'
group by owner, table_name;

— 아래와 같은 뷰 생성 스크립트가 제너레이트 됩니다.
create or replace view VIEWNAME
as
select ID, ...
SDO_UTIL.TO_WKBGEOMETRY(SHAPE) as shape,
from TABLENAME;

2.  뷰 생성을 끝나쳤다면, PPAS 에서 oracle_fdw extension / server / foreign table / view를 생성합니다.

— postGIS가 설치된 DB여야 합니다. postGIS를 설치해놓은 DB가 있습니다. (gisdb)
\c gisdb

— PPAS로 이동하여 EXTENSION 설치
CREATE EXTENSION oracle_fdw;

— SERVER 정의 생성
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//IP/SID');

— 권한 제공(현재 서버에서는 생략가능)
GRANT USAGE ON FOREIGN SERVER oradb TO enterprisedb;

— 유저매핑 생성
CREATE USER MAPPING FOR enterprisedb SERVER oradb options (user 'user', password 'password');

— 오라클에서 생성한 뷰를 바라보는 Foreign Table 생성
create foreign table FOREIGNTABLENAME
(ID NUMBER, SHAPE bytea, OBJECTID NUMBER, REGUSERID NVARCHAR2) server oradb options (table 'VIEWNAME');

— 아핫.. 서비스중인 실DB에서 하지 말고 SLAVE 로 재생성하겠습니다.
— 이런 경우 서버부터 재설정하시면 되며, 이후에는 foreign 테이블만 추가하면 됩니다.
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//IP/SID');

GRANT USAGE ON FOREIGN SERVER oradb TO enterprisedb;

CREATE USER MAPPING FOR enterprisedb SERVER oradb options (user 'user', password 'password');

create foreign table FOREIGNTABLENAME
(ID NUMBER, SHAPE bytea, OBJECTID NUMBER, REGUSERID NVARCHAR2) server oradb options (table 'VIEWNAME');

— 다른 필드들은 문제없이 옮겨질테니 sdo_geometry 필드와 UK만으로 뷰를 생성합니다. (테스트용이니까요)
create or replace view FOREIGNVIEWNAME AS 
select id, st_geomfromwkb(shape) as shape from FOREIGNTABLENAME;

3. 이전(Migration) 테스트 방식은 뷰(오라클) -> fdw 테이블(PPAS, 위 2번에서 생성) -> 뷰(PPAS, 위 2번에서 생성) -> 물리테이블(PPAS) 순으로 진행되며,
TABLENAME는 총 18만여건의 SDO_GEOMETRY 타입 컬럼을 가진 테이블입니다.

- 테스트 bash 스크립트

#!/bin/bash
date
source ~enterprisedb/.bash_profile
~/PostgresPlus/9.3AS/bin/psql -U enterprisedb -d gisdb -a -f ./scr.sql
date

오라클 - 뷰를 이용하여 쿼리

nohup.out :

Wed Feb  4 10:26:06 KST 2015
drop table test_for_view;
DROP TABLE
Time: 5.296 ms
create table test_for_view as select id, st_astext(shape), sysdate from FOREIGNVIEWNAME;
SELECT 180912
Time: 912403.602 ms
Wed Feb  4 10:41:19 KST 2015


오라클 - 뷰에 parallel 4 힌트를 주고 쿼리

nohup.out :

Tue Feb  3 20:05:59 KST 2015
drop table test_for_view;
LOCATION:  DropErrorMsgNonExistent, tablecmds.c:994
Time: 0.479 ms
create table test_for_view as select id, st_astext(shape), sysdate from FOREIGNVIEWNAME;
SELECT 180912
Time: 882637.519 ms
Tue Feb  3 20:20:42 KST 2015

약간의 성능향상 -0-; 이 있었습니다.. (응?)


physical table (TABLENAME2) 생성후 테스트



테이블 생성에 걸린시간 2125.144초;;;;;;

- PPAS로 이동하여, 포린테이블 생성하고 뷰 새로 만듭니다.
create foreign table FOREIGNTABLENAME2
(ID NUMBER, SHAPE bytea) server oradb options (table 'TABLENAME2');

create or replace VIEW FOREIGNVIEWNAME2 AS select id, st_geomfromwkb(shape) as shape from FOREIGNTABLENAME2;

- nohup.out
Wed Feb  4 15:06:39 KST 2015
drop table test_physical;
DROP TABLE
Time: 3.240 ms
create table test_physical as select id, shape from FOREIGNVIEWNAME2;
SELECT 180912
Time: 850286.049 ms
Wed Feb  4 15:20:49 KST 2015

아… 쪼오끔 더 빨라집니다.. -0-;;;;;
바이너리는 기대가 커서 실망도 크군요… 그렇다면, 텍스트는 어떨까요???
오라클 VIEWNAME 뷰에서 

SDO_UTIL.TO_WKBGEOMETRY(SHAPE) shape, 

이 부분을,

SDO_UTIL.TO_WKTGEOMETRY(SHAPE) shape,

로 변경해주고 TABLENAME3 로 만들고, 테이블로 CTAS합니다. (휴.. TBS가 남는 곳이 별로 없어요;)


시작이 좋습니다. 흠.. 그런데;
같은 18만건인데 -_-;; 용량 차이가 좀 많이 나는군요

— 에잇 -_-; 원본보다 사이즈가 커지면 안됩니다. dbms_lob package 를 사용해서 좀 더 줄여보겠습니다.

음하하.. 크기도 70%정도로 줄어듦.


- PPAS로 이동하여, 
create foreign table FOREIGNTABLENAME3
(ID NUMBER, SHAPE bytea) server oradb options (table 'TABLENAME3');

create or replace VIEW FOREIGNVIEWNAME3 AS select id, st_geomfromtext(shape) as shape from FOREIGNTABLENAME3;

- 테스트 쉘 실행~
- nohup.out
Wed Feb  4 15:00:01 KST 2015

create table test_physical_txt as select id, shape from gis_txt;
SELECT 180912
Time: 36108.939 ms
Wed Feb  4 15:00:37 KST 2015

36초 -_-; 테스트 끝.

4. 결론
개발팀에서 요청한 테이블은 3600만건 정도 된다고 보고 산술적으로 200배 되겠습니다 ㅎㅎ (다른 필드까지 포함하면 +a 입니다)

1. SDO_GEOMETRY를 텍스트로 변환
(단, POINT일 경우만 이며, 폴리곤은 바이너리보다 클 수 있습니다. 4000byte까지는 큰 폭의 차이는 없을 것으로 판단되지만, 다소 가변적입니다.
4000byte를 넘어가는 경우, 컬럼TYPE이 CLOB으로 인지되며, 이 경우 바이너리보다 성능이 떨어집니다.)
및 substr하여 VARCHAR2로 작업할 경우 2시간, 

2. SDO_GEOMETRY를 SDO_UTIL.TO_WKBGEOMETRY() 함수를 이용하여 바이너리로 변환하여 작업할 경우,
최대 약 2일 2시간 30분정도 소요됩니다.



댓글
댓글쓰기 폼