1
Introduction

Oracle8i Time Series (in previous releases called the Oracle8 Time Series Cartridge) is an extension to Oracle8i that provides storage and retrieval of timestamped data through object types. Oracle8i Time Series is a building block for applications rather than being an end-user application in itself. It consists of data types along with related functions for managing and processing time series data.

For example, applications can use this product to process historical data derived from financial market transactions, such as trades of stocks, bonds, and mutual fund shares. In such applications, the functions included with Oracle8i Time Series let you conveniently perform operations ranging from the simple to the complex, such as:

Finding the opening, closing, low, and high prices for a stock on a specific date
Calculating monthly volumes for a stock for a specific year
Deriving the 30-day moving average for a stock over a year

Time series applications have certain distinct requirements and some degree of commonality. The time series data types accommodate the commonality and support extensions that address application-specific requirements. With Oracle8i Time Series, time series data can be managed more conveniently and efficiently than is possible using only traditional data types and user-defined functions.

You can use or adapt existing tables for time series applications, or you can create new tables. You can also extend the capabilities of Oracle8i Time Series to add or modify functions and to create customized calendars.

1.1 Oracle8i Time Series and Object-Relational Technology

The Oracle8i architecture allows clients, application-specific servers, and database servers to be extended easily and reliably. Oracle8i Time Series provides support for time series domain-specific types, functions, and interfaces. The product focuses on a set of time series data representation and access mechanisms sufficient to support many applications and the development of more specialized time series functions.

The objects option makes Oracle8i an object-relational database management system, which means that users can define additional kinds of data -- specifying both the structure of the data and the ways of operating on it -- and use these types within the relational model. This approach adds value to the data stored in a database.

Oracle8i with the objects option stores structured business data in its natural form and allows applications to retrieve it that way. For that reason, it works efficiently with applications developed using object-oriented programming techniques.

1.2 Storing and Accessing Data

Oracle8i Time Series can store time series data in the database under transactional control.

Once stored in the database, this data can be queried and retrieved by finding a row in a table that contains the primary key (which includes the timestamp) using the various alphanumeric columns (attributes) of the table. Typical queries might include the following:

Select the closing price from a stock market data table where the ticker (stock symbol) is XYZ and the date is 30-May-1997.
Select the 30-day moving average of stock XYZ for the month of May 1997.

Applications access and manipulate time series data using SQL or PL/SQL^TM. See the Oracle8i SQL Reference manual for information on SQL syntax.

1.3 Time Series Usage Models

Most Oracle8i Time Series users fit into one of a few usage models, depending on their needs. The two basic usage models are as follows:

No need for calendars: You do not need to use a calendar if the timestamps have no pattern, if the timestamps have a pattern but it does not need to be checked, or if the pattern is important but the timestamps have extraneous elements (for example, hourly timestamps created using SYSDATE, which includes the minutes and seconds).

Many time series applications, including some for financial markets and other environments with regular data, do not need to use calendars.
Need for calendars: You may need to use calendars for any of several reasons, such as to use the Lead and Lag functions (which require calendars) or to ensure the validity of insert, update, and delete operations on the timestamped data. However, depending on your needs, you may or may not need to specify certain elements in the calendar definition, such as:
- Lower and upper date boundaries for the calendar
- Exception timestamps (for example, to identify holidays)
For example, you may be interested only in the pattern of timestamps, but not in defining date boundaries for the calendar or specifying exceptions for holidays. In this case, if no data exists for a valid timestamp (for example, no price for a stock on Friday, 04-Jul-1997 because U.S. financial markets were closed that day), you can simply insert a null (that is, treat it as a valid timestamp but with a null associated data value) because you are confident that your data is accurate.

This rest of this section describes these usage models. It does not explain in detail any of the concepts mentioned; these are explained in Chapter 2. You may want to find the model that best fits your needs, follow the instructions in that section, and refer to the other sections in this document as necessary.

1.3.1 No Need for Calendars

Many Oracle8i Time Series users do not need to use calendars with their timestamped data. Situations where calendars are not needed include the following:

The timestamps have no pattern. Examples include timestamps for the opening or closing of a valve, fluctuations in electrical power demand, transactions at an automatic teller machine (ATM), and trades during the day on a financial market.
The timestamps have a pattern, but you do not need to use it. (You must also assume that all timestamps for the data are correct.) For example, you can derive a 30-day moving average for stock XYZ for 1998 without using a calendar, as long as you know that the closing price data is valid (that is, there are closing prices for all trading days and no closing prices for nontrading days).

