copyright oracle corporation, 2000. all rights reserved. geoinfo june 13, 20001 oracle 8i...

GeoInfo June 13, 2000GeoInfo June 13, 2000 11Copyright Oracle Corporation, 2000. All rights reserved.

Oracle 8i Extensibility & SpatialOracle 8i Extensibility & Spatial

Presented By:

Jayant Sharma

[email protected]

Presented By:

Jayant Sharma

[email protected]


OutlineOutline

• Extensibility Framework

– Type system

– Indexing

– Optimizer

• Oracle Spatial

– Features

– Usage examples

• Extensibility Framework

– Type system

– Indexing

– Optimizer

• Oracle Spatial

– Features

– Usage examples


Extensibility FrameworkExtensibility Framework

OptimizerOptimizer

Query EngineQuery Engine

Index EngineIndex Engine

Type ManagerType Manager

Exten

sibility

UtilitiesUtilities ToolsTools Text Text

Text

Spatial

ImageSpatial

Image

Sp

atial

Imag

e

CalloutsCallouts


Object TypesObject Types

• Ability to define new data types

– A type system to support user defined object types

– Made up of predefined types or user-defined types

• Type Methods (PL/SQL, C, Java)

• Ability to define new data types

– A type system to support user defined object types

– Made up of predefined types or user-defined types

• Type Methods (PL/SQL, C, Java)


LOBsLOBs

• Helps capture unstructured data

• Stored inside or outside database, can be temporary

• Piecewise access to LOB data

• Three types of LOBs - CLOB, BLOB, BFILE

• Helps capture unstructured data

• Stored inside or outside database, can be temporary

• Piecewise access to LOB data

• Three types of LOBs - CLOB, BLOB, BFILE

LOBs can be stored in a separate tablespace or in operating system files

LOBs can be stored in a separate tablespace or in operating system files

Table

BFILE

BLOB


Oracle

DB

Oracle Address Space External Address Space

External ProceduresExternal Procedures

Oracle 8i PL/SQL Listener

extproc

/sh_libs/utils.so


OutlineOutline

• Extensible Type System

• Extensible Indexing

• Extensible Optimizer





What is an Index ?What is an Index ?

• Compressed representation of relevant information

• Used to efficiently obtain result set for queries (with or without predicates)

• Compressed representation of relevant information

• Used to efficiently obtain result set for queries (with or without predicates)


Built-in IndexesBuilt-in Indexes

• B-Tree Index

– (key,ROWID) stored in a tree

– used for predicates with relational operators

• Bitmap Index

– stores a bitmap per key value

– handles relational operators

• B-Tree Index

– (key,ROWID) stored in a tree

– used for predicates with relational operators

• Bitmap Index

– stores a bitmap per key value

– handles relational operators


Built-in Indexing LimitationsBuilt-in Indexing Limitations

• Can NOT use index to evaluate predicates containing relational operators with:

– LONG and LOB columns

– Object Types columns

– VARRAY columns

– Nested Table columns

• Can NOT use index to evaluate predicates containing relational operators with:

– LONG and LOB columns

– Object Types columns

– VARRAY columns

– Nested Table columns

SELECT * FROM T WHERE C1 = :1;SELECT * FROM T WHERE C1 = :1;



• Can NOT use index to evaluate predicates containing user-defined functions

• Can NOT use index to evaluate predicates containing user-defined functions

SELECT * FROM employees WHERE age(emp) > 30;SELECT * FROM employees WHERE age(emp) > 30;



• Can NOT use index to efficiently evaluate domain-specific operators

• Can NOT use index to efficiently evaluate domain-specific operators

SELECT * FROM Documents WHERE Contains(Doc,’Oracle’)=1;

SELECT * FROM Roads WHERE Overlaps(Rd, Region(..))=1;

SELECT * FROM Images WHERE Similar(Img, :1)=1;

SELECT * FROM Documents WHERE Contains(Doc,’Oracle’)=1;

SELECT * FROM Roads WHERE Overlaps(Rd, Region(..))=1;

SELECT * FROM Images WHERE Similar(Img, :1)=1;


Built-in vs. Extensible IndexingBuilt-in vs. Extensible Indexing

