Hi!
My database has 2 schemas. On SCHEMA_ONE I have a view that runs in 3 seconds to return 420.000 rows.
In SCHEMA_TWO I have a materialized view, that I always drop and recreate like this:

CREATE MATERIALIZED VIEW my_mat_view_name
REFRESH COMPLETE
AS
SELECT * FROM SCHEMA_ONE.my_view_name;


The script above needs 10:30 minutes to complete.
If I just create a table like below, it takes 1 second.
CREATE TABLE my_table_name
AS
SELECT * FROM SCHEMA_ONE.my_view_name


Why do I have suck a big difference between the 2 scripts? I would like to make the matview script to run as fast as the second script.

--
-- my sample table (you would use your real table)
--
SQL> create table t as select d.* from dba_objects d,
  2    ( select 1 from dual connect by level <= 20 ) ;

Table created.

SQL>
SQL> exec dbms_monitor.session_trace_enable(waits=>true);

PL/SQL procedure successfully completed.

SQL>
SQL> CREATE MATERIALIZED VIEW my_mat_view_name
  2  REFRESH COMPLETE
  3  AS
  4  SELECT * FROM mcdonac.t;

Materialized view created.

SQL>
SQL> exec dbms_monitor.session_trace_disable;

PL/SQL procedure successfully completed.