One variation of having a pattern but no need to use it occurs when the timestamps contain extraneous elements. For example, an electric utility may want to collect hourly data on power demand use for different regions, but it is unimportant whether the timestamp is exactly on the hour or contains minutes and seconds. For example, using SYSDATE to create timestamps might result in data for 4 p.m. (16:00) being stored with a timestamp of 16:00:03, 15:59:37, or 16:01:30.

You can use all time series and time scaling functions except Lead and Lag without a calendar. You can use all time scaling functions except ScaledownRepeat and ScaledownSplit without a calendar for the input data (for example, daily trading volume for stock XYZ); however, you must have a calendar to which to scale the data (for example, a monthly calendar for deriving monthly trading volume for stock XYZ).

Note:
A time series used without an associated calendar is called an irregular time series, regardless of whether or not the timestamps are predictable. For more information about irregular time series, see Section 2.1.1.

If you do not need to use a calendar with input timestamped data, you can follow these steps to use Oracle8i Time Series:

Use the administrative tools procedures to create the time series schema objects. See the description of the quick-start demo in Section 1.6.1, and use that demo file as a model for creating your own definitions. Note that this demo also includes a calendar definition to be used for daily to monthly scaling.
Load the data. The quick-start demo provides an example using the SQL*Loader utility.
Use standard time series and time scaling functions for queries. See the quick-start demo for some examples.

1.3.2 Need for Calendars

Many Oracle8i Time Series users need to use calendars to take advantage of the full range of functions, including Lead and Lag. They also want to identify a pattern for the timestamps and to perform at least some validation of those timestamps. The extent of calendar maintenance required depends on whether they specify any of the following for each calendar:

Lower and upper date boundaries for the calendar (to let you use time series functions to ensure that all timestamps are within a defined date range)
Exception timestamps (for example, to identify holidays)

These users can also use shared calendars (described in Section 2.2) to associate multiple time series with a single calendar.

The rest of this section describes some calendar usage models involving different levels of specification and maintenance.

1.3.2.1 Minimal Calendar Maintenance

Many Oracle8i Time Series users need to use calendars with their timestamped data, but do not want or need to do substantial maintenance of calendars. They need to use calendars to use the full range of functions, including Lead and Lag, but they do not need to define beginning and ending boundary dates for calendars or to specify every holiday within the date range (including adding or changing holidays as needed). They are confident that the timestamps are correct and valid.

In this usage model, each time series has a calendar with a pattern. For example, for daily stock market data, a calendar is defined with a frequency of day and a pattern of '0,1,1,1,1,1,0' to reflect a Monday-to-Friday normal business week. However, no beginning or ending date for the calendar is specified, and no exceptions are defined for any Monday-to-Friday dates on which the markets are closed. If the data does not include a timestamp for a particular Monday-to-Friday date (for example, Friday, 04-Jul-1997), you must insert a null value for the data associated with that timestamp.

This approach allows for some validation of input data. For example, trading data with a timestamp of Saturday, 08-Aug-1998 would be invalid. However, this approach does not catch many possible kinds of input timestamp errors. For example, the following errors would not be detected:

A timestamp of 19-Aug-1997 (a Tuesday) when 19-Aug-1998 (a Wednesday) was intended, if the year was incorrectly typed and the time series is supposed to contain only 1998 data, but the calendar does not specify a starting date
Price data entered for 25-Dec-1998 (a Friday), a holiday for U.S. financial markets

If you need to use a calendar but do not need to maintain calendars to enforce input timestamp validation, you can follow these steps to use Oracle8i Time Series:

Use the administrative tools procedures to create the time series schema objects. See the description of the quick-start demo in Section 1.6.1, and use that demo file as a model for creating your own definitions. Note that this demo creates a table to hold calendars and a calendar definition for use with daily to monthly scaling.

Create one or more calendar definitions in which only the essential elements are defined (frequency and pattern). The following example creates a Monday-to-Friday calendar with no date boundaries and no exceptions (see Section 3.2 for more detailed information about calendar definition):

