Oracle8i Data Cartridge Developer's Guide
Release 8.1.5

A68002-01

Library

Product

Contents

Index

Prev Next

11
Power Demand Cartridge Example

This chapter explains the power demand sample data cartridge that is included with the Oracle8i Data Cartridge Software Development Kit (SDK). The power demand cartridge includes a user-defined object type and extensible indexing. This chapter covers the following topics:

This chapter does not explain in detail the concepts related to the features illustrated. For information about extensible indexing, see Chapter 7, "Building Domain Indexes". For information about cartridge services, see Chapter 9, "Using Cartridge Services"].

This chapter divides the example into segments and provides commentary. The entire cartridge definition is available online in the following location:

...directory-path?.../tkqxpwr.sql   [location and name TBS]

Feature Requirements

A power utility, Power-To-The-People, develops a sophisticated model to decide how to deploy its resources. The region served by the utility is represented by a grid laid over a geographic area.


This region may be surrounded by other regions some of whose power needs are supplied by other utilities. As pictured above, every region is composed of geographic quadrants referred to as "cells" on a 10x10 grid. There are a number of ways of identifying cells -- by spatial coordinates (longitude/latitude), by a matrix numbering (1,1; 1,2;...), and by numbering them sequentially:

Figure 11-1 Regional Grid Cells in Numbered Sequence


Within the area represented by each cell, the power used by consumers in that area is recorded each hour. For example, the power demand readings for a particular hour might be represented by Table 11-2 (cells here represented on a matrix):

Table 11-1 Sample Power Demand Readings for an Hour

 

1  

2  

3  

4  

5  

6  

7  

8  

9  

10  

1  

23  

21  

25  

23  

24  

25  

27  

32  

31  

30  

2  

33  

32  

31  

33  

34  

32  

23  

22  

21  

34  

3  

45  

44  

43  

33  

44  

43  

42  

41  

45  

46  

4  

44  

45  

45  

43  

42  

26  

19  

44  

33  

43  

5  

45  

44  

43  

42  

41  

44  

45  

46  

47  

44  

6  

43  

45  

98  

55  

54  

43  

44  

33  

34  

44  

7  

33  

45  

44  

43  

33  

44  

34  

55  

46  

34  

8  

87  

34  

33  

32  

31  

34  

35  

38  

33  

39  

9  

30  

40  

43  

42  

33  

43  

34  

32  

34  

46  

10  

43  

42  

34  

12  

43  

45  

48  

45  

43  

32  

The power stations also receives reports from two other sources:

Figure 11-2 Grey Scale Representation of Satellite Image


These images are designed so that 'lighter is colder'. The image above shows a cold front moving into the region from the south-west. By correlating the data provided by the grey scale images with temperature readings taken at the same time, the utility has been able to determine what the power demand is given weather conditions viewed from the stratosphere.

The reason that this is important is that a crucial part of this modeling has to do with noting the rapidity and degree of change in the incoming reports as weather changes and power is deployed. The following diagram shows same cold front at a second recording:

Figure 11-3 Grey-Scale Representation of Weather Conditions at Second Recording


By analyzing the extent and speed of the cold front, the utility is able to project what the conditions are likely to be in the short and medium term:

Figure 11-4 Grey-Scale Representation of Conditions as Projected


By combing this data about these conditions, and other anomalous situations (such as the failure of a substation) the utility must be able to organize the most optimal deployment of its resources. The following drawing reflects the distribution of substations across the region:

Figure 11-5 Distribution of Power Stations Across the Region


The distribution of power stations means that the utility can redirect its deployment of electricity to the areas of greatest need. The following figure gives a pictorial representation of the overlap between three stations:

Figure 11-6 Areas Served by Three Power Stations


Depending on fluctuating requirements, the utility must be able to decide how to deploy its resources, and even whether to purchase power from a neighboring utility in the event of shortfall.

Modelling the Application

The following Class Diagram describes the application objects using the Unified Modelling Language (UML) notation.

Figure 11-7 Use Case Diagram for Power Demand Cartridge


Sample Queries

Modelling the application in this way, makes possible the following specific queries:

These queries are, of course, only a short list of the possible information that could be gleaned from the system. For instance, it is obvious that the developer of such an application would want to build queries that are based on the information derived from prior queries e.g.,

The Power Demand cartridge as implemented is described in the class diagram below.

Figure 11-8 Use Case Diagram for Power Demand Cartridge

The utility gets ongoing reports from weather centers about current conditions and from power stations about ongoing power utilization for specific geographical areas (represented by cells on a 10x10 grid). It then compares this information to historical data in order to predict demand for power in the different geographic areas for given time periods.

Each service area for the utility is considered as a 10x10 grid of cells, where each cell's boundaries are associated with spatial coordinates (longitude/latitude). The geographical areas represented by the cells can be uniform or can have different shapes and sizes. Within the area represented by each cell, the power used by consumers in that area is recorded each hour. For example, the power demand readings for a particular hour might be represented by Table 11-2.

Table 11-2 Sample Power Demand Readings for an Hour

 

1  

2  

3  

4  

5  

6  

7  

8  

9  

10  

1  

23  

21  

25  

23  

24  

25  

27  

32  

31  

30  

2  

33  

32  

31  

33  

34  

32  

23  

22  

21  

34  

3  

45  

44  

43  

33  

44  

43  

42  

41  

45  

46  

4  

44  

45  

45  

43  

42  

26  

19  

44  

33  

43  

5  

45  

44  

43  

42  

41  

44  

45  

46  

47  

44  

6  

43  

45  

98  

55  

54  

43  

44  

33  

34  

44  

7  

33  

45  

44  

43  

33  

44  

34  

55  

46  

34  

8  

87  

34  

33  

32  

31  

34  

35  

38  

33  

39  

9  

30  

40  

43  

42  

33  

43  

34  

32  

34  

46  

10  

43  

42  

34  

12  

43  

45  

48  

45  

43  

32  

The numbers in each cell reflect power demand (in some unit of measurement determined by the electric utility) for the hour for that area. For example, the demand for the first cell (1,1) was 23, the demand for the second cell (1,2) was 21, and so on. The demand for the last cell (10, 10) was 32.

The utility uses this data for many monitoring and analytical applications. Readings for individual cells are monitored for unusual surges or decreases in demand. For example, the readings of 98 for (6,3) and 87 for (8,1) might be unusually high, and the readings of 19 for (4,7) and 12 for (10,4) might be unusually low. Trends are also analyzed, such as significant increases or decreases in total, per-neighborhood, and per-station demand over time.

Queries and Extensible Indexing

Before you use extensible indexing, you should first ask whether the users of the table will benefit from having the domain index. That is, will they execute queries that could run just as efficiently using a standard Oracle index (or using no index at all)?

Queries Not Benefiting from Extensible Indexing

A query does not require a domain index if both of the following are true:

In the PowerDemand_Typ object type cartridge example, the values for three columns (TotGridDemand, MaxCellDemand, and MinCellDemand) are set by functions, after which the values do not change. (For example, the total grid power demand for 13:00 on 01-Jan-1998 does not change after it has been computed.) For queries that use these columns, a standard b-tree index on each column is sufficient and recommended for operations like equals, lessthan, greaterthan, max, and min.

Examples of queries that would not benefit from extensible indexing (using the power demand cartridge) include:

Queries Benefiting from Extensible Indexing

A query benefits from a domain index if the data being queried against cannot be made a simple attribute of a table or if the operation to be performed on the data is not one of the standard operations supported by Oracle indexes.

Examples of queries that would benefit from extensible indexing (using the power demand cartridge) include:

Creating the Domain Index

This section explains the parts of the power demand cartridge as they relate to extensible indexing. Explanatory text and code segments are mixed.

The entire cartridge definition is available online as extdemo1.sql in the standard Oracle demo directory (location is platform-dependent).

Creating the Schema to Own the Index

Before you create a domain index, create a database user (schema) to own the index. In the power demand example, the user PowerCartUser is created and granted the appropriate privileges. All database structures related to the cartridge are created under this user (that is, while the cartridge developer or DBA is connected to the database as PowerCartUser).

set echo on
connect sys/knl_test7 as sysdba;
drop user PowerCartUser cascade;
create user PowerCartUser identified by PowerCartUser;

-------------------------------------------------------------------
-- INITIAL SET-UP
-------------------------------------------------------------------
-- grant privileges --
grant connect, resource to PowerCartUser;
-- do we need to grant these privileges --
grant create operator to PowerCartUser;
grant create indextype to PowerCartUser;
grant create table to PowerCartUser;

Creating the Object Type (PowerDemand_Typ)

The object type PowerDemand_Typ is used to store the hourly power grid readings. This type is used to define a column in the table in which the readings are stored.

First, two types are defined for later use:

The PowerDemand_Typ type includes:

Defining the Object Type Methods

The PowerDemand_Typ object type has methods that set the first three attributes in the type definition:

The logic for each procedure is not complicated. SetTotDemand loops through the cell values and creates a running total. SetMaxDemand compares the first two cell values and saves the higher as the current highest value; it then examines each successive cell, comparing it against the current highest value and saving the higher of the two as the current highest value, until it reaches the end of the cell values. SetMinDemand uses the same approach as SetMaxDemand, but it continually saves the lower value in comparisons to derive the lowest value overall.

