Release 8.0.4
A57501-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Time Series Cartridge User's Guide Release 8.0.4 A57501-01 |
|
This chapter explains important procedures related to using the Oracle8 Time Series Cartridge. It covers the following topics:
This section provides a technical overview of using the Time Series cartridge. It presents the major steps, with examples.
For more detailed explanations of the concepts and terminology, see Chapter 2.
Create the table to hold the time series data. Example 3-1 shows the table definition for a stock trading database.
/* Table Creation (user) */ 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))
ORGANIZATION INDEX;
Notes on Example 3-1:
The table is named stockdemo and has the columns for the ticker (stock symbol), the timestamp (date on which stocks are traded), that day's opening, high, low, and closing prices, and the trading volume.
The constraint named pk_stockdemo defines the primary key as the ticker plus the timestamp.
ORGANIZATION INDEX indicates that this is an index-organized table.
The CREATE TABLE statement can also include other keywords, such as TABLESPACE and STORAGE.
If the calendar does not already exist, create it by inserting its definition in a table of calendars. If the table of calendars does not already exist, create it first.
Your calendar will be based on the system-defined datatype ORDTCalendar, which is supplied with the cartridge. ORDTCalendar has the following definition:
/* System-Defined Calendar Datatype */ CREATE TYPE ORDSYS.ORDTCalendar AS OBJECT ( caltype INTEGER, name VARCHAR2(256), frequency INTEGER, pattern ORDSYS.ORDTPattern, minDate DATE, maxDate DATE, offExceptions ORDSYS.ORDTExceptions, onExceptions ORDSYS.ORDTExceptions);
The following example creates a table named stockdemo_calendars and defines a calendar named BusinessDays. The BusinessDays calendar includes Mondays through Fridays in 1997, but excludes 04-Jul-1997 and 25-Dec-1997. Explanatory notes follow the example.
CREATE TABLE stockdemo_calendars of ORDSYS.ORDTCalendar; INSERT INTO stockdemo_calendarsVALUES( ORDSYS.ORDTCalendar( 0,
`BusinessDays',
4,
ORDSYS.ORDTPattern(ORDTPatternBits(0,1,1,1,1,1,0),
(to_date(`01-05-97','MM-DD-YY'))), to_date(`01-01-97','MM-DD-YY'),
to_date(`01-01-98','MM-DD-YY'), ORDSYS. ORDTExceptions(to_date(`07-04-97','MM-DD-YY'),
to_date(`12-25-97','MM-DD-YY')), NULL));
Notes on Example 3-2:
stockdemo_calendars is a table of ORDSYS.ORDTCalendar objects. The ORDTCalendar datatype is described in Section 2.2.2.
0 (zero) for calendar type (caltype) indicates that this is an exception-based calendar. (This is the only calendar type currently supported.)
BusinessDays is the name of this calendar.
4 is the frequency code for day.
The pattern is defined as an excluded occurrence followed by five included occurrences followed by an excluded occurrence (0,1,1,1,1,1,0). Because the frequency is daily and because the anchor date (05-Jan-1997) is a Sunday, Sundays are excluded, Mondays through Fridays are included, and Saturdays are excluded.
The calendar begins at the start of 01-Jan-1997 and ends at the start of 01-Jan-1998.
04-Jul-1997 and 25-Dec-1997 are off-exceptions (that is, excluded from the calendar).
NULL indicates that there are no on-exceptions (that is, no Saturday or Sunday dates to be included in the calendar).
Perform a bulk load of the time series data in order to populate the underlying data storage tables. Follow the guidelines and instructions for bulk loading in Section 3.2.
Create a security view and INSTEAD OF triggers, to ensure the consistency and integrity of time series data, as explained in Section 2.6.2.
Example 3-3 creates a security view (stockdemo_sv) to get all ticker values.
CREATE OR REPLACE VIEW stockdemo_sv AS SELECT * FROM stockdemo;
After you create the view, create INSTEAD OF triggers using the definitions in the securevw.sql demo file as examples or templates. Example 3-4 creates an INSTEAD OF trigger (stockdemo_sv_delete) that ensures the following:
If you grant users access to the security view and deny access to the underlying tables, you can ensure that all delete operations are checked and performed by the trigger. (Similar INSTEAD OF triggers can be written to allow safe insert and update operations. For more information about using INSTEAD OF triggers with security views, see Section 2.6.2.2.)
CREATE OR REPLACE TRIGGER stockdemo_sv_delete INSTEAD OF DELETE on stockdemo_sv REFERENCING old AS o FOR EACH row DECLARE cal ORDSYS.ORDTCalendar := NULL; purifieddate DATE; startdate DATE; enddate DATE; BEGIN -- -- Retrieve the calendar that maps to the stock ticker. -- BEGIN SELECT VALUE(c) INTO cal FROM stockdemo_calendars c, stockdemo_metadata m WHERE m.tickername = :o.ticker AND c.name = m.calendarname; EXCEPTION when NO_DATA_FOUND THEN raise_application_error(-20000,'Could not find calendar'); END; IF cal IS null THEN raise_application_error(-20000, 'NULL calendar found'); END IF; -- -- Set the precision of timestamp to correspond to the precision -- of the calendar. -- purifieddate := ORDSYS.Calendar.SetPrecision(:o.tstamp,cal.frequency); -- -- Retrieve the current startdate AND enddate for the stock ticker; -- SELECT max(tstamp),min(tstamp) INTO enddate,startdate FROM stockdemo_sv WHERE ticker = :o.ticker; -- -- There are three cases of deletion to consider: -- -- Case 1: The table does not have any existing time series -- entries for the given ticker. In this case the -- trigger raises an exception. -- -- Case 2: The tstamp is equal to the current startdate. -- This routine verifies this and then deletes -- the row. -- -- Case 3: The tstamp is equal to the current enddate. -- This routine verifies this and then deletes the row. -- -- If the time series is not empty and if the row being -- deleted is not the startdate or enddate, an exception -- is raised. -- IF startdate IS null THEN raise_application_error(-20000,'Timeseries is empty'); ELSE IF (NOT ((purifieddate = startdate) or (purifieddate = enddate))) THEN raise_application_error(-20000, 'Timestamp date not startdate or enddate'); END IF; END IF; -- -- Delete the row in the time series. -- DELETE FROM stockdemo WHERE ticker = :o.ticker AND tstamp = purifieddate; END; -- -- Next, create two other triggers: one update-specific (for -- example, stockdemo_sv_update) and the other insert-specific -- (for example, stockdemo_sv_insert). See the Time Series -- cartridge usage demo for an example. -- After creating all appropriate triggers, grant SELECT, DELETE, -- UPDATE, and INSERT privileges on the security view to the -- appropriate users. For example: -- GRANT SELECT,DELETE,UPDATE,INSERT on stockdemo_sv TO ordtuser;
Create a reference-based view for convenient and efficient access to time series data, as explained in Section 2.5.2.
Example 3-5 creates a reference-based view for stock price data.
CREATE VIEW stockdemo_ts(ticker,open,high,low,close,volume) AS SELECT meta.tickername, ORDSYS.ORDTNumSeriesIOTRef( substr(meta.tickername, 1, 230) || ' open NumSeries', Ref(cal), 'ORDTDEV.stockdemo', 'tstamp', 'open', 'ticker', meta.tickername), ORDSYS.ORDTNumSeriesIOTRef( substr(meta.tickername, 1, 230) || ' high NumSeries', Ref(cal), 'ORDTDEV.stockdemo', 'tstamp', 'high', 'ticker', meta.tickername), ORDSYS.ORDTNumSeriesIOTRef( substr(meta.tickername, 1, 230) || ' low NumSeries', Ref(cal), 'ORDTDEV.stockdemo', 'tstamp', 'low', 'ticker', meta.tickername), ORDSYS.ORDTNumSeriesIOTRef( substr(meta.tickername, 1, 230) || ' close NumSeries', Ref(cal), 'ORDTDEV.stockdemo', 'tstamp', 'close', 'ticker', meta.tickername), ORDSYS.ORDTNumSeriesIOTRef( substr(meta.tickername, 1, 230) || ' volume NumSeries', Ref(cal), 'ORDTDEV.stockdemo', 'tstamp', 'volume', 'ticker', meta.tickername) FROM stockdemo_metadata meta, stockdemo_calendars cal WHERE meta.calendarname = cal.name;
The refvw.sql demo file creates a reference-based view.
Choose one of the following approaches to ensuring the consistency of time series data, using the guidelines in Section 2.6.3:
Use the DeriveExceptions function in adjusting a calendar to be consistent with the time series. See Section 2.2.4 for more information about this approach.
Use the IsValidTS function to check that the time series is consistent with the calendar. See the IsValidTS function reference information in Chapter 5.
Formulating time series queries involves invoking time series or time scaling functions, or both. Example 3-6 uses the Mavg time series function to obtain 30-day moving averages for stock ACME, and it uses the ScaleupSum time scaling function to obtain monthly volumes for stock ACME. (The results shown in the example reflect sample data for the cartridge usage demo.)
The queries in this step use the reference-based view (stockdemo_ts) that was created in step 6.
SELECT * FROM THE( SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Mavg(close, to_date('11-01-96','MM-DD-YY'), to_date('12-31-96','MM-DD-YY'), 10)) as ORDSYS.ORDTNumtab) FROM stockdemo_ts WHERE ticker = 'ACME'); TSTAMP VALUE --------- ---------- 01-NOV-96 04-NOV-96 05-NOV-96 06-NOV-96 07-NOV-96 08-NOV-96 11-NOV-96 12-NOV-96 13-NOV-96 14-NOV-96 63.5 15-NOV-96 64.5 18-NOV-96 65.5 19-NOV-96 66.5 20-NOV-96 67.5 21-NOV-96 68.5 22-NOV-96 69.5 25-NOV-96 70.5 26-NOV-96 71.5 27-NOV-96 72.5 29-NOV-96 73.5 02-DEC-96 74.5 03-DEC-96 75.5 04-DEC-96 76.5 05-DEC-96 77.5 06-DEC-96 78.5 09-DEC-96 79.5 10-DEC-96 80.5 11-DEC-96 81.5 12-DEC-96 82.5 13-DEC-96 83.5 16-DEC-96 84.5 17-DEC-96 85.5 18-DEC-96 86.5 19-DEC-96 87.5 20-DEC-96 88.5 23-DEC-96 89.5 24-DEC-96 90.5 26-DEC-96 91.5 27-DEC-96 92.5 30-DEC-96 93.5 31-DEC-96 94.5 41 rows selected. SELECT * FROM THE( SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.ScaleupSum(volume,value(cal))) as ORDSYS.ORDTNumtab) FROM stockdemo_ts, stockdemo_calendars cal WHERE ticker = 'ACME' AND cal.name = 'MONTHLY'); TSTAMP VALUE --------- ---------- 01-NOV-96 20000 01-DEC-96 21000 2 rows selected.
This section describes how to use the SQL*Loader utility to perform bulk loading and incremental loading of time series data.
To ensure the consistency of time series data during loading, you must choose one of the approaches described in Section 2.6.3:
This strategy is normally appropriate when there is a unique calendar per time series.
This approach is particularly useful if the same calendar is used for all time series data being loaded.
This section describes how to perform bulk loading using these two approaches, and it also describes how to perform incremental loading.
The loading of time series data is usually performed under controlled circumstances, so it is safe to perform these loads directly to an underlying table instead of to a security view.
After you create an index-organized table (IOT) to hold time series data (such as for the stockdemo demo database), you must populate the table with data. For a database of stock information, you may need to load millions of rows of daily summary information into the IOT.
SQL*Loader is recommended for loading large amounts of time series data. The following example shows a SQL*Loader script, with an excerpt from the sample data (stockdat.dat) and the SQL*Loader control file (stockdat.ctl). For complete information about SQL*Loader, see the Oracle8 Utilities manual.
The SQL*Loader script contains the following:
% sqlldr userid=ordtdev/ordtdev control=stockdat.ctl log=stockdat.log bad=stockdat.bad errors=1000
The stockdat.dat sample data file includes the following:
ACME 01-NOV-96 59.00 60.00 58.00 59.00 1000 ACME 04-NOV-96 60.00 61.00 59.00 60.00 1000 ACME 05-NOV-96 61.00 62.00 60.00 61.00 1000 ...
The stockdat.ctl file contains the following
options (direct=true) unrecoverable load data infile 'stockdat.dat' replace into table stockdemo sorted indexes (StockTabx) fields terminated by whitespace (ticker, tstamp DATE(13) "DD-MON-YY", open, high, low, close, volume)
SQL*Loader can handle many file formats and delimiters, as documented in the Oracle8 Utilities manual.
After the load has completed, you may want to choose one of the following approaches for ensuring calendar consistency:
In either case, you may need to update the exception lists of your calendars.
Often you will want to create calendars that conform to the time series data that you are receiving. In this case, you usually know the frequency and the pattern of a calendar, but not the specific on- or off-exceptions. You can extract these exceptions from the data by using the DeriveExceptions function.
Often you will want to ensure that the time series data extracted from the incoming data conforms to a predefined calendar. To do this, insert the exceptions either when you create the calendar or afterward with the InsertExceptions functions (or do both, creating the calendar with some exceptions and then adding others); then use the IsValidTimeSeries function to check that the time series is consistent with the calendar.
You can insert exceptions when you define the calendar. For example, the following statement specifies 28-Nov-1996 and 25-Dec-1996 as off-exceptions in the calendar named BUSINESS-96:
INSERT INTO stockdemo_calendars VALUES( ORDSYS.ORDTCalendar( 0, 'BUSINESS-96', 4, ORDSYS.ORDTPattern( ORDSYS.ORDTPatternBits(0,1,1,1,1,1,0), TO_DATE('01-JAN-1995','DD-MON-YYYY')), TO_DATE('01-JAN-1990','DD-MON-YYYY'), TO_DATE('01-JAN-2001','DD-MON-YYYY'), ORDSYS.ORDTExceptions( TO_DATE('28-NOV-1996','DD-MON-YYYY'), TO_DATE('25-DEC-1996','DD-MON-YYYY')), ORDSYS.ORDTExceptions() ));
You can also add exceptions after the calendar is defined by using the InsertExceptions function. For example, the following statement adds 01-Jan-1997, 17-Feb-1997, and 26-May-1997 as off-exceptions:
UPDATE stockdemo_calendars cal SET cal = (SELECT ORDSYS.Calendar.InsertExceptions( VALUE(cal), ORDSYS.ORDTDateTab( to_date('01-JAN-97','DD-MON-YY'), to_date('17-FEB-97','DD-MON-YY'), to_date('26-MAY-97','DD-MON-YY'))) FROM dual) WHERE cal.name = 'BUSINESS-96';
After you have defined the calendar and populated the exception lists, you can use the IsValidTimeSeries function to check that the time series is consistent with the calendar.
After you have performed the bulk load of time series data and have started using the Time Series cartridge, you will probably want to add data periodically. For example, every trading day after the stock exchange closes, that day's data for each ticker becomes available.
As with bulk loading, incremental loading is typically done in a controlled environment. You know which timestamps will become off-exceptions, and you can explicitly update the exception lists of the appropriate calendars. The following example demonstrates such an update:
UPDATE stockdemo_calendars cal SET cal = (SELECT ORDSYS.Calendar.InsertExceptions( VALUE(cal), to_date('01-JAN-97','DD-MON-YY')) FROM dual) WHERE cal.name = 'XCORP';
The SQL*Loader utility is recommended for performing an incremental load of such additional data. The following example shows a SQL*Loader script, with an excerpt from the sample daily data (stockinc.dat) and the SQL*Loader control file (stockinc.ctl).
The SQL*Loader script contains the following:
sqlldr userid=ordtdev/ordtdev control=stockinc.ctl log=stockinc.log bad=stockinc.bad errors=1000
The stockinc.dat sample data file includes the following:
ACME 02-JAN-97 100.00 101.00 99.00 100.00 1000 FUNCO 02-JAN-97 25.00 25.00 25.00 25.00 2000 SAMCO 02-JAN-97 39.00 40.00 38.00 39.50 30000 ...
The stockinc.ctl file contains the following:
load data infile 'stockinc.dat' append into table stockdemo fields terminated by whitespace (ticker, tstamp DATE(13) "DD-MON-YY", open, high, low, close, volume)
Note the following differences in the control file for incremental loading as opposed to bulk loading:
The conventional path is better for incremental loading because the amount of new data (daily stock information) is small relative to the total amount of data. For an explanation of conventional and direct paths, including situations in which the conventional path is necessary or preferable, see the SQL*Loader documentation in the Oracle8 Utilities manual.
This section explains in greater detail the two approaches to deriving calendar exceptions from time series data. These two approaches were introduced in Section 2.2.4; see that section for information on concepts related to exceptions and the reasons for choosing a particular approach.
The first approach to deriving exceptions takes a calendar and an ORDTDateTab (that is, a table of dates) as input parameters, using the following form of the function:
DeriveExceptions(cal ORDTCalendar, DateTab ORDTDateTab)
The table of dates (DateTab parameter) includes all dates in the time series, for example, all dates on which stock XYZ traded. A calendar is returned that is defined on the same pattern and frequency as the input calendar, and the exception lists of the returned calendar are populated to be consistent with the time series data in DateTab. The exception lists are updated based on finding timestamps that are in the calendar pattern or in the table of dates, but not in both. (A timestamp is in the calendar pattern if it is within the date range of the calendar and maps to an on (1) bit in the pattern.)
The returned calendar's on- and off- exceptions are populated based on the calendar pattern and the table of dates, as follows:
For example, 04-Jul-1997 (Friday) is in the pattern of a stock trading calendar, but it is not a date on which U.S. stocks were traded.
The following example derives the exceptions for all time series in the stockdemo table and updates the corresponding calendars in the stockdemo_calendars table:
UPDATE stockdemo_calendars cal SET cal = (SELECT ORDSYS.Calendar.DeriveExceptions( VALUE(cal), CAST(multiset( SELECT s.tstamp FROM stockdemo s WHERE cal.name = s.ticker) AS ORDSYS.ORDTDateTab)) FROM dual);
This approach (Approach 1) to deriving calendar exceptions has the following requirements:
The second approach to deriving exceptions takes two time series references as input parameters, using the following form of the function:
DeriveExceptions(series1 ORDTNumSeriesIOTRef,
series2 ORDTNumSeriesIOTRef)
or
DeriveExceptions(series1 ORDTVarchar2SeriesIOTRef,
series2 ORDTVarchar2SeriesIOTRef)
This overloading of the DeriveExceptions function allows the input parameters to be time series REFs (either two ORDTNumSeriesIOTRef parameters or two ORDTVarchar2SeriesIOTRef parameters).
Before calling DeriveExceptions, you must construct a time series based on a reference calendar. This time series will contain all the timestamps within the date range (minDate through maxDate) of the calendar.
The following example builds a reference time series based on a calendar named PATTERN-ONLY. An INSERT statement populates the time series named PATTERN-ONLY with the valid timestamps between the starting and ending dates of the calendar.
INSERT INTO stocks(ticker,tstamp) SELECT 'PATTERN-ONLY', t1.c1 FROM (SELECT column_value c1 FROM the (SELECT CAST(ORDSYS.Calendar.TimeStampsBetween(VALUE(cal), cal.mindate, cal.maxdate) AS ORDSYS.ORDTDateTab) FROM stock_calendars cal WHERE cal.name = 'PATTERN-ONLY')) t1;
The insertion is made directly into the underlying table, not into the security view. Using the underlying table is safe here because the time series is presumed to be correct, so the mechanisms for ensuring consistency between the time series and the calendar provided by the security view are not needed in this case.
The PATTERN-ONLY calendar should have no exceptions. If this calendar has any exceptions, the resulting time series will have non-null exception lists, which will cause the DeriveExceptions function to report an error.
After you create the reference time series, call the DeriveExceptions function with the reference time series as the first parameter (series1). DeriveExceptions compares the dates in series1 with the dates in series2, and it returns the calendar of series1 with the exceptions created as follows:
For example, if series2 contains dates on which stock XYZ traded and 04-Jul-1997 (Friday) is not in that time series, then 04-Jul-1997 is added to the calendar as an off-exception.
The following example uses the reference time series created in the preceding statement to update the exception lists of every other calendar in the stockdemo_calendars table, with the exceptions for each calendar derived from the timestamps in the associated time series. (This example assumes that each calendar maps to a time series with the same name.)
UPDATE stockdemo_calendars cal SET cal = (SELECT ORDSYS.TimeSeries.DeriveExceptions(ts1.open,ts2.open) FROM stocks_ts ts1, stocks_ts ts2 WHERE ts1.ticker = 'PATTERN-ONLY' and ts2.ticker = cal.name) WHERE cal.name <> 'PATTERN-ONLY';
This approach (Approach 2) to deriving calendar exceptions has the following requirements:
Product-developer functions, described in Section 2.7.2, let you modify and expand the Time Series cartridge capabilities. For example, an ISV could develop additional time series analysis functions by calling product-developer functions.
The following example shows the use of the IsValidDate, NumTstampsBetween, and OffsetDate product-developer functions in a PL/SQL implementation of the Lead function. The Lead function inputs a time series and a lead_date, and returns a time series where the starting timestamp is the lead_date. (Note that to simplify the presentation, some error checking has been omitted.)
create function Lead (ts ORDSYS.ORDTNumSeries, lead_date date) return ORDSYS.ORDTNumSeries is i integer; outts ORDSYS.ORDTNumSeries; /* Temporary Storage for Result */ new_tstamp date; /* Changeable version of lead_date */ last_lead_date date; /* Last timestamp of the output time series*/ first_tstamp date; /* First timestamp of the input time series */ last_index integer; /* Last index of the input time series */ last_tstamp date; /* Last timestamp of the input time series */ units integer; /* Number of timestamps between input and output time series */ ERR_LEAD_TSTAMP_BOUNDS constant integer := 20540; ERR_LEAD_TSTAMP_BOUNDS_MSG constant varchar2(100) := 'Projected lead timestamp beyond calendar bounds'; begin first_tstamp :=ts.series(1).tstamp; last_index :=ts.series.last; last_tstamp :=ts.series(last_index).tstamp; if ORDSYS.Calendar.IsValidDate(ts.cal, lead_date) = 0 then Raise_Application_Error(ERR_LEAD_TSTAMP_BOUNDS, ERR_LEAD_TSTAMP_BOUNDS_MSG); end if; /* units is the number of timestamps between the first timestamp of the input time series and lead_date. */ units := ORDSYS.Calendar.NumTimeStampsBetween(ts.cal, first_tstamp, lead_date); last_lead_date := ORDSYS.Calendar.OffsetDate(ts.cal, last_tstamp, units); if last_lead_date is null then Raise_Application_Error(ERR_LEAD_TSTAMP_BOUNDS, ERR_LEAD_TSTAMP_BOUNDS_MSG); end if; /* Instantiate output time series. */ outts := ORDSYS.ORDTNumSeries('Lead Result', ts.cal, ORDSYS.ORDTNumTab()); outts.series.extend(last_index); /* Assign the first timestamp of the output time series to first_lead_date. Copy value from input time series to output time series. */ new_tstamp := lead_date; outts.series(1) := ORDSYS.ORDTNumCell(new_tstamp, ts.series(1).value); /* Assign subsequent timestamps by calling OffsetDate with the previous date and an offset of 1. */ for i in 2..outts.series.last loop new_tstamp := ORDSYS.Calendar.OffsetDate(ts.cal, outts.series(i-1).tstamp, 1); outts.series(i) := ORDSYS.ORDTNumCell(new_tstamp, ts.series(i).value); end loop; return(outts); end;
For other examples of using product-developer functions, see the files for the advanced-developer demo (described briefly in Table 1-1 in Section 1.6).