INSERT INTO tsquick_cal VALUES(
     ORDSYS.ORDTCalendar(
       0,                 -- Calendar type (0 = standard)
       'BUSINESSDAYS',    -- Name of this calendar
       4,                 -- 4 = frequency code for day
       ORDSYS.ORDTPattern(  -- Pattern definition (required)
          ORDSYS.ORDTPatternBits(0.1,1,1,1,1,0),
            TO_DATE('05-JAN-1998','DD-MON-YYYY')),
       NULL,              -- No lower date boundary (minDate) 
       NULL,              -- No upper date boundary (maxDate) 
       NULL, NULL)        -- No off- or on-exceptions
);

Load the data. The quick-start demo provides an example using the SQL*Loader utility.
Use standard time series and time scaling functions for queries. See the quick-start demo and the usage demo (see Section 1.6.2) for some examples.

1.3.2.2 Complete Calendar Definition and Maintenance

Some Oracle8i Time Series users need to create and maintain calendars, specifying the beginning and ending boundary dates for calendars and exceptions to the normal pattern, such as all holidays and any normally "off" days that become work days. These users may need to check the data to ensure that all timestamps are valid.

In this usage model, each time series has a calendar with a pattern, starting and ending date boundaries, and full specification of all exceptions (such as holidays). Users adopting this usage model will be able to use Oracle8i Time Series functions to determine if any timestamps in the input data are invalid. For example, the following errors would be detected:

A timestamp of 19-Aug-1997 (a Tuesday) when 19-Aug-1998 (a Wednesday) was intended, if the year was incorrectly typed and the time series is restricted to 1998 data
Price data entered for 25-Dec-1998 (a Friday), a holiday for U.S. financial markets

If you need to perform complete calendar definition and maintenance, read the information about calendars in Section 2.2 and follow the guidelines in Chapter 3.

1.4 Installing the Kit

Oracle8i Time Series installation consists of the following basic steps:

Installing the software on your computer

Use the Oracle Universal Installer to install the software.
Loading the necessary objects into the database

You can use the Oracle Database Configuration Assistant (ODCA) to automate the creation of the necessary objects. If you are not familiar with Oracle8i database creation, you are especially encouraged to use the ODCA. If you plan to create the database without using the ODCA, instructions are provided in Section 1.4.3.

Oracle8i Time Series is installed under the ORDSYS schema.

1.4.1 Required Software for Using Oracle8i Time Series

To use Oracle8i Time Series, at least the following software components must be installed: Oracle8i (RDBMS), PL/SQL (on systems on which it is a separate installation option), and Oracle8i Time Series. These components can be installed all at once, or Oracle8i Time Series can be added to an existing Oracle8i installation that includes PL/SQL.

1.4.2 After Installing Oracle8i Time Series

After installing Oracle8i Time Series, read the README.txt file for your platform, which can be found either in $ORACLE_HOME/ord/ts/admin (UNIX systems) or $ORACLE_HOME\ord80\ts\admin (Windows NT systems). Follow any instructions appropriate for your environment (for example, adjusting certain quota values, if necessary).

You may also want to do either or both of the following:

Create public synonyms for the Oracle8i Time Series packages (see Section 1.5), to eliminate the need to type the schema name with the package name when calling functions or procedures.
Run the quick-start demo or the usage demo, or both (see Section 1.6), to familiarize yourself with the Oracle8i Time Series product.

1.4.3 Creating Database Objects Without Using ODCA

The following instructions are for database administrators planning to create the database without using the Oracle Database Configuration Assistant (ODCA).

Create and start the database.

The ORDSYS schema shares the SYSTEM tablespace. You should allow approximately 25 megabytes for the SYSTEM tablespace, so that the Oracle8i Time Series components and metadata can be accommodated.

For detailed information about database creation and startup, see the Oracle8i Installation and Configuration Guide for your operating system, the Oracle8i Administrator's Guide, and the Oracle8i Concepts manual.
Install shared components.

Connect as user SYS, and run the following SQL procedure to install ORDSYS and certain shared components.

On Solaris systems (example showing the default SYS password):
```
SVRMGR> connect sys/change_on_install as sysdba 
SVRMGR> @<ORACLE_HOME>/ord/admin/ordinst.sql
```
Replace <ORACLE_HOME> with your $ORACLE_HOME directory.

On NT systems (example showing the default SYS password):
```
SVRMGR> connect sys/change_on_install as sysdba 
SVRMGR> @c:\orant\ord\admin\ordinst.sql
```
c:\orant is the usual $ORACLE_HOME directory.
Install the Oracle8i Time Series components (data types, packages, and metadata tables).

