SQL & PL/SQL

We're developing some complex queries, multi-table joins, which take many minutes to execute. We, the developers, want to make the queries into materialized views. This would require basically wrapping the query with the create materialized view command with a complete refresh nightly, such as trunc(sysdate) + 1 + 22/24. As they are complex queries, materialized view logs are not an option. We feel that once we set it up, that's pretty much it. No maintenance other than whether or not the refresh works. An alert that queries the materialized view (or table) for stale last refresh dates will tell us that. This seems easy-peasy.

The DBA strongly reject this idea. They want us to create a table, create a PL/SQL package to clear it (delete or truncate) and populate and create a concurrent job to run it nightly. They feel that this will have less maintenance. When we ask what maintenance problem there is with materialized views is that they'd have to keep track of the logs (which aren't an option for complex queries) and that other users may want to query the data and that there will be problems when upgrading to another version.

This makes absolutely no sense to us. Logs are irrelevent, they can't exist. What relevence is there that others are querying as well? Concurrent jobs and custom PL/SQL packages are more likely to be impacted by upgrades than materialized views.

So the question is: Are they missing something? Or are we?

I will add that several years ago, I created our first materialized view, for a complex query, basically introducing the concept. The DBA's were completely unfamiliar. In the ensuing years, and upgrades, there have been no problems with it.

Thank you for your kind attention