Specifying Fetch Sizes and Fetch Blocks

The number of elements of a Cursor that Oracle OLAP sends to the client application during one fetch operation depends on the fetch size specified for that Cursor. For a CompoundCursor, you can set the fetch size on the CompoundCursor itself or at one or more levels of its descendent Cursor components. Setting the fetch size on a CompoundCursor specifies that fetch size for its child Cursor components.

The set of elements the Cursor retrieves in a single fetch is the fetch block. The shape of the fetch block is determined by the set of Cursor components on which you set the fetch sizes. For more information on fetch sizes and fetch blocks, see Chapter 8.

You specify the shape of the fetch block and the specific fetch sizes according to the needs of your display of the data. To display the results of a query in a table view, you specify the fetch size on the top-level CompoundCursor.

To display the results in a crosstab view, you specify the fetch sizes on the children of the top-level CompoundCursor. For a crosstab that displays the results of a query that has nested levels of outputs, you might specify fetch sizes at different levels of the children of the component CompoundCursor objects.

You use methods on a CursorSpecification to set the default fetch size for its Cursor. For a CompoundCursorSpecification, you can specify setting the fetch sizes on its children and thereby determine the shape of the fetch block.

If a default fetch size is set on a CursorSpecification, you can use the setFetchSize method on the Cursor for that CursorSpecification to change the fetch size of the Cursor. By default, the root CursorSpecification of a CursorManagerSpecification has the fetch size set to 100.

Example 9-13 creates a Source that represents the sales amount measure values as specified by selections of values from the dimensions of the measure. The product and customer selections each have ten values, the time selection has four values, and the promotion and channel selections each have one value. Assuming that a sales amount exists for each set of dimension values, the result set of the query has 300 elements (10*10*3*1*1).

To match a display of the elements that contains only twenty rows, the example sets a fetch size of twenty elements on the top-level CompoundCursor. Because the default fetch size is automatically set on the root CursorSpecification, which in this example is the CompoundCursorSpecification for the top-level CompoundCursor, the example just uses the setFetchSize method on the CompoundCursor to change the fetch size. The fetch block is the set of output and base values specified by twenty positions of the top-level CompoundCursor. The TransactionProvider is tp and the DataProvider is dp.

Example 9-13 Specifying the Fetch Size and Fetch Block for a Table View

Source salesAmountsForSelections = salesAmount.join(customerSel)
                                              .join(productSel);
                                              .join(timeSel);
                                              .join(channelSel);
                                              .join(promotionSel);
try{
  tp.prepareCurrentTransaction();
}
catch(NotCommittableException e){
  output.println("Caught exception " + e + ".");
}
tp.commitCurrentTransaction();

// Create a Cursor for salesAmountsForSelections
CursorManagerSpecification cursorMngrSpec =
     dp.createCursorManagerSpecification(salesAmountsForSelections);
SpecifiedCursorManager cursorMngr = dp.createCursorManager(cursorMngrSpec);
Cursor cursor = cursorMngr.createCursor();

// Set the fetch size of the top-level CompoundCursor to 20
cursor.setFetchSize(20);

Example 9-14 modifies the example in Example 9-7. In Example 9-14, the number of times that the for loops are repeated depends upon the extent of the Cursor. As the conditional statement of the for loops, instead of specifying the number of positions that the Cursor has, this example gets the extent of the Cursor and uses the extent as the condition. The optimal fetch block for the crosstab display is a fetch block that contains, for each position of the CompoundCursor, the extent of the child Cursor elements at that position.

This example creates a CursorManagerSpecification and gets the root CursorSpecification. It casts the root CursorSpecification as a CompoundCursorSpecification. The example specifies setting the default fetch sizes on the children of the root CompoundCursorSpecification and it specifies the calculation of its extent.

The example sets the fetch size on each output ValueCursor equal to the extent of the ValueCursor. It then gets the displayable portion of the crosstab by looping through the positions of the child ValueCursor objects.

Example 9-14 Using Extents To Specify the Fetch Sizes for a Crosstab View

Source unitPriceByDay = unitPrice.join(productSel)
                                 .join(timeSel);
try{
  tp.prepareCurrentTransaction();
}
catch(NotCommittableException e){
  output.println("Caught exception " + e + ".");
}
tp.commitCurrentTransaction();

// Create a CursorManagerSpecification for unitPriceByDay
CursorManagerSpecification cursorMngrSpec =
            dp.createCursorManagerSpecification(unitPriceByDay);

// Get the root CursorSpecification and cast it to a
// CompoundCursorSpecification
CompoundCursorSpecification rootSpec = 
(CompoundCursorSpecification) cursorMngrSpec.getRootCursorSpecification();

// Specify setting the fetch size on the child Cursor objects
// and calculating the extent of the positions in the Cursor
rootSpec.specifyDefaultFetchSizeOnChildren();
rootSpec.setExtentCalculationSpecified(true);

// Create the CursorManager and the Cursor
SpecifiedCursorManager cursorMngr = 
                             dp.createCursorManager(cursorMngrSpec);
Cursor unitPriceByDayCursor = cursorMngr.createCursor();

// Cast the Cursor to a CompoundCursor
CompoundCursor rootCursor = (CompoundCursor) unitPriceByDayCursor;

// Determine a starting position and the number of rows to display.
// The position in columnCursor at which the current display starts
// is colStart and rowStart is the position in rowCursor at which
// the current display starts.
int colStart = 1;
int rowStart = 1;
String productValue;
String timeValue;
double price;

// The number of values from the ValueCursor objects for products and
// days are now initialized as 1 because the ValueCursor objects have
// at least one element.
int numProducts = 1;
int numDays = 1;

// Get the ValueCursor and the outputs
CompoundCursor rootCursor = (CompoundCursor) unitPriceByDayCursor;
List outputs = rootCursor.getOutputs();
// The first output has the values of timeSel, the slower varying output
ValueCursor rowCursor = (ValueCursor) outputs.get(0);
// The second output has the faster varying values of productSel
ValueCursor columnCursor = (ValueCursor) outputs.get(1);
ValueCursor unitPriceValues = rootCursor.getValueCursor();// Prices

// Loop through the positions of the faster varying output Cursor
for(int pPos = colStart; pPos < colStart + numProducts; pPos++) {
  columnCursor.setPosition(pPos);
  // Get the extents of the output ValueCursor objects
  numProducts = columnCursor.getExtent();
  numDays = rowCursor.getExtent();
  // Set the fetch sizes
  columnCursor.setFetchSize(numProducts);
  rowCursor.setFetchSize(numMonths);
  // Loop through the positions of the slower varying output Cursor
  for(int tPos = rowStart; tPos < rowStart + numDays; tPos++) {
    rowCursor.setPosition(tPos);

    // Get the values. Sending the values to the appropriate
    // display mechanism is not shown.
    productValue = columnCursor.getCurrentString();
    timeValue = rowCursor.getCurrentString();
    price = unitPriceValues.getCurrentDouble();
  }
}