|
"God
will not look your over for medals, degrees or
diplomas, but for scars."
-Elbert
Hubbard (1856-1915)
|
How
to use very fast time series queries?
The
Reference View Mechanism encapsulates information related to
the tables and columns where the time series data is stored,
and a runtime mechanism that interprets this information and
dynamically generates a SQL query during the invocation of a
time series function.
This
reference view delivers very high performance for a wide range
of time series queries.
The
reference view is defined over the same columns as the time
series detail table. However, instead of returning all the
underlying data for a particular time series, the view returns
a structure to be used at runtime by database internal engine.
This structure provides the necessary meta-data
enabling the runtime to obtain the information pertaining to
the referenced time series, without fetching the entire time
series from disk. ONLY
THE DATA NECESSARY FOR THE QUERY IS FETCHED FROM DISK
Example
Set up
Consider
the SQL syntax for invoking a moving average.
The following query issues a 10-day moving average for
the close price of 'ORCL' for the month of December 2000.
SELECT *
FROM stockdemo_ts ts,
TABLE (CAST(ordsys.timeseries.ExtractTable(
ordsys.timeseries.Mavg(ts.close,
to_date('01-DEC-00','DD-MON-YY'),
to_date('31-DEC-00','DD-MON-YY'),
10)
) AS ordsys.ordtNumTab))
WHERE ts.ticker='ORCL';
Here
we see the Mavg function invoked with the qualifier
"close", the first and last date in December 2000,
and a window of size 10. The ExtractTable function produces a
nested table type (ordtNumTab), which is a table of DATE and
NUMBER columns.
Note
that the Reference View is included in the FROM-list of time
series queries. Also note that the 'ORCL' time series may
contain many years of data, and the query may only require a
few of these data points.
For
a more complete context, we next consider the tables involved
in one approach for using the reference view.
First,
consider an example of an underlying table of time series
detail data. OTS
(Oracle Time Series) employs three basic tables to store time
series information:
1.
Time Series Detail Table
The
Detail Table stores the "raw" time series data. The
detail table has three types of columns:
Identifier
Column(s) - The detail table also has one or more
identifier columns (such as "ticker") used to
identify the time series.
Timestamp
Column - A timestamp indicates the time when the
observation(s) were made.
Observation
Column(s) - The detail table has data for five
observations columns, corresponding to open, high, low,
volume, and close. These
columns correspond to observed numerical values in the time
series. (Note that in general, a detail table may have an
arbitrary number of observation columns, and the names of
these columns are also arbitrary).
An example time series detail table is show below:
CREATE TABLE stockdemo
(ticker VARCHAR2(5),
tstamp DATE,
open
NUMBER,
high
NUMBER,
low
NUMBER,
close
NUMBER,
volume INTEGER,
CONSTRAINT pk_stockdemo PRIMARY KEY (ticker, tstamp));
2.
Calendar Table
The
calendar table stores instances of calendars.
Calendars essentially describe ALL VALID TIMESTAMPS in
a time series, and are used as a navigational aid in time
series functions to enable functions such as lead/lag,
annualized sum, and moving average.
The
calendar is implemented as an object type
CREATE TABLE stockdemo_calendars of ordsys.ordtCalendar
(
name CONSTRAINT pk_stockdemo_calendars PRIMARY KEY);
The
fields of the type include the calendar name (for retrieval
indexed by name), the frequency of the time series, boundary
dates, and other information relevant to describing which
dates are valid dates in a time series.
The
map table associates a time series identifier with a specific
calendar:
CREATE TABLE stockdemo_map (
qualname VARCHAR2(20),
calendarname VARCHAR2(20),
CONSTRAINT pk_stockdemo_map
PRIMARY KEY (qualname));
The
reference view is defined over the qualifier and observation
columns.
CREATE OR REPLACE VIEW stockdemo_ts(ticker,
open,
high,
low,
close,
volume)
AS
SELECT map.qualname,
ordsys.ordtNumSeriesIOTRef(
map.qualname, Ref(cal), 'stockdemo_sv',
'tstamp', 'open', 'ticker', map.qualname),
ordsys.ordtNumSeriesIOTRef(
map.qualname, Ref(cal), 'stockdemo_sv',
'tstamp', 'high', 'ticker', map.qualname),
ordsys.ordtNumSeriesIOTRef(
map.qualname, Ref(cal),
'stockdemo_sv',
'tstamp', 'low', 'ticker', map.qualname),
ordsys.ordtNumSeriesIOTRef(
map.qualname, Ref(cal),
'stockdemo_sv',
'tstamp', 'close', 'ticker', map.qualname),
ordsys.ordtNumSeriesIOTRef(
map.qualname, Ref(cal),
'stockdemo_sv',
'tstamp', 'volume', 'ticker', map.qualname)
FROM stockdemo_map
map, stockdemo_calendars cal
WHERE map.calendarname = cal.name;
The
type ordsys.ordtNumSeriesIOTRef includes the following fields:
i) name
of returned time series
ii) reference
to the calendar
iii)
name of time series detail table
iv) name
of timestamp column
v) name
of observation column
vi) name
of qualifier column
vii) qualifier name
As
mentioned earlier, these fields are used by the internal
runtime to issue queries to the appropriate tables, and the
appropriate columns of these tables.
|