On Solaris systems:
```
SVRMGR> @<ORACLE_HOME>/ord/ts/admin/tsinst.sql
```
Replace <ORACLE_HOME> with your $ORACLE_HOME directory.

On NT systems:
```
SVRMGR> @c:\orant\ord\ts\admin\tsinst.sql
```
c:\orant is the usual $ORACLE_HOME directory.

The user group PUBLIC is granted execute privilege on all Oracle8i Time Series data types and packages.

1.5 Creating Public Synonyms for Oracle8i Time Series Packages

All Oracle8i Time Series packages and data types are installed under the ORDSYS schema, and all users must include the ORDSYS schema name when referring to these packages and data types. However, to simplify references to packages, you can define public synonyms for packages that contain the functions and procedures documented in this guide.

To create public synonyms, run the ordtsyn.sql file supplied with Oracle8i Time Series in the admin directory. The ordtsyn.sql file creates the following public synonyms:

CREATE PUBLIC SYNONYM TimeSeries FOR ORDSYS.TimeSeries;
CREATE PUBLIC SYNONYM Calendar   FOR ORDSYS.Calendar;
CREATE PUBLIC SYNONYM TSTools    FOR ORDSYS.TSTools;
CREATE PUBLIC SYNONYM TimeScale  FOR ORDSYS.TimeScale;

1.6 Oracle8i Time Series Demos (Demonstrations)

Table 1-1 shows the demos (files that demonstrate capabilities) included with Oracle8i Time Series. This table includes a description of each demo and the default directory in which its files are installed. (The exact location and directory syntax are system-dependent.)

Table 1-1 Oracle8i Time Series Demos

Description	Directory
Quick-start demo: quick and easy start using Oracle8i Time Series (See Section 1.6.1.)	demo/tsquick
Usage demo for end users and product developers who want to use existing Oracle8i Time Series features (See Section 1.6.2.)	demo/usage
Electric utility application demonstrating how to compute peak and off-peak summaries of 15-minute data	demo/usageutl
Java-based retrieval of time series data, using the prototype Oracle8i Time Series Java API and designed to run in a Web browser (See Section 1.7.)	demo/applet
Simple Java code segments that perform time series operations and print the results (See Section 1.7.)	demo/java
Demo showing the use of administrative tools procedures to "retrofit" existing time series detail tables; also, how to support time series queries for multiple qualifier columns in the time series detail table	demo/retrofit
Advanced-developer demo for those who want to extend Oracle8i Time Series features	demo/extend
OCI demo showing how to call Oracle8i Time Series functions using the Oracle Call Interface	demo/oci
PROC/C++ demo showing how to call Oracle8i Time Series functions in applications created using the Oracle ProC/C++ Precompiler	demo/proc
Oracle Developer demo showing how to call Oracle8i Time Series functions in an Oracle Forms^TM application	demo/dev2k

The README.txt file in the demo directory introduces the demos and describes each briefly. Also, the directory for each demo contains a README.txt file with a more detailed description of that demo.

1.6.1 Quick-Start Demo

The quick-start demo provides a quick and easy start using Oracle8i Time Series. It uses the same stock market trading data as in the usage demo (described in Section 1.6.2); however, it simplifies the process by:

Using the Oracle8i Time Series administrative tools procedures to create the schema objects (accepting defaults for most object names)
Not associating a calendar with the detail data (the daily stock market trading data)

This approach assumes that the existing stockdemo data is valid, and it is used here solely to make the quick-start demo simpler. Note, however, that using a calendar with detail data is required if you need to use Oracle8i Time Series functions to validate time series data (for example, to check that trading data is not entered for an invalid date, such as for a nontrading date or a date outside a desired start-end date range). Using a calendar with detail data is also required for using the Lead and Lag functions.

The administrative tools procedures create all the schema objects needed for this demo, including:

TSQUICK: the object view. Use this when using Oracle8i Time Series functions. For example:
```
SELECT ticker, ORDSYS.TimeSeries.TSAvg(close) FROM TSQUICK ts;
```
TSQUICK_RVW: the relational view. Use this for protected insert, update, and delete operations. Uses an INSTEAD OF trigger.
TSQUICK_TAB: the table for detail data.
TSQUICK_MAP: the mapping (metadata) table. A null calendar is later associated with each ticker.
TSQUICK_CAL: the table for calendar definitions. A monthly calendar is later defined, for use with scaleup operations.

