Oracle8i Time Series User's Guide
Release 8.1.5

A67294-01

Library

Product

Contents

Index

Prev Next

2
Time Series Concepts

This chapter explains concepts related to Oracle8i Time Series, and it provides information on using the product. It contains the following major sections:

2.1 Overview of Time Series Data

A time series is a set of timestamped data entries. A time series allows a natural association of data collected over intervals of time. For example, summaries of stock market trading or banking transactions are typically collected daily, and are naturally modeled with time series.

2.1.1 Regular and Irregular Time Series

A time series can be regular or irregular, depending on whether or not the time series has an associated calendar.

2.1.2 Data Generation for a Time Series

Data generation for a time series begins with individual transactions, such as trades on a stock exchange or purchases of products. Each transaction has a timestamp and sufficient information to identify that transaction uniquely (such as a stock ticker or a product ID), as well as other pertinent information (such as the price and information to identify the party initiating the purchase or sale).

Individual transaction data is typically rolled up to produce summary data for a meaningful time period, such as a daily summary indicating the trade volume and the opening, high, low, and closing prices for each stock traded that day. This summary data is collected to produce historical data, such as a table of all daily volumes and opening, high, low, and closing prices for all stocks traded for the year 1997. For example, Figure 2-1 shows how data related to securities on a stock exchange is generated.

Figure 2-1 Data Generation in Equities Markets


In Figure 2-1, each trade on the stock exchange includes several items of information, including a ticker and a price (for example, stock XYZ at 37.50). The daily summary data includes the opening, high, low, and closing prices for each ticker (for example, for XYZ: 37.75, 38.25, 37.00, 37.625). The daily data for each ticker is appended to the historical data for the ticker. The daily data is used for such purposes as quote server applications and listing in the next day's newspapers; the historical data is used by such applications as price and volume charting and technical analysis.

The data-collection model for historical data has the following characteristics:

This historical data is modeled using multiple regular time series.

Oracle8i Time Series and the Oracle8i utilities, with their bulk-loading capabilities and transactional semantics, are well suited for the requirements of time series data generation.

2.1.3 Historical Data

Oracle8i Time Series is especially useful in dealing with historical data. This type of data typically has relatively simple metadata but massive data storage requirements. That is, the data attributes (columns) are relatively few and easy to understand (such as ticker, volume, and opening, high, low, and closing prices); however, the number of rows is enormous (for example, data for all listed stocks for all trading days for several years). Moreover, the number of functions that users might want to perform on the data is large: for example, finding various sums, counts, maximum and minimum values, averages, number of trading days between two dates, moving average, and so on.

Figure 2-2 shows an example of historical data stored in a database.

Figure 2-2 Historical Data for Stocks

Ticker  Tstamp  Open  High  Low  Close  Volume 

XYZ  

01-02-1997  

21.75  

22.75  

21.50  

22.00  

352,000  

XYZ  

01-03-1997  

22.125  

22.50  

21.00  

21.75  

530,000  

XYZ  

01-06-1997  

21.625  

22.00  

21.625  

21.875  

490,000  

...  

...  

...  

...  

...  

...  

...  

YZA  

01-02-1997  

44.25  

44.25  

43.50  

43.875  

125,000  

YZA  

01-03-1997  

43.75  

44.25  

43.75  

44.125  

97,000  

YZA  

01-06-1997  

44.25  

44.50  

44.125  

44.125  

107,000  

...  

...  

...  

...  

...  

...  

...  

Stock market historical databases have the following general characteristics:

This kind of financial historical data is used in examples in this guide and in the usage demo (see Section 1.6) provided with Oracle8i Time Series.

2.2 Calendars

An Oracle8i Time Series calendar maps human-meaningful time values to underlying machine representations of time.

A calendar can be associated with a time series. However, a calendar does not need to be associated with a time series unless you need to do any of the following:

A time series with an associated calendar is called a regular time series, as described in Section 2.1.1.

A calendar is, of course, necessary for using any of the calendar functions (described in Section 2.9).

A business day calendar, for example, can define the days of the week on which stocks are traded. The holidays, when trading does not occur, are also included in the calendar as exceptions.

If you have more than one time series with the same timestamps, you can associate these time series with the same calendar (that is, use a shared calendar). For example, a calendar of U.S. stock market trading days could be used for all stocks that trade every day. For any stocks that do not fit this pattern, you could create private calendars (for example, an ACME_cal calendar for stock ACME).

The following are key components of a calendar:

2.2.1 Frequency

Each frequency has an associated integer code that is used in function calls. Table 2-1 lists the supported frequencies and their integer codes. The frequencies are explained in Table 2-2.

Table 2-1 Frequency Codes
Frequency  Integer Code 
second   1  
minute   2  
hour   3  
day   4  
week   5  
month   6  
quarter   7  
year   8  
10-day   10  
semi-monthly   16  
semi-annual   18  

Some frequencies allow flexibility in defining pattern anchor dates, whereas other frequencies are more restrictive. Table 2-2 explains the frequencies and any requirements and options relating to the pattern anchor date.

Table 2-2 Frequencies and Their Requirements
Frequency  Explanation and Requirements 
second   Every second. The anchor date can be any timestamp with a valid value for seconds.  
minute   Every minute. The anchor date can be any timestamp with a valid value for minutes. The value for seconds should be zero.  
hour   Every hour. The anchor date can be any timestamp with a valid value for hours. The values for minutes and seconds should be zero.  
day   Every day. The anchor date can be any timestamp with a valid value for the day. The values for hours, minutes, and seconds should be zero  
week   Once every 7 days. Can start on any day of the week. For example, defining a weekly calendar with an anchor date of 23-Jun-1998 means that each timestamp must be for a Tuesday.  
month   Once every month. Can start on days 1-28 or 31. (Defining an anchor date of the 31st of a month means the last day of each month.) For example, defining a monthly calendar with an anchor date of 01-Jul-1998 means that each timestamp must be for the 1st of a month.  
quarter   Four times per year. Can start on days 1-28 or 31 of any month. (Defining an anchor date of the 31st of a month means the last day of each month.) For example, defining a quarterly calendar with an anchor date of 01-Jan-1998 means that each timestamp must be for the 1st of January, April, July, or October. Defining a quarterly calendar with an anchor date of 15-Feb-1998 means that each timestamp must be for the 15th of February, May, August, or November.  
year   Once per year. Can start on days 1-28 or 31 of any month. (Defining an anchor date of the 31st of a month means the last day of that month.) For example, defining an annual calendar with an anchor date of 01-Jan-1998 means that each timestamp must be for the 1st of January. Defining an annual calendar with an anchor date of 15-Feb-1998 means that each timestamp must be for the 15th of February.  
10-day   The 1st, 11th, and 21st days of each month. (Used for automobile sales data.) No other dates are permitted for a 10-day calendar, and any anchor date is ignored.  
semi-monthly   The 1st and 16th days of each month. No other dates are permitted for a semi-monthly calendar, and any anchor date is ignored.  
semi-annual   Twice per year. Can start on days 1-28 or 31 of any month. (Defining an anchor date of the 31st of a month means the last day of each month.) For example, defining a semi-annual calendar with an anchor date of 01-Jan-1998 means that each timestamp must be for the 1st of January or July. Defining a semi-annual calendar with an anchor date of 15-Feb-1998 means that each timestamp must be for the 15th of February or August.  