CREATE OR REPLACE TYPE BODY PowerDemand_Typ 
IS
  --
  -- Methods (Set...) for this type:
  -- Total demand for the entire power grid for a
  -- SampleTime: sets the value of TotGridDemand.
  Member Procedure SetTotalDemand 
  IS
  I BINARY_INTEGER;
  Total NUMBER;
  BEGIN
    Total :=0;
    I := CellDemandValues.FIRST;   
    WHILE I IS NOT NULL LOOP
   Total := Total + CellDemandValues(I);
        I := CellDemandValues.NEXT(I);
    END LOOP;
    TotGridDemand := Total;
  END;

  -- Maximum demand for the entire power grid for a
  -- SampleTime: sets the value of MaxCellDemand.
  Member Procedure SetMaxDemand 
  IS
  I BINARY_INTEGER;
  Temp NUMBER;
  BEGIN
    I := CellDemandValues.FIRST;   
    Temp := CellDemandValues(I);
    WHILE I IS NOT NULL LOOP
   IF Temp < CellDemandValues(I) THEN
      Temp := CellDemandValues(I);
   END IF;
        I := CellDemandValues.NEXT(I);
    END LOOP;
    MaxCellDemand := Temp;
  END;

  -- Minimum demand for the entire power grid for a
  -- SampleTime: sets the value of MinCellDemand.
  Member Procedure SetMinDemand 
  IS
  I BINARY_INTEGER;
  Temp NUMBER;
  BEGIN
    I := CellDemandValues.FIRST;   
    Temp := CellDemandValues(I);
    WHILE I IS NOT NULL LOOP
   IF Temp > CellDemandValues(I) THEN
      Temp := CellDemandValues(I);
   END IF;
        I := CellDemandValues.NEXT(I);
    END LOOP;
    MinCellDemand := Temp;
  END;
END;
/

Creating the Functions and Operators

The power demand cartridge is designed so that users can query the power grid for relationships of equality, greaterthan, or lessthan. However, because of the way the cell demand data is stored, the standard operators (=, >, <) cannot be used. Instead, new operators must be created, and a function must be created to define the implementation for each new operator (that is, how the operator is to be interpreted by Oracle).

For this cartridge, each of the three relationships can be checked in two ways:

For each operator-function pair, the function is defined first and then the operator as using the function. The function is the implementation that would be used if there were no index defined. This implementation must be specified so that the Oracle optimizer can determine costs, decide whether the index should be used, and create an execution plan.

Table 11-3 shows the operators and implementing functions:

Table 11-3 Operators and Implementing Functions
Operator  Implementing Function 
Power_EqualsSpecific
 
Power_EqualsSpecific_Func
 
Power_EqualsAny
 
Power_EqualsAny_Func
 
Power_LessThanSpecific
 
Power_LessThanSpecific_Func
 
Power_LessThanAny
 
Power_LessThanAny_Func
 
Power_
GreaterThanSpecific
 
Power_GreaterThanSpecific_Func
 
Power_GreaterThanAny
 
Power_GreaterThanAny_Func
 

Each function and operator returns a numeric value of 1 if the condition is true (for example, if the specified cell is equal to the specified value), 0 if the condition is not true, or null if the specified cell number is invalid.

The following statements create the implementing functions (Power_xxx_Func), first the specific and then the any implementations.

CREATE FUNCTION Power_EqualsSpecific_Func(
  object PowerDemand_Typ, cell NUMBER, value NUMBER)
RETURN NUMBER AS
  BEGIN
  IF cell <= object.CellDemandValues.LAST
  THEN
     IF (object.CellDemandValues(cell) = value) THEN
   RETURN 1;
     ELSE
   RETURN 0;
     END IF;
  ELSE
     RETURN NULL;
  END IF;
  END;
/
CREATE FUNCTION Power_GreaterThanSpecific_Func(
  object PowerDemand_Typ, cell NUMBER, value NUMBER)
RETURN NUMBER AS
  BEGIN
  IF cell <= object.CellDemandValues.LAST
  THEN
     IF (object.CellDemandValues(cell) > value) THEN
   RETURN 1;
     ELSE
   RETURN 0;
     END IF;
  ELSE
     RETURN NULL;
  END IF;
  END;
/
CREATE FUNCTION Power_LessThanSpecific_Func(
  object PowerDemand_Typ, cell NUMBER, value NUMBER)
RETURN NUMBER AS
  BEGIN
  IF cell <= object.CellDemandValues.LAST
  THEN
     IF (object.CellDemandValues(cell) < value) THEN
   RETURN 1;
     ELSE
   RETURN 0;
     END IF;
  ELSE
     RETURN NULL;
  END IF;
  END;
/
CREATE FUNCTION Power_EqualsAny_Func(
  object PowerDemand_Typ, value NUMBER)
RETURN NUMBER AS
   idx NUMBER;
  BEGIN
    FOR idx IN object.CellDemandValues.FIRST..object.CellDemandValues.LAST LOOP
      IF (object.CellDemandValues(idx) = value) THEN
   RETURN 1;
      END IF;
    END LOOP;
   RETURN 0;
  END;
/
CREATE FUNCTION Power_GreaterThanAny_Func(
  object PowerDemand_Typ, value NUMBER)
RETURN NUMBER AS
   idx NUMBER;
  BEGIN
    FOR idx IN object.CellDemandValues.FIRST..object.CellDemandValues.LAST LOOP
      IF (object.CellDemandValues(idx) > value) THEN
   RETURN 1;
      END IF;
    END LOOP;
   RETURN 0;
  END;
/
CREATE FUNCTION Power_LessThanAny_Func(
  object PowerDemand_Typ, value NUMBER)
RETURN NUMBER AS
   idx NUMBER;
  BEGIN
    FOR idx IN object.CellDemandValues.FIRST..object.CellDemandValues.LAST LOOP
      IF (object.CellDemandValues(idx) < value) THEN
   RETURN 1;
      END IF;
    END LOOP;
   RETURN 0;
  END;
/

The following statements create the operators (Power_xxx). Each statement specifies an implementing function.

CREATE OPERATOR Power_Equals BINDING(PowerDemand_Typ, NUMBER, NUMBER)
  RETURN NUMBER USING Power_EqualsSpecific_Func;
CREATE OPERATOR Power_GreaterThan BINDING(PowerDemand_Typ, NUMBER, NUMBER)
  RETURN NUMBER USING Power_GreaterThanSpecific_Func;
CREATE OPERATOR Power_LessThan BINDING(PowerDemand_Typ, NUMBER, NUMBER)
  RETURN NUMBER USING Power_LessThanSpecific_Func;
  
CREATE OPERATOR Power_EqualsAny BINDING(PowerDemand_Typ, NUMBER)
  RETURN NUMBER USING Power_EqualsAny_Func;
CREATE OPERATOR Power_GreaterThanAny BINDING(PowerDemand_Typ, NUMBER)
  RETURN NUMBER USING Power_GreaterThanAny_Func;
CREATE OPERATOR Power_LessThanAny BINDING(PowerDemand_Typ, NUMBER)
  RETURN NUMBER USING Power_LessThanAny_Func;

Creating the Indextype Implementation Methods

The power demand cartridge creates an object type for the indextype that specifies methods for the domain index. These methods are part of the ODCIIndex (Oracle Data Cartridge Interface Index) interface, and they collectively define the behavior of the index in terms of the methods for defining, manipulating, scanning, and exporting the index.

Table 11-4 shows the method functions (all but one starting with ODCIIndex) created for the power demand cartridge.

Table 11-4 Indextype Methods
Method  Description 

ODCIGetInterfaces  

Returns the list of names of the interfaces implemented by the type.  

ODCIIndexCreate
 

Creates the index-organized table to store index data. If the base table containing data to be indexed is not empty, this method builds the index for existing data.

This method is called when a CREATE INDEX statement is issued that refers to the indextype. Upon invocation, any parameters specified in the PARAMETERS clause are passed in along with a description of the index.  

ODCIIndexDrop
 

Deletes the index. This method is called when a DROP INDEX statement specifies the index.  

ODCIIndexStart
 

Initializes the scan of the index for the operator predicate. This method is invoked when a query is submitted involving an operator that can be executed using the domain index.  

ODCIIndexFetch
 

Returns the ROWID of each row that satisfies the operator predicate.  

ODCIIndexClose
 

Ends the current use of the index. This method can perform any necessary clean-up.  

.ODCIIndexInsert
 

Maintains the index structure when a record is inserted in a table that contains columns or object attributes indexed by the indextype.  

ODCIIndexDelete
 

Maintains the index structure when a record is deleted from a table that contains columns or object attributes indexed by the indextype.  

ODCIIndexUpdate
 

Maintains the index structure when a record is updated (modified) in a table that contains columns or object attributes indexed by the indextype.  

ODCIIndexGet
Metadata
 

Allows the export and import of implementation-specific metadata associated with the index.  

Type Definition

The following statement creates the power_idxtype_im object type. The methods of this type are the ODCI methods to define, manipulate, and scan the domain index. The curnum attribute is the cursor number used as context for the scan routines (ODCIIndexStart, ODCIIndexFetch, and ODCIIndexClose).

CREATE OR REPLACE TYPE power_idxtype_im AS OBJECT
(
  curnum NUMBER,
  MEMBER FUNCTION ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList)
     RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexCreate (ia sys.odciindexinfo, parms VARCHAR2)
     RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexDrop(ia sys.odciindexinfo) RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexStart(self IN OUT power_idxtype_im,
     ia sys.odciindexinfo,
     op sys.odciPredInfo, qi sys.ODCIQueryInfo,
     strt NUMBER, stop NUMBER,
     cmppos NUMBER, cmpval NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexStart(self IN OUT power_idxtype_im,
     ia sys.odciindexinfo,
     op sys.odciPredInfo, qi sys.ODCIQueryInfo,
     strt NUMBER, stop NUMBER,
     cmpval NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexFetch(nrows NUMBER, 
     rids OUT sys.odciridlist)
     RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexClose RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexInsert(ia sys.odciindexinfo, rid VARCHAR2,
     newval PowerDemand_Typ) RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexDelete(ia sys.odciindexinfo, rid VARCHAR2,
     oldval PowerDemand_Typ) RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexUpdate(ia sys.odciindexinfo, rid VARCHAR2,
     oldval PowerDemand_Typ, newval PowerDemand_Typ) RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexGetMetadata(ia sys.odciindexinfo, expversion
     VARCHAR2, newblock OUT PLS_INTEGER) RETURN VARCHAR2
);
/

The CREATE TYPE statement is followed by a CREATE TYPE BODY statement that specifies the implementation for each member function:

CREATE OR REPLACE TYPE BODY power_idxtype_im 
IS
...

Each member function is described in a separate section, but the function definitions (except for ODCIIndexGetMetadata, which returns a VARCHAR2 string) have the following general form:

  MEMBER FUNCTION function-name (...) 
    RETURN NUMBER
  IS
  ...
  END;

ODCIGetInterfaces Method

The ODCIGetInterfaces member function returns the list of names of the interfaces implemented by the type. In release 8.1, there is only one set of the extensible indexing interface routines, called SYS.ODCIINDEX1. Thus, in release 8.1, the ODCIGetInterfaces routine must return 'SYS'.'ODCIINDEX1' as one of the implemented interfaces.

  MEMBER FUNCTION ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList)
       return number is
   BEGIN
       ifclist := sys.ODCIObjectList(sys.ODCIObject('SYS','ODCIINDEX1'));
       return ODCIConst.Success;
   END ODCIGetInterfaces;

