Oracle 8.1 Extensibility Framework
Download
Report
Transcript Oracle 8.1 Extensibility Framework
Oracle 8i Extensibility & Spatial
Presented By:
Jayant Sharma
[email protected]
GeoInfo June 13, 2000
1
Copyright Oracle Corporation, 2000. All rights reserved.
Outline
•
Extensibility Framework
– Type system
– Indexing
– Optimizer
•
Oracle Spatial
– Features
– Usage examples
GeoInfo June 13, 2000
2
Copyright Oracle Corporation, 2000. All rights reserved.
Extensibility Framework
Text
Utilities
Spatial
Image
Tools
Query Engine
Index Engine
Type Manager
Callouts
Spatial
Image
Extensibility
Optimizer
Text
GeoInfo June 13, 2000
3
Copyright Oracle Corporation, 2000. All rights reserved.
Object Types
•
Ability to define new data types
– A type system to support user
defined object types
– Made up of predefined types or userdefined types
•
Type Methods (PL/SQL, C, Java)
GeoInfo June 13, 2000
4
Copyright Oracle Corporation, 2000. All rights reserved.
LOBs
Table
BLOB
BFILE
LOBs can be stored in a
separate tablespace or in
operating system files
•
Helps capture
unstructured data
•
Stored inside or
outside database, can
be temporary
•
Piecewise access to
LOB data
•
Three types of LOBs CLOB, BLOB, BFILE
GeoInfo June 13, 2000
5
Copyright Oracle Corporation, 2000. All rights reserved.
External Procedures
Oracle 8i
PL/SQL
Listener
extproc
Oracle
DB
/sh_libs/utils.so
Oracle Address Space
External Address Space
GeoInfo June 13, 2000
6
Copyright Oracle Corporation, 2000. All rights reserved.
Outline
•
Extensible Type System
•
Extensible Indexing
•
Extensible Optimizer
GeoInfo June 13, 2000
7
Copyright Oracle Corporation, 2000. All rights reserved.
What is an Index ?
•
Compressed representation of relevant
information
•
Used to efficiently obtain result set for
queries (with or without predicates)
GeoInfo June 13, 2000
8
Copyright Oracle Corporation, 2000. All rights reserved.
Built-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
GeoInfo June 13, 2000
9
Copyright Oracle Corporation, 2000. All rights reserved.
Built-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
SELECT * FROM T
WHERE C1 = :1;
GeoInfo June 13, 2000
10
Copyright Oracle Corporation, 2000. All rights reserved.
Built-in Indexing Limitations
•
Can NOT use index to evaluate
predicates containing user-defined
functions
SELECT * FROM employees
WHERE age(emp) > 30;
GeoInfo June 13, 2000
11
Copyright Oracle Corporation, 2000. All rights reserved.
Built-in Indexing Limitations
•
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;
GeoInfo June 13, 2000
12
Copyright Oracle Corporation, 2000. All rights reserved.
Built-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
• 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
GeoInfo June 13, 2000
13
Copyright Oracle Corporation, 2000. All rights reserved.
Integration with RDBMS
•
•
•
Interoperability of Domains
Better Optimization Opportunities
Uniform easy SQL access
GeoInfo June 13, 2000
14
Copyright Oracle Corporation, 2000. All rights reserved.
Operators
•
•
•
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;
GeoInfo June 13, 2000
15
Copyright Oracle Corporation, 2000. All rights reserved.
Predicate Forms
•
op(…) relop <valexp>
– relop: =, <, >, <=, >=
•
op(…) BETWEEN <valexp1> AND
<valexp2>
•
op(…) LIKE <valexp>
GeoInfo June 13, 2000
16
Copyright Oracle Corporation, 2000. All rights reserved.
Operator 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
GeoInfo June 13, 2000
17
Copyright Oracle Corporation, 2000. All rights reserved.
Multi Domain Queries
•
Single query with multiple operators
SELECT *
FROM patients
WHERE Contains(history, ‘tumor’) = 1
AND Overlaps(location, :bay_area) = 1
AND Similar(xray, :tumorscan) = 1;
GeoInfo June 13, 2000
18
Copyright Oracle Corporation, 2000. All rights reserved.
Agenda
•
Extensible Type System
•
Extensible Indexing
•
Extensible Optimizer
GeoInfo June 13, 2000
19
Copyright Oracle Corporation, 2000. All rights reserved.
Cost Based Optimizer
C(P1)
Query
CBO
Hints
C(P2)
C(P3)
(min)
Execution
Plan
C(Pn)
Statistics
GeoInfo June 13, 2000
20
Copyright Oracle Corporation, 2000. All rights reserved.
Execution 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
GeoInfo June 13, 2000
21
Copyright Oracle Corporation, 2000. All rights reserved.
Why 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
GeoInfo June 13, 2000
22
Copyright Oracle Corporation, 2000. All rights reserved.
Goal
Generate optimal execution plans for
queries with predicates involving:
user-defined operators
user-defined functions
object type columns
GeoInfo June 13, 2000
23
Copyright Oracle Corporation, 2000. All rights reserved.
Cost & 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
GeoInfo June 13, 2000
24
Copyright Oracle Corporation, 2000. All rights reserved.
System-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
SELECT * FROM emp WHERE salary < 50000
ANALYZE TABLE emp COMPUTE STATISTICS FOR
COLUMNS salary
GeoInfo June 13, 2000
25
Copyright Oracle Corporation, 2000. All rights reserved.
User-Defined Statistics
•
Example - occurrences of keywords in a
text column
SELECT * FROM emp e WHERE
contains(e.resume, ‘Oracle’) = 1
GeoInfo June 13, 2000
26
Copyright Oracle Corporation, 2000. All rights reserved.
User-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
GeoInfo June 13, 2000
27
Copyright Oracle Corporation, 2000. All rights reserved.
Model
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
GeoInfo June 13, 2000
28
Copyright Oracle Corporation, 2000. All rights reserved.
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
GeoInfo June 13, 2000
29
Copyright Oracle Corporation, 2000. All rights reserved.
Applications
•
8i Options
– ConText
– Spatial
– interMedia & Visual Info Retrieval
GeoInfo June 13, 2000
30
Copyright Oracle Corporation, 2000. All rights reserved.
Geometric Types
Point
Line string
Self-crossing
line strings
Valid
Polygon
Polygon
with hole
Self-crossing
polygons
Not valid
GeoInfo June 13, 2000
32
Copyright Oracle Corporation, 2000. All rights reserved.
Spatial Data Model
Spatial layer
release 8i only:
Geometries
Elements
Point
Linestring
• circular arcs
• circles
• optimized rectangle
• compound linestring
• compound polygon
Polygon Compound Compound
Linestring Polygon
GeoInfo June 13, 2000
33
Copyright Oracle Corporation, 2000. All rights reserved.
Elements
•
•
Basic building block of a geometry
Element types
–
Point
–
Line
–
Polygon
–
Compound Linestring
–
•
Element 6
Compound Polygon Element
Constructed
using coordinates
7
Element 5
Element 4
Element 2
Element 3
Element 1
Hawaii, USA
GeoInfo June 13, 2000
34
Copyright Oracle Corporation, 2000. All rights reserved.
Geometry
Geometry 1
California
•
Geometry 2
Texas
Represents a
spatial feature
• Consists of an
ordered set of
primitive elements
•
Uniquely identified Geometry 3
with a geometry
Florida
identifier
Geometry 4
Hawaii
GeoInfo June 13, 2000
35
Copyright Oracle Corporation, 2000. All rights reserved.
Layers
•
Consist of
geometries that
share a common set
of attributes
States layer
GeoInfo June 13, 2000
36
Copyright Oracle Corporation, 2000. All rights reserved.
Query Model
Spatial Layer
Data
Primary
Filter
Spatial Index
Table where
coordinates
are
stored
Index retrieves
area of interest
(window)
Reduced
Data Set
Secondary
Filter
Spatial
Functions
Exact
Result
Set
Procedures
that determine
exact relationship
GeoInfo June 13, 2000
37
Copyright Oracle Corporation, 2000. All rights reserved.
Primary and Secondary Filter
Concept
GeoInfo June 13, 2000
38
Copyright Oracle Corporation, 2000. All rights reserved.
Spatial 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
GeoInfo June 13, 2000
39
Copyright Oracle Corporation, 2000. All rights reserved.
Spatial 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
GeoInfo June 13, 2000
40
Copyright Oracle Corporation, 2000. All rights reserved.
Spatial 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
GeoInfo June 13, 2000
41
Copyright Oracle Corporation, 2000. All rights reserved.
How Geometry is Indexed
•
DBA
Determines:
– Number of
Tiles = 3
– Level = 1
SDO_CODE
SDO_GROUPCODE
VAR_TILE1
FIXED_TILE1
VAR_TILE2
FIXED_TILE2
VAR_TILE3
FIXED_TILE2
GeoInfo June 13, 2000
42
Copyright Oracle Corporation, 2000. All rights reserved.
Primary Filter Example - FIXED
sdo_level = 2
•
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
GeoInfo June 13, 2000
43
Copyright Oracle Corporation, 2000. All rights reserved.
Primary Filter Example - HYBRID
sdo_numtiles = 8
•
Hybrid filter:
– First does a fixed tile comparison
– Then does a variable tile comparison
– Result is not exact because still comparing
geometry approximations
GeoInfo June 13, 2000
44
Copyright Oracle Corporation, 2000. All rights reserved.
Spatial 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
GeoInfo June 13, 2000
45
Copyright Oracle Corporation, 2000. All rights reserved.
SDO_GEOM_METADATA Views
• 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);
GeoInfo June 13, 2000
46
Copyright Oracle Corporation, 2000. All rights reserved.
CREATE 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>]’
);
GeoInfo June 13, 2000
47
Copyright Oracle Corporation, 2000. All rights reserved.
ALTER INDEX
ALTER INDEX <index_name>
PARAMETERS (<parameter string>);
ALTER INDEX <index_name> REBUILD
PARAMETERS (<parameter string>);
ALTER INDEX <index_name> RENAME TO
<new_index_name>
GeoInfo June 13, 2000
48
Copyright Oracle Corporation, 2000. All rights reserved.
DROP INDEX
DROP INDEX <index_name> FORCE;
•
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.
GeoInfo June 13, 2000
49
Copyright Oracle Corporation, 2000. All rights reserved.
Spatial Operators vs Functions
• Spatial operators:
– Take advantage of spatial indexes
– Require that spatial index exists on
the first geometry specified in the
operator.
•
Spatial Functions:
– Do not take advantage of spatial
indexes
– Could be used on small tables that
are not spatially indexed
GeoInfo June 13, 2000
50
Copyright Oracle Corporation, 2000. All rights reserved.
Spatial Operators vs Functions
Operators
•
•
Performs a primary and
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_FILTER
–
•
•
SDO_RELATE
–
Functions
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.WITHIN_DISTANCE
–
Generates a buffer around a
geometry and performs a
secondary filter
GeoInfo June 13, 2000
51
Copyright Oracle Corporation, 2000. All rights reserved.
SDO_FILTER Example
•
Find all cities in a selected rectangular
area
•
Result is approximate
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';
GeoInfo June 13, 2000
52
Copyright Oracle Corporation, 2000. All rights reserved.
Spatial (topological) relationships
B
A
A
A
B
B
Inside
Contains
Covers
Covered by
Touch
A
A
A red
B
B
Overlap
Boundaries Intersect
B green
A
Overlap
Boundaries Disjoint
Equal
B
Disjoint
GeoInfo June 13, 2000
53
Copyright Oracle Corporation, 2000. All rights reserved.
SDO_RELATE - A window query
•
Find all counties in the state of New
Hampshire
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';
GeoInfo June 13, 2000
54
Copyright Oracle Corporation, 2000. All rights reserved.
SDO_RELATE - A window query
•
Find all cities in a selected rectangular
area
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’;
GeoInfo June 13, 2000
55
Copyright Oracle Corporation, 2000. All rights reserved.
SDO_WITHIN_DISTANCE Examples
•
Find all cities within a distance from an
interstate
select
from
where
and
•
c.city
cities c, interstates i
interstate = ‘I175’
mdsys.sdo_within_distance (
c.location, i.geom,
‘distance=0.5') = 'TRUE';
Find intersates within a distance from a
city
select
from
where
and
i.interstate
interstates i, cities c
city = 'Tampa'
mdsys.sdo_within_distance (
i.geom, c.location,'distance=0.5') = 'TRUE';
GeoInfo June 13, 2000
56
Copyright Oracle Corporation, 2000. All rights reserved.
SDO_GEOM.RELATE Function
•
Determine relationship of counties and
states
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
------------------------------Atlantic
Cape May
Cumberland
Essex
RELATIONSHIP
-------------COVERS
COVERS
COVERS
CONTAINS
Note: this is simplified syntax
GeoInfo June 13, 2000
57
Copyright Oracle Corporation, 2000. All rights reserved.
®
Oracle Corporation
Jayant Sharma, Ph.D.
Consulting Member Technical Staff
Spatial Products
Server Technologies
One Oracle Drive
Nashua, NH 03062
Phone : (603) 897 - 3187
[email protected]
GeoInfo June 13, 2000
59
Copyright Oracle Corporation, 2000. All rights reserved.