• All aspects of indexing handled by DBMS

• A single B-tree used as storage

• DBMS builds as well as interpret the index for evaluating relational operators

• All aspects of indexing handled by DBMS

• A single B-tree used as storage

• DBMS builds as well as interpret the index for evaluating relational operators

• Application and DBMS share responsibility

• Application controls semantic content of index

• DBMS handles physical storage of related data structures

• Application interprets the index for evaluating operators

• Application and DBMS share responsibility

• Application controls semantic content of index

• DBMS handles physical storage of related data structures

• Application interprets the index for evaluating operators


Integration with RDBMSIntegration with RDBMS

• Interoperability of Domains

• Better Optimization Opportunities

• Uniform easy SQL access

• Interoperability of Domains

• Better Optimization Opportunities

• Uniform easy SQL access


OperatorsOperators

• Domain specific operation

• Multiple bindings

• Evaluate using function or index

• Domain specific operation

• Multiple bindings

• Evaluate using function or index

CREATE OPERATOR Contains BINDING (CLOB, VARCHAR2) RETURN NUMBER USING Text.Contains, (Spatial.Geo, Spatial.Geo) RETURN NUMBER USING Spatial.Contains;

SELECT * FROM employees WHERE Contains(resume, ‘Oracle’) = 1;

CREATE OPERATOR Contains BINDING (CLOB, VARCHAR2) RETURN NUMBER USING Text.Contains, (Spatial.Geo, Spatial.Geo) RETURN NUMBER USING Spatial.Contains;

SELECT * FROM employees WHERE Contains(resume, ‘Oracle’) = 1;


Predicate FormsPredicate Forms

• op(…) relop <valexp>

– relop: =, <, >, <=, >=

• op(…) BETWEEN <valexp1> AND <valexp2>

• op(…) LIKE <valexp>

• op(…) relop <valexp>

– relop: =, <, >, <=, >=

• op(…) BETWEEN <valexp1> AND <valexp2>

• op(…) LIKE <valexp>


Operator EvaluationOperator Evaluation

• Operator evaluated as

– Functional implementation or

– Domain Index Scan

• Index Scan chosen iff

– column arg has domain index

– indextype supports operator

• Cost based optimizer

• Operator evaluated as

– Functional implementation or

– Domain Index Scan

• Index Scan chosen iff

– column arg has domain index

– indextype supports operator

• Cost based optimizer


Multi Domain QueriesMulti Domain Queries

• Single query with multiple operators• Single query with multiple operators

SELECT *FROM patientsWHERE Contains(history, ‘tumor’) = 1 AND Overlaps(location, :bay_area) = 1 AND Similar(xray, :tumorscan) = 1;

SELECT *FROM patientsWHERE Contains(history, ‘tumor’) = 1 AND Overlaps(location, :bay_area) = 1 AND Similar(xray, :tumorscan) = 1;


AgendaAgenda








Cost Based OptimizerCost Based Optimizer

Query

Hints

Statistics

Execution

Plan

C(P1)

C(P2)

C(P3)

C(Pn)

CBO (min)


Execution Plan CharacteristicsExecution Plan Characteristics

• Cost of query execution depends upon:

– Cost of Join methods

– Cost of Access methods

– Cost of function invocation

• Selectivity of predicates

• Statistics of columns or indexes

• Cost of query execution depends upon:

– Cost of Join methods

– Cost of Access methods

– Cost of function invocation

• Selectivity of predicates

• Statistics of columns or indexes


Why Extensible OptimizerWhy Extensible Optimizer

Extensible framework allows new access methods

Cannot ignore the cost of user-defined functions

User-defined Types More applications and cartridges are

using new access methods and complex user-defined functions

Extensible framework allows new access methods

Cannot ignore the cost of user-defined functions

User-defined Types More applications and cartridges are

using new access methods and complex user-defined functions


GoalGoal

Generate optimal execution plans for queries with predicates involving: user-defined operators user-defined functions object type columns

Generate optimal execution plans for queries with predicates involving: user-defined operators user-defined functions object type columns


Cost & SelectivityCost & Selectivity

User feedback for estimating cost required for: User-defined access methods Functional predicates

User feedback for estimating selectivity required for Predicates involving user-defined

