오라클 DB 의 Spatial ( 스파샬 )

Spatial Overview

Oracle 9i Spatial에서 새롭게 추가된 특징은 Spatial 자체의 enhancement라기 보다는

Domain Index 자체의 enhancement에 기인한 바가 크다.

Oracle 9i Spatial은 데이터를 엑세스하고 다루는 데 있어서

domain index 타입인 spatial indextype을 항상 사용한다고 볼 수 있다.

크게 Oracle 9i Spatial에 새롭게 추가된 특징으로는 사용자가 aggregate 함수를 정의할 수 있고

range-partition 테이블에 각 partition에 spatial index를 생성할 수 있으며

function의 return값에도 spatial index를 생성할 수 있다.

이 장에서 지금부터 하나하나 자세히 살펴 보기로 한다.

Using Partitioned Spatial Indexes

지리 정보 데이터는 일반적으로 매우 커서 하나의 테이블에 몇 백만개의 Row가 존재하거나 하는 상황이 흔할 수 있다.

이러한 경우에 I/O의 로드 분산과 관리의 편의를 위하여 partitioning을 사용하는 경우가 많았다.

그러나 8i 이전 버전에서는  Spatial index의 경우에는 원래의 테이블이 partition되어 있는 경우에도

domain index는 partition되어 있지 않고 하나의 global domain index 만 가능하였으며 partitioning  기법을 사용할 수 없었다.

그러나 9i 부터는 spatial index에 대해서도 partitioning을 지원하는 것이 가능하여 local domain index를 만드는 것이 가능하다.

그러나 모든 경우에 partitioning이 이루어지는 것은 아니며 현재까지는 spatial column이 아닌 스칼라 칼럼에 대해서

range partitioned 된 테이블만 가능하며 hash parition 이나 list partition에 대해서는 지원되지 않는다.

Local domain index를 생성하는 것은 “Local” keyword를 통하여 이루어져서 아래와 같이 일반적인 local partitioned index 처럼

각각의  partition당 storage parameter를 다르게 설정하여 주는 것이 가능하다.

그러나 spatial parameter 에 대해서는 전체 테이블에 대해서 같은 값을 유지해야 한다.

Function Based Index

Oracle 9i에 새롭게 추가된 기능으로 function의 output에 domain index를 생성할 수 있는 기능이다.

이 기능을 사용하면 sdo_geometry  타입의 spatial 칼럼이 아닌 일반 relational 칼럼에 있는 데이터에 대해서 spatial index를 설정할 수 있게 된다.

예를 들면 특정 지점에 대한 위도와 경도가 relational column으로 존재하는 다음과 같은 테이블이 존재하고

이를 이용하여 point 타입의 sdo_geometry를  return하는 아래와 같은 함수가 있다고 하면 이 함수 자체에 spatial index를 생성할 수 있는 것이다.

함수의 return 값이 sdo_geometry이기만 하면 일반 데이터 테이블에 인덱스를 생성하는 것과 같은 방식으로 spatial index를 생성할 수 있게 된다.

위와 같은 방법으로 생성된 spatial index에는 모든 spatial operator를 문제없이 사용할 수 있다.

Control-0

User defined Aggregate

Oracle 9i는 extensibility의 enhancement로 user defined aggregate fuction 함수를 만들 수 있게 되어 object 타입에 대해서도

sum이나 AVG와 비슷한 방식으로 동작하는 aggregation 함수를 만들 수 있게 되었다.

Oracle 9i Spatial은 이를 이용하여 group by 에 대해서 동작하는 aggregation 함수를 새로 제시하였다.

다음은 이번에 새롭게 등장한 aggregation 함수에 대한 소개이다.

실제로 위의 함수들이 받아들이는 argument 타입은 mdsys.sdoaggrtype을 받아들인다.

그러나 이는 실제로 사용되는 타입이 아니며 spatial aggregation 함수 호출시 내부적으로 이 타입을 사용한다.

테스트

환경 설정

Spatial index를 생성하기 위해서는 해당 테이블과 테이블 안의 어떤 칼럼이  지리 정보를 담고 있는지에

대한 정보와 이 지리 데이터의 dimension 정보가 user_sdo_geom_metadata 테이블에 하나의 레코드로 들어가 있어야 한다.

User_sdo_geom_metadata에는 아래에서 보듯이 테이블 정보, 칼럼 정보, 그리고 Dimension 정보를 가지고 있다.

일반적으로 vector data는 값들의 쌍으로 구성되어 있고 이차원 정보를 나타내는 경우에는 두 값의 쌍으로 구성된다.

이 값들에 대한 정보가 dimenison 정보인데 아래의 예제에서는 ‘spatial_table’ 칼럼 안의 모든 vector 정보의

첫 번째 값은 ‘longitude’으로 지칭되며 이는 –180 부터 180까지의 값을 가질 수 있다는 의미이다.

그리고 두 번째 값도 ‘latitude’으로 지칭되며 같은 범위를 가진다. 여기에 3개의 dimension에 대한 정보를 입력하면

