SQL & PL/SQL

Hey Guys,

I have a particular query that is taking a long time to execute. After spending some time on the query, I have decided to go the route of a materialized view.

In my application, I have unit tests. These unit tests basically load up data in a transaction, run tests, then rollback the transaction. This is nice because the data never gets inserted into the database and there is no cleanup required. If I use a materialized view (using on-demand refresh), however, the data will be inserted into the database (since there is an implicit commit).

One possible solution to get around the commit issue, is to call the refresh in an autonomous transaction. This works great, but I think if I do this my tests will fail (since the test data was never committed). Is there any way around this?

From my perspective, I think I have three options:

1. Do not run automated test cases on this data.
2. Write a script to delete test data from the database for these particular cases.
3. Do not use a materialized view

Lastly, I have a question about the refreshing of a materialized view. It seems as though there are two options 1. Refresh view whenever table data gets modified 2. Refresh view on demand. Is there any option to have the view refresh itself whenever necessary? From the database side, it seems like this should be easy. Oracle could check the date the view was last refreshed and compare it to the dates the tables were last modified.

Thanks,
Ryan