functions

User feedback for estimating cost required for: User-defined access methods Functional predicates

User feedback for estimating selectivity required for Predicates involving user-defined

functions


System-Defined StatisticsSystem-Defined Statistics

Information gathered about column or index data to better estimate cost and selectivity logical - histogram of value

distribution, etc. physical - # of data blocks, etc.

Collected using ANALYZE command

Information gathered about column or index data to better estimate cost and selectivity logical - histogram of value

distribution, etc. physical - # of data blocks, etc.

Collected using ANALYZE command

SELECT * FROM emp WHERE salary < 50000

ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMNS salary

SELECT * FROM emp WHERE salary < 50000

ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMNS salary


User-Defined StatisticsUser-Defined Statistics

• Example - occurrences of keywords in a text column

• Example - occurrences of keywords in a text column

SELECT * FROM emp e WHERE contains(e.resume, ‘Oracle’) = 1SELECT * FROM emp e WHERE contains(e.resume, ‘Oracle’) = 1


User-Defined StatisticsUser-Defined Statistics

Columns statistics for columns of object

types domain specific statistics for

columns of scalar type Domain indexes

structure of domain index is not known to server

Columns statistics for columns of object

types domain specific statistics for

columns of scalar type Domain indexes

structure of domain index is not known to server


ModelModel

User provides functions for collecting statistics, estimating selectivity and cost

These functions registered with schema objects - e.g. domain index, object type

ANALYZE uses these functions to collect user-defined statistics

Optimizer uses these functions to estimate cost and selectivity

User provides functions for collecting statistics, estimating selectivity and cost

These functions registered with schema objects - e.g. domain index, object type

ANALYZE uses these functions to collect user-defined statistics

Optimizer uses these functions to estimate cost and selectivity


Example

CREATE TYPE text_statstype AS OBJECT( member functions ODCIStatsCollect() RETURN NUMBER )

CREATE TABLE emp (resume VARCHAR2(2000), state VARCHAR2)

ASSOCIATE STATISTICS WITH COLUMNS emp.resume USING text_statstype

ANALYZE TABLE emp ESTIMATE STATISTICS FOR COLUMNS resume SAMPLE SIZE 20 PERCENT

CREATE TYPE text_statstype AS OBJECT( member functions ODCIStatsCollect() RETURN NUMBER )

CREATE TABLE emp (resume VARCHAR2(2000), state VARCHAR2)

ASSOCIATE STATISTICS WITH COLUMNS emp.resume USING text_statstype

ANALYZE TABLE emp ESTIMATE STATISTICS FOR COLUMNS resume SAMPLE SIZE 20 PERCENT


Applications Applications

• 8i Options

– ConText

– Spatial

– interMedia & Visual Info Retrieval

• 8i Options

– ConText

– Spatial

– interMedia & Visual Info Retrieval


PointPoint Line stringLine string PolygonPolygonPolygon Polygon with holewith hole

Self-crossingSelf-crossingline strings line strings

Self-crossingSelf-crossingpolygonspolygons

ValidValid Not validNot valid

Geometric TypesGeometric Types


Spatial Data ModelSpatial Data Model

Elements

Geometries

Spatial layer

Point Linestring Polygon CompoundPolygon

CompoundLinestring

release 8i only: • circular arcs• circles• optimized rectangle • compound linestring• compound polygon


ElementsElements

Element 1

Element 2

Element 3

Element 4Element 5

Element 6

Element 7

• Basic building block of a geometry

• Element types– Point

– Line

– Polygon

– Compound Linestring

– Compound Polygon

• Constructed using coordinates

Hawaii, USA


Geometry 1California

Geometry 2Texas

Geometry 3Florida

Geometry 4Hawaii

GeometryGeometry

• Represents a spatial feature

• Consists of an ordered set of primitive elements

• Uniquely identified with a geometry identifier

• Represents a spatial feature

• Consists of an ordered set of primitive elements

• Uniquely identified with a geometry identifier


LayersLayers

• Consist of geometries that share a common set of attributes

• Consist of geometries that share a common set of attributes

States layer


Query ModelQuery Model

Spatial LayerData

Table wherecoordinates are stored

PrimaryFilter

Spatial Index

