Oracle Create Materialized View Script - Takes 12 minutes in DEV Schema, 5+ days in QA Schema

Question

I have a materialized view in my Oracle 10g database that I need to rebuild. So I'm doing something like this...

drop materialized view ViewName;

CREATE MATERIALIZED VIEW ViewName
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS 
SELECT ....
FROM ....

This creation of the materialized view takes 12 minutes to run in our DEV schema and at one time ran 5+ days in our QA schema without completing. There are no locks on the tables when running the script in the QA schema. Both of the schemas arre also on the same table space. The DB definition is exactly the same.

Any ideas on why it is taking so long to execute the DDL statement to create the MV in our QA schema? Or any ideas what's preventing it from being executed?

Thanks

Answer 1

Start by focusing on the query used by the materialized view. Examine why the query is running slow and you will solve why the materialized view creation is running slow. If this is the case it may require another question on the query itself. In the unlikely event that the queries run in the same amount of time, let us know.