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

Oracle 8i Extensibility & SpatialOracle 8i Extensibility & Spatial

Presented By:

Jayant Sharma

Jayant.Sharma@oracle.com

Presented By:

Jayant Sharma

Jayant.Sharma@oracle.com

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

sibility

UtilitiesUtilities ToolsTools Text Text

Spatial

ImageSpatial

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

Oracle

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

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

• 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;

AgendaAgenda

Cost Based OptimizerCost Based Optimizer

Statistics

Execution

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

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

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

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

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

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>

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';

SDO_FILTER ExampleSDO_FILTER Example

• Find all cities in a selected rectangular area

• Result is approximate

A red B green

InsideContains

CoversCovered by

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';

SDO_RELATE - A window querySDO_RELATE - A window query

• 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’;

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';

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';

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

Jayant Sharma, Ph.D.

Consulting Member Technical Staff

Spatial Products

Server Technologies

Phone : (603) 897 - 3187Jayant.Sharma@oracle.com

Oracle Corporation

One Oracle DriveNashua, NH 03062

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

Documents

risk-driven database security - oracle.com · adm is...

stp feedback us@oracle · pdf filesubmit comments about this...

for immediate release oracle announces fiscal …...oracle...

oracle database appliance sales jarosław skibiński...

all your xml sandeepan banerjee oracle corporation...

oracle. oracle solutions for retailers’ rfid mandates...

performance tuning in oracle 10g feel the power ! kyle...

oracle epm cloud · 2019-12-02 · oracle epm solution...

mysql database service - oracle.com … · oracle golden...

oracle database 12c : new features for...

- oracle.com · oracle application framework (oaf):...

oracle identity management erika.leetmae@oracle.com senior...

1 charon-vax in rdb engineering norman lastovica oracle rdb...

ana rezende product support manager – mit and gateways...

thomas kyte thomas.kyte@oracle.com oracle corporation

transformation oracle certification course practical...

1 exploring javafx 3d jim weaver java technology ambassador...

oracle identity manager 11gr2-ps2 hands-on workshop ·...

oracle business intelligence & data warehousing overview...

markus.michalewicz@oracle.com bu database technologies...