Index retrieves area of interest (window)

ReducedData Set

SecondaryFilter

SpatialFunctions

Proceduresthat determineexact relationship

ExactResult

Set


Primary and Secondary Filter Concept

Primary and Secondary Filter Concept


Spatial QueriesSpatial Queries

• Issued through SQL

• Contains a locational constraint, for example:

– Find all policy holders in the projected path of a hurricane

– Find all ATMs in my area

• Issued through SQL

• Contains a locational constraint, for example:

– Find all policy holders in the projected path of a hurricane

– Find all ATMs in my area


Spatial JoinsSpatial Joins

• A join of two layers based on the spatial component of the layers

• Implemented using spatial index and spatial operators

• Example:

– Find all the wetlands within the national parks

• A join of two layers based on the spatial component of the layers

• Implemented using spatial index and spatial operators

• Example:

– Find all the wetlands within the national parks


Spatial IndexingSpatial Indexing

• There are two methods for indexing spatial data:

– Fixed tiling

– Hybrid tiling

• The process of indexing spatial data is called tessellation.

• The results of the tessellation process are stored in the spatial index.

• The tesselation process is performed by creating the spatial index

• There are two methods for indexing spatial data:

– Fixed tiling

– Hybrid tiling

• The process of indexing spatial data is called tessellation.

• The results of the tessellation process are stored in the spatial index.

• The tesselation process is performed by creating the spatial index


SDO_GROUPCODE

FIXED_TILE1

FIXED_TILE2

FIXED_TILE2

SDO_CODE

VAR_TILE1

VAR_TILE2

VAR_TILE3

• DBA Determines:

– Number of Tiles = 3

– Level = 1

• DBA Determines:


– Level = 1

How Geometry is IndexedHow Geometry is Indexed

• DBA Determines:


– Level = 1

• DBA Determines:


– Level = 1


Primary Filter Example - FIXEDPrimary Filter Example - FIXED

• Compares fixed sized tiles that approximate the area of interest, with fixed sized tiles that approximate each geometry

• Result is not exact because comparing approximations

• Compares fixed sized tiles that approximate the area of interest, with fixed sized tiles that approximate each geometry

• Result is not exact because comparing approximations

sdo_level = 2sdo_level = 2


Primary Filter Example - HYBRIDPrimary Filter Example - HYBRID

• Hybrid filter:

– First does a fixed tile comparison

– Then does a variable tile comparison

– Result is not exact because still comparing geometry approximations

• Hybrid filter:

– First does a fixed tile comparison

– Then does a variable tile comparison

– Result is not exact because still comparing geometry approximations

sdo_numtiles = 8sdo_numtiles = 8


Spatial Index MetadataSpatial Index Metadata

• The spatial indexing routines require you to populate a table that contains metadata about SDO_GEOMETRY columns

• The metadata is created under the Oracle user that owns the geometry

• The metadata view is called USER_SDO_GEOM_METADATA

• For every SDO_GEOMETRY column, insert a row in USER_SDO_GEOM_METADATA

• The spatial indexing routines require you to populate a table that contains metadata about SDO_GEOMETRY columns

• The metadata is created under the Oracle user that owns the geometry

• The metadata view is called USER_SDO_GEOM_METADATA

• For every SDO_GEOMETRY column, insert a row in USER_SDO_GEOM_METADATA


SDO_GEOM_METADATA ViewsSDO_GEOM_METADATA Views

• SDO_GEOM_METADATA table has 3 system views:

– user_sdo_geom_metadata

– all_sdo_geom_metadata

– dba_sdo_geom_metadata

• SDO_GEOM_METADATA table has 3 system views:

– user_sdo_geom_metadata

– all_sdo_geom_metadata

– dba_sdo_geom_metadata

SQL> INSERT INTO user_sdo_geom_metadata

(table_name, column_name, diminfo, srid)

values (

‘ROADS’, ‘GEOMETRY’,

mdsys.sdo_dim_array (

mdsys.sdo_dim_element (‘X’,-180,180,.005),

mdsys.sdo_dim_element (‘Y’,-90,90,.005)),

NULL);

SQL> INSERT INTO user_sdo_geom_metadata

(table_name, column_name, diminfo, srid)

