13.2 Extensibility Features and Options
Oracle's extensibility features and options extend
SQL to perform tasks that can't otherwise be easily
programmed in a relational database. These include manipulation of
time-series data, multimedia, and spatial data. These features are
typically used by application developers but are sometimes bundled
with applications sold by Oracle partners.
13.2.1 Oracle Time Series
A time series is
a set of data in which each entry contains a timestamp. Such data is
typically used in financial and trading applications and is often
obtained from data-collection devices. In general, tables with
time-series data don't contain a lot of columns but
do contain many rows of data (perhaps representing a long history). A
common example of a time series is the daily reporting of a
stock's highs and lows, opens and closes, and
volumes, as shown in Table 13-1.
Table 13-1. Typical historical time series of stock trading data|
BIGCO
|
03-01-1999
|
30.125
|
29.75
|
30.50
|
29.50
|
285,000
|
BIGCO
|
03-02-1999
|
30.00
|
29.50
|
30.00
|
28.50
|
290,000
|
BIGCO
|
03-03-1999
|
29.25
|
30.125
|
30.50
|
29.00
|
275,000
|
BIGCO
|
03-04-1999
|
30.00
|
30.50
|
31.125
|
30.00
|
285,000
|
BIGCO
|
03-05-1999
|
30.25
|
31.125
|
32.00
|
30.00
|
310,000
|
BIGCO
|
03-08-1999
|
31.00
|
30.25
|
31.50
|
29.75
|
295,000
|
BIGCO
|
03-09-1999
|
30.50
|
31.00
|
30.50
|
32.125
|
300,000
|
Time-series functions for analyzing this data were first introduced
as a separate option in Oracle8. Today, time-series data can be
analyzed using SQL analytic functions that are included with the
Oracle database, and the separate option no longer exists. The SQL
analytic functions are further described in Chapter 9.
13.2.2 Oracle interMedia and Oracle Text
Oracle
interMedia
has been included with the database since Version 8.1.6 of
Oracle8i. In Oracle9i, the
product's text features became known as Oracle Text.
These features were available as options in previous versions of
Oracle:
The Text Management feature was formerly known as the ConText option. The Locator feature evolved from the Spatial option and supports the
location queries and the geocoding described later, in Section 13.2.3. Image storage and manipulation features were formerly bundled in the
Image option.
Additionally, the product extensions enable the storage and
manipulation of audio and video clips. Oracle has
positioned Oracle interMedia and Oracle Text as
being useful features for applications that typically include
multiple media types because the features integrate all of these key
datatypes and their associated functions.
Oracle interMedia and Oracle Text utilize a
number of underlying database storage options, which are described in
Table 13-2.
Table 13-2. Storage options for Oracle interMedia and Oracle Text|
Text/images
|
VARCHAR2
BLOB
CLOB
VARCHAR
CHAR
LONG
LONG RAW
Object attribute
Master-detail stores (in which the master table identifies the text
or image and the detail table contains the content)
BFILEs
URLs that point to content
|
Audio and video clips
|
BLOB
BFILE
URLs that point to content
|
Locator ordinates
|
VARRAYs
|
Oracle Database 10g has been enhanced to store
large documents of up to 128 terabytes in LOBs.
Oracle interMedia and
Oracle Text support a number of commonly
used formats:
Documents can be indexed while stored in formats such as ASCII,
Microsoft Word, Excel, PowerPoint, WordPerfect, HTML, XML, and Adobe
Acrobat (PDF). Audio formats supported include AU, AIFF, AIFF-C, WAV, MPEG1, MPEG2,
and MPEG4. Video formats supported include Apple QuickTime 3.0, AVI, video MPEG
formats (MPEG and MP4), and Real Networks Real video format (RMFF). Image formats supported include BMPF, CALS, FPIX, GIFF (GIF), JFIF
(JPEG), PBMF, PGMF, PPMF, PPNF, PCXF (PCX), PICT, PNGF, RPIX, RASF,
TGAF, TIFF, and WBMP. Image compression formats supported include
ASCII encoding, BMPRLE, DEFLATE, DEFLATE-ADAM7, FAX3, FAX4, GIFLZW,
GIFLZW-INTERLACED, HUFFMAN3, JPEG, JPEG-PROGRESSIVE, LZW, LZWHDIFF,
NONE, PACKBITS, PCXRLE, RAW, SUNRLE, and TARGARLE.
With Oracle's text-management capabilities, you can
identify the strongest theme (or gist) of a
document and generate document summaries based on that theme. Oracle
Database 10g additions include theme (NEAR)
proximity searching and the ability to determine the character set
and language of documents with unknown content. Searching
capabilities include full-text searches for word and phrase matching,
theme searches, and mixed searches for both text and non-text data.
Oracle Database 10g adds native indexing columns
of type XMLType using Oracle Text. Typical users of Oracle text
management are news services that publish news items to interested
users via the Web. Oracle Database 10g includes
an algorithm for determining popularity rankings of web pages and
content.
Oracle Database 10g adds an easy custom text
application building interface through JDeveloper with a text
application generator, a catalog search application generator wizard,
and a classification training set wizard.
Oracle9i introduced Ultra Search, a prebuilt
application built upon Text for performing searches. Today, Ultra
Search is included with the Oracle database, Oracle Application
Server, and Oracle Collaboration Suite. Ultra Search can search and
locate text in Oracle databases, other ODBC-accessible databases,
Oracle Portal repositories, IMAP mail servers, HTML documents
available from web servers, and other files. Using a
crawler to index documents, this index of
documents residing in the different servers is then stored in an
Oracle database. Oracle Application Server users can access Ultra
Search through a portlet. Applications builders can invoke Ultra
Search using PL/SQL or Java procedures and use the APIs to make
crawler results "searchable."
Collaboration Suite leverages Ultra Search in searches of its Mail
and Files components. Searches in Oracle Files use the Files fulltext
index; thus, crawling to build an index is not needed in this
component.
Image support
in the Oracle database includes conversion among image and
compression formats, access to raw pixel data, and support for basic
image-manipulation functions such as scaling and cropping.
Clients can access audio and video files through Java Media Framework
(JMF) players. (Java Advanced Imaging in
Oracle9i and more recent releases also provides
image support through JMF.) Streaming servers such as the Real
Networks Server can also deliver audio and video content on demand.
You can also access images, audio, and video stored in Oracle and
interMedia through C++, Java, OCI, or PL/SQL.
Oracle Database 10g image object types support
the SQL/MM Still Image standard, ISO/IEC 13249-5 SQL. Also added in
Oracle Database 10g is support for the Sun
Microsystems Java Advanced Imaging (JAI) package for storing and
processing content. Audio, video, and images stored using
interMedia might also be included as part of a
web site using a variety of web- authoring tools, including the
Portal in the Oracle Application Server, Symantec Visual Page,
Microsoft FrontPage, Macromedia Dreamweaver, and Ultradev.
13.2.3 Oracle Spatial Option
Spatial data is data that contains location information. The
Oracle
Spatial option provides the functions and procedures that allow
spatial data to be stored in an Oracle database and then accessed and
analyzed according to location comparisons.
An example of using spatial query functions to combine spatial and
standard relational conditions would be to "find all
homes within two square miles of the intersection of Main Street and
First Avenue in which the residents' income is
greater than $100,000, and show their location."
This query might return a list of home addresses or, when used with a
Geographic Information System (GIS), plot the home locations on a
map, as shown in Figure 13-2. Geocoding matches references such as
addresses, phone numbers (including area codes), and postal codes
(with longitude and latitude), which are then stored in the database.
Multiple geometric forms are supported by the Oracle Spatial option
to represent many different types of spatial data, including points
and point clusters, lines and line strings, polygons and complex
polygons with holes, arc strings, line strings, compound polygons,
and circles. You can determine the interaction of these features
through the use of operators such as touch, overlap, inside, and
disjoint.
Data that shares the same object space and coordinates but represents
different characteristics (such as physical and economic) is often
modeled in layers. Each layer is divided into tiles representing
smaller subareas within the larger area. A representation of this
tile is stored with a spatial index that provides for quick lookups
of multiple characteristics in the same tile. The Spatial option uses
these representations to rapidly retrieve data based on spatial
characteristics. For example, you can perform a query against a
physical area to examine where pollutants, minerals, and water are
present. Each of these characteristics is likely to be stored in a
separate layer, but they can be quickly mapped to their common tiles.
The designers of these spatial-based databases can increase the
resolution of the maps by increasing the number of tiles representing
the geography.
The Spatial option fully leverages Oracle's object
features through the use of a spatial object type
that represents single- or multi-element geometries. Spatial
coordinates are stored in VARRAYs.
Oracle Database 10g introduces the GeoRaster for
storing, indexing, querying, analyzing, and delivering raster image
data, associated Spatial vector geometry data, and metadata. This
feature enables storage of multidimensional grid layers and digital
images in an object-relational schema that are referenced to
coordinate systems.
Oracle Database 10g also introduces topology and
network management, enabling manipulation of data-describing nodes
and edges in a topology or network or manipulation of faces in a
topology.
In the real world, most spatial implementations in business
aren't custom-built from SQL, but instead utilize
purchased GIS solutions that are built on top of databases. Many of
these GIS providers include Oracle Spatial technology as part of
their product bundles. With the Oracle Database
10g release, you can also use newly introduced
spatial analysis and mining subprograms in Oracle Data Mining option
applications.
|
No comments:
Post a Comment