ODCIIndexCreate Method

The ODCIIndexCreate member function creates the index-organized table to store index data. If the base table containing data to be indexed is not empty, this method inserts the index data entries for existing data.

The function takes the index information as an object parameter whose type is SYS.ODCIINDEXINFO. The type attributes include the index name, owner name, and so forth. The PARAMETERS string specified in the CREATE INDEX statement is also passed in as a parameter to the function.

  MEMBER FUNCTION ODCIIndexCreate (ia sys.odciindexinfo, parms VARCHAR2)
     RETURN NUMBER
  is
   i INTEGER;
   r ROWID;
   p NUMBER;
   v NUMBER;
   stmt1 VARCHAR2(1000);
   stmt2 VARCHAR2(1000);
   stmt3 VARCHAR2(1000);
   cnum1 INTEGER;
   cnum2 INTEGER;
   cnum3 INTEGER;
   junk NUMBER;

The SQL statement to create the index-organized table for the index data is constructed and executed. The table includes the ROWID of the base table (r), the cell position number (cpos) in the grid from 1 to 100, and the power demand value in that cell (cval).

BEGIN
   -- Construct the SQL statement.
   stmt1 := 'CREATE TABLE ' || ia.IndexSchema || '.' || ia.IndexName ||
           '_pidx' || '( r ROWID, cpos NUMBER, cval NUMBER)';

   -- Dump the SQL statement.
   dbms_output.put_line('ODCIIndexCreate>>>>>');
   sys.ODCIIndexInfoDump(ia);
   dbms_output.put_line('ODCIIndexCreate>>>>>'||stmt1);

   -- Execute the statement.
   cnum1 := dbms_sql.open_cursor;
   dbms_sql.parse(cnum1, stmt1, dbms_sql.native);
   junk := dbms_sql.execute(cnum1);
   dbms_sql.close_cursor(cnum1);

The function populates the index by inserting rows into the index-organized table. The function "unnests" the VARRAY attribute and inserts a row for each cell into the index-organized table. Thus, each 10 X 10 grid (10 rows, 10 values per row) becomes 100 rows in the index-organized table (one row per cell).

   -- Now populate the table.
   stmt2 := ' INSERT INTO '|| ia.IndexSchema || '.' || 
       ia.IndexName || '_pidx' ||
       ' SELECT :rr, ROWNUM, column_value FROM THE' ||
       ' (SELECT CAST (P.'|| ia.IndexCols(1).ColName||'.CellDemandValues 
          AS NumTab_Typ)'||
       ' FROM ' || ia.IndexCols(1).TableSchema || '.' || 
       ia.IndexCols(1).TableName || ' P' ||
       ' WHERE P.ROWID = :rr)';
 
   -- Execute the statement.
   dbms_output.put_line('ODCIIndexCreate>>>>>'||stmt2);
 
   -- Parse the statement.
   cnum2 := dbms_sql.open_cursor;
   dbms_sql.parse(cnum2, stmt2, dbms_sql.native);
 
   stmt3 := 'SELECT ROWID FROM '|| ia.IndexCols(1).TableSchema 
     || '.' || ia.IndexCols(1).TableName;
   dbms_output.put_line('ODCIIndexCreate>>>>>'||stmt3);
   cnum3 := dbms_sql.open_cursor;
   dbms_sql.parse(cnum3, stmt3, dbms_sql.native);
   dbms_sql.define_column_rowid(cnum3, 1, r);   
   junk := dbms_sql.execute(cnum3);
 
   WHILE dbms_sql.fetch_rows(cnum3) > 0 LOOP
      -- Get column values of the row. --
      dbms_sql.column_value_rowid(cnum3, 1, r);
      -- Bind the row into the cursor for the next insert. --
      dbms_sql.bind_variable_rowid(cnum2, ':rr', r);
      junk := dbms_sql.execute(cnum2);
   END LOOP;

The function concludes by closing the cursors and returning a success status.

   dbms_sql.close_cursor(cnum2);
   dbms_sql.close_cursor(cnum3);
   RETURN ODCICONST.SUCCESS;
  END;

ODCIIndexDrop Method

The ODCIIndexDrop member function deletes the index. This method is called when a DROP INDEX statement specifies the index.

  MEMBER FUNCTION ODCIIndexDrop(ia sys.odciindexinfo) RETURN NUMBER is
   stmt VARCHAR2(1000);
   cnum INTEGER;
   junk INTEGER;
  BEGIN
    -- Construct the SQL statement.
   stmt := 'drop table ' || ia.IndexSchema || '.' || ia.IndexName 
     || '_pidx';
  
   dbms_output.put_line('ODCIIndexDrop>>>>>');
   sys.ODCIIndexInfoDump(ia);
   dbms_output.put_line('ODCIIndexDrop>>>>>'||stmt);
  
   -- Execute the statement.
   cnum := dbms_sql.open_cursor;
   dbms_sql.parse(cnum, stmt, dbms_sql.native);
   junk := dbms_sql.execute(cnum);
   dbms_sql.close_cursor(cnum);
  
   RETURN ODCICONST.SUCCESS;
  END;

ODCIIndexStart Method (for Specific Queries)

The first definition of the ODCIIndexStart member function initializes the scan of the index to return all rows that satisfy the operator predicate. For example, if a query asks for all instances where cell (3,7) has a value equal to 25, the function initializes the scan to return all rows in the index-organized table for which that cell has that value. (This definition of ODCIIndexStart differs from the definition in the next section in that it includes the cmppos parameter for the position of the cell.)

The self parameter is the context that is shared with the ODCIIndexFetch and ODCIIndexClose functions. The ia parameter contains the index information (an object instance of type SYS.ODCIINDEXINFO), and the op parameter contains the operator information (an object instance of type SYS.ODCIOPERINFO). The strt and stop parameters are the lower and upper boundary points for the operator return value. The cmppos parameter is the cell position and cmpval is the value in the cell specified by the operator (Power_XxxxxSpecific).

  MEMBER FUNCTION ODCIIndexStart(self IN OUT power_idxtype_im, 
        ia sys.odciindexinfo,
        op sys.odciPredInfo, qi sys.ODCIQueryInfo,
        strt NUMBER, stop NUMBER,
        cmppos NUMBER, cmpval NUMBER ) RETURN NUMBER is
    cnum INTEGER;
    rid ROWID;
    nrows INTEGER;
    relop VARCHAR2(2);
    stmt VARCHAR2(1000);
  BEGIN
    dbms_output.put_line('ODCIIndexStart>>>>>');
    sys.ODCIIndexInfoDump(ia);
    sys.ODCIPredInfoDump(op);
    dbms_output.put_line('start key : '||strt);
    dbms_output.put_line('stop key : '||stop);
    dbms_output.put_line('compare position : '||cmppos);
    dbms_output.put_line('compare value : '||cmpval);

The function checks for errors in the predicate.

    -- Take care of some error cases.
    -- The only predicates in which btree operators can appear are
    --    op() = 1     OR    op() = 0
    if (strt != 1) and (strt != 0) then
    raise_application_error(-20101, 'Incorrect predicate for operator');
    END if;
 
    if (stop != 1) and (stop != 0) then
    raise_application_error(-20101, 'Incorrect predicate for operator');
    END if;

The function generates the SQL statement to be executed. It determines the operator name and the lower and upper index value bounds (the start and stop keys). The start and stop keys can both be 1 (= TRUE) or both be 0 (= FALSE).

    -- Generate the SQL statement to be executed.
    -- First, figure out the relational operator needed for the statement.
    -- Take into account the operator name and the start and stop keys.
    -- For now, the start and stop keys can both be 1 (= TRUE) or 
    -- both be 0 (= FALSE).
    if op.ObjectName = 'POWER_EQUALS' then
      if strt = 1 then 
        relop := '=';
      else
        relop := '!=';
      end if;
    elsif op.ObjectName = 'POWER_LESSTHAN' then
      if strt = 1 then 
        relop := '<';
      else
        relop := '>=';
      end if;
    elsif op.ObjectName = 'POWER_GREATERTHAN' then
      if strt = 1 then 
        relop := '>';
      else
        relop := '<=';
      end if;
    else
      raise_application_error(-20101, 'Unsupported operator');
    end if;
 
    stmt := 'select r from '||ia.IndexSchema||'.'||ia.IndexName||'_pidx'||
              ' where cpos '|| '=' ||''''||cmppos||''''||
              ' and cval '||relop||''''||cmpval||'''';
 
    dbms_output.put_line('ODCIIndexStart>>>>>' || stmt);
    cnum := dbms_sql.open_cursor;
    dbms_sql.parse(cnum, stmt, dbms_sql.native);
    dbms_sql.define_column_rowid(cnum, 1, rid);   
    nrows := dbms_sql.execute(cnum);

The function stores the cursor number in the context, which is used by the ODCIIndexFetch function, and sets a success return status.

    -- Set context as the cursor number.
    self := power_idxtype_im(cnum);
 
    -- Return success.
    RETURN ODCICONST.SUCCESS;
  END;

ODCIIndexStart Method (for Any Queries)