values (

‘ROADS’, ‘GEOMETRY’,

mdsys.sdo_dim_array (

mdsys.sdo_dim_element (‘X’,-180,180,.005),

mdsys.sdo_dim_element (‘Y’,-90,90,.005)),

NULL);


CREATE INDEXCREATE INDEX

CREATE INDEX <index-name>

ON <table-name> (<column-name>)

INDEXTYPE IS MDSYS.SPATIAL_INDEX

PARAMETERS (

‘SDO_LEVEL = <level>,

[SDO_NUMTILES = <numtiles>],

[SDO_COMMIT_INTERVAL = <n>],

[<parameter> = <param_value>]’

);

CREATE INDEX <index-name>

ON <table-name> (<column-name>)

INDEXTYPE IS MDSYS.SPATIAL_INDEX

PARAMETERS (

‘SDO_LEVEL = <level>,

[SDO_NUMTILES = <numtiles>],

[SDO_COMMIT_INTERVAL = <n>],

[<parameter> = <param_value>]’

);


ALTER INDEXALTER INDEX

ALTER INDEX <index_name> RENAME TO <new_index_name>ALTER INDEX <index_name> RENAME TO <new_index_name>

ALTER INDEX <index_name> REBUILD

PARAMETERS (<parameter string>);

ALTER INDEX <index_name> REBUILD


ALTER INDEX <index_name>


ALTER INDEX <index_name>



DROP INDEXDROP INDEX

• Drops all index tables associated with a column of type SDO_GEOMETRY

• If a CREATE INDEX on a column of type SDO_GEOMETRY does not successfully complete, you must call DROP INDEX using the FORCE option.

• Drops all index tables associated with a column of type SDO_GEOMETRY

• If a CREATE INDEX on a column of type SDO_GEOMETRY does not successfully complete, you must call DROP INDEX using the FORCE option.

DROP INDEX <index_name> FORCE;DROP INDEX <index_name> FORCE;


Spatial Operators vs FunctionsSpatial Operators vs Functions

• Spatial operators:Spatial operators:

– Take advantage of spatial indexesTake advantage of spatial indexes

– Require that spatial index exists on Require that spatial index exists on the first geometry specified in the the first geometry specified in the operator.operator.

• Spatial Functions:Spatial Functions:

– Do not take advantage of spatial Do not take advantage of spatial indexesindexes

– Could be used on small tables that Could be used on small tables that are not spatially indexedare not spatially indexed

• Spatial operators:Spatial operators:

– Take advantage of spatial indexesTake advantage of spatial indexes

– Require that spatial index exists on Require that spatial index exists on the first geometry specified in the the first geometry specified in the operator.operator.

• Spatial Functions:Spatial Functions:

– Do not take advantage of spatial Do not take advantage of spatial indexesindexes

– Could be used on small tables that Could be used on small tables that are not spatially indexedare not spatially indexed


Spatial Operators vs FunctionsSpatial Operators vs Functions

• SDO_RELATE

– Performs a primary and secondary filter

• SDO_FILTER

– Performs a primary filter only

• SDO_WITHIN_DISTANCE

– Generates a buffer around a geometry and performs a primary and optionally a secondary filter

• SDO_RELATE

– Performs a primary and secondary filter

• SDO_FILTER

– Performs a primary filter only

• SDO_WITHIN_DISTANCE

– Generates a buffer around a geometry and performs a primary and optionally a secondary filter

• SDO_GEOM.RELATE

– To determine the relationship between two geometries

– To perform a spatial query without using a spatial index (I.e. on a small table)

• SDO_GEOM.WITHIN_DISTANCE

– Generates a buffer around a geometry and performs a secondary filter

• SDO_GEOM.RELATE

– To determine the relationship between two geometries

– To perform a spatial query without using a spatial index (I.e. on a small table)

• SDO_GEOM.WITHIN_DISTANCE

– Generates a buffer around a geometry and performs a secondary filter

Operators Functions


select c.city, c.pop90 from cities c where mdsys.sdo_filter ( c.location, mdsys.sdo_geometry (3, null, null, mdsys.sdo_elem_info_array (1,3,3), mdsys.sdo_ordinate_array (-109,37,-102,40)), ‘querytype=WINDOW’) = 'TRUE';

