Globally setting the Row Fetch Limit for all ViewObjects

There is an interesting feature available with 11gR1PS3(11.1.1.4.0) release which may help you to set a default 'row fetch limit' for all ViewObjects at application level. This is very useful to avoid expensive table scan if you don't have maxfetchsize defined for individual ViewObjects. Apparently, this 'global setting' fits well for those ViewObjects where each query execution may result in large number of records. Please note that, even the 'row count' query issued by the framework may also result in performance issues if the table is having huge chunks of data.

How do you set the 'Row Fetch Limit' globally?

This value can be configured using 'rowLimit' under <adf-adfm-config> section of <adf-config> file from your application.

<adf-adfm-config xmlns="http://xmlns.oracle.com/adfm/config">
<defaults useBindVarsForViewCriteriaLiterals="true" rowLimit="10000"/>
   ...
</adf-adfm-config>

If you would like to edit this configuration visually, then please use the <adf-config> editor to key in value for 'Row Fetch Limit', as shown in the following screen shot.


How does 'Row Fetch Limit' help to limit the fetch size at runtime?

From the UI perspective, when the user scrolls down beyond the 'row fetch limit' the run time would generate oracle.jbo.RowLimitExceededWarning and the same would be displayed as a message dialog to the user. The same exception is thrown if you try to access the row(s) programmatically as well, exceeding the fetch size.

oracle.jbo.RowLimitExceededWarning: JBO-25089: View object AppModule.EmployeesView1 attempted to fetch rows beyond the row limit
  at oracle.jbo.server.ViewObjectImpl.createRowFromResultSet(ViewObjectImpl.java:5765)
  at oracle.jbo.server.ViewObjectImpl.createInstanceFromResultSet(ViewObjectImpl.java:5588)
  at oracle.jbo.server.QueryCollection.populateRow(QueryCollection.java:3528)


How do you override the global Row Fetch Limit for a specific ViewObject?

It is always possible to override the global row fetch limit for specific ViewObject(s) based on your use cases. There are two possible approaches,

1. Specify maxfetchsize for the ViewObject. You can see this option under ViewObject's tuning section in the editor window.

2. Override ViewObjectImpl::getRowLimit() from your ViewOblectImpl to return -1.

/**
* A limit may be applied on the number of rows in a query collection. When
* a new row is requested from the database and the limit is exceeded a
* warning is raised. By default the limit is configured as an application
* wide setting in adf-config.xml.
* @return a limit on the number of rows in a query collection. If no row limit is enforced
*/
@Override
protected long getRowLimit()
{
  return -1;
}

Comments

  1. We get this issue in the UI where RowCountThreshold is not set in an iterator binding. Should this be set to -1 instead of overriding getRowLimit()?

    ReplyDelete
  2. getRowLimit() for a specifc VO overrides the the global setting on the 'max fetch limit'.
    Reg 'RowCountThreshold' the value determines whether to execute count query based on the values.Copying the excerpts from from Dev guide...
    -----------------------------------
    Specify a value to determine if a result set returns the number of rows you specify as a value. If you set RowCountThreshold to 0, the iterator returns the estimated row count in the result set by executing the count query. If you set RowCountThreshold to less than 0, the iterator does not execute the count query.

    Set RowCountThreshold to a value greater than 0 if you want the iterator to execute the count query with the maximum value equal to the value you specify for RowCountThreshold. If the estimated row count is less than the value of RowCountThreshold, return the number of rows in the estimated row count. If the estimated row count is greater than the value of RowCountThreshold, return -1.

    ReplyDelete
  3. Is there a way to know the value of the max fetch size programatically?

    I mean, is there a method like getMaxFetchSize() that returns the value set in the adf-config.xml?

    ReplyDelete
  4. Check this out - http://jobinesh.blogspot.com/2011/10/reading-adf-configxml-entires.html

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. I could not understand to the exact point.

    Consider in the pagedef we defined like

    Iterator Binds ="PVO" RangeSize="25" DataControl="QAMDataControl" id="SIterator" RowCountThreshold="300"


    In VO given these values for the properties :

    Query Optimizer hint : FIRST_ROWS(10)
    Access Mode: Scrollable
    Range Size: 1
    Fetch Mode: Fetch as needed

    Suppose if i override the getRowLimit() in my VO, too avoid row limit error.And in the query am getting 600 rows and in the rowcountthreshold i have given 300 ..What will happen exactly..could you please clarify this?

    ReplyDelete

Post a Comment