Hi,
We have a huge report which usually runs in 30 mins.
To finetune the same Oracle Materialized Views came into picture, after which report run time came down to 10 seconds ( as everthing in MV's is pre-computed).
This is in development, when we moved to testing environment, we are again facing performance issues.
DBA is saying that MV's are present in Testing environment as well...but here the report is again taking 30 mins to give output.
Can anyone suggest .....
Thanks in advance,
Joys
Check where your package is pointed to. It's possible that the package in testing environment is actually pointed to Production where you don't have the MV.
Also check that the user in test has the same privileges granted as the user in dev. On Oracle a user must be granted the query rewrite privilege in order to be able to take advantage of automatically rewriting queries to materialized views. Not sure what DB you're using but it could have something similar.