This definition of the ODCIIndexStart member function initializes the scan of the index to return all rows that satisfy the operator predicate. For example, if a query asks for all instances where any cell has a value equal to 25, the function initializes the scan to return all rows in the index-organized table for which that cell has that value. (This definition of ODCIIndexStart differs from the definition in the preceding section in that it does not include the cmppos parameter.)

The self parameter is the context that is shared with the ODCIIndexFetch and ODCIIndexClose functions. The ia parameter contains the index information (an object instance of type SYS.ODCIINDEXINFO), and the op parameter contains the operator information (an object instance of type SYS.ODCIOPERINFO). The strt and stop parameters are the lower and upper boundary points for the operator return value. The cmpval parameter is the value in the cell specified by the operator (Power_Xxxxx).

  MEMBER FUNCTION ODCIIndexStart(self IN OUT power_idxtype_im, 
        ia sys.odciindexinfo,
        op sys.odciPredInfo, qi sys.ODCIQueryInfo,
        strt NUMBER, stop NUMBER,
        cmpval NUMBER ) RETURN NUMBER is
    cnum INTEGER;
    rid ROWID;
    nrows INTEGER;
    relop VARCHAR2(2);
    stmt VARCHAR2(1000);
  BEGIN
    dbms_output.put_line('ODCIIndexStart>>>>>');
    sys.ODCIIndexInfoDump(ia);
    sys.ODCIPredInfoDump(op);
    dbms_output.put_line('start key : '||strt);
    dbms_output.put_line('stop key : '||stop);
    dbms_output.put_line('compare value : '||cmpval);

The function checks for errors in the predicate.

    -- Take care of some error cases.
    -- The only predicates in which btree operators can appear are
    --    op() = 1     OR    op() = 0
    if (strt != 1) and (strt != 0) then
    raise_application_error(-20101, 'Incorrect predicate for operator');
    END if;
 
    if (stop != 1) and (stop != 0) then
    raise_application_error(-20101, 'Incorrect predicate for operator');
    END if;

The function generates the SQL statement to be executed. It determines the operator name and the lower and upper index value bounds (the start and stop keys). The start and stop keys can both be 1 (= TRUE) or both be 0 (= FALSE).

    -- Generate the SQL statement to be executed.
    -- First, figure out the relational operator needed for the statement.
    -- Take into account the operator name and the start and stop keys.
    -- For now, the start and stop keys can both be 1 (= TRUE) or 
    -- both be 0 (= FALSE).
    if op.ObjectName = 'POWER_EQUALSANY' then
      relop := '=';
    elsif op.ObjectName = 'POWER_LESSTHANANY' then
        relop := '<';
    elsif op.ObjectName = 'POWER_GREATERTHANANY' then
        relop := '>';
    else
      raise_application_error(-20101, 'Unsupported operator');
    end if;
 
    -- This statement returns the qualifying rows for the TRUE case.
    stmt := 'select distinct r from '||ia.IndexSchema||'.'||ia.IndexName||
            '_pidx'||' where cval '||relop||''''||cmpval||'''';
    -- In the FALSE case, we need to find the  complement of the rows.
    if (strt = 0) then
      stmt := 'select distinct r from '||ia.IndexSchema||'.'||
              ia.IndexName||'_pidx'||' minus '||stmt;
    end if;
 
    dbms_output.put_line('ODCIIndexStart>>>>>' || stmt);
    cnum := dbms_sql.open_cursor;
    dbms_sql.parse(cnum, stmt, dbms_sql.native);
    dbms_sql.define_column_rowid(cnum, 1, rid);   
    nrows := dbms_sql.execute(cnum);

The function stores the cursor number in the context, which is used by the ODCIIndexFetch function, and sets a success return status.

    -- Set context as the cursor number.
    self := power_idxtype_im(cnum);
 
    -- Return success.
    RETURN ODCICONST.SUCCESS;
  END;

ODCIIndexFetch Method

The ODCIIndexFetch member function returns a batch of ROWIDs for the rows that satisfy the operator predicate. Each time ODCIIndexFetch is invoked, it returns the next batch of rows (rids parameter, a collection of type SYS.ODCIRIDLIST) that satisfy the operator predicate. The maximum number of rows that can be returned on each invocation is specified by the nrows parameter.

Oracle invokes ODCIIndexFetch repeatedly until all rows that satisfy the operator predicate have been returned.

  MEMBER FUNCTION ODCIIndexFetch(nrows NUMBER, rids OUT sys.odciridlist)
   RETURN NUMBER is
    cnum INTEGER;
    idx INTEGER := 1;
    rlist sys.odciridlist := sys.odciridlist();
    done boolean := FALSE;

The function loops through the collection of rows selected by the ODCIIndexStart function, using the same cursor number (cnum) as in the ODCIIndexStart function, and returns the ROWIDs.

  BEGIN
    dbms_output.put_line('ODCIIndexFetch>>>>>');
    dbms_output.put_line('Nrows : '||round(nrows));
 
    cnum := self.curnum;
 
    WHILE not done LOOP
      if idx > nrows then
         done := TRUE;
      else
         rlist.extEND;
         if dbms_sql.fetch_rows(cnum) > 0 then
            dbms_sql.column_value_rowid(cnum, 1, rlist(idx));
            idx := idx + 1;
         else
            rlist(idx) := null;
            done := TRUE;
         END if;
      END if;   
    END LOOP;
 
    rids := rlist;
    RETURN ODCICONST.SUCCESS;
  END;

ODCIIndexClose Method

The ODCIIndexClose member function closes the cursor used by the ODCIIndexStart and ODCIIndexFetch functions.

  MEMBER FUNCTION ODCIIndexClose RETURN NUMBER is 
    cnum INTEGER;
  BEGIN
    dbms_output.put_line('ODCIIndexClose>>>>>');
 
    cnum := self.curnum;
    dbms_sql.close_cursor(cnum);
    RETURN ODCICONST.SUCCESS;
  END;

ODCIIndexInsert Method

The ODCIIndexInsert member function is called when a record is inserted in a table that contains columns or OBJECT attributes indexed by the indextype. The new values in the indexed columns are passed in as arguments along with the corresponding row identifier.

When an array of rows is inserted into the base table, an array of new values is passed in to the insert routine. This results in better performance than invoking the insert routine with one new row value at a time.

  MEMBER FUNCTION ODCIIndexInsert(ia sys.odciindexinfo, rid VARCHAR2, 
   newval PowerDemand_Typ) 
       RETURN NUMBER as 
       cid INTEGER; 
       i BINARY_INTEGER;
       nrows INTEGER;
       stmt VARCHAR2(1000);
   BEGIN 
     dbms_output.put_line(' ');
     dbms_output.put_line('ODCIIndexInsert>>>>>'|| 
      ' TotGridDemand= '||newval.TotGridDemand ||
      ' MaxCellDemand= '||newval.MaxCellDemand ||
      ' MinCellDemand= '||newval.MinCellDemand) ;
     sys.ODCIIndexInfoDump(ia); 
      
     -- Construct the statement.
     stmt := ' INSERT INTO '|| ia.IndexSchema || '.' || ia.IndexName
            || '_pidx' ||' VALUES (:rr, :pos, :val)';
  
     -- Execute the statement.
     dbms_output.put_line('ODCIIndexInsert>>>>>'||stmt);
     -- Parse the statement.
     cid := dbms_sql.open_cursor;
     dbms_sql.parse(cid, stmt, dbms_sql.native);
     dbms_sql.bind_variable_rowid(cid, ':rr', rid);
      
     -- Iterate over the rows of the Varray and insert them.
     i := newval.CellDemandValues.FIRST;   
     WHILE i IS NOT NULL LOOP
         -- Bind the row into the cursor for insert.
         dbms_sql.bind_variable(cid, ':pos', i);   
         dbms_sql.bind_variable(cid, ':val', newval.CellDemandValues(i));
         -- Execute.
         nrows := dbms_sql.execute(cid);
         dbms_output.put_line('ODCIIndexInsert>>>>>('|| 
                               'RID' ||' , '||
                               i   || ' , '||
                               newval.CellDemandValues(i)|| ')');
         i := newval.CellDemandValues.NEXT(i);
      END LOOP;
     dbms_sql.close_cursor(cid);
     RETURN ODCICONST.SUCCESS;
   END ODCIIndexInsert;

ODCIIndexDelete Method

The ODCIIndexDelete member function is called when a record is deleted from a table that contains columns or object attributes indexed by the indextype. The old values in the indexed columns are passed in as arguments along with the corresponding row identifier. When an array of rows is deleted, an array of old values and the corresponding row identifiers are passed.

  MEMBER FUNCTION ODCIIndexDelete(ia sys.odciindexinfo, rid VARCHAR2,
oldval PowerDemand_Typ) 
       RETURN NUMBER as 
       cid INTEGER; 
       stmt VARCHAR2(1000);
       nrows INTEGER; 
   BEGIN 
     dbms_output.put_line(' ');
     dbms_output.put_line('ODCIIndexDelete>>>>>'|| 
      ' TotGridDemand= '||oldval.TotGridDemand ||
      ' MaxCellDemand= '||oldval.MaxCellDemand ||
      ' MinCellDemand= '||oldval.MinCellDemand) ;
     sys.ODCIIndexInfoDump(ia); 
 
     -- Construct the statement.
     stmt := ' DELETE FROM '|| ia.IndexSchema || '.' || ia.IndexName
            || '_pidx' || ' WHERE r=:rr';
     dbms_output.put_line('ODCIIndexDelete>>>>>'||stmt);
 
     -- Parse and execute the statement.
     cid := dbms_sql.open_cursor;
     dbms_sql.parse(cid, stmt, dbms_sql.native);
     dbms_sql.bind_variable_rowid(cid, ':rr', rid);
     nrows := dbms_sql.execute(cid);     
     dbms_sql.close_cursor(cid);
 
     RETURN ODCICONST.SUCCESS;
   END ODCIIndexDelete;

ODCIIndexUpdate Method

