Tried to create a materialized view on a schema I did not own, which produced permission errors. Solution turned out to be to grant the owner GLOBAL QUERY REWRITE. The solution was found here http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm#i2162987
Other problem was that even thought a fairly complicated query materialized view created OK, (It consisted of two inline views) when the optimizer tried to use it via 'query rewrite' the query bombed out with ora-00600 [] [] [] etc.
Solution turned out to be to create two separate materialized views.
The optimizer then used the two materialized views with no problem and the query run time went from 45-60 seconds to instant! No rewriting of the query. Result.
Finally, if the mat view is created on a prebuilt table the user creating the view must have 'select ....with grant option' on the 'prebuilt' table.
Tuesday, 19 February 2008
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment