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:

• The desired information can be made an attribute (column) of the table and a standard index can be defined on that column.

• The operations in queries on the data are limited to those operations supported by the standard index, such as equals, lessthan, greaterthan, max, and min for a b-tree index.

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:

• Find the cell with the highest power demand for a specific time.

• Find the time when the total grid power demand was highest.

• Find all cells where the power demand is greater than a specified value.

• Find the times for which the average cell demand or the median cell demand was greater than a specified value.

  To make this query run efficiently, define two additional columns in the PowerDemand_Typ object type (AverageCellDemand and MedianCellDemand), and create functions to set the values of these columns. (For example, AverageCellDemand is TotGridDemand divided by 100.) Then, create b-tree indexes on the AverageCellDemand and MedianCellDemand columns.

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:

• Find the first cell for a specified time where the power demand was equal to a specified value.

  By asking for the first cell, the query goes beyond a simple true-false check (such as finding out whether any cell for a specified time had a demand equal to a specified value), and thus benefits from a domain index.

• Find the time for which there was the greatest disparity (difference) between the cell with the minimum demand and the cell with the maximum demand.

• Find all times for which 3 or more cells had a demand greater than a specified value.

• Find all times for which 10 or more cells had a demand not less than a specified value.

• Find all times for which the total grid demand rose 10 percent or more over the preceding time's total grid demand.