The ODCIIndexUpdate member function is called when a record is updated in a table that contains columns or object attributes indexed by the indextype. The old and new values in the indexed columns are passed in as arguments along with the row identifier. When an array of rows get updated, arrays of old values, new values and row identifiers are passed.

  MEMBER FUNCTION ODCIIndexUpdate(ia sys.odciindexinfo, rid VARCHAR2, 
   oldval PowerDemand_Typ, newval PowerDemand_Typ) 
       RETURN NUMBER as 
       cid INTEGER; 
       cid2 INTEGER; 
       stmt VARCHAR2(1000);
       stmt2 VARCHAR2(1000);
       nrows INTEGER; 
       i NUMBER;
   BEGIN 
     dbms_output.put_line(' ');
     dbms_output.put_line('ODCIIndexUpdate>>>>> Old'|| 
      ' TotGridDemand= '||oldval.TotGridDemand ||
      ' MaxCellDemand= '||oldval.MaxCellDemand ||
      ' MinCellDemand= '||oldval.MinCellDemand) ;
     dbms_output.put_line('ODCIIndexUpdate>>>>> New'|| 
      ' TotGridDemand= '||newval.TotGridDemand ||
      ' MaxCellDemand= '||newval.MaxCellDemand ||
      ' MinCellDemand= '||newval.MinCellDemand) ;
     sys.ODCIIndexInfoDump(ia); 
 
     -- Delete old entries.
     stmt := ' DELETE FROM '|| ia.IndexSchema || '.' || ia.IndexName
            || '_pidx' || ' WHERE r=:rr';
     dbms_output.put_line('ODCIIndexUpdate>>>>>'||stmt);
  
     -- Parse and execute the statement.
     cid := dbms_sql.open_cursor;
     dbms_sql.parse(cid, stmt, dbms_sql.native);
     dbms_sql.bind_variable_rowid(cid, ':rr', rid);
     nrows := dbms_sql.execute(cid);     
     dbms_sql.close_cursor(cid);
 
     -- Insert new entries.
     stmt2 := ' INSERT INTO '|| ia.IndexSchema || '.' || ia.IndexName
            || '_pidx' || ' VALUES (:rr, :pos, :val)';
     dbms_output.put_line('ODCIIndexUpdate>>>>>'||stmt2);
 
     -- Parse and execute the statement.
     cid2 := dbms_sql.open_cursor;
     dbms_sql.parse(cid2, stmt2, dbms_sql.native);
     dbms_sql.bind_variable_rowid(cid2, ':rr', rid);
     
     -- Iterate over the rows of the Varray and insert them.
     i := newval.CellDemandValues.FIRST;   
     WHILE i IS NOT NULL LOOP
         -- Bind the row into the cursor for insert.
         dbms_sql.bind_variable(cid2, ':pos', i);   
         dbms_sql.bind_variable(cid2, ':val', newval.CellDemandValues(i));
         nrows := dbms_sql.execute(cid2);
         dbms_output.put_line('ODCIIndexUpdate>>>>>('|| 
                               'RID' || ' , '||
                               i   || ' , '||
                               newval.CellDemandValues(i)|| ')');
         i := newval.CellDemandValues.NEXT(i);
      END LOOP;
     dbms_sql.close_cursor(cid2);
 
     RETURN ODCICONST.SUCCESS;
   END ODCIIndexUpdate;

ODCIIndexUpdate is the last method defined in the CREATE TYPE BODY statement, which ends as follows:

END;
/

ODCIIndexGetMetadata Method

The optional ODCIIndexGetMetadata member function, if present, is called by the Export utility in order to write implementation-specific metadata (which is not stored in the system catalogs) into the export dump file. This metadata might be policy information, version information, per-user settings, and so on. This metadata is written to the dump file as anonymous PL/SQL blocks that are executed at import time, immediately before the associated index is created.

This method returns strings to the Export utility that comprise the code of the PL/SQL blocks. The Export utility repeatedly calls this method until a zero-length string is returned, thus allowing the creation of any number of PL/SQL blocks of arbitrary complexity. Normally, this method calls functions within a PL/SQL package in order to make use of package-level variables, such as cursors and iteration counters, that maintain state across multiple calls by Export.

For information about the Export and Import utilities, see the Oracle8i Utilities manual.

In the power demand cartridge, the only metadata that is passed is a version string of V1.0, identifying the current format of the index-organized table that underlies the domain index. The power_pkg.getversion function generates a call to the power_pkg.checkversion procedure, to be executed at import time to check that the version string is V1.0.

MEMBER FUNCTION ODCIIndexGetMetadata(ia sys.odciindexinfo, expversion 
VARCHAR2, newblock OUT PLS_INTEGER) 
  RETURN VARCHAR2 is 
 
BEGIN 
-- Let getversion do all the work since it has to maintain state across calls. 
 
  RETURN power_pkg.getversion (ia.IndexSchema, ia.IndexName, newblock); 
 
EXCEPTION 
  WHEN OTHERS THEN 
      RAISE; 
 
END ODCIIndexGetMetaData; 
 

The power_pkg package is defined as follows:

CREATE OR REPLACE PACKAGE power_pkg AS  
  FUNCTION getversion(idxschema IN VARCHAR2, idxname IN VARCHAR2,
      newblock OUT PLS_INTEGER) RETURN VARCHAR2;  
  PROCEDURE checkversion (version IN VARCHAR2);  
END power_pkg; 
/ 
SHOW ERRORS; 
 
CREATE OR REPLACE PACKAGE BODY power_pkg AS  
  
-- iterate is a package-level variable used to maintain state across calls 
-- by Export in this session.  
  
iterate NUMBER := 0;  
  
FUNCTION getversion(idxschema IN VARCHAR2, idxname IN VARCHAR2,  
      newblock OUT PLS_INTEGER) RETURN VARCHAR2 IS  
  
BEGIN  
  
-- We are generating only one PL/SQL block consisting of one line of code.
  newblock := 1; 
  
  IF iterate = 0 
  THEN  
-- Increment iterate so we'll know we're done next time we're called. 
    iterate := iterate + 1;  
  
-- Return a string that calls checkversion with a version 'V1.0' 
-- Note that export adds the surrounding BEGIN/END pair to form the anon. 
-- block... we don't have to.  
  
    RETURN 'power_pkg.checkversion(''V1.0'');';  
  ELSE  
-- reset iterate for next index  
    iterate := 0;  
-- Return a 0-length string; we won't be called again for this index. 
    RETURN '';  
  END IF;  
END getversion;  
  
PROCEDURE checkversion (version IN VARCHAR2) IS  
  
  wrong_version            EXCEPTION; 
 
BEGIN  
  IF version != 'V1.0' THEN 
     RAISE wrong_version; 
  END IF; 
END checkversion;  
  
END power_pkg;

Creating the Indextype

The power demand cartridge creates the indextype for the domain index. The specification includes the list of operators supported by the indextype. It also identifies the implementation type containing the OCDI index routines.

CREATE OR REPLACE INDEXTYPE power_idxtype
FOR
   Power_Equals(PowerDemand_Typ, NUMBER, NUMBER),
   Power_GreaterThan(PowerDemand_Typ, NUMBER, NUMBER),
   Power_LessThan(PowerDemand_Typ, NUMBER, NUMBER),
   Power_EqualsAny(PowerDemand_Typ, NUMBER),
   Power_GreaterThanAny(PowerDemand_Typ, NUMBER),
   Power_LessThanAny(PowerDemand_Typ, NUMBER)
USING power_idxtype_im;

Testing the Domain Index

This section explains the parts of the power demand example that perform some simple tests of the domain index. These tests consist of:

The statements in this section are available online in the example file (tkqxpwr.sql).

Creating and Populating the Power Demand Table

The power demand table is created with two columns:

Several rows are inserted, representing power demand data for two regions (1 and 2) for several hourly timestamps. For simplicity, values are inserted only into the first 5 positions of each grid (the remaining 95 values are set to null).

-- The next INSERT statements "cheat" by supplying
-- only 5 grid values (instead of 100).
  
-- First 5 INSERT statements are for region 1 (1 AM to 5 AM on
-- 01-Feb-1998), to get enough timestamps for a moving
-- average using the Time Series cartridge. (Time Series 
-- cartridge tests are in a separate file.)
 
INSERT INTO PowerDemand_Tab VALUES(1,
   PowerDemand_Typ(NULL, NULL, NULL, PowerGrid_Typ(55,8,13,9,5),
   to_date('02-01-1998 01','MM-DD-YYYY HH'))
);
 
INSERT INTO PowerDemand_Tab VALUES(1,
   PowerDemand_Typ(NULL, NULL, NULL, PowerGrid_Typ(56,8,13,9,3),
   to_date('02-01-1998 02','MM-DD-YYYY HH'))
);
 
INSERT INTO PowerDemand_Tab VALUES(1,
   PowerDemand_Typ(NULL, NULL, NULL, PowerGrid_Typ(55,8,13,9,3),
   to_date('02-01-1998 03','MM-DD-YYYY HH'))
);
  
INSERT INTO PowerDemand_Tab VALUES(1,
   PowerDemand_Typ(NULL, NULL, NULL, PowerGrid_Typ(54,8,13,9,3),
   to_date('02-01-1998 04','MM-DD-YYYY HH'))
);
 
INSERT INTO PowerDemand_Tab VALUES(1,
   PowerDemand_Typ(NULL, NULL, NULL, PowerGrid_Typ(54,8,12,9,3),
   to_date('02-01-1998 05','MM-DD-YYYY HH'))
);
 
-- Also insert some rows for region 2.
 
INSERT INTO PowerDemand_Tab VALUES(2,
   PowerDemand_Typ(NULL, NULL, NULL, PowerGrid_Typ(9,8,11,16,5),
   to_date('02-01-1998 01','MM-DD-YYYY HH'))
);
  
INSERT INTO PowerDemand_Tab VALUES(2,
   PowerDemand_Typ(NULL, NULL, NULL, PowerGrid_Typ(9,8,11,20,5),
   to_date('02-01-1998 02','MM-DD-YYYY HH'))
);

