Monday, November 4, 2013

Smart Declarative Mode Support in ADF BC View Object Join

Declarative mode is known feature of ADF BC and promoted by Steve Muench back in 2008 - Declarative Data Filtering. Declarative mode allows to construct SQL statement on runtime dynamically, based on displayed attributes and ADF bindings in page definition. This is specifically useful for systems created with ADF, where tables are generic and contain long list of attributes. Instead of loading all attributes from DB, it makes perfect sense to load only required ones. There is one more cool feature of declarative mode - it knows how control SQL join on View Object level. Meaning - if attributes coming from joined EO are not rendered, ADF BC will update SQL statement and remove such join. Runtime control for joins is really important, as it may give real additional performance to the system.

Here you can see, what I'm talking about - Jobs EO is joined into Employees VO to provide additional attributes:


Empoyees VO is configured with Declarative mode - SQL statement is hidden, as it will be constructed dynamically on runtime:


Here is important hint - as you might be confused why Declarative mode doesn't work. You must go and set Selected in Query = false for each attribute in the VO. It seems to me like a JDEV bug, this should happen automatically. In this example, Selected in Query = false is set for Email attribute and all others:


There are two attributes added from joined Jobs EO - JobTitle and JobId. These attributes are set with Selected in Query = false as well:


There are two ADF task flows created for test purposes. Both are set with Isolate mode, in order to maintain separate instances of Employees VO. I will be using different sets of attributes in both of these task flows, to demonstrate that Declarative mode really works:


First ADF task flow brings table with 6 attributes. You should pay attention - there are no JOBS related attributes coming from the join:


Make sure ADF Logger is set to FINEST level for oracle.jbo package, this would allow to see a log output from ADF BC and spot executed SQL statement:


Table from above generates SQL statement for 6 attributes, as expected. There is one more great thing - SQL join with JOBS table is skipped, as there are no attributes displayed from that join:


Go to the second ADF task flow, here we are displaying 3 attributes only. One of these attributes - JobTitle, coming from the SQL join:


As it would be expected - SQL join is constructed in this case and 3 attributes are included into SQL query. This would result in less traffic between DB and the server, comparing to the default case when we would fetch all attribute values:


Download sample application - DeclarativeModeApp.zip.

No comments: