Suppose we have two view objects in out model-project:
First we need to define a view accessor to the DepartmentsView.
|fig 2 - List of Values|
To show the effect of this default LOV definition I've added some logging information in the executeQueryForCollection method of the view objects base-class.
Now suppose we create a simple employees.jspx view and we drag-and-drop the EmployeesView as a table from the datacontrol-pallet to the page. The page will look something like this
|fig 3 - employees.jspx|
|fig 4 - logging|
As you can see, the executeQueryForCollection is called once for the EmployeesView, as expected, but many times for the DepartmentsView. The query to determine the choice-list values for the field DepartmentId is executed for every row in the EmployeesView. This is unnecessary and can have a negative effect on the performance of your application.
The behavior is caused by a property of the view accessor. If we take a closer look to the Property Inspector in fig. 1, we see a property 'Row Level Bind Values' with a value 'true'. The purpose of this property is to indicate that there are bind variables defined in the lookup view object that can have a different value for each row in the base view object. In out situation that is not the case. A valid example would be a list of values for the attribute ManagerId, where the list of managers is limited to the department of the employee.
Now lets turn the value of the 'Row Level Bind Values' to 'false' and run the screen again. This generates the following log-information:
|fig 5 - logging with Row Level Bind Values = false|
Setting the value of the property to empty will have the same effect, as long as there are now bind variables in the lookup view object at all. If there are bind variables, an empty value will have the same effect as the value 'true'. This seems logical, but if you assign literal values to the bind variables instead of 'row level bindings', it is still undesired.
The default behavior of JDeveloper when creating a view accessor is that it will set the property 'Row Level Bind Values' to 'true'. As we have seen this can have a (very) negative effect on the number of queries executed, and the performance of your application. Leaving the property blank works in many cases, but not always. My advise is to set this value explicitly to 'false' if you have no bind variables that really depend on some attribute values in the row.
The problem is that property is not visible in the create/edit window for a view accessor. Furthermore JDeveloper has it's own ideas of what the value for this property should be. Sometimes when you change something in the view object, the value for the property is changed automatically. You should be aware of this.
To deal with this issue I've created a small extension for JDeveloper that defines an auditrule that checks for the correct settings of the 'Row Level Bind Values' property. It also provides an automatically fix for the settings. I will soon make this extension available.
Remarks: I'm working with JDeveloper 126.96.36.199. I don't know the behavior of JDeveloper 11.1.2.* for this issue.