All of these schema objects, as well as concepts related to calendars, are explained in Chapter 2. The administrative tools procedures are introduced in Section 2.12.

The quick-start demo also includes queries using several Oracle8i Time Series functions.

1.6.1.1 Running the Quick-Start Demo

After Oracle8i Time Series has been installed, you can run the quick-start demo by going to the appropriate directory (see Table 1-1) and invoking the tsquick.sql procedure, as follows:

% svrmgrl 
SVRMGR> @tsquick

1.6.1.2 Quick-Start Demo Files

The quick-start demo files are listed in Table 1-2.

Table 1-2 Quick-Start Demo Files

File	Description
tsquick.sql	Main procedure file: creates all schema objects, loads tables, performs queries.
tsquick.ctl	SQL*Loader control file
tsquick.dat	SQL*Loader data file
README.txt	Description and instructions for the demo

1.6.2 Usage Demo

The usage demo is a working example of using Oracle8i Time Series. The example models a historical database of stock pricing and provides sample queries using this data.

The usage demo is designed to guide you through Oracle8i Time Series in a step-by-step fashion. It includes example code for creating and populating tables and calendars, constructing relational views, constructing views to synthesize the interface to Oracle8i Time Series functions, and running some example queries.

1.6.2.1 Running the Usage Demo

After Oracle8i Time Series has been installed, you can run the usage demo by going to the appropriate directory (see Table 1-1) and invoking the demo.sql procedure, as follows:

% svrmgrl 
SVRMGR> @demo

1.6.2.2 Usage Demo Files

The usage demo files include examples of bulk and incremental loading; defining tables, calendars, and views; and running example queries. These files are listed in Table 1-3.

Table 1-3 Usage Demo Files

File	Description
demo.sql	Main procedure file
stockdat.ctl	SQL*Loader control file
stockdat.dat	SQL*Loader data file for time series data
tables.sql	DDL for tables
popcal.sql	Defines calendars and populates calendar table
queries.sql	Example time series queries (SQL)
queplsql.sql	Example time series queries (PL/SQL)
calqueries.sql	Example calendar queries (PL/SQL)
incload.sql	Incremental load script
stockinc.ctl	SQL*Loader control file for incremental load
stockinc.dat	SQL*Loader data file for incremental time series data
verical.sql	Verifies the correctness of calendars associated with the demo
verits.sql	Verifies the correctness of a time series (ACME) associated with the demo
cleanup.sql	Deletes database objects created by the demo
README.txt	Description and instructions for the demo

1.6.2.3 Tables and Views in the Usage Demo

The stock database consists of three tables:

stockdemo stores historical time series pricing data.
stockdemo_calendars stores instances of calendars.
stockdemo_metadata maintains mapping between time series (here, for tickers) and calendars.

To maintain time series consistency and provide a collection-based interface for time series functions, two views are constructed using these tables.

stockdemo_sv is a relational view. A relational view should be used for any insert, update, and delete operations to time series data.
stockdemo_ts is an object (reference-based) view. A reference-based view provides an object model of a time series, and it can be used for efficient read-only access using Oracle8i Time Series functions.

The relational view ensures that insert, update, and delete operations maintain a time series that is consistent with the associated calendar. (Time series consistency is explained in Section 2.8.) The relational view and the object view access the three underlying tables. The object view synthesizes references to collections.

Figure 1-1 shows the relationships between the object and relational views and the underlying tables.

Figure 1-1 Tables and Views in the Time Series Usage Demo

1.7 Java Client-Side API (Prototype)

A prototype Java client-side application programming interface (API) is provided in the following file:

<ORACLE_HOME>/ord/ts/jlib/thindriver.zip

Documentation (generated by javadoc) for this API is in the following directory:

<ORACLE_HOME>/ord/ts/doc

The README.txt file in this directory discusses Java support.

The following directories contain demos (introduced in Table 1-1 in Section 1.6) that can help you to learn and use the API:

The demo/java directory contains sample code segments that demonstrate the basic use of the API. Although your applications will probably be more complex, you can use the techniques in these code segments to retrieve time series data.
The demo/applet directory contains a demonstration applet (built using JDeveloper) that runs in a Web browser or using appletviewer. This applet can connect to the database, perform queries on the sample time series data, and display the results.

1 Introduction