select c.city, c.pop90 from cities c where mdsys.sdo_filter ( c.location, mdsys.sdo_geometry (3, null, null, mdsys.sdo_elem_info_array (1,3,3), mdsys.sdo_ordinate_array (-109,37,-102,40)), ‘querytype=WINDOW’) = 'TRUE';

SDO_FILTER ExampleSDO_FILTER Example

• Find all cities in a selected rectangular area

• Result is approximate


• Result is approximate


A

B

A

B

A

B

A

B

A

B

A red B green

A

B

InsideContains

CoversCovered by

Touch

OverlapBoundaries Intersect

OverlapBoundaries Disjoint Equal

Disjoint

Spatial (topological) relationshipsSpatial (topological) relationships


select c.county, c.state_abrv from counties c, states s where s.state = 'New Hampshire' and mdsys.sdo_relate (c.geom, s.geom, 'mask=INSIDE+COVEREDBY querytype=WINDOW') = 'TRUE';

select c.county, c.state_abrv from counties c, states s where s.state = 'New Hampshire' and mdsys.sdo_relate (c.geom, s.geom, 'mask=INSIDE+COVEREDBY querytype=WINDOW') = 'TRUE';

SDO_RELATE - A window querySDO_RELATE - A window query

• Find all counties in the state of New Hampshire

• Find all counties in the state of New Hampshire


select c.city, c.pop90 from cities c where mdsys.sdo_relate ( c.location, mdsys.sdo_geometry (3, null, null, mdsys.sdo_elem_info_array (1,3,3), mdsys.sdo_ordinate_array (-109,37,-102,40)), 'mask=ANYINTERACT querytype=WINDOW')=‘TRUE’;

select c.city, c.pop90 from cities c where mdsys.sdo_relate ( c.location, mdsys.sdo_geometry (3, null, null, mdsys.sdo_elem_info_array (1,3,3), mdsys.sdo_ordinate_array (-109,37,-102,40)), 'mask=ANYINTERACT querytype=WINDOW')=‘TRUE’;

SDO_RELATE - A window querySDO_RELATE - A window query




• Find all cities within a distance from an interstate

• Find intersates within a distance from a city

• Find all cities within a distance from an interstate

• Find intersates within a distance from a city

select c.city from cities c, interstates i where interstate = ‘I175’ and mdsys.sdo_within_distance ( c.location, i.geom, ‘distance=0.5') = 'TRUE';

select c.city from cities c, interstates i where interstate = ‘I175’ and mdsys.sdo_within_distance ( c.location, i.geom, ‘distance=0.5') = 'TRUE';

SDO_WITHIN_DISTANCE ExamplesSDO_WITHIN_DISTANCE Examples

select i.interstate from interstates i, cities c where city = 'Tampa' and mdsys.sdo_within_distance ( i.geom, c.location,'distance=0.5') = 'TRUE';

select i.interstate from interstates i, cities c where city = 'Tampa' and mdsys.sdo_within_distance ( i.geom, c.location,'distance=0.5') = 'TRUE';


SDO_GEOM.RELATE Function

select c.county, mdsys.sdo_geom.relate (s.geom, <diminfo for states>, 'determine', c.geom, <diminfo for counties>) from states s, counties c where s.state = 'New Jersey' and s.state = c.state;

COUNTY RELATIONSHIP------------------------------- --------------Atlantic COVERSCape May COVERSCumberland COVERSEssex CONTAINS

select c.county, mdsys.sdo_geom.relate (s.geom, <diminfo for states>, 'determine', c.geom, <diminfo for counties>) from states s, counties c where s.state = 'New Jersey' and s.state = c.state;

COUNTY RELATIONSHIP------------------------------- --------------Atlantic COVERSCape May COVERSCumberland COVERSEssex CONTAINS

Note: this is simplified syntaxNote: this is simplified syntax

• Determine relationship of counties and states

• Determine relationship of counties and states


Jayant Sharma, Ph.D.

Consulting Member Technical Staff

Spatial Products

Server Technologies

Phone : (603) 897 - [email protected]

Oracle Corporation

®

One Oracle DriveNashua, NH 03062