Finally, the values for TotGridDemand, MaxCellDemand, and MinCellDemand are computed and set for each of the newly inserted rows, and these values are displayed.

DECLARE
CURSOR c1 IS SELECT Sample, Region FROM PowerDemand_Tab FOR UPDATE;
s PowerDemand_Typ;
r NUMBER;
BEGIN
  OPEN c1;
  LOOP
     FETCH c1 INTO s,r;
     EXIT WHEN c1%NOTFOUND;
     s.SetTotalDemand;
     s.SetMaxDemand;
     s.SetMinDemand;
     dbms_output.put_line(s.TotGridDemand);
     dbms_output.put_line(s.MaxCellDemand);
     dbms_output.put_line(s.MinCellDemand);
     UPDATE PowerDemand_Tab SET Sample = s WHERE CURRENT OF c1;
  END LOOP;
  CLOSE c1;
END;
/

-- Examine the values. 
SELECT region, P.Sample.TotGridDemand, P.Sample.MaxCellDemand,
   P.Sample.MinCellDemand,
   to_char(P.sample.sampletime, 'MM-DD-YYYY HH') 
 FROM PowerDemand_Tab P;

Querying without the Index

The queries is this section are executed by applying the underlying function (PowerEqualsSpecific_Func) for every row in the table, because the index has not yet been defined.

The example file includes queries that check, both for a specific cell number and for any cell number, for values equal to, greater than, and less than a specified value. For example, the equality queries are as follows:

SET SERVEROUTPUT ON
-------------------------------------------------------------------
-- Query, referencing the operators (without index)
-------------------------------------------------------------------
explain plan for
SELECT P.Region, P.Sample.TotGridDemand ,P.Sample.MaxCellDemand, 
     P.Sample.MinCellDemand
   FROM PowerDemand_Tab P
   WHERE Power_Equals(P.Sample,2,10) = 1;
@tkoqxpll
 
SELECT P.Region, P.Sample.TotGridDemand ,P.Sample.MaxCellDemand, 
     P.Sample.MinCellDemand
   FROM PowerDemand_Tab P
   WHERE Power_Equals(P.Sample,2,10) = 1;
 
explain plan for
SELECT P.Region, P.Sample.TotGridDemand ,P.Sample.MaxCellDemand, 
     P.Sample.MinCellDemand
   FROM PowerDemand_Tab P
   WHERE Power_Equals(P.Sample,2,8) = 1;
@tkoqxpll
 
SELECT P.Region, P.Sample.TotGridDemand ,P.Sample.MaxCellDemand, 
     P.Sample.MinCellDemand
   FROM PowerDemand_Tab P
   WHERE Power_Equals(P.Sample,2,8) = 1;
 
explain plan for
SELECT P.Region, P.Sample.TotGridDemand ,P.Sample.MaxCellDemand, 
     P.Sample.MinCellDemand
   FROM PowerDemand_Tab P
   WHERE Power_EqualsAny(P.Sample,9) = 1;
@tkoqxpll
 
SELECT P.Region, P.Sample.TotGridDemand ,P.Sample.MaxCellDemand, 
     P.Sample.MinCellDemand
   FROM PowerDemand_Tab P
   WHERE Power_EqualsAny(P.Sample,9) = 1;

The execution plans show that a full table scan is performed in each case:

OPERATIONS      OPTIONS         OBJECT_NAME    
--------------- --------------- ---------------
SELECT STATEMEN                                
TABLE ACCESS    FULL            POWERDEMAND_TAB

Creating the Index

The index is created on the sample column in the power demand table.

CREATE INDEX PowerIndex ON PowerDemand_Tab(Sample) 
   INDEXTYPE IS power_idxtype;

Querying with the Index

The queries is this section are the same as those in "Querying without the Index", but this time the index is used.

The execution plans show that Oracle is using the domain index and not performing full table scans, thus resulting in more efficient execution. For example:

SVRMGR> -------------------------------------------------------------------
SVRMGR> -- Query, referencing the operators (with index)
SVRMGR> -------------------------------------------------------------------
SVRMGR> explain plan for
     2> SELECT P.Region, P.Sample.TotGridDemand ,P.Sample.MaxCellDemand, 
     3>      P.Sample.MinCellDemand
     4>    FROM PowerDemand_Tab P
     5>    WHERE Power_Equals(P.Sample,2,10) = 1;
Statement processed.
SVRMGR> @tkoqxpll
SVRMGR> set echo off
Echo                            OFF
Charwidth                       15
OPERATIONS      OPTIONS         OBJECT_NAME    
--------------- --------------- ---------------
SELECT STATEMEN                                
TABLE ACCESS    BY ROWID        POWERDEMAND_TAB
DOMAIN INDEX                    POWERINDEX     
3 rows selected.
Statement processed.
Echo                            ON
SVRMGR>  
SVRMGR> SELECT P.Region, P.Sample.TotGridDemand ,P.Sample.MaxCellDemand, 
     2>      P.Sample.MinCellDemand
     3>    FROM PowerDemand_Tab P
     4>    WHERE Power_Equals(P.Sample,2,10) = 1;
REGION     SAMPLE.TOT SAMPLE.MAX SAMPLE.MIN
---------- ---------- ---------- ----------
0 rows selected.
ODCIIndexStart>>>>>
ODCIIndexInfo
Index owner : POWERCARTUSER
Index name : POWERINDEX
Table owner : POWERCARTUSER
Table name : POWERDEMAND_TAB
Indexed column : "SAMPLE"
Indexed column type :POWERDEMAND_TYP
Indexed column type schema:POWERCARTUSER
ODCIPredInfo
Object owner : POWERCARTUSER
Object name : POWER_EQUALS
Method name : 
Predicate bounds flag :
     Exact Match
     Include Start Key
     Include Stop Key
start key : 1
stop key : 1
compare position : 2
compare value : 10
ODCIIndexStart>>>>>select r from POWERCARTUSER.POWERINDEX_pidx where cpos ='2' 
and cval ='10'
ODCIIndexFetch>>>>>
Nrows : 2000
ODCIIndexClose>>>>>
SVRMGR>  
SVRMGR> explain plan for
     2> SELECT P.Region, P.Sample.TotGridDemand ,P.Sample.MaxCellDemand, 
     3>      P.Sample.MinCellDemand
     4>    FROM PowerDemand_Tab P
     5>    WHERE Power_Equals(P.Sample,2,8) = 1;
Statement processed.
SVRMGR> @tkoqxpll
SVRMGR> set echo off
Echo                            OFF
Charwidth                       15
OPERATIONS      OPTIONS         OBJECT_NAME    
--------------- --------------- ---------------
SELECT STATEMEN                                
TABLE ACCESS    BY ROWID        POWERDEMAND_TAB
DOMAIN INDEX                    POWERINDEX     
3 rows selected.
Statement processed.
Echo                            ON
SVRMGR>  
SVRMGR> SELECT P.Region, P.Sample.TotGridDemand ,P.Sample.MaxCellDemand, 
     2>      P.Sample.MinCellDemand
     3>    FROM PowerDemand_Tab P
     4>    WHERE Power_Equals(P.Sample,2,8) = 1;
REGION     SAMPLE.TOT SAMPLE.MAX SAMPLE.MIN
---------- ---------- ---------- ----------
         1         90         55          5
         1         89         56          3
         1         88         55          3
         1         87         54          3
         1         86         54          3
         2         49         16          5
         2         53         20          5
7 rows selected.
ODCIIndexStart>>>>>
ODCIIndexInfo
Index owner : POWERCARTUSER
Index name : POWERINDEX
Table owner : POWERCARTUSER
Table name : POWERDEMAND_TAB
Indexed column : "SAMPLE"
Indexed column type :POWERDEMAND_TYP
Indexed column type schema:POWERCARTUSER
ODCIPredInfo
Object owner : POWERCARTUSER
Object name : POWER_EQUALS
Method name : 
Predicate bounds flag :
     Exact Match
     Include Start Key
     Include Stop Key
start key : 1
stop key : 1
compare position : 2
compare value : 8
ODCIIndexStart>>>>>select r from POWERCARTUSER.POWERINDEX_pidx where cpos ='2' 
and cval ='8'
ODCIIndexFetch>>>>>
Nrows : 2000
ODCIIndexClose>>>>>
SVRMGR>  
SVRMGR> explain plan for
     2> SELECT P.Region, P.Sample.TotGridDemand ,P.Sample.MaxCellDemand, 
     3>      P.Sample.MinCellDemand
     4>    FROM PowerDemand_Tab P
     5>    WHERE Power_EqualsAny(P.Sample,9) = 1;
Statement processed.
SVRMGR> @tkoqxpll
SVRMGR> set echo off
Echo                            OFF
Charwidth                       15
OPERATIONS      OPTIONS         OBJECT_NAME    
--------------- --------------- ---------------
SELECT STATEMEN                                
TABLE ACCESS    BY ROWID        POWERDEMAND_TAB
DOMAIN INDEX                    POWERINDEX     
3 rows selected.
Statement processed.
Echo                            ON
SVRMGR>  
SVRMGR> SELECT P.Region, P.Sample.TotGridDemand ,P.Sample.MaxCellDemand, 
     2>      P.Sample.MinCellDemand
     3>    FROM PowerDemand_Tab P
     4>    WHERE Power_EqualsAny(P.Sample,9) = 1;
REGION     SAMPLE.TOT SAMPLE.MAX SAMPLE.MIN
---------- ---------- ---------- ----------
         1         90         55          5
         1         89         56          3
         1         88         55          3
         1         87         54          3
         1         86         54          3
         2         49         16          5
         2         53         20          5