이는 3차원 vector 정보라는 것을 의미하나 아직 Oracle Spatial은 3차원을 지원하지 않는다.

샘플 프로그램

Using Partitioned Indexes

현재 spatial column을 기준으로 한 partitioning을 지원하지 않고 range partitioning만을 지원하기 때문에 partition 테이블을 만드는 방법과 같다.

이는 위의 예제에서 설명되었기에 여기에서는 생략한다. 실제로 수행을 해보기 위해서는

script 파일 cr_partbls.sql을 참조하기 바란다.  이 script를 수행하면 partition table이 생성되고 sql*loader를 이용하여 테이블로 data가 populate된다.

그리고 인덱스를 생성하면 partition의 수만큼 테이블이 생기는 것을 확인할 수 있다. 인덱스 생성 scirpt는 다음과 같다.

아래에서 local keyword가 local domain index를 생성하라고 하는 키워드이다.

Spatial index의 경우에는 spatial index에 대한 데이터를 저장하기 위해서 인덱스가 생성될 때마다 이를 위한 새로운 테이블이 생성된다.

Partitioned된 경우에는 각 partition마다 local index가 생성되어 이를 spatial index의 metadata view인 user_sdo_index_info에서 확인할 수 있다.

Spatial Table에 query하는 경우에 local domain index가 어떻게 사용되는지 확인해 보기 위해 trace를 수행하면 별다른 차이점이 없이

domain index를 사용한다는 정보만 나온다. 그리고 spatial index의 개별 partition table만 사용하는 지, 안 하는지를 확인하기 위하여

sql*trace를 수행하여도 아래와 같이 각각의 global  index에 대한 정보만 나타날 뿐 local index에 대한  정보는 나타나지 않는다.

이는 spatial domain index에 대해서는 sql*trace가 상세한 정보를 나타내지 않기 때문이다. 현재로서는 각각 개별 index table이 개별적으로 사용되는 지를 알 수 있는 정보는 없다.

Note : 테이블 생성 및 data population script : cr_partbls.sql

           인덱스 생성 script : cr_parindex.sql

Function based Index

Function Based Index를 생성하기 위해서는 사용자에게 “Query Rewrite” 권한이 주어져야 한다.

위의 SQL 문을 수행하여 환경을 설정해 준 후의 테스트 시나리오는 매우 간단하다.

먼저 테스트를 위한 테이블을 생성한다. 이 테이블은 특정 지점의 이름과 함께 고도, 경도를 column으로 가지고 있다.  이 테이블에 대해서

수행할 함수를 작성하고 user_sdo_geom_metadata에 이 테이블에 대한 record를 입력한다.

이 경우에는 function-based index이므로 function에 대한 정보가 칼럼 이름 대신 입력된다.

그리고 함수를 생성한 후 인덱스를 생성하면 된다.

인덱스가 생성되면 기존의 GIS query와 같은 방식으로 query를 수행할 수 있다. 이는 attach된 script를 참조하도록 한다.

Note :테이블 생성 및 data population 및 metadata 입력 script : func_tbls.sql

함수 생성 및 인덱스 생성 scirpt : func_index.sql

query script : query1.sql, query2.sql

User Aggregate Function

Oracle 9i Spatial이 제공하는 user aggregate function은 크게 두 가지 타입으로 나눌 수 있다.

하나는 input 으로 mdsys.sdo_geometry를 받아들이는 sdo_aggr_mbr과 다른 하나는 input으로 mdsys.sdoaggrtype을 받아 들이는 나머지 함수들이다. [1]

sdoaggrtype은 이와 같아서 함수 호출시 이러한 타입을 생성하여 input으로 전달하여야 한다.

sdoaggrtype은  함수의 argument로만 사용되어야 하며 테이블의 칼럼으로는 사용되지 못한다. 다음은 이를 사용하는 sdo_aggr_union 함수의 예이다.

mdsys.sdo_geometry를 input으로 받아들이는 sdo_aggr_mbr은 훨씬 쉽게 사용될 수 있다.

단순히 input으로 주기만 하면 된다. 다음은 이 함수의 사용예와 return 결과이다.

활용가이드 및 결론

Oracle 9i에서는 좀 더 유용한 spatial의 함수들과 기능들이 추가되었다.

8i 보다는 좀더 편리한 기능들이 제공되어 사용이 편리해지고 좀 더 효과적으로 사용될 수 있는 방법들이 제공되어 졌다고 볼 수 있다.

특히 기존의 데이터가 spatial data가 아닌 경우 이제까지는 이 모든 데이터들을 다 mdsys.sdo-geometry로 변환시키는 작업이 필요했으나

이제는 function based index를 사용함으로써 converting 작업이 필요 없어져서 이러한 application이 요구되는 경우에 편리하게 사용될 수 있다고 본다.

[1]             개별 함수에 대한 설명은 위의 overview에 있으므로 이를 참조하기 바란다.