2.2.2 Precision

Each frequency has an associated precision. Oracle8i Time Series functions require that input timestamps be of the precision of the frequency associated with the calendar. (The SetPrecision function is the exception: this function takes a calendar and a timestamp and returns a timestamp that conforms to the frequency of the associated calendar.)

A timestamp that is not consistent with the frequency is said to be imprecise. For example, a timestamp of 09-Sep-1997 is imprecise if it is input to a function that is dealing with a calendar whose frequency is 6 (month) or 8 (year) and whose pattern anchor date is not the 9th of a month. When you create a calendar, all timestamps used in the calendar definition (the anchor date for the pattern, and all off- and on-exceptions) must be precise with respect to the frequency. For example, the calendar will not be valid if you specify a frequency of day, an anchor date of 01-Jun-1998 13:00:00. An anchor date of just 01-Jun-1999, however, would valid in this case. (The calendar data types and their attributes are presented in Section 2.3.1.)

Table 2-3 shows the frequencies, their precision conventions, and an example timestamp of each precision using a pattern anchor date of 01-Jan-1998 00:00:00 (midnight), which was a Thursday.

Table 2-3 Precisions Using 01-Jan-1998 00:00:00 Anchor Date
Frequency  Precision Convention  Example Result 
second   MM-DD-YYYY HH24:MI:SS   09-09-1997 09:09:09  
minute   MM-DD-YYYY HH24:MI:00   09-09-1997 09:09:00  
hour   MM-DD-YYYY HH24:00:00   09-09-1997 09:00:00  
day   MM-DD-YYYY 00:00:00 (midnight)   09-09-1997 00:00:00  
week   MM-DD-YYYY 00:00:00 (midnight of the preceding Thursday)   09-04-1997 00:00:00  
month   MM-01-YYYY 00:00:00 (midnight of first day of month)   09-01-1997 00:00:00  
quarter   MM-01-YYYY 00:00:00 (midnight of first day of quarter)   07-01-1997 00:00:00  
year   01-01-YYYY 00:00:00 (midnight of first day of year)   01-01-1997 00:00:00  
10-day   MM-DD-YYYY 00:00:00 (midnight of 1st, 11th, or 21st of month)   09-01-1997 00:00:00  
semi-monthly   MM-DD-YYYY 00:00:00 (midnight of 1st or 15th of month   09-01-1997 00:00:00  
semi-annual   MM-01-YYYY 00:00:00 (midnight of first day of half year)   07-01-1997 00:00:00  

2.2.3 Pattern

A calendar pattern is specified as one or more zeroes (0) and/or positive integers.

For patterns represented by zeroes and/or ones, each '1' represents a valid timestamp of the frequency and each '0' represents an invalid timestamp. For example:

For patterns containing one or more integers greater than 1, each such integer represents an interval that is a multiple of the frequency. For example, a calendar with a day frequency, a Sunday anchor date, and a pattern of '7' (ORDSYS.ORDTPatternBits(7)) is in effect a weekly calendar where all Sunday timestamps are included and all other days of the week are excluded.

Note that while the actual timestamps that are valid for the calendar will be identical for each of the preceding weekly calendar examples (ORDSYS.ORDTPatternBits(1,0,0,0,0,0,0) and ORDSYS.ORDTPatternBits(7) with day frequency and Sunday anchor dates), these calendars have two different interpretations for use in the context of time scaling. For example, if the ScaleupSum function is invoked on a time series containing data defined for every day for scaling to these two calendars, the following differences in behavior occur:

2.2.4 Overview of Calendar Definition

To define a calendar, you create a table in which to store calendar definitions and then store a row for each calendar to be defined.

Example 2-1 creates a table named stockdemo_calendars and defines a calendar named BusinessDays. The BusinessDays calendar includes Mondays through Fridays, but excludes 28-Nov-1996 and 25-Dec-1996. Explanatory notes follow the example. (For more information and examples of calendar creation, see Section 3.2.)

Example 2-1 Overview of Calendar Definition

CREATE TABLE stockdemo_calendars of ORDSYS.ORDTCalendar (
    name CONSTRAINT calkey PRIMARY KEY); 
 
INSERT INTO stockdemo_calendars VALUES( 
  ORDSYS.ORDTCalendar( 
         0 
         'BusinessDays', 
         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() 
         ));

Notes on Example 2-1:

The stockdemo_calendars table has rows of data type ORDTCalendar, which is described in Section 2.3.1.

0 indicates that this is a standard calendar (the only type of calendar currently supported).

BusinessDays is the name of this calendar.

4 is the frequency code for day.

The calendar's pattern consists of an excluded occurrence followed by five included occurrences followed by an excluded occurrence (0,1,1,1,1,1,0). Because the frequency is day and because the anchor date (01-Jan-1995) is a Sunday, Sundays are excluded, Mondays through Fridays are included, and Saturdays are excluded.

The calendar begins at the start of 01-Jan-1990 and ends at the start of 01-Jan-2001.


Note:

minDate and maxDate can each be null. If minDate is null, the calendar has no lower boundary date; if maxDate is null, the calendar has no upper boundary date. Specifying a null minDate and maxDate simplifies calendar maintenance, but means you cannot have timestamps validated against the calendar's desired date range.  


28-Nov-1996 and 25-Dec-1996 are off-exceptions (that is, excluded from the calendar).


Note:

All exceptions (off- and on-) must be specified in ascending sorted order.  


ORDSYS.ORDTExceptions( ) indicates that there are no on-exceptions (that is, no Saturday or Sunday dates to be included in the calendar).

2.2.5 Deriving Calendar Exceptions from Time Series Data

When you want to create calendars that conform to time series data, you can use the DeriveExceptions function to simplify the process. You can use one of several approaches with DeriveExceptions, depending on your needs and the requirements for each approach:

While Approaches 1 and 1A can be performed in a single step, Approach 2 requires an additional step (before DeriveExceptions is called) in order to construct the first time series.

Although Approaches 1 and 1A are simpler in practice, Approach 2 has significant performance advantages when you need to define multiple calendars that have the same frequency and pattern but different exception lists. The first two approaches are less efficient than Approach 2 in this case, because the internal implementation of the first two approaches generates a collection of dates based on the input calendar. If you need to derive exceptions for multiple calendars defined on the same frequency and pattern, this date-generation operation is performed multiple times. You can avoid these multiple date-generation operations by using Approach 2.

Section 3.8 contains more detailed information about using each approach to deriving calendar exceptions.

2.3 Data Types

Oracle8i Time Series provides data types for working with calendars and time series.

All Oracle8i Time Series data types are installed under the ORDSYS schema, and all users must include the ORDSYS schema name when referring to these data types.


Note:

The CREATE TYPE statements in this section do not include the TIMESTAMP and OID keywords that are part of the object type definitions when the product is installed. These keywords are used internally by products for version control.  


2.3.1 Calendar Data Types

Oracle8i Time Series provides the following calendar data types. (Time series data types are described in Section 2.3.2.)

2.3.2 Time Series Data Types

Oracle8i Time Series provides the following time series data types. (Calendar data types are described in Section 2.3.1.)

CREATE TYPE ORDSYS.ORDTNumCell AS OBJECT 
   (tstamp DATE, value NUMBER);

CREATE TYPE ORDSYS.ORDTNumTab AS TABLE OF 
   ORDSYS.ORDTNumCell;

CREATE TYPE ORDSYS.ORDTNumSeries AS OBJECT
   (
   name         VARCHAR2(256),
   cal          ORDSYS.ORDTCalendar,
   series       ORDSYS.ORDTNumTab
   );

CREATE TYPE ORDSYS.ORDTNumSeriesIOTRef AS OBJECT
   (
   name                VARCHAR2(256),
   cal                 REF ORDSYS.ORDTCalendar,
   table_name          VARCHAR2(256),
   tstamp_colname      VARCHAR2(30),
   value_colname       VARCHAR2(30),
   qualifier_colname   VARCHAR2(30),
   qualifier_value     VARCHAR2(4000)
   );

CREATE TYPE ORDSYS.ORDTVarchar2Cell AS OBJECT
   (tstamp DATE, value VARCHAR2(4000));
  
CREATE TYPE ORDSYS.ORDTVarchar2Tab AS TABLE OF 
   ORDSYS.ORDTVarchar2Cell;

CREATE TYPE ORDSYS.ORDTVarchar2Series AS OBJECT
   (
   name         VARCHAR2(256),
   cal          ORDSYS.ORDTCalendar,
   series       ORDSYS.ORDTVarchar2Tab
   );

CREATE TYPE ORDSYS.ORDTVarchar2SeriesIOTRef AS OBJECT
   (
   name                VARCHAR2(256),
   cal                 REF ORDSYS.ORDTCalendar,
   table_name          VARCHAR2(256),
   tstamp_colname      VARCHAR2(30),
   value_colname       VARCHAR2(30),
   qualifier_colname   VARCHAR2(30),
   qualifier_value     VARCHAR2(4000)
   );

CREATE TYPE ORDSYS.ORDTDateTab AS TABLE OF DATE;

The preceding statements show the definition of a numeric time series and a character time series (instance-based and reference-based interfaces), each composed of a calendar instance and a collection. The collection (ORDTxxxTab) is defined as a table of ORDTxxxCell (except for ORDTDateTab, which is a table of DATE). Oracle8i Time Series data types, such as ORDTNumSeries and ORDTVarchar2Series, are input and output parameters of time series functions.

The following statements show the definitions for the ORDTDateRange and ORDTDateRangeTab types. The latter is returned by the GenDateRangeTab function, which is described in Chapter 4.

CREATE TYPE ORDSYS.ORDTDateRange AS OBJECT 
   (start_date DATE, end_date DATE);

CREATE TYPE ORDSYS.ORDTDateRangeTabTab AS TABLE OF 
   ORDSYS.ORDTDateRange;

2.4 Conventions and Semantics

For time series functions that accept two time series, both time series must be defined on calendars that have the same frequency and the same pattern. The calendars may have different exceptions lists and different starting and ending dates.

2.4.1 Semantics of Null Operands

A number of time series functions perform arithmetic, comparison, and grouping operations. When nulls are encountered in this context, the default behavior is to mirror SQL:

Some functions allow alternate semantics in the form of an option. For example, the scaleup functions allow you to specify IgnoreNulls or IgnoreNullsOFF, as explained in Section 2.11.2. The reference information for each function describes any alternate semantics options.

2.4.2 Semantics of Off-Exception Operands

In comparisons of two time series, it is possible that a timestamp valid for one time series is not valid for the other time series. Operations on two time series having similar calendars return a time series that is defined over a new calendar. This new calendar is derived from the two input calendars, using all of the following:

For example, assume the following two calendars:

The new (derived) calendar is: 01-Feb-1997 through 01-Dec-1997, daily pattern '0,1,1,1,1,1,0' (Monday through Friday), off-exceptions 01-May-1997 and 14-Jul-1997, on-exception 29-Jun-1997.

2.5 Oracle8i Time Series Architecture

Figure 2-3 shows the Oracle8i Time Series architecture. At the lowest level, a storage option is required, and this must be a flat index-organized table (IOT), a flat table, or a nested IOT. The actual product consists of PL/SQL packages for calendar, time series, and time scaling functions and for administrative tools procedures. In addition, a collection-based interface between time series storage and the packaged functions is provided.

Figure 2-3 Time Series Architecture


The rest of this chapter describes this architecture, working from bottom to top in Figure 2-3:

2.6 Storage of Time Series Data

Using Oracle8i Time Series involves storing three different kinds of information:

In the flat table or IOT storage model, these requirements are implemented using three separate tables, and time series detail data is stored as multiple rows in the table or IOT. (Figure 1-1 in Section 1.6.2 shows the tables created by the usage demo.)

In the nested table storage model, the time series detail data is stored as an object in a nested table, that is, as rows at the second level of a nested IOT.

In the time series detail table, the data should be stored in timestamp order, because many of the analytical functions require access to the data in this order. (If timestamps are not in order, the functions perform an internal sort before processing the timestamps.)

2.6.1 Flat IOT or Flat Table Storage

A time series is stored as multiple rows in a flat index-organized table (IOT) or a flat table.1 Each row stores a ticker, a timestamp, and composite data. This storage option is shown in Figure 2-2.

The flat IOT or table model has some benefits compared to nested IOTs:

However, using this model means that ensuring time series and calendar integrity cannot be encapsulated. The highest-integrity solution to time series and calendar integrity would disable insert, update, and delete operations using SQL and would implement these operations using member methods of time series and time scaling functions. This approach is not possible with a flat IOT storage model. Instead, a relational view must be defined to ensure integrity (see Section 2.8.2).

To minimize the burden of creating the relational view and other required schema objects, you can use the administrative tools procedures described in Section 2.12.

2.6.2 Nested IOT Storage (Object Model)

A time series is stored as rows in a nested IOT. At the first nesting level, the ticker symbol and any metadata associated with the time series are stored. At the second level and associated with each ticker symbol, timestamp and composite data is stored.

The nested IOT storage model has the following advantages compared to the flat IOT or table model:

However, the nested IOT storage model has the following disadvantage compared to the flat IOT model: each attribute column requires a separate timestamp column. This increases the storage overhead for multiple-attribute time series data (such as daily stock market data that includes open, high, low, close, and volume attributes). However, many forms of time series data are single-attribute (such as the monthly unemployment rate), and for these formats the nested IOT storage model is ideal.

2.7 Interfaces to Time Series and Time Scaling Functions

The interfaces to the time series and time scaling functions rely on the following aspects of the Oracle8i Time Series architecture:

Two basic interfaces to time series and time scaling functions are defined:

The data types related to the instance-based and reference-based interfaces (for example, ORDTNumSeries and ORDTNumSeriesIOTRef) are discussed in Sections 2.7.1 and 2.7.2.

Note that both types of interfaces return only instances of time series (for example, ORDTNumSeries). Also, because nesting of time series and time scaling functions is allowed (for example, SELECT (Lead(Mavg, ...) ...)), the instance-based interface is used internally for the second and subsequent levels of nesting.

When possible, you should use the reference-based interface. Although this interface may be difficult to understand initially, it offers significant performance advantages over the instance-based interface. The examples in this guide emphasize the reference-based interface.

2.7.1 Instance-Based Interface

Time series and time scaling functions operate on instances of time series objects (for example, an ORDTNumSeries). An instance of a time series object includes a name field, an instance of a calendar, and an instance of a time series. For example, as the following data type definitions for a numeric time series show, ORDTNumTab defines a collection and ORDTNumSeries combines a calendar instance with a collection:

CREATE TYPE ORDSYS.ORDTNumCell AS OBJECT (tstamp DATE, value NUMBER);
CREATE TYPE ORDSYS.ORDTNumTab AS TABLE OF ORDTNumCell;
CREATE TYPE ORDSYS.ORDTNumSeries AS OBJECT (
     name VARCHAR2(256),  
     cal ORDTCalendar,  
     series ORDTNumTab 
);

For a numeric time series, the time series data is contained in the ORDTNumTab structure. This structure is a table (collection) of a DATE column and a NUMBER column.

Figure 2-4 shows an example of an ORDTNumTab collection type.

Figure 2-4 Example of ORDTNumTab Collection Type

Tstamp   Value  

01-01-1996  

22.00  

01-02-1996  

23.00  

...  

...  

12-31-1996  

...  

Functions such as Mavg (Moving Average, described in Section 2.10.7) use the ORDTNumTab structure as the source data for performing computations, and they use the ORDTCalendar data type to enable navigation through the time series data. The calendar-based navigation is especially useful for functions such as Mavg, which has as input parameters the starting date (startDate) and ending date (endDate) for which to return moving averages and an integer (k) indicating the lookback window (k denoting the number of timestamps, including the current one, over which to compute the moving average). Calendar-based navigation is used to determine the date that is k-1 timestamps previous to startDate.

Although time series and time scaling functions operate on time series instances, they are invoked from SQL using a REF to a time series. For a numeric time series, this type is an ORDTNumSeriesIOTRef. (Section 2.7.2 explains the use of REFs in the reference-based interface.) The REF contains enough information so that time series and time scaling functions can derive the instance (ORDTNumSeries) at runtime (using dynamic SQL).

The convention of defining an interface on a DATE column and a single NUMBER column provides a uniform interface for time series and time scaling functions. Because the underlying IOT that stores time series detail data may have multiple NUMBER columns, the view defining the REF also maps the underlying storage to conform to the two-column interface defined by the ORDTNumSeries data type.

The following are the key aspects of the instance-based interface to time series and time scaling functions:

2.7.2 Reference-Based Interface

Oracle8i Time Series provides a reference-based interface for time series and time scaling functions.

This interface provides efficient performance, especially when only a portion of the time series is accessed. The performance benefit of this interface results from the fact that at runtime, the reference-based interface materializes only those rows within the specified date range, as opposed to materializing the entire collection of rows from the time series.


Note:

You should use administrative tools procedures (documented in Chapter 7) to create and maintain objects for use with the reference-based interface. Normally, you should not manually create any views used by the reference-based interface, because the specific view definitions may change in future releases and because the administrative tools procedures provide a simple interface.  


The reference-based interface uses the ORDTNumSeriesIOTRef and ORDTVarchar2SeriesIOTRef data types, which include a REF to a calendar, plus several literal values. At runtime, reference-based time series and time scaling functions use these literal values to form and execute a SQL statement (using dynamic SQL) that derives an instance of a time series that contains only the timestamps needed for this instance. The function determines which timestamps are needed based on the startDate and endDate parameters.

The ORDTNumSeriesIOTRef data type is defined as follows:

CREATE TYPE ORDSYS.ORDTNumSeriesIOTRef AS OBJECT 
   ( 
   name                VARCHAR2(256), 
   cal                 REF ORDSYS.ORDTCalendar, 
   table_name          VARCHAR2(256), 
   tstamp_colname      VARCHAR2(30), 
   value_colname       VARCHAR2(30), 
   qualifier_colname   VARCHAR2(30), 
   qualifier_value     VARCHAR2(4000) 
   ); 
 

The attributes of the ORDTNumSeriesIOTRef data type are as follows:

In the Oracle8i Time Series usage demo, the view stockdemo_ts uses the reference-based interface to time series and time scaling functions. The stockdemo_ts view determines which calendar should be associated with the time series by accessing the calendar (stockdemo_calendars) and metadata (stockdemo_metadata) tables. The pricing data is accessed through the underlying table containing historical time series pricing data (stockdemo). For an explanation of the relationship between the reference-based view and the underlying tables in the usage demo, see Section 1.6.2.3.

The stockdemo_ts view is generated by the administrative tools procedures (documented in Chapter 7) with the following definition for the current release of Oracle8i Time Series. (This definition may change in future releases, and therefore you are encouraged to use the administrative tools procedures rather than manually creating such a view.)

CREATE OR REPLACE VIEW stockdemo_ts(ticker,open,high,low,close,volume) AS 
  SELECT meta.tickername, 
  ORDSYS.ORDTNumSeriesIOTRef( 
       substr(meta.tickername, 1, 230) || ' open NumSeries', 
              Ref(cal), 'tsdev.stockdemo', 
              'tstamp', 'open', 'ticker', meta.tickername), 
  ORDSYS.ORDTNumSeriesIOTRef( 
       substr(meta.tickername, 1, 230) || ' high NumSeries', 
              Ref(cal), 'tsdev.stockdemo', 
              'tstamp', 'high', 'ticker', meta.tickername), 
  ORDSYS.ORDTNumSeriesIOTRef( 
       substr(meta.tickername, 1, 230) || ' low NumSeries', 
               Ref(cal), 'tsdev.stockdemo', 
               'tstamp', 'low', 'ticker', meta.tickername), 
  ORDSYS.ORDTNumSeriesIOTRef( 
       substr(meta.tickername, 1, 230) || ' close NumSeries', 
               Ref(cal), 'tsdev.stockdemo', 
               'tstamp', 'close', 'ticker', meta.tickername), 
  ORDSYS.ORDTNumSeriesIOTRef( 
       substr(meta.tickername, 1, 230) || ' volume NumSeries',  
               Ref(cal), 'tsdev.stockdemo', 
               'tstamp', 'volume', 'ticker', meta.tickername) 
  FROM stockdemo_metadata meta, stockdemo_calendars cal 
  WHERE meta.calendarname = cal.name;

Depending on which column is selected, a different literal value is applied as an attribute of the ORDTNumSeriesIOTRef data type. For example, for the following query:

SELECT ORDSYS.TimeSeries.Mavg(close,
                              to_date('02-DEC-96','DD-MON-YY'),
                              to_date('31-DEC-96','DD-MON-YY'),
                              10) 
FROM TSDEV.stockdemo_ts 
WHERE ticker='ACME';

The literal value close is used as the value_colname column name. The other attributes of the ORDTNumSeriesIOTRef data type include the timestamp column name (tstamp), a qualifying column name (ticker), and the actual value of the qualifying column (meta.tickername).

The implementation of time series and time scaling functions uses the information stored in the ORDTNumSeriesIOTRef data type to generate the appropriate dynamic SQL statement at runtime. Using the preceding example, to instantiate a time series object (that is, to convert an ORDTNumSeriesIOTRef to an ORDTNumSeries), the Mavg function generates a query that performs the following action (with the logic shown, not the exact syntax):

SELECT tstamp, close 
FROM tsdev.stockdemo_ts 
WHERE ticker='ACME' and tstamp BETWEEN <a date range adjusted
                    to reflect the 10-day window and the
                    calendar, including any holidays>;
                   

The Mavg function computes the moving average and returns the result as a time series instance (ORDTNumSeries). For more information about the Mavg function, see Section 2.10.7.

2.8 Consistency of Time Series Data

Most time series and time scaling functions rely on calendars that are consistent with time series data.2 By assuming a time series is consistent with its calendar, time series and time scaling functions can use the calendar as a basis for navigation of time series data.

Time series consistency must be maintained; otherwise, functions might raise exceptions or return incorrect results.

2.8.1 Rules for Time Series Consistency

For a time series to be consistent, the following must be true:

If some mechanism is not used to enforce these consistency rules, accidental or malicious actions could destroy the integrity of the time series data. For example, a user might delete rows from the middle of the time series, rather than being restricted to deleting rows at the beginning and the end of the date range for the time series.

2.8.2 Enforcing Time Series Consistency with Relational Views

Enforcing time series consistency can be accomplished with a relational view of time series data that uses an INSTEAD OF trigger to maintain time series consistency. (For an explanation of INSTEAD OF triggers, see the Oracle8i Concepts manual.) This relational view is intended to be used for limited or moderate insert, update, and delete operations; it is not intended for bulk changes to time series data.

The usage demo (see Section 1.6) includes a relational view named stockdemo_sv. This view:

2.8.2.1 Precision

With the relational view, if a timestamp to be inserted is imprecise, an exception is raised. If a timestamp to be deleted is imprecise and if a matching timestamp exists in the time series, the deletion is permitted.

2.8.2.2 INSTEAD OF Trigger

An INSTEAD OF trigger in a relational view enforces rules on insert, delete, and update operations. These rules maintain time series data that conforms to the associated calendar.

For insert operations, the following rules apply:

For delete operations, the following rules apply:

For update operations, the following rules apply:

The INSTEAD OF trigger in a relational view enables you to ensure that a time series meets the consistency requirements described in Section 2.8.1.

The INSTEAD OF trigger allows for multiple timestamps to be inserted or deleted in a single query, given that the group of timestamps inserted or deleted are in the proper order. For example, a specified number of timestamps can be deleted from the beginning of a time series by using a simple range restriction on the timestamp. A specified number of timestamps can be inserted at the end of a time series by using a subquery that references another table containing time series data.

2.8.3 Bulk Loading and Consistency

The SQL*Loader utility is useful for loading large amounts of data into a table. For better performance, you should perform bulk loads on underlying tables instead of on relational views. However, after you load data into the tables, you must ensure time series consistency by using one of the following approaches:

Section 3.4 contains additional information and examples of bulk and incremental loading of time series data.

2.9 Calendar Functions

Oracle8i Time Series provides calendar functions for querying and modifying calendars. The calendar functions can be divided into the following categories:

Reference information for all calendar functions is in Chapter 4.

2.9.1 End-User Functions

End-user functions let you use the main calendar-related features of Oracle8i Time Series. If you do not need to modify or expand the product's capabilities, you probably can limit your use of calendar functions to those listed in Table 2-4.

Table 2-4 End-User Calendar Functions
Function  Description 
Calendar-Creation Functions1  
Second   Creates a calendar with a frequency of second.  
Minute   Creates a calendar with a frequency of minute.  
Hour   Creates a calendar with a frequency of hour.  
Day   Creates a calendar with a frequency of day.  
Week   Creates a calendar with a frequency of week.  
Ten_day   Creates a calendar with a frequency of 10-day.  
Semi_monthly   Creates a calendar with a frequency of semi-monthly.  
Month   Creates a calendar with a frequency of month.  
Quarter   Creates a calendar with a frequency of quarter.  
Semi_annual   Creates a calendar with a frequency of semi-annual.  
Year   Creates a calendar with a frequency of year.  
Calendar-Related Functions  
EqualCals   Returns 1 if the two calendars are equivalent. If a date range is provided, tests only equivalence between the supplied dates.  
GenDateRangeTab   Returns a table of date ranges that represent all of the valid intervals in the input calendar (or from startDate through endDate).  
IntersectCals   Intersects two calendars.  
UnionCals   Returns the union of two calendars.  
IsValidCal   Returns 1 if a calendar is valid and 0 if a calendar is not valid.  
ValidateCal   Validates a calendar; repairs errors where possible.  
Exception-Related Functions  
InsertExceptions   Inserts a list of timestamps into the appropriate exceptions list or lists.  
DeleteExceptions   Deletes a list of timestamps from the appropriate exceptions list or lists.  
1 The calendar-creation functions create a calendar of with a frequency corresponding to the function name, a pattern of '1' (all timestamps included), no lower or upper boundary dates (minDate or maxDate), no off-exceptions or on-exceptions, and a specified or default name and anchor date.

2.9.2 Product-Developer Functions

Product-developer functions let you modify and expand the Oracle8i Time Series capabilities. For example, you could use product-developer calendar functions in creating a new function that modified the information returned for the moving average or that returned a net present value for a portfolio of stocks at a specified date.


Note:

It is recommended that you not modify the functions provided with Oracle8i Time Series. If you want a function with a behavior different from an existing function, create a new function with a different name or put the function in a different package, or do both. For example, if you work for XYZ Corporation and create a modified moving average function, you could name the function MavgXYZ and put it in a package named XYZPackage.  


Table 2-5 lists the product-developer calendar functions.

Table 2-5 Product-Developer Calendar Functions
Function  Description 
Calendar-Related Functions  
CombineCals   Combines two calendars. Similar to IntersectCals, except the patterns must be identical.  
Exception-Related Functions  
NumOffExceptions   Returns the number of off-exceptions between two dates.  
NumOnExceptions   Returns the number of on-exceptions between two dates.  
Date and Index-Related Functions  
IsValidDate   Determines if a supplied date is valid.  
OffsetDate   Returns a date that is k dates in the future (or k in the past if k is negative) of the supplied date.  
GetIntervalStart   Returns the start of the interval that includes the input timestamp.  
GetIntervalEnd   Returns the end of the interval that includes the input timestamp.  
NumInvalidTstampsBetween   Returns the number of invalid timestamps between two dates.  
NumTstampsBetween   Returns the number of valid timestamps between two dates.  
TstampsBetween   Returns the valid timestamps between two dates.  
InvalidTstampsBetween   Returns the invalid timestamps between two dates.  
SetPrecision   Sets the precision of the input timestamp to correspond to the frequency of the input calendar.  

For an example of using product-developer functions, see Section 3.9.

2.10 Time Series Functions

Time series functions operate on a time series. A time series data type is always used as the input parameter to a time series function.

Reference information for all time series functions is in Chapter 5.

2.10.1 Extraction, Retrieval, and Trim Functions

Time series extraction, retrieval, and trim functions operate on any time series type. Extraction functions return one or more time series rows, while retrieval and trim functions return a time series.

Table 2-6 lists the extraction functions.

Table 2-6 Extraction Functions
Function  Description 
DeriveExceptions   Returns a calendar populated with exceptions derived from either a calendar and a table of dates or two time series.  
ExtractCal   Returns a calendar that is the same as the calendar on which the time series is based.  
ExtractDate   Gets the date from an element in a time series.  
ExtractTable   Returns the time series table (ORDTNumTab or ORDTVarchar2Tab) associated with a time series.  
ExtractValue   Gets the value stored in an element in a time series.  
First   Gets the first element in a time series.  
GetDatedElement   Gets the element of a time series at a supplied date.  
GetNthElement   Gets the Nth element of a time series.  
Last   Gets the last element in a time series.  

Table 2-7 lists the retrieval and trim functions.

Table 2-7 Retrieval and Trim Functions
Function  Description 
FirstN   Gets the first n elements in a time series.  
GetSeries   Returns the entire time series.  
LastN   Gets the last n elements in a time series.  
TrimSeries   Returns the time series data between the supplied dates.  

2.10.2 Shift Functions

Shift functions (listed in Table 2-8) lead or lag a time series by a specified number of units, where units reflects the frequency of the calendar for the time series.

Table 2-8 Shift Functions
Function  Description 
Lead   Leads a time series by the specified number of units.  
Lag   Lags a time series by the specified number of units.  

2.10.3 SQL Formatting Functions

When called from a SQL SELECT expression, a time series function returns an instance of a time series data type, which cannot be displayed. The SQL formatting functions (listed in Table 2-9) facilitate format conversions that allow time series to be displayed.

Table 2-9 SQL Formatting Functions
Function  Description 
ExtractCal   Given a time series, returns a calendar that is the same as the calendar on which the time series is based.  
ExtractDate   Given an element in a time series, returns the date.  
ExtractTable   Given a time series, returns the time series table (ORDTNumTab or ORDTVarchar2Tab) associated with the time series.  
ExtractValue   Given an element in a time series, returns the value stored in it.  

2.10.4 Aggregate Functions

Aggregate functions (listed in Table 2-10) return scalar or ORDTNumTab values. Each aggregate function can be used in either of the following ways:

Thus, each aggregate function is of the form:

f(ts ORTDNumSeries, [date1 DATE, date2 DATE])

Table 2-10 Aggregate Functions
Function  Returns 
TSAvg   Average (mean) of a time series  
TSCount   Number of elements in a time series  
TSMax   Maximum value of a time series  
TSMaxN   Specified number of top (highest) values in a time series  
TSMedian   Middle element of a time series  
TSMin   Minimum value of a times series  
TSMinN   Specified number of bottom (lowest) values in a time series  
TSProd   Product of the elements of a time series  
TSStddev   Standard deviation (square root of VAR)  
TSSum   Sum of the elements of a time series  
TSVariance   Variance (analogous to the SQL group function VAR)  

2.10.5 Arithmetic Functions

Arithmetic functions (listed in Table 2-11) accept two time series (ORDTNumSeries1,ORDTNumSeries2) or a time series and a constant (ORDTNumSeries1, Const), and perform a pairwise arithmetic operation on each element of the time series. This operation determines the value of each element of the returned time series:

Algorithm for f(ts1, ts2)
ForAll i, tsRet(i) = ts1(i) op ts2(i);

Table 2-11 Arithmetic Functions
Function  Description 
TSAdd   Time series addition  
TSDivide   Time series division  
TSMultiply   Time series multiplication  
TSSubtract   Time series subtraction  

2.10.6 Cumulative Sequence Functions

Cumulative sequence functions (listed in Table 2-12) operate on successive elements of a time series, accumulating the result into the current element of the output time series. For example, CSUM((1,2,3,4,5)) => (1,3,6,10,15). In this example, the result time series (f(i)), is computed from the input time series (I(i)) as follows:

f(1) = I(1)
ForAll i > 1, f(i) = f(i - 1) + I(i)

Table 2-12 Cumulative Sequence Functions
Function  Returns 
Cavg   Cumulative average  
Cmax   Cumulative maximum  
Cmin   Cumulative minimum  
Cprod   Cumulative product  
Csum   Cumulative sum  

2.10.7 Moving Average and Sum Functions

The Moving Average (Mavg) function returns a time series that contains the averages of values from each successive timestamp for a specified interval over a range of dates. For example, the 30-day moving average for a stock is the average of the closing price for the specified date and the 29 trading days preceding it.

The Moving Sum (Msum) function returns a sum of values from each successive timestamp for a specified interval over a range of dates. For example, the 30-day moving sum of trading volumes for a stock is the sum of the volume for the specified date and for 29 trading days preceding it.

Table 2-13 lists the moving average and sum functions.

Table 2-13 Moving Average and Sum Functions
Function  Returns 
Mavg   Moving average  
Msum   Moving sum  

The relationship between the input and output time series in the computation of a moving average or sum is illustrated in Figure 2-5. The figure focuses on the common invocation of moving average or sum, where k is the number of timestamps in the lookback window (for example, 30) and a date range (startDate and endDate) is supplied. (For more information about the parameters, see the Mavg function description in Chapter 5.)

Figure 2-5 Relationship of Input and Output Time Series in Moving Average/Sum


2.10.8 Conversion Functions

Conversion functions (see Table 2-14) convert a time series by filling in missing timestamp-value pairs. With the Fill function, any timestamps that are valid calendar timestamps but missing from the time series are inserted into the time series. This function is especially useful for converting a time series from one calendar to another.

Table 2-14 Conversion Functions
Function  Description 
Fill   Fills a time series based on the calendar and fill type.  

2.11 Time Scaling Functions

Oracle8i Time Series provides functions to scale time series data:

The relationship between the input and output time series in a scaleup operation is illustrated in Figure 2-6, which shows a mapping when scaling from a daily frequency to a monthly frequency.

Figure 2-6 Time Scaling from Daily to Monthly Frequency


Figure 2-6 shows all days in February being mapped to the month of February. This mapping also suggests the importance of the precision of timestamps of different frequencies. In the example shown in this figure:

Time scaling is permitted only when the calendar for the target time series is an integral multiple of the calendar for the data to be scaled. For example, weekly data (data associated with a calendar with a week frequency) cannot be scaled up to a calendar with a frequency of month, quarter, half year, or year because a week does not divide evenly into any of these time periods. However, monthly data can be scaled up to a calendar with a frequency of quarter, half year, or year.

Table 2-15 provides a scaling compatibility matrix that shows for each frequency the frequencies to which you can scale up and scale down data. For each cell in the matrix, a Y means that scaling is permitted and a blank means that scaling is not permitted. For scaleup operations, go down the left column to find the desired scale-from frequency, then go across that row to see if scaling is permitted for the desired scale-to frequency. For scaledown operations, go across the top row to find the desired scale-from frequency, then go down that column to see if scaling is permitted for the desired scale-to frequency.

Table 2-15 Scaling Compatibility Matrix

 

Day  

Week  

10-day  

Semi-month  

Month  

Quarter  

Semi-annual  

Annual  

Day  

Y  

Y  

Y  

Y  

Y  

Y  

Y  

Y  

Week  

 

Y  

 

 

 

 

 

 

10-day  

 

 

Y  

 

Y  

Y  

Y  

Y  

Semi-month  

 

 

 

Y  

Y  

Y  

Y  

Y  

Month  

 

 

 

 

Y  

Y  

Y  

Y  

Quarter  

 

 

 

 

 

Y  

Y  

Y  

Semi-annual  

 

 

 

 

 

 

Y  

Y  

Annual  

 

 

 

 

 

 

 

Y  

If the calendar for the target time series has a zero ('0') pattern and one or more on-exceptions that are precise with respect to the calendar frequency, the time scaling functions use the on-exceptions to perform scaling. For example, if quarterly dividend payment dates are defined as on-exceptions with a day frequency calendar that has a zero pattern (ORDSYS.ORDTPatternBits(0)), the ScaledownRepeat function could be used to insert the current quarterly dividend rate in each daily timestamp.

The collection-based interface (operations on collections) for time scaling is discussed in Section 2.11.1.

Reference information for all time scaling functions is in Chapter 6.

2.11.1 Time Scaling on Collections

The scaleup and scaledown functions accept as input a numeric time series and a destination calendar. A numeric time series is returned, which is scaled based on the destination calendar.

For example, the following statement returns the last closing prices for stock SAMCO for the months of October, November, and December of 1996:

select * from the
  (select cast(ORDSYS.TimeSeries.ExtractTable(
                 ORDSYS.TimeSeries.ScaleupLast(
                        ts.close,
                        sc.calendar,
                        to_date('01-OCT-1996','DD-MON-YYYY'),
                        to_date('01-JAN-1997','DD-MON-YYYY')
                        )
              ) as ORDSYS.ORDTNumTab)
       from tsdev.stockdemo_ts ts, tsdev.scale sc
       where ts.ticker='SAMCO' and
             sc.name  ='MONTHLY');

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
01-OCT-96     42.375
01-NOV-96      38.25
01-DEC-96      39.75
3 rows selected.

Note that each timestamp reflects the first date of the month in the calendar (following the convention illustrated in Table 2-3), and each value in this case reflects the closing price on the last date for that month in the calendar.

Scaleup functions ignore nulls. For example, ScaleupAvg returns a time series reflecting the average value of each scaled group of non-null values.

Table 2-16 lists the scaleup functions, and Table 2-17 lists the scaledown functions.

Table 2-16 Scaleup Functions for Collections
Function  Description 
ScaleupAvg   Returns the average value of each group.  
ScaleupAvgX   Returns the average value of the sum of each group and the immediately preceding source period (for example, a first-quarter average computed as the average for the months of December, January, February, and March).  
ScaleupCount   Returns the count of timestamps in each group.  
ScaleupGMean   Returns the geometric mean of each group.  
ScaleupSum   Returns the sum of each group.  
ScaleupSumAnnual   Returns the sum of each group multiplied by a factor to state the resulting time series at annual rates.  
ScaleupMin   Returns the minimum of each group.  
ScaleupMax   Returns the maximum of each group.  
ScaleupFirst   Returns the first value of each group.  
ScaleupLast   Returns the last value of each group.  
Table 2-17 Scaledown Functions for Collections
Function  Description 
ScaledownInterpolate   Returns missing values by interpolating between the values of the input time series.  
ScaledownRepeat   Returns missing values by repeating the value of the input time series.  
ScaledownSplit   Returns missing values by splitting (dividing) the value in the input time series evenly.  

2.11.2 Scaleup Options: IgnoreNulls and DiscardError

All scaleup functions allow you to specify either or both of the following options:

These options do not apply to scaledown functions.

IgnoreNulls controls the behavior with respect to null values. If IgnoreNulls is in effect, nulls in the input time series are not be included in the aggregation being performed. For example, if ScaleupAvg is operating on a group with 12 values, 3 of which are null, only the 9 non-null values are averaged. If IgnoreNullsOFF is specified, then any calculation involving one or more nulls results in a null. For example, if IgnoreNullsOFF is specified and ScaleupAvg is operating on a group with 12 values, 3 of which are null, a null is returned.

DiscardError controls the behavior with respect to gaps in the target calendar. If DiscardError (the default setting) is in effect, then whenever data from the source time series has no corresponding interval in the target time series (which can result from zeros in the pattern bits of the target calendar), an exception is raised. An example of this condition is scaling up daily data to a monthly calendar for the months January through March (anchor date of 01-Jan and pattern of '1,1,1,0,0,0,0,0,0,0,0,0'). Using this example, the default behavior (DiscardError) raises an exception if any input timestamps are from April through December; however, DiscardErrorOFF performs the scaling for January through March and ignores any input timestamps from April through December. The default behavior ensures that certain types of incompatible calendars are not inadvertently used in scaling.

2.11.2.1 Syntax Options: Names and Numbers

You can express the IgnoreNulls and DiscardError options syntactically using either names or a number. Using names, you can specify one or both of the following as the final parameter or parameters of a scaleup function call:

Instead of using names, you can use a one-digit or two-digit number from Table 2-18:

Table 2-18 IgnoreNulls and DiscardError Syntax Options

IgnoreNulls  

 

 

ON  

OFF  

DiscardError

 

ON  

0  

1  

OFF  

10  

11  

The following examples show the use of names and a number to specify the same options (IgnoreNullsOFF and DiscardErrorOFF):

ORDSYS.TimeScale.ScaleUpAvg('My Timeseries', myTS, targetCal,
                         ORDSYS.TimeScale.IgnoreNullsOFF,
                         ORDSYS.TimeScale.DiscardErrorOFF);
ORDSYS.TimeScale.ScaleUpAvg('My Timeseries', myTS, targetCal,
                         11);

Names and numbers for the options cannot be used in the same function call. For example, the following is not valid:

ORDSYS.TimeScale.ScaleUpAvg('My Timeseries', myTS, targetCal,
                         1, ORDSYS.TimeScale.DiscardErrorOFF);

An exception is raised if an option is specified twice or if conflicting options are specified (for example, specifying IgnoreNulls and IgnoreNullsOff in the same call). These options can be specified in any order, but they must appear after any other parameters to the function.

2.12 Administrative Tools Procedures

Oracle8i Time Series provides procedures that simplify the creation of time series schema objects. The quick-start demo (described in Section 1.6.1) illustrates the use of several of these administrative tools procedures.

Table 2-19 lists the administrative tools procedures. Reference information for these procedures is in Chapter 7.

Table 2-19 Administrative Tools Procedures
Procedure  Description 

Add_Existing_Column  

Adds a column attribute from an existing flat table to a time series.  

Add_Integer_Column  

Adds an integer column attribute to an ongoing flat time series creation specification.  

Add_Number_Column  

Adds a number column attribute to an ongoing flat time series creation specification.  

Add_Varchar2_Column  

Adds a VARCHAR2 column attribute to an ongoing flat time series creation specification.  

Begin_Create_TS_Group  

Initiates the context for creating a time series group (the schema objects for a time series).  

Cancel_Create_TS_Group  

Cancels the creation of a time series group, that is, cancels the context initiated by the Begin_Create_TS_Group procedure.  

Close_Log  

Closes the log file that had been opened by the Open_Log procedure.  

Display_Attributes  

Displays information about the time series schema being created.  

Drop_TS_Group  

Deletes the time series definition and views associated with it. However, the underlying tables (calendar tables, detail data tables, and so on) are not deleted.  

Drop_TS_Group_All  

Deletes the time series definition and all tables, views, indexes, constraints, and triggers associated with it.  

End_Create_TS_Group  

Closes the context established by the Begin_Create_TS_Group procedure and creates all appropriate schema objects.  

Get_Flat_Attributes  

Retrieves the attributes of a flat time series.  

Get_Object_Attributes  

Retrieves the attributes of an object-model time series.  

Get_Status  

Checks to see if a time series creation sequence is in progress.  

Open_Log  

Opens a log file that will contain the data definition language (DDL) statements generated by the Time Series administrative tools procedures.  

Set_Flat_Attributes  

Sets the attributes of a flat time series.  

Set_Object_Attributes  

Sets the attributes of an object-model time series.  

Trace_Off  

Disables debugging for Oracle8i Time Series administrative tools procedures. Any data definition language (DDL) statements and errors encountered when generating DDL statements will not be logged to SERVEROUTPUT.  

Trace_On  

Enables debugging for Oracle8i Time Series administrative tools procedures. Any data definition language (DDL) statements and errors encountered when generating DDL statements will be logged to SERVEROUTPUT.  

2.12.1 Role Requirement for Administrative Tools Procedures

To create, delete, and modify schema objects using the Oracle8i Time Series administrative tools procedures, you must have been granted one or more of the following roles:

For deletion of time series schema objects, the DBA and TIMESERIES_DBA roles let you delete objects that belong to any user (schema), but the TIMESERIES_DEVELOPER role lets you delete only objects that belong to the current user.

2.12.2 Other Requirements for Administrative Tools Procedures

Logging, which is controlled by the Open_Log and Close_Log procedures, relies on the PL/SQL file I/O procedure UTL_FILE, which is documented in the Oracle8i Supplied Packages Reference manual.

To use logging, one or more directories for UTL_FILE output must be defined using the UTL_FILE_DIR parameter in the Oracle initialization file. For information about the UTL_FILE_DIR parameter, see the Oracle8i Reference manual.


1 A time series can be stored in a standard table; however, for performance reasons it is recommended that you use an IOT rather than a standard table.
2 An exception is the Fill function, which can be used to add pairs of timestamps and values to make a time series consistent with the calendar.



Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index