View Criteria: Database vs Memory vs Both

Question

I was wondering if someone knows what is the best practice for using the Query Execution Mode inside the ADF? From what I understand:

Database: The search results are filtered from the Database table every time the query is hit. In Memory: The results are filtered from the VO cache memory. It will use the rows which are already in the row set. It will stop the unnecessary hits to the DB. Both: The results are filtered from the existing row set and also from the filtered results from Database. This is useful when you want to filter from uncommitted records also.

The reason why I am asking is because I had some issues with using Both option. My bind variables weren't properly updated.

Thank you in advance!

Answer 1

From my experience you should always use Database unless you need to filter on newly created and not yet commited row.

Here is the different use case between Database vs Memory vs Both

Database mode allow to query the Database table and ensure the data you serve your user is up to date. If you just created a new row and didn't commit it to the database, applying the view criteria on a view object in database mode will not display this new row. This is the default mode.

Memory mode allow to query the View Object cache data. So executing the query will display your newly created and not commited row but will not display any newly created row in database.

Both mode allow to display your newly created and not commited row and display any newly created row in database. But it's usually quite slow for some reason.

You can read more here (https://docs.oracle.com/middleware/12213/adf/develop/working-programmatically-view-objects.htm#ADFFD1223)

By default a view object performs its query against the database to retrieve the rows in its resulting row set. However, you can also use view objects to perform in-memory searches and sorting to avoid unnecessary trips to the database. This type of operation is ideal for sorting and filtering the new, as yet unposted rows of the view object row set; otherwise, unposted rows are added to the top of the row set.