7 rows selected.
ODCIIndexStart>>>>>
ODCIIndexInfo
Index owner : POWERCARTUSER
Index name : POWERINDEX
Table owner : POWERCARTUSER
Table name : POWERDEMAND_TAB
Indexed column : "SAMPLE"
Indexed column type :POWERDEMAND_TYP
Indexed column type schema:POWERCARTUSER
ODCIPredInfo
Object owner : POWERCARTUSER
Object name : POWER_EQUALSANY
Method name : 
Predicate bounds flag :
     Exact Match
     Include Start Key
     Include Stop Key
start key : 1
stop key : 1
compare value : 9
ODCIIndexStart>>>>>select distinct r from POWERCARTUSER.POWERINDEX_pidx where 
cval ='9'
ODCIIndexFetch>>>>>
Nrows : 2000
ODCIIndexClose>>>>>

Using the Time Series Cartridge with the Power Demand Cartridge

This section describes how the Oracle8i Time Series Cartridge can be used with the Power Demand cartridge. With the Time Series cartridge, you can apply the many time series and time scaling functions, such as various averages (arithmetic, cumulative, moving) and scaleup, to the power demand data.

This section assumes that you are familiar with time series concepts and basic usage information for the Time Series cartridge. For detailed conceptual and usage information, see the Oracle8i Time Series User's Guide.

To use the Time Series cartridge with the power demand data, you must perform the following steps:

  1. Identify the timestamp column and other columns of interest in the power demand table.

  2. Define the calendars to be used.

  3. Create a mapping table to map regions to an hourly calendar.

  4. Create a reference-based view.

  5. Formulate time series queries.

This section does not discuss creating the underlying table for time series data or loading the data, because these operations were done in "Creating and Populating the Power Demand Table".

The following operations are not illustrated in this section, but should be included for use in a production environment:

Identifying Columns to be Used

The Time Series cartridge requires that each data row include the following:

Defining the Calendars

To define the calendars, you must create their definitions in a table of calendars. If the table of calendars does not already exist, create it first. (The calendar table might not exist because this is your first us of the Time Series cartridge; or a calendar table might exist for another application, but you choose to place power demand calendars in a separate table.)

The following statements create a table named PowerDemand_calendars and defines the first of three calendars, this one named PowerReadingsCal_Hourly. Explanatory notes follow the example.

-- Create the table for the calendars.
CREATE TABLE PowerDemand_calendars of ORDSYS.ORDTCalendar;

-- Create three calendars: first one for hourly readings, the
-- other two for daily and monthly scaleup operations.
INSERT INTO PowerDemand_calendars 
VALUES(
  ORDSYS.ORDTCalendar(
    0, 
    'PowerReadingsCal_Hourly', 
    3, 
    ORDSYS.ORDTPattern(ORDSYS.ORDTPatternBits(1), 
      to_date('01-01-1998 01','MM-DD-YYYY HH')),
    to_date('01-01-1998 01','MM-DD-YYYY HH'), 
    to_date('01-01-2008 01','MM-DD-YYYY HH'), 
    ORDSYS.ORDTExceptions(), 
    ORDSYS.ORDTExceptions() 
    )
  );

Notes on the preceding example:

PowerDemand_calendars is a table of ORDSYS.ORDTCalendar objects.

0 (zero) for calendar type (caltype) indicates that this is an exception-based calendar.

PowerReadingsCal_Hourly is the name of this calendar.

3 is the frequency code for hour.

The pattern is defined as having all timestamps included (1). Because there are no off timestamps (that is, power readings are taken for all hours), any hourly timestamp can be used for the anchor date ('01-01-1998 01' is used in this example).

The calendar begins at 1:00 AM on 01-Jan-1998 and ends at 1:00 AM on 01-Jan-2008

There are no off-exceptions (that is, no hours when readings are not taken) and no on-exceptions (which are impossible in this case anyway, because there are no zeroes in the calendar pattern).

The following statements create two additional calendars, for use with scaleup operations in which hourly power demand readings are scaled up to compute daily and monthly values:

INSERT INTO PowerDemand_calendars
VALUES(
  ORDSYS.ORDTCalendar(
    0,
    'PowerReadingsCal_Daily',
    4,
    ORDSYS.ORDTPattern(ORDSYS.ORDTPatternBits(1),
      to_date('01-01-1998','MM-DD-YYYY')),
    to_date('01-01-1998','MM-DD-YYYY'),
    to_date('01-01-2008','MM-DD-YYYY'),
    ORDSYS.ORDTExceptions(),
    ORDSYS.ORDTExceptions()
    )
  );
INSERT INTO PowerDemand_calendars
VALUES(
  ORDSYS.ORDTCalendar(
    0,
    'PowerReadingsCal_Monthly',
    6,
    ORDSYS.ORDTPattern(ORDSYS.ORDTPatternBits(1),
      to_date('01-01-1998','MM-DD-YYYY')),
    to_date('01-01-1998','MM-DD-YYYY'),
    to_date('01-01-2008','MM-DD-YYYY'),
    ORDSYS.ORDTExceptions(),
    ORDSYS.ORDTExceptions()
    )
  );

Creating the Mapping Table

Create a table to map regions to calendars, and insert a row for each region. In this example, regions 1 and 2 are associated with the PowerReadingsCal_Hourly calendar. This mapping allows the reference-based view to be used.

-- Create the metadata table and insert rows for two regions.
 
CREATE TABLE PowerDemand_metadata ( 
    region number,
    calendarname varchar2(30), 
    constraint  pk_PowerDemand_metadata primary key (region));
 
INSERT INTO PowerDemand_metadata VALUES(1, 'PowerReadingsCal_Hourly');
INSERT INTO PowerDemand_metadata VALUES(2, 'PowerReadingsCal_Hourly');

Creating the Reference-Based View

Create a reference-based view for convenient and efficient access to time series data.

The following statements create a reference-based view for the power demand data:

-- Create the reference view.
 
CREATE OR REPLACE VIEW PowerDemand_rv(region,TotGridDemand,
     MaxCellDemand,MinCellDemand,CellDemandValues) AS
  SELECT meta.region,
  ORDSYS.ORDTNumSeriesIOTRef(
          'TotGridDemand_ts', Ref(cal), 'PowerCartUser.PowerDemand_Tab T',
          'T.sample.SampleTime', 
          'T.sample.TotGridDemand', 
          'region', meta.region),
  ORDSYS.ORDTNumSeriesIOTRef(
          'MaxCellDemand_ts', Ref(cal), 'PowerCartUser.PowerDemand_Tab T',
          'T.sample.SampleTime', 
          'T.sample.MaxCellDemand', 
          'region', meta.region),
  ORDSYS.ORDTNumSeriesIOTRef(
          'MinCellDemand_ts', Ref(cal), 'PowerCartUser.PowerDemand_Tab T',
          'T.sample.SampleTime', 
          'T.sample.MinCellDemand', 
          'region', meta.region),
  ORDSYS.ORDTNumSeriesIOTRef(
          'CellDemandValues_ts', Ref(cal), 'PowerCartUser.PowerDemand_Tab T',
          'T.sample.SampleTime', 
          'T.sample.CellDemandValues', 
          'region', meta.region)
  FROM PowerDemand_metadata meta, PowerDemand_calendars cal
  WHERE meta.calendarname = cal.name;

Formulating Time Series Queries

Formulating time series queries involves invoking time series or time scaling functions, or both. The power demand example includes SQL and PL/SQL queries that return the following information:

The following example shows the execution of the Lead and Moving average functions with power demand data. This example includes the SQL statements and the output with ECHO and SERVEROUTPUT turned on.

SVRMGR> -- Lead: Using timestamps from 1:00 through 5:00 AM on 01-Feb-1998,
SVRMGR> -- create a time series of the number of timestamps 24 hours later.
SVRMGR> -- The result is a time series from 1:00 through 5:00 AM on 02-Feb-1998
SVRMGR> -- containing the same TotGridDemand values as the corresponding
SVRMGR> -- timestamps on 01-Feb-1998.
SVRMGR> --
SVRMGR> SELECT * FROM the 
     2>   (SELECT CAST(ORDSYS.TIMESERIES.ExtractTable(
     3>       ORDSYS.TIMESERIES.Lead(ts.TotGridDemand, 24, 
     4>         to_date('01-FEB-98 01','DD-MON-YY HH'),
     5>         to_date('01-FEB-98 05','DD-MON-YY HH'))
     6>       ) AS ordsys.ordtNumTab)
     7> FROM PowerCartUser.powerdemand_rv ts
     8> WHERE region = 1);
TSTAMP    VALUE     
--------- ----------
02-FEB-98         90
02-FEB-98         89
02-FEB-98         88
02-FEB-98         87
02-FEB-98         86
5 rows selected.
SVRMGR> 
SVRMGR> --
SVRMGR> -- Compute a moving average over a window of 3 timestamps
SVRMGR> -- for region 1.
SVRMGR> --
SVRMGR> 
SVRMGR> SELECT * FROM the 
     2>   (SELECT CAST(ORDSYS.TIMESERIES.ExtractTable(
     3>                ORDSYS.TIMESERIES.Mavg(ts.TotGridDemand,3)
     4>               ) AS ordsys.ordtNumTab)
     5> FROM PowerCartUser.powerdemand_rv ts
     6> WHERE region = 1);
TSTAMP    VALUE     
--------- ----------
01-FEB-98           
01-FEB-98           
01-FEB-98         89
01-FEB-98         88
01-FEB-98         87
5 rows selected.

Figure 11-9 Power Regions and Area of Interest (Spatial Cartridge)

Using the Spatial Cartridge with the Power Demand Cartridge

This section describes how the Oracle8i Spatial Cartridge can be used with the Power Demand cartridge. With the Spatial cartridge, you can perform geospatial queries against the power demand regions to select power demand data from areas of interest.

This example defines some regions with rectangular coordinates, and it also defines a rectangular area of interest that partially overlaps region 1 but does not touch regions 2 or 3. This area of interest might reflect the extent of some natural phenomenon, such as a mass of cold air or the path of a tornado (although an actual representation would require more than a simple rectangle). Figure 11-9 shows four regions, each represented as a square on a grid, and the area of interest represented as a gray rectangle. The figure uses a very simple (and arbitrary) coordinate system.

The queries select power demand data from regions that are overlapped in any way by the area of interest. In this example, the queries return data only from region 1. Note that this simple example considers the entire region; it does not provide coordinates for individual cells (neighborhoods), and it does not check which specific cells within region 1 are inside the area of interest.

This section assumes that you are familiar with the concepts and basic usage information for the Spatial cartridge. For detailed conceptual and usage information, see the Oracle8i Spatial User's Guide and Reference.

To use the Spatial cartridge with the power demand data, you must perform the following steps:

  1. Create a Spatial layer consisting of four tables, with the geometry table related to the PowerDemand_tab table by region.

  2. Create coordinates (x,y) for the power demand regions.

  3. Populate the power demand regions index.

  4. Create coordinates (x,y) for the area of interest to be used in queries against the power demand regions.

  5. Populate the windows index.

  6. Perform spatial queries.

Creating the Spatial Layer (PowerDemandRegions)

To use the Spatial cartridge with power demand data, create a Spatial layer (called PowerDemandRegions in this example) consisting of four tables, with the geometry table (PowerDemandRegions_SDOGEOM) related to the PowerDemand_tab table by the region attribute.

The following statements define this layer.

-- Simple scenario for integrating Spatial Cartridge.
-- Regions have spatial extent (e.g., states of the U.S.). 
-- A person wanting to select data may either want data for 
-- predefined regions or data for those units (such as
-- states) that intersect a dynamically created 
-- area-of-interest (such as one you would draw when choosing 
-- a zoom area from a Web-based mapping service).  
 
-- Create a Spatial layer called PowerDemandRegions. This 
-- consists of four tables, and the geometry table is related to 
-- the PowerDemand_Tab table by Region. 
set serveroutput on
set echo on
 
CREATE TABLE PowerDemandRegions_SDODIM ( 
  sdo_dimnum number, 
  sdo_dimname varchar2(32), 
  sdo_lb number, 
  sdo_ub number, 
  sdo_tolerance number ); 
 
CREATE TABLE PowerDemandRegions_SDOLAYER ( 
  sdo_ordcnt number, 
  sdo_level number,
  sdo_numtiles number ); 
 
-- In the following definition of PowerDemandRegions_SDOGEOM,
-- sdo_gid equates to Region in PowerDemand_Tab. 
CREATE TABLE PowerDemandRegions_SDOGEOM ( 
  sdo_gid number, 
  sdo_eseq number, 
  sdo_etype number,  
  sdo_seq number, 
  sdo_x1 number, 
  sdo_y1 number, 
  sdo_x2 number, 
  sdo_y2 number, 
  sdo_x3 number, 
  sdo_y3 number, 
  sdo_x4 number, 
  sdo_y4 number, 
  sdo_x5 number, 
  sdo_y5 number ) ; 
CREATE TABLE PowerDemandRegions_SDOINDEX ( 
  sdo_gid number, 
  sdo_code raw(255),
  sdo_meta raw(255)); 
 
-- Create some coordinates for the example and show how an 
-- index is created etc. We will also assume that some windows of 
-- interest have been pre-defined and stored in a Window_layer. 
 
CREATE TABLE Windows_SDODIM ( 
  sdo_dimnum number, 
  sdo_dimname varchar2(32), 
  sdo_lb number, 
  sdo_ub number, 
  sdo_tolerance number ); 
 
CREATE TABLE Windows_SDOLAYER ( 
  sdo_ordcnt number, 
  sdo_level number,
  sdo_numtiles number ); 
 
CREATE TABLE Windows_SDOGEOM ( 
  sdo_gid number, 
  sdo_eseq number, 
  sdo_etype number,  
  sdo_seq number, 
  sdo_x1 number, 
  sdo_y1 number, 
  sdo_x2 number, 
  sdo_y2 number, 
  sdo_x3 number, 
  sdo_y3 number, 
  sdo_x4 number, 
  sdo_y4 number, 
  sdo_x5 number, 
  sdo_y5 number ); 
 
CREATE TABLE Windows_SDOINDEX ( 
  sdo_gid number, 
  sdo_code raw(255),
  sdo_meta raw(255)); 

Creating Coordinates for the Power Demand Regions

To enable the Spatial cartridge to recognize and work with the power demand regions, populate the Spatial layer tables with the necessary data. The following statements provide data, including x,y coordinates for region boundary corners, for three regions.

-- Populate the tables for power regions.
INSERT INTO PowerDemandRegions_SDODIM VALUES(
 1,
 'x-axis',
 0,
 100,
 0.0005);
INSERT INTO PowerDemandRegions_SDODIM VALUES(
 2,
 'y-axis',
 0,
 100,
 0.0005);
INSERT INTO PowerDemandRegions_SDOLAYER VALUES(
 10,
 5,
 NULL);
INSERT INTO PowerDemandRegions_SDOGEOM VALUES(
 1,
 0,
 3,
 1,
 2,2,
 4,2,
 4,4,
 2,4,
 2,2);
INSERT INTO PowerDemandRegions_SDOGEOM VALUES(
 2,
 0,
 3,
 1,
 4,2,
 6,2,
 6,4,
 4,4,
 4,2);
INSERT INTO PowerDemandRegions_SDOGEOM VALUES(
 3,
 0,
 3,
 1,
 6,2,
 8,2,
 8,4,
 6,4,
 6,2);

Populating the Power Demand Regions Index

The following statement populates the index for the Spatial layer PowerDemandRegions.

-- Populate the index for Spatial layer PowerDemandRegions 
EXECUTE sdo_admin.populate_index('POWERDEMANDREGIONS');

Creating Coordinates for the Area of Interest

To enable the Spatial cartridge to recognize and work with the area of interest, populate the Windows layer tables with the necessary data. The following statements provide data, including x,y coordinates for the boundary corners, for the area of interest.

-- Populate the tables for region of interest.
INSERT INTO Windows_SDODIM VALUES(
 1,
 'x-axis',
 0,
 100,
 0.0005);
INSERT INTO Windows_SDODIM VALUES(
 2,
 'y-axis',
 0,
 100,
 0.0005);
INSERT INTO Windows_SDOLAYER VALUES(
 10,
 5,
 NULL);

-- The next INSERT creates a small rectangle that
-- partially overlaps region 1 but does not touch
-- regions 2 or 3.

INSERT INTO Windows_SDOGEOM VALUES(
 1,
 0,
 3,
 1,
 2,3,
 3,3,
 3,5,
 2,5,
 2,3);

Populating the Windows Index

The following statement populates the index for the Windows layer.

-- Populate the index for the WINDOWS layer (Windows)
EXECUTE sdo_admin.populate_index('WINDOWS');

Performing Spatial Queries

The following queries use the SDOGEOM.Relate function to retrieve data from regions that are within or overlapping the area of interest. Because only region 1 overlaps the area of interest, only rows from region 1 are considered. Each row returned reflects power demand data for a particular hourly timestamp (for example, aggregate data for region 1 at 5 AM on 01-Feb-1998).

The first query returns only rows for which the third cell has a power demand reading of 12. Given the actual sample data (see the INSERT statements in "Creating and Populating the Power Demand Table"), only one row meets this criterion.

Each of the following statements performs this query, but the second one uses the spatial index for primary filtering (for performance reasons).

-- Now some queries.
-- Query 1:  
-- Find Regions Within_Or_Overlapping the Area-of-Interest 1 
-- whose 3rd Cell has a power demand value of 12.
 
Select P.Region, P.Sample.TotGridDemand, P.Sample.MaxCellDemand, 
P.Sample.MinCellDemand 
FROM PowerDemand_Tab P 
WHERE Power_Equals(P.Sample, 3, 12) = 1  
AND P.Region IN ( 
Select S.sdo_gid 
from powerdemandregions_sdogeom S, windows_sdogeom 
where  
sdo_geom.Relate('PowerDemandRegions', S.sdo_gid, 'ANYINTERACT', 'WINDOWS', 1) = 
'TRUE' ); 

-- Use the spatial index for primary filtering (for performance reasons)

Select P.Region, P.Sample.TotGridDemand, P.Sample.MaxCellDemand, 
P.Sample.MinCellDemand 
FROM PowerDemand_Tab P 
WHERE Power_Equals(P.Sample, 3, 12) = 1  
AND P.Region IN ( 
Select sdo_gid gid1 
from (select distinct s.sdo_gid from powerdemandregions_sdoindex S, 
      windows_sdoindex w
      where s.sdo_code = w.sdo_code and w.sdo_gid = 1)
where  
sdo_geom.Relate('PowerDemandRegions', sdo_gid, 'ANYINTERACT', 'WINDOWS', 1) = 
'TRUE' ); 

Both queries return the following result:

REGION     SAMPLE.TOT SAMPLE.MAX SAMPLE.MIN
---------- ---------- ---------- ----------
         1         86         54          3
1 row selected.

The second query returns only rows for which any cell has a power demand reading of 9. Given the actual sample data (see the INSERT statements in "Creating and Populating the Power Demand Table"), five rows meets this criterion.

-- Query 2: Same thing for PowerEqualsAny() - in this case where
-- any cell has a power demand value of 9.
 
Select P.Region, P.Sample.TotGridDemand, P.Sample.MaxCellDemand, 
P.Sample.MinCellDemand 
FROM PowerDemand_Tab P
WHERE Power_EqualsAny(P.Sample, 9) = 1  
AND P.Region IN ( 
Select S.sdo_gid 
from powerdemandregions_sdogeom S, windows_sdogeom 
where  
sdo_geom.Relate('PowerDemandRegions', S.sdo_gid, 'ANYINTERACT', 'WINDOWS', 1) = 
'TRUE' ); 

This query returns the following result:

REGION     SAMPLE.TOT SAMPLE.MAX SAMPLE.MIN
---------- ---------- ---------- ----------
         1         90         55          5
         1         89         56          3
         1         88         55          3
         1         87         54          3
         1         86         54